Ordenação em arquivo texto é possivel?

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
daniel.mendonça
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 06 Mar 2008 2:39 pm
Localização: Mococa-SP
Contato:
Daniel Mendonça

Eu tenho uma carga de dados que deverá ser feita a partir de um arquivo de texto que possui um padrão de layout, porém os dados não estão ordenados.

Exemplo de arquivo:

Selecionar tudo

02000000016IMPOR1000010000
03000000018BLIND1000013000
01000000016DEMAI1000010000
05000000018IMPSV1000010000
04000000018DEMVB1000010000
...
Layout do arquivo:

Selecionar tudo

COD    NUMBER(2);
DEMAND NUMBER(9);
TIPO   VARCHAR2(5);
TEMPPR NUMBER(5);
FULLPR NUMBER(5);
Duvidas:
* Utilizando o UTL_FILE é possível ordenar os campos? Por exemplo pelo campo de código ‘COD’ que não esta na seqüência correta...

* È possível alterar uma linha especifica sem ter que carregar todo o arquivo?

A versão do banco é a Oracle 9i...
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother,
Ordenar o txt acho q não tem como não, à não ser re-escrevendo o mesmo.
Mas, o que você pode fazer é o seguinte:
1)Jogar o conteúdo do arquivo texto numa tabela temporária;
2)Ler esta tabela ordenando-a em tempo de utilização;
O processo irá ficar um pouco mais demorado, devido à esta leitura e gravação e, posterior utilização.
qualquer coisa, manda ai.
Julian Campagnoli
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 82
Registrado em: Ter, 26 Fev 2008 11:33 am
Localização: MG e SP

Aproveitando a pergunta do Daniel,

Poderia também ser feita essa ordenação utilizando array ...

alguém saberia me dizer qual teria melhor performance "utilizar tabelas" ou "array", para fazer a ordenação ??????


Abraço

Julian A. Campagnoli
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 264
Registrado em: Dom, 19 Ago 2007 8:18 pm
Localização: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

bem, não sei direito qual tem a melhor performace
mais pensando bem... um array usaria a memoria do computador, enquanto em uma tabela você iria usar apenas o banco...

então no caso o array seria mais rápido, mais nem sempre é a melhor solução...
eu usaria uma temp table mesmo, porque eu acho mais fácil trabalhar com o select do que com arrays =D

no caso a diferença não vai ser mta coisa não...
mais quem puder esclacer melhor pra gente....


:wink:
daniel.mendonça
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 06 Mar 2008 2:39 pm
Localização: Mococa-SP
Contato:
Daniel Mendonça

Ola a todos,

Eu optei por jogar o conteúdo do arquivo texto numa tabela temporária...
Esse procedimento será executado por um período de tempo razoável... Gostaria de saber se durante a execução da procedure que vai popular essa tabela é possível criar e depois excluir a tabela temporária criada?
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Boa noite brother,

Você consegue, via PL/SQL dropar esta tabela, com o Execute Imemdiate.
Faça um teste:

Selecionar tudo

BEGIN
  EXECUTE IMMEDIATE('drop table sua_tabela');
END;

Só verifique a real necessidade de se dropar e recriar sempre esta tabela, no teu processo, pois, se for uma temp, estará disponível apenas na tua sessão e, também pode optar por deletar as linhas sempre que commitar.

Fica ai a dica.

qualquer coisa, manda ai.
margaridi
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 08 Abr 2008 11:09 am
Localização: Curitiba - PR
Contato:
----------------------------
Margaridi, Marco Aurélio
www.margaridi.com

Daniel,

em primeiro lugar, por que você precisa classificar os dados para carga?

O único motivo que eu vejo para você fazer isso, seria se a sua carga for realmente muito grande de modo que você precise fazer a carga via SQL Loader no modo direto (lembrando, o SQL Loader no modo convencional faz um INSERT para cada linha, no modo direto ele faz carga de bloco de dados, exigindo que os dados estejam classificados pela chave primária já existente no arquivo de carga), uma vez que esta abordagem reduz o tempo de carga em 60 a 90%.

Supondo que você tenha algum outro motivo para isso, não justifica onerar o banco de dados com este processamento pesado, uma vez que você tem ferramentas a nível de sistema operacional, específica para isso:

Se você esta num ambiente Windows, pode criar um arquivo bat para executar o comando de SORT, conforme abaixo:

sort /+[Pos_Ini] /M [KBytes] [Arquivo] /O [Arquivo Saida]

Exemplo:
sort /+7 /M 10240 Data.txt /O DataClassificado.txt

Classifica o arquivo Data.txt a partir da coluna 7 gerando o arquivo de saída como DataClassificado.txt, e reservando 10M de memória para a classificação.

Se você precisa classificar por mais de uma coluna, pode fazer várias chamadas, por exemplo:

Para classificar pelas colunas 23 a 30 depois por 11 a 20 e por fim por 7 a 10, faça 3 chamadas, na ordem inversa de classificação

sort /+7 /M 10240 Data.txt /O Data1.srt
sort /+11 /M 10240 Data1.srt /O Data2.srt
sort /+23 /M 10240 Data2.srt /O DataClassificado.txt

No UNIX, o comando SORT permite definir "n" posições de classificação em um único comando. Se seu ambiente for UNIX, me avise que procurarei a sintaxe do comando que eu não me lembro muito bem.
daniel.mendonça
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 06 Mar 2008 2:39 pm
Localização: Mococa-SP
Contato:
Daniel Mendonça

O que ocorre é o seguinte:
Essa é uma migração de dados para Oracle... Eu tenho um arquivo de texto com mais de 2GB e uma tabela Oracle com basicamente os mesmos campos do arquivo texto a não ser por um superdescritor.

Esse superdescritor forma 9 campos da tabela e são eles que devem ser classificados. Só que para inserir esses dados do arquivo de texto na tabela Oracle é necessário saber se foram ou não alterados em relação aos campos do superdescritor que já estão na tabela Oracle e realizar alguns tratamentos nos dados. Depois todos os dados voltam tratados para a tabela Oracle.

Essa migração não ira ocorrer rapidamente, por isso há uma necessidade continua de executar esse processo que representa a atualização da tabela Oracle até que todos os outros processos migrem para uma nova versão.

Não sei quanto às ferramentas a nível de sistema operacional podem ser úteis nesse caso, por que tudo é executado remotamente por aplicações (O banco de teste esta em um ambiente UNIX) e os arquivos só podem ser acessados por aplicações do banco.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Lembrando que a partir do oracle 9i existe o conceito de EXTERNAL TABLES, que dá pra fazer SELECT direto num arquivo txt.
Abaixo deixo um link muito bom sobre isso:
http://www.oracle-base.com/articles/9i/ ... bles9i.php

Selecionar tudo

    CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\temp\';

Selecionar tudo

    CREATE TABLE countries_ext (
      country_code      VARCHAR2(5),
      country_name      VARCHAR2(50),
      country_language  VARCHAR2(50)
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tables
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
          country_code      CHAR(5),
          country_name      CHAR(50),
          country_language  CHAR(50)
        )
      )
      LOCATION ('Countries1.txt','Countries2.txt')
    )
    PARALLEL 5
    REJECT LIMIT UNLIMITED;

Selecionar tudo

    SQL> SELECT *
      2  FROM   countries_ext
      3  ORDER BY country_name;

    COUNT COUNTRY_NAME                 COUNTRY_LANGUAGE
    ----- ---------------------------- -----------------------------
    ENG   England                      English
    FRA   France                       French
    GER   Germany                      German
    IRE   Ireland                      English
    SCO   Scotland                     English
    USA   Unites States of America     English
    WAL   Wales                        Welsh

    7 rows selected.

    SQL>
daniel.mendonça
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 06 Mar 2008 2:39 pm
Localização: Mococa-SP
Contato:
Daniel Mendonça

A solução que mais deu certo no meu caso foi criar uma tabela externa do arquivo de texto... Ficou muito mais rápido que UTL_file.

Selecionar tudo

DECLARE

   -- cria o diretório de acordo com o parametro de entrada
   DIR_PAREMETRO_ENT VARCHAR2(90) :=  '/tmp/proc';
   DIR               VARCHAR2(90) :=  'CREATE OR REPLACE DIRECTORY proc_dir AS '''||DIR_PAREMETRO_ENT||'''' ;

BEGIN
   
   --execura o comando SQL dentro da variavel DIR
   EXECUTE IMMEDIATE(DIR);
   
   -- cria a tabela baseada no layout do arquivo de texto
   EXECUTE IMMEDIATE(
      'CREATE TABLE ORA_TEST (
       CD_CAMPO_1   VARCHAR2(9 BYTE), 
       CD_CAMPO_2   VARCHAR2(9 BYTE), 
       CD_CAMPO_3   VARCHAR2(9 BYTE),  
       CD_CAMPO_4   VARCHAR2(9 BYTE),  
       CD_CAMPO_5   VARCHAR2(9 BYTE),  
       CD_CAMPO_6   VARCHAR2(9 BYTE),  
       CD_CAMPO_7   VARCHAR2(9 BYTE),  
       CD_CAMPO_8   VARCHAR2(9 BYTE)   
       ) organization external (
       type oracle_loader
       default directory proc_dir -- definição do diretório usado
       access parameters (
       records delimited by newline
       fields (
       CD_COBERTURA         position(1:5)     CHAR(5),
       CD_CAMPO_1   POSITION(6:14)    CHAR(9), 
       CD_CAMPO_2   POSITION(15:23)   CHAR(9), 
       CD_CAMPO_3   POSITION(24:32)   CHAR(9), 
       CD_CAMPO_4   POSITION(33:41)   CHAR(9), 
       CD_CAMPO_5   POSITION(42:50)   CHAR(9), 
       CD_CAMPO_6   POSITION(51:59)   CHAR(9), 
       CD_CAMPO_7   POSITION(60:68)   CHAR(9), 
       CD_CAMPO_8   POSITION(69:77)   CHAR(9)
       ))
       location (''file1.txt''))' ); -- Arquivo

END;
Depois de processar os dados e inserir os resultados obitidos do arquivo texto para a tabela oracle definitiva, foi só excluir a tabela externa criada.

Selecionar tudo

BEGIN
   -- EXECUTE IMMEDIATE('DROP TABLE ORA_TEST');
END;
Em questão de desempenho, não testei ainda por que são muitas linhas... Assim que concluir essa procedure eu posto os resultados...

Obrigado a todos que ajudaram...
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 264
Registrado em: Dom, 19 Ago 2007 8:18 pm
Localização: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

Caramba.... to chocado com isso vei =D
mais como será qui o banco interpreta isso ??
ele vai direto no arquivo mesmo ??
e se tiver uma imagem ??? dá pra fazer isso com BLOB ??
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Acho que é só com texto mesmo.
Isso é uma feature bem velha ate! EU lembro que saiu isso numa Oracle Magazine há muitos anos!
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 264
Registrado em: Dom, 19 Ago 2007 8:18 pm
Localização: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

galera
eu estou tentando fazer um teste aqui...
e está retornando este erro:

Selecionar tudo

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file COUNTRIES_EXT_1260_1200.log
OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_LOADER", line 19
alguém sabe o porque ??
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother,

O usuário que você está logando, tem permissão de WRITE no diretório criado pelo System?

Outra coisa, no diretório que o System criou, existe o arquivo: COUNTRIES_EXT_1260_1200.log ?

Caso exista, o nome é identico ao qual você passou na criação da external table?
daniel.mendonça
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 06 Mar 2008 2:39 pm
Localização: Mococa-SP
Contato:
Daniel Mendonça

Ola a todos,
Depois de um tempo testando, cheguei a uma solução por completo. Qualquer duvida é só postar...

No meu caso, com todo o tratamento e validações necessarias, o procedimento com external tables rendeu 35% mais em comparação com o UTL_file nas 125.000 linhas tratadas.

O procedimento final:

Selecionar tudo

DECLARE
    
    ----------------------------------------------------------------------------
    
    /* É necessario as GRANTS para CREATE ANY DIRECTORY, DROP ANY DIRECTORY,  
    ** CREATE TABLE e DROP TABLE */
    
    FOLDER           VARCHAR2(90)    := '/tmp';
    READ_FILE        VARCHAR2(90)    := 'teste_external.txt';
    
    ----------------------------------------------------------------------------
    
    DIR              VARCHAR2(90)    := 'CREATE OR REPLACE DIRECTORY external_dir AS '''||FOLDER||'''' ;
    
    DROP_TABELA      VARCHAR2(90)    := 'DROP TABLE TESTE_EXTERNAL';
    
    SQL_TABELA       VARCHAR2(4500)  := 'CREATE TABLE TESTE_EXTERNAL (
                                         CD_IDENTIFICACAO   VARCHAR2(10 BYTE),
                                         CONTEUDO_CAMPO_1   VARCHAR2(10 BYTE), 
                                         CONTEUDO_CAMPO_2   VARCHAR2(10 BYTE), 
                                         CONTEUDO_CAMPO_3   VARCHAR2(10 BYTE),  
                                         CONTEUDO_CAMPO_4   VARCHAR2(10 BYTE),  
                                         CONTEUDO_CAMPO_5   VARCHAR2(10 BYTE),  
                                         CONTEUDO_CAMPO_6   VARCHAR2(10 BYTE),  
                                         CONTEUDO_CAMPO_7   VARCHAR2(10 BYTE)  
                                         ) organization external (
                                         type oracle_loader
                                         default directory external_dir
                                         access parameters (
                                         records delimited by newline
                                         fields (
                                         CD_IDENTIFICACAO   POSITION(01:10)   CHAR(10),
                                         CONTEUDO_CAMPO_1   POSITION(11:20)   CHAR(10), 
                                         CONTEUDO_CAMPO_2   POSITION(21:30)   CHAR(10), 
                                         CONTEUDO_CAMPO_3   POSITION(31:40)   CHAR(10), 
                                         CONTEUDO_CAMPO_4   POSITION(41:50)   CHAR(10), 
                                         CONTEUDO_CAMPO_5   POSITION(51:60)   CHAR(10), 
                                         CONTEUDO_CAMPO_6   POSITION(61:70)   CHAR(10), 
                                         CONTEUDO_CAMPO_7   POSITION(71:80)   CHAR(10)
                                         ))
                                         location ('''||READ_FILE||'''))';
                                         
    ----------------------------------------------------------------------------
    
    TYPE CUR_TYPE IS REF CURSOR;
    C_TESTE_EXTERNAL CUR_TYPE;
    
    CD_IDENTIFICACAO   VARCHAR2(10 BYTE);
    CONTEUDO_CAMPO_1   VARCHAR2(10 BYTE);
    CONTEUDO_CAMPO_2   VARCHAR2(10 BYTE);
    CONTEUDO_CAMPO_3   VARCHAR2(10 BYTE);
    CONTEUDO_CAMPO_4   VARCHAR2(10 BYTE);
    CONTEUDO_CAMPO_5   VARCHAR2(10 BYTE);
    CONTEUDO_CAMPO_6   VARCHAR2(10 BYTE);
    CONTEUDO_CAMPO_7   VARCHAR2(10 BYTE);
    
    SQL_STR          VARCHAR2(90);
    
    ----------------------------------------------------------------------------
    
    CONTADOR NUMBER := 0;
    
    C_NOTFOUND BOOLEAN := FALSE;
    
    ----------------------------------------------------------------------------
 
BEGIN
    
    ----------------------------------------------------------------------------
    
    /* Execução dos comandos necessarios para a criação da tabela e do diretório */
    EXECUTE IMMEDIATE(DIR);
    DBMS_OUTPUT.PUT_LINE('Criou diretório');
    
    EXECUTE IMMEDIATE(SQL_TABELA);
    DBMS_OUTPUT.PUT_LINE('Criou tabela');
    ----------------------------------------------------------------------------
    
    /* Definindo os parametros necessarios para as consultas nas tabelas geradas */
    SQL_STR := 'SELECT * FROM TESTE_EXTERNAL';
    
    OPEN C_TESTE_EXTERNAL FOR SQL_STR;    
       LOOP
       -------------------------------------------------------------------------
       FETCH C_TESTE_EXTERNAL INTO CD_IDENTIFICACAO, 
                                   CONTEUDO_CAMPO_1, 
                                   CONTEUDO_CAMPO_2,
                                   CONTEUDO_CAMPO_3,
                                   CONTEUDO_CAMPO_4,
                                   CONTEUDO_CAMPO_5,
                                   CONTEUDO_CAMPO_6,
                                   CONTEUDO_CAMPO_7;
                                   
       EXIT WHEN C_TESTE_EXTERNAL%NOTFOUND;
       -------------------------------------------------------------------------
          
          /* Tratamento do arquivo texto como for necessario. */
          CONTADOR := CONTADOR + 1;
          
       -------------------------------------------------------------------------
       END LOOP;
    CLOSE C_TESTE_EXTERNAL; 
    
    DBMS_OUTPUT.PUT_LINE('Terminou');
    
    EXECUTE IMMEDIATE(DROP_TABELA);
    
    DBMS_OUTPUT.PUT_LINE('Deletou a tabela');
    
    DBMS_OUTPUT.PUT_LINE(CONTADOR);
    
    ----------------------------------------------------------------------------
 
EXCEPTION
    WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20001,'Erro.');
 
END;
Exemplo do arquiv texto (teste_external.txt):

Selecionar tudo

0000000001AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG
0000000002AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG
0000000003AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG
Obrigado a todos pela ajuda...
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 11 visitantes