Pegando arquivo .txt e inserindo na tabela com varias coluna

[cleberzumba]

Pessoal estou pegando um arquivo .txt com o utilitario utl_file e querendo inserir em uma tabela. funcionou perfeitamente quando o arquivo tinha apenas uma coluna e a tabela uma coluna. agora o arquivo tem 3 colunas separadas por ';' ponto e virgula e a tabela tem 3 colunas.

como eu faço?...olha o meu script de execução a baixo:

Selecionar tudo

--- Abre arquivo txt para leitura
v_file_handle:= Utl_File.FOpen('C:\pasta1','teste.txt','r'); 
    
-- Loop para Ler o Arquivo Txt
    Loop 
      Begin 
      
        -- Ler o arquivo
        Utl_File.Get_Line(v_file_handle, vLinha); 

        -- grava o arquivo na tabela
        Insert Into teste (teste,CABECALHO,RODAPE) 
        Values (vLinha,vLinha,vLinha); 

        commit;
     end;
   End Loop; 
   Utl_File.FClose(v_file_handle);

[diegolenhardt]

Cara, eu faria um while, pegando sempre do inicio da string até a posição onde encontra o ;, com INSTR('IUAHUIA;UIAHUA', ';'), vai retornar a posicao, e ai você joga isso pra uma variavel, e depois retira o conteudo já lido, e continua o while, ate que o instr do while não encontre mais ';',
algo como o codigo abaixo,

Selecionar tudo

while (instr(arquivos,';') > 0) loop
    vet_arquivos(vet_arquivos.count + 1) := replace(replace(trim(substr(arquivos,1,instr(arquivos,';')-1)),' '),chr(10));
    arquivos := substr(arquivos,instr(arquivos,';')+1,length(arquivos));
end loop;

Ou percorre a string com for i in 1..length(vLinha) loop e testa se encontrou, acho mais facil o while, só usa INSTR e SUBSTR,
qualquer coisa posta o conteudo ai do arquivo,

[cleberzumba]

o insert ficaria dentro do while?

[cleberzumba]

essa variavel vet_arquivos será declarada de que tipo?

[diegolenhardt]

no meu caso vet_arquivos é um vetor, o que eu faco nesse procedimento é percorrer uma string que tem varios arquivos separados por ponto e virgula assim:

Selecionar tudo

declare
  type tp_arquivos is table of varchar2(255) index by binary_integer;
  vet_arquivos tp_arquivos;

arquivos := ' bkpf-arch-D-XXX-20091106-NB-11022.bkp;
                    bkpf-arch-D-XXX-20091112-NB-11046.bkp;
                    bkpi0-data-D-XXX-20091106-NB-11019.bkp;
                    bkpi0-data-D-XXX20091106-NB-11020.bkp;';

  while (instr(arquivos,';') > 0) loop
    vet_arquivos(vet_arquivos.count + 1) := replace(replace(trim(substr(arquivos,1,instr(arquivos,';')-1)),' '),chr(10));
    arquivos := substr(arquivos,instr(arquivos,';')+1,length(arquivos));
  end loop;

O que eu faco nesse caso é jogar para o vet_arquivos, em cada posicao do vetor eu jogo um nome de arquivo, que esta separado por ';' na string acima, no seu caso ao invés de jogar no vetor, que tambem poderia ser, você pode ir armazenando em var1, var2, var3.

e o insert ficaria por fora do while, nesse caso seria muito mais simples você usar o sqlloader, qualquer cooisa diga ai..

[cleberzumba]

brother. estou com a corda no pescoço aqui, tentei falar para fazer via sqlloader, mas eles não querem. se você quiser eu te passo a minha PROC e a gente faz juntos. ela não é grande é pequena, bem pequena. e eu te explico todo processo dela.

[diegolenhardt]

manda aí a procedure e um exemplo de um arquivo que você tem,

só que agora to com um rolo aqui, manda ai pra eu ver

[cleberzumba]

ok.
Os arquivos ficam armazenados em um determinado diretorio. essa procedure vai nesse diretotio e pega um arquivo de cada vez todo dia 30 de cada mês

aqui o conteúdo do arquivo. lembrando que são tres arquivos.

Selecionar tudo

"ESTOQUE DE DÍVIDAS";"001 - BB";"SP";"FUNCAFE CPR";"AGRÍCOLA";"FUNCAFÉ";6;1166502,91;2,00;65911,56;0,00;0,00;281162,54;"PRÓPRIO BB";"09/11/2009";"30/10/2009"
"ESTOQUE DE DÍVIDAS";"001 - BB";"DF";"FCO RISCO FUNDO";"AGRÍCOLA";"FCO";16;594398,03;0,00;0,00;0,00;0,00;99425,25;"TERCEIROS";"09/11/2009";"30/10/2009"
"ESTOQUE DE DÍVIDAS";"001 - BB";"GO";"FCO RISCO FUNDO";"AGRÍCOLA";"FCO";357;15963570,54;132,00;5053227,98;59,00;1868867,31;1643524,02;"TERCEIROS";"09/11/2009";"30/10/2009"
"ESTOQUE DE DÍVIDAS";"001 - BB";"MS";"FCO RISCO FUNDO";"AGRÍCOLA";"FCO";291;12227602,41;125,00;2411899,96;52,00;786835,61;1885475,22;"TERCEIROS";"09/11/2009";"30/10/2009"
"ESTOQUE DE DÍVIDAS";"001 - BB";"muito";"FCO RISCO FUNDO";"AGRÍCOLA";"FCO";601;27891759,00;254,00;13010828,58;139,00;4475617,57;2442733,04;"TERCEIROS";"09/11/2009";"30/10/2009"

aqui a proc:

Selecionar tudo

PROCEDURE SIGER_LER_ARQUIVO_PC IS

  vLinha               Varchar2(2000); 
  v_file_handle        Utl_File.File_Type; 
  

  
Begin 

    --- Abre arquivo txt para leitura
     v_file_handle:= Utl_File.FOpen('C:\Cleber','teste.txt','r'); 
    -- o nome do arquivo tem que vir dinamico. cada mês é um nome diferente porque vem pela data do mês.
    
    -- Loop para Ler o Arquivo Txt
    Loop 
      Begin 

      
        -- Ler o arquivo
        Utl_File.Get_Line(v_file_handle, vLinha); 


        -- grava o arquivo na tabela
        Insert Into T_BB_Ativa (CD_CLASSIFICACAO,
                  CD_BANCO,
                  SG_UF,
                  CD_MODALIDADE_ATIVA,
                  CD_FINALIDADE,
                  CD_FONTE_RECURSO,
                  QT_CONTRATOS,
                  VL_SALDO_DEVEDOR,
                  QT_CONTRATOS_VENCIDOS,
                  VL_SALDO_DEVEDOR_VENCIDO,
                  QT_VENC_MAIS_90_DIAS,
                  VL_SALDO_DEVEDOR_VENC_90_DIAS,
                  VL_RETORNO_PREVISTO,
                  CD_RISCO,
                  DT_BASE,
                  DH_CARGA,
                  VL_RETORNO_VENCIDO) 
        Values (.....);  --como fazer aqui?

        commit;
        
      Exception 
           When No_Data_Found Then 
                -- arquivo não encontrado 
                exit;
           When Value_Error Then 
                -- linha do arquivo muito grande para armazenar.
                Raise_Application_Error(-20214, 'Value_Error '||sqlerrm);  
           When Utl_File.Invalid_FileHandle Then 
                -- arquivo não válido
                Raise_Application_Error(-20215, 'Invalid_filehandle '||sqlerrm); 
           When Utl_File.Invalid_Operation Then 
                -- arquivo não está aberto para leitura
                Raise_Application_Error(-20216, 'invalid_operation '||sqlerrm); 
           When Utl_File.Read_Error Then 
                -- erro durante a leitura
                Raise_Application_Error(-20217, 'Read_Error '||sqlerrm);
      end; 
              
      End Loop; 
      Utl_File.FClose(v_file_handle);         

end;

[diegolenhardt]

Blza cara, fica frauz que damos um jeito, antes preciso acabar um esquema aqui,

[cleberzumba]

beleza brother....vai dá tudo certo!

[diegolenhardt]

vai precisar de um TYPE,

uma variavel tipada com:

Selecionar tudo

CD_CLASSIFICACAO,
CD_BANCO,
SG_UF,
CD_MODALIDADE_ATIVA,
CD_FINALIDADE,
CD_FONTE_RECURSO,
QT_CONTRATOS,
VL_SALDO_DEVEDOR,
QT_CONTRATOS_VENCIDOS,
VL_SALDO_DEVEDOR_VENCIDO,
QT_VENC_MAIS_90_DIAS,
VL_SALDO_DEVEDOR_VENC_90_DIAS,
VL_RETORNO_PREVISTO,
CD_RISCO,
DT_BASE,
DH_CARGA,
VL_RETORNO_VENCIDO

para que no nosso while a gente trate o ; e va jogando pra

Selecionar tudo

variavel(1).CD_CLASSIFICACAO := xx;

etc

[cleberzumba]

não to entendendo brother.

[cleberzumba]

Veja:

Selecionar tudo

type table is record
(campo1  varchar2(10),
.
.
.
.);

emp_record   emp_record_type;

assim?...isso para cada arquivo?...pois cada arquivo as colunas se parecem com essas q te mandei.

[cleberzumba]

ok.
creie tres TYPE assim:

Selecionar tudo

  type  arq_bb_type is record
     (CD_BANCO                                  VARCHAR2(100),
      SG_UF                                     VARCHAR2(100),
      CD_MODALIDADE_ATIVA                       VARCHAR2(100),
      CD_FINALIDADE                             VARCHAR2(100),
      CD_FONTE_RECURSO                          VARCHAR2(100),
      QT_CONTRATOS                              VARCHAR2(100),
      VL_SALDO_DEVEDOR                          VARCHAR2(100),
      QT_CONTRATOS_VENCIDOS                     VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENCIDO                  VARCHAR2(100),
      QT_VENC_MAIS_90_DIAS                      VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENC_90_DIAS             VARCHAR2(100),
      VL_RETORNO_PREVISTO                       VARCHAR2(100),
      CD_RISCO                                  VARCHAR2(100),
      DT_BASE                                   VARCHAR2(100),
      DH_CARGA                                  VARCHAR2(100),
      VL_RETORNO_VENCIDO                        VARCHAR2(100),
      CD_CLASSIFICACAO                          VARCHAR2(100));
      
  arq_bb_record              arq_bb_type;
  
  
  type  arq_bndes_type is record
     (CD_BANCO                                  VARCHAR2(100),
      SG_UF                                     VARCHAR2(100),
      CD_MODALIDADE_ATIVA                       VARCHAR2(100),
      CD_FINALIDADE                             VARCHAR2(100),
      CD_PROGRAMAS_BNDES                        VARCHAR2(100),
      QT_CONTRATOS                              VARCHAR2(100),
      VL_SALDO_DEVEDOR                          VARCHAR2(100),
      QT_CONTRATOS_VENCIDOS                     VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENCIDO                  VARCHAR2(100),
      QT_VENC_MAIS_90_DIAS                      VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENC_90_DIAS             VARCHAR2(100),
      VL_RETORNO_PREVISTO                       VARCHAR2(100),
      CD_RISCO                                  VARCHAR2(100),
      DT_BASE                                   VARCHAR2(100),
      DH_CARGA                                  VARCHAR2(100),
      VL_RETORNO_VENCIDO                        VARCHAR2(100),
      CD_CLASSIFICACAO                          VARCHAR2(100));
      
  arq_bndes_record       arq_bndes_type;
  
  
  type  arq_divida_type is record
     (CD_BANCO                                  VARCHAR2(100),
      SG_UF                                     VARCHAR2(100),
      CD_MODALIDADE_DIVIDA                      VARCHAR2(100),
      CD_FINALIDADE                             VARCHAR2(100),
      CD_FONTE_RECURSO                          VARCHAR2(100),
      QT_CONTRATOS                              VARCHAR2(100),
      VL_SALDO_DEVEDOR                          VARCHAR2(100),
      QT_CONTRATOS_VENCIDOS                     VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENCIDO                  VARCHAR2(100),
      QT_VENC_MAIS_90_DIAS                      VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENC_90_DIAS             VARCHAR2(100),
      VL_RETORNO_PREVISTO                       VARCHAR2(100),
      CD_RISCO                                  VARCHAR2(100),
      DT_BASE                                   VARCHAR2(100),
      DH_CARGA                                  VARCHAR2(100),
      VL_RETORNO_VENCIDO                        VARCHAR2(100),
      CD_CLASSIFICACAO                          VARCHAR2(100));
      
  arq_divida_record   arq_divida_type;

[diegolenhardt]

Cara tava com uns problemas aqui, um TYPE só está bom, eu vi que são todos iguais, é isso aí, vou escrever um codigo aqui vamos ver...
já te retorno

[cleberzumba]

são todos iguais não...tem diferença e são tres arquivos diferente.

[diegolenhardt]

Olha só cara, a cada volta do while vai retornar um campo, ate que não exista mais ';' na variavel auxiliar, é a ideia que tive, acredito que vai funcionar, =)

Selecionar tudo

Declare
  Vlinha        Varchar2(2000);
  v_File_Handle Utl_File.File_Type;
  vLinhaAux     Varchar2(2000);
  vCont         Number := 1;
  valor         Varchar2(500); 
  
  type arq_bb_type is record
    (CD_BANCO VARCHAR2(100),
    SG_UF VARCHAR2(100),
    CD_MODALIDADE_ATIVA VARCHAR2(100),
    CD_FINALIDADE VARCHAR2(100),
    CD_FONTE_RECURSO VARCHAR2(100),
    QT_CONTRATOS VARCHAR2(100),
    VL_SALDO_DEVEDOR VARCHAR2(100),
    QT_CONTRATOS_VENCIDOS VARCHAR2(100),
    VL_SALDO_DEVEDOR_VENCIDO VARCHAR2(100),
    QT_VENC_MAIS_90_DIAS VARCHAR2(100),
    VL_SALDO_DEVEDOR_VENC_90_DIAS VARCHAR2(100),
    VL_RETORNO_PREVISTO VARCHAR2(100),
    CD_RISCO VARCHAR2(100),
    DT_BASE VARCHAR2(100),
    DH_CARGA VARCHAR2(100),
    VL_RETORNO_VENCIDO VARCHAR2(100),
    CD_CLASSIFICACAO VARCHAR2(100));

  linha arq_bb_type; 
  
  
Begin

  --- Abre arquivo txt para leitura
  --v_File_Handle := Utl_File.Fopen('C:\Cleber', 'teste.txt', 'r');
  -- o nome do arquivo tem que vir dinamico. cada mês é um nome diferente porque vem pela data do mês.

  -- Loop para Ler o Arquivo Txt
  --Loop
    Begin
      linha.delete; --sempre limpa o vetor
      -- Ler o arquivo
      --Utl_File.Get_Line(v_File_Handle, Vlinha);
      
      vLinha := '"ESTOQUE DE DÍVIDAS";"001 - BB";"SP";"FUNCAFE CPR";"AGRÍCOLA";"FUNCAFÉ";6;1166502,91;2,00;65911,56;0,00;0,00;281162,54;"PRÓPRIO BB";"09/11/2009";"30/10/2009"';

      vLinhaAux := vLinha;
      
      while (instr(vLinhaAux,';') > 0) loop
        valor := replace(replace(trim(substr(vLinhaAux,1,instr(vLinhaAux,';')-1)),' '),chr(10));
        
        if (vCont = 1) Then
          linha(1).CD_BANCO := valor;   
        elsif (vCont = 2) then
          linha(1).SG_UF := valor;   
        elsif (vCont = 3) then
          linha(1).CD_MODALIDADE_ATIVA := valor;   --aqui você trata todos os seus campos
        end if;

        vLinhaAux := substr(vLinhaAux,instr(vLinhaAux,';')+1,length(vLinhaAux));
        vCont := vCont + 1;
      end loop;
      
      -- grava o arquivo na tabela
      Insert Into t_Bb_Ativa
        (Cd_Classificacao,
         Cd_Banco,
         Sg_Uf,
         Cd_Modalidade_Ativa,
         Cd_Finalidade,
         Cd_Fonte_Recurso,
         Qt_Contratos,
         Vl_Saldo_Devedor,
         Qt_Contratos_Vencidos,
         Vl_Saldo_Devedor_Vencido,
         Qt_Venc_Mais_90_Dias,
         Vl_Saldo_Devedor_Venc_90_Dias,
         Vl_Retorno_Previsto,
         Cd_Risco,
         Dt_Base,
         Dh_Carga,
         Vl_Retorno_Vencido)
      Values
        (linha(1).cd_banco, linha(1).Sg_Uf); --como fazer aqui? etc
    
      Commit;
    
    Exception
      When No_Data_Found Then
        -- arquivo não encontrado
        Exit;
      When Value_Error Then
        -- linha do arquivo muito grande para armazenar.
        Raise_Application_Error(-20214, 'Value_Error ' || Sqlerrm);
      When Utl_File.Invalid_Filehandle Then
        -- arquivo não válido
        Raise_Application_Error(-20215, 'Invalid_filehandle ' || Sqlerrm);
      When Utl_File.Invalid_Operation Then
        -- arquivo não está aberto para leitura
        Raise_Application_Error(-20216, 'invalid_operation ' || Sqlerrm);
      When Utl_File.Read_Error Then
        -- erro durante a leitura
        Raise_Application_Error(-20217, 'Read_Error ' || Sqlerrm);
    End;
  
  End Loop;
  Utl_File.Fclose(v_File_Handle);

End;

[cleberzumba]

TEMOS QUE DECLARAR O LINHA, MAS não TEMOS CURSOR PARA ISSO, POIS EE ESTÁ ASSIM:

linha.delete

[cleberzumba]

está dando erro no campo "linha"
veja:

Selecionar tudo

    Vlinha Varchar2(2000); 
    v_File_Handle Utl_File.File_Type; 
    vLinhaAux Varchar2(2000); 
    vCont Number := 1; 
    valor Varchar2(500);   

  type  arq_bb_type is record
     (CD_BANCO                                  VARCHAR2(100),
      SG_UF                                     VARCHAR2(100),
      CD_MODALIDADE_ATIVA                       VARCHAR2(100),
      CD_FINALIDADE                             VARCHAR2(100),
      CD_FONTE_RECURSO                          VARCHAR2(100),
      QT_CONTRATOS                              VARCHAR2(100),
      VL_SALDO_DEVEDOR                          VARCHAR2(100),
      QT_CONTRATOS_VENCIDOS                     VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENCIDO                  VARCHAR2(100),
      QT_VENC_MAIS_90_DIAS                      VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENC_90_DIAS             VARCHAR2(100),
      VL_RETORNO_PREVISTO                       VARCHAR2(100),
      CD_RISCO                                  VARCHAR2(100),
      DT_BASE                                   VARCHAR2(100),
      DH_CARGA                                  VARCHAR2(100),
      VL_RETORNO_VENCIDO                        VARCHAR2(100),
      CD_CLASSIFICACAO                          VARCHAR2(100));
      
  linha              arq_bb_type;
  
  
  
  
begin
--- Abre arquivo txt para leitura 
--v_File_Handle := Utl_File.Fopen('C:\Cleber', 'teste.txt', 'r'); 
-- o nome do arquivo tem que vir dinamico. cada mês é um nome diferente porque vem pela data do mês. 

-- Loop para Ler o Arquivo Txt 
--Loop 
     Begin 
        --linha.delete; --sempre limpa o vetor 
        -- Ler o arquivo 
        --Utl_File.Get_Line(v_File_Handle, Vlinha); 
        
        vLinha := '"ESTOQUE DE DÍVIDAS";"001 - BB";"SP";"FUNCAFE CPR";"AGRÍCOLA";"FUNCAFÉ";6;1166502,91;2,00;65911,56;0,00;0,00;281162,54;"PRÓPRIO BB";"09/11/2009";"30/10/2009"'; 
        
        vLinhaAux := vLinha; 
        
        while (instr(vLinhaAux,';') > 0) loop 
            valor := replace(replace(trim(substr(vLinhaAux,1,instr(vLinhaAux,';')-1)),' '),chr(10)); 
            
            if (vCont = 1) Then 
               linha(1).CD_BANCO := valor; 
            elsif (vCont = 2) then 
               linha(1).SG_UF := valor; 
            elsif (vCont = 3) then 
               linha(1).CD_MODALIDADE_ATIVA := valor; --aqui você trata todos os seus campos 
            elsif (vCont = 4) then 
               linha(1).CD_FINALIDADE := valor;         
            elsif (vCont = 5) then 
               linha(1).CD_FONTE_RECURSO := valor;         
            elsif (vCont = 6) then 
               linha(1).QT_CONTRATOS := valor;         
            elsif (vCont = 7) then 
               linha(1).VL_SALDO_DEVEDOR := valor;         
            elsif (vCont = 8) then 
               linha(1).QT_CONTRATOS_VENCIDOS := valor;         
            elsif (vCont = 9) then 
               linha(1).VL_SALDO_DEVEDOR_VENCIDO := valor;         
            elsif (vCont = 10) then 
               linha(1).QT_VENC_MAIS_90_DIAS := valor;         
            elsif (vCont = 11) then 
               linha(1).VL_SALDO_DEVEDOR_VENC_90_DIAS := valor;         
            elsif (vCont = 12) then     
               linha(1).CD_RISCO := valor;                                                                                
            elsif (vCont = 12) then     
               linha(1).DT_BASE := valor;                                                                                
            elsif (vCont = 12) then     
               linha(1).DH_CARGA := valor;                                                                                
            elsif (vCont = 12) then     
               linha(1).CD_CLASSIFICACAO := valor;                                                                                
            end if; 
            
            vLinhaAux := substr(vLinhaAux,instr(vLinhaAux,';')+1,length(vLinhaAux)); 
            vCont := vCont + 1; 
            
        end loop; 
        
        -- grava o arquivo na tabela 
        Insert Into t_Bb_Ativa (Cd_Classificacao, 
                                Cd_Banco, 
                                Sg_Uf, 
                                Cd_Modalidade_Ativa, 
                                Cd_Finalidade, 
                                Cd_Fonte_Recurso, 
                                Qt_Contratos, 
                                Vl_Saldo_Devedor, 
                                Qt_Contratos_Vencidos, 
                                Vl_Saldo_Devedor_Vencido, 
                                Qt_Venc_Mais_90_Dias, 
                                Vl_Saldo_Devedor_Venc_90_Dias, 
                                Vl_Retorno_Previsto, 
                                Cd_Risco, 
                                Dt_Base, 
                                Dh_Carga, 
                                Vl_Retorno_Vencido) 
        Values (linha(1).Cd_Classificacao,
                linha(1).cd_banco, 
                linha(1).Sg_Uf,
                linha(1).Cd_Modalidade_Ativa,
                linha(1).Cd_Finalidade,
                linha(1).Cd_Fonte_Recurso,
                linha(1).Qt_Contratos,
                linha(1).Vl_Saldo_Devedor,
                linha(1).Qt_Contratos_Vencidos,
                linha(1).Vl_Saldo_Devedor_Vencido,
                linha(1).Qt_Venc_Mais_90_Dias,
                linha(1).Vl_Saldo_Devedor_Venc_90_Dias,
                linha(1).Vl_Retorno_Previsto,
                linha(1).Cd_Risco,
                linha(1).Dt_Base,
                linha(1).Dh_Carga,
                linha(1).Vl_Retorno_Vencido);
        
        Commit; 
              
        
      Exception 
           --When No_Data_Found Then 
                -- arquivo não encontrado 
               -- exit;
           When Value_Error Then 
                -- linha do arquivo muito grande para armazenar.
                Raise_Application_Error(-20214, 'Value_Error '||sqlerrm);  
           When Utl_File.Invalid_FileHandle Then 
                -- arquivo não válido
                Raise_Application_Error(-20215, 'Invalid_filehandle '||sqlerrm); 
           When Utl_File.Invalid_Operation Then 
                -- arquivo não está aberto para leitura
                Raise_Application_Error(-20216, 'invalid_operation '||sqlerrm); 
           When Utl_File.Read_Error Then 
                -- erro durante a leitura
                Raise_Application_Error(-20217, 'Read_Error '||sqlerrm);
      end; 
              
      --End Loop; 
      Utl_File.FClose(v_file_handle);         

end;

[cleberzumba]

está dando erro no campo "linha"
veja:

Selecionar tudo

begin


    Vlinha Varchar2(2000); 
    v_File_Handle Utl_File.File_Type; 
    vLinhaAux Varchar2(2000); 
    vCont Number := 1; 
    valor Varchar2(500);   

  type  arq_bb_type is record
     (CD_BANCO                                  VARCHAR2(100),
      SG_UF                                     VARCHAR2(100),
      CD_MODALIDADE_ATIVA                       VARCHAR2(100),
      CD_FINALIDADE                             VARCHAR2(100),
      CD_FONTE_RECURSO                          VARCHAR2(100),
      QT_CONTRATOS                              VARCHAR2(100),
      VL_SALDO_DEVEDOR                          VARCHAR2(100),
      QT_CONTRATOS_VENCIDOS                     VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENCIDO                  VARCHAR2(100),
      QT_VENC_MAIS_90_DIAS                      VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENC_90_DIAS             VARCHAR2(100),
      VL_RETORNO_PREVISTO                       VARCHAR2(100),
      CD_RISCO                                  VARCHAR2(100),
      DT_BASE                                   VARCHAR2(100),
      DH_CARGA                                  VARCHAR2(100),
      VL_RETORNO_VENCIDO                        VARCHAR2(100),
      CD_CLASSIFICACAO                          VARCHAR2(100));
      
  linha              arq_bb_type;
  
  
  
  
begin
--- Abre arquivo txt para leitura 
--v_File_Handle := Utl_File.Fopen('C:\Cleber', 'teste.txt', 'r'); 
-- o nome do arquivo tem que vir dinamico. cada mês é um nome diferente porque vem pela data do mês. 

-- Loop para Ler o Arquivo Txt 
--Loop 
     Begin 
        --linha.delete; --sempre limpa o vetor 
        -- Ler o arquivo 
        --Utl_File.Get_Line(v_File_Handle, Vlinha); 
        
        vLinha := '"ESTOQUE DE DÍVIDAS";"001 - BB";"SP";"FUNCAFE CPR";"AGRÍCOLA";"FUNCAFÉ";6;1166502,91;2,00;65911,56;0,00;0,00;281162,54;"PRÓPRIO BB";"09/11/2009";"30/10/2009"'; 
        
        vLinhaAux := vLinha; 
        
        while (instr(vLinhaAux,';') > 0) loop 
            valor := replace(replace(trim(substr(vLinhaAux,1,instr(vLinhaAux,';')-1)),' '),chr(10)); 
            
            if (vCont = 1) Then 
               linha(1).CD_BANCO := valor; 
            elsif (vCont = 2) then 
               linha(1).SG_UF := valor; 
            elsif (vCont = 3) then 
               linha(1).CD_MODALIDADE_ATIVA := valor; --aqui você trata todos os seus campos 
            elsif (vCont = 4) then 
               linha(1).CD_FINALIDADE := valor;         
            elsif (vCont = 5) then 
               linha(1).CD_FONTE_RECURSO := valor;         
            elsif (vCont = 6) then 
               linha(1).QT_CONTRATOS := valor;         
            elsif (vCont = 7) then 
               linha(1).VL_SALDO_DEVEDOR := valor;         
            elsif (vCont = 8) then 
               linha(1).QT_CONTRATOS_VENCIDOS := valor;         
            elsif (vCont = 9) then 
               linha(1).VL_SALDO_DEVEDOR_VENCIDO := valor;         
            elsif (vCont = 10) then 
               linha(1).QT_VENC_MAIS_90_DIAS := valor;         
            elsif (vCont = 11) then 
               linha(1).VL_SALDO_DEVEDOR_VENC_90_DIAS := valor;         
            elsif (vCont = 12) then     
               linha(1).CD_RISCO := valor;                                                                                
            elsif (vCont = 12) then     
               linha(1).DT_BASE := valor;                                                                                
            elsif (vCont = 12) then     
               linha(1).DH_CARGA := valor;                                                                                
            elsif (vCont = 12) then     
               linha(1).CD_CLASSIFICACAO := valor;                                                                                
            end if; 
            
            vLinhaAux := substr(vLinhaAux,instr(vLinhaAux,';')+1,length(vLinhaAux)); 
            vCont := vCont + 1; 
            
        end loop; 
        
        -- grava o arquivo na tabela 
        Insert Into t_Bb_Ativa (Cd_Classificacao, 
                                Cd_Banco, 
                                Sg_Uf, 
                                Cd_Modalidade_Ativa, 
                                Cd_Finalidade, 
                                Cd_Fonte_Recurso, 
                                Qt_Contratos, 
                                Vl_Saldo_Devedor, 
                                Qt_Contratos_Vencidos, 
                                Vl_Saldo_Devedor_Vencido, 
                                Qt_Venc_Mais_90_Dias, 
                                Vl_Saldo_Devedor_Venc_90_Dias, 
                                Vl_Retorno_Previsto, 
                                Cd_Risco, 
                                Dt_Base, 
                                Dh_Carga, 
                                Vl_Retorno_Vencido) 
        Values (linha(1).Cd_Classificacao,
                linha(1).cd_banco, 
                linha(1).Sg_Uf,
                linha(1).Cd_Modalidade_Ativa,
                linha(1).Cd_Finalidade,
                linha(1).Cd_Fonte_Recurso,
                linha(1).Qt_Contratos,
                linha(1).Vl_Saldo_Devedor,
                linha(1).Qt_Contratos_Vencidos,
                linha(1).Vl_Saldo_Devedor_Vencido,
                linha(1).Qt_Venc_Mais_90_Dias,
                linha(1).Vl_Saldo_Devedor_Venc_90_Dias,
                linha(1).Vl_Retorno_Previsto,
                linha(1).Cd_Risco,
                linha(1).Dt_Base,
                linha(1).Dh_Carga,
                linha(1).Vl_Retorno_Vencido);
        
        Commit; 
              
        
      Exception 
           --When No_Data_Found Then 
                -- arquivo não encontrado 
               -- exit;
           When Value_Error Then 
                -- linha do arquivo muito grande para armazenar.
                Raise_Application_Error(-20214, 'Value_Error '||sqlerrm);  
           When Utl_File.Invalid_FileHandle Then 
                -- arquivo não válido
                Raise_Application_Error(-20215, 'Invalid_filehandle '||sqlerrm); 
           When Utl_File.Invalid_Operation Then 
                -- arquivo não está aberto para leitura
                Raise_Application_Error(-20216, 'invalid_operation '||sqlerrm); 
           When Utl_File.Read_Error Then 
                -- erro durante a leitura
                Raise_Application_Error(-20217, 'Read_Error '||sqlerrm);
      end; 
              
      --End Loop; 
      Utl_File.FClose(v_file_handle);         

end;

[cleberzumba]

Brother.....funcionou..mas nos colocamos o valor do arquivo fixo na procedure. essa procedure é startada e ela tem q pegar o arquivo e carregar os dados. não posso colocar o valor que está no arquivo fixo na rotina.

[diegolenhardt]

Aí cara, o codigo abaixo esta rodando, basta colocar linha.campo[

agora vai,. abraço

Selecionar tudo

declare

  Vlinha Varchar2(2000);
  v_File_Handle Utl_File.File_Type;
  vLinhaAux Varchar2(2000);
  vCont Number := 1;
  valor Varchar2(500);

  type arq_bb_type is record
  (CD_BANCO VARCHAR2(100),
  SG_UF VARCHAR2(100),
  CD_MODALIDADE_ATIVA VARCHAR2(100),
  CD_FINALIDADE VARCHAR2(100),
  CD_FONTE_RECURSO VARCHAR2(100),
  QT_CONTRATOS VARCHAR2(100),
  VL_SALDO_DEVEDOR VARCHAR2(100),
  QT_CONTRATOS_VENCIDOS VARCHAR2(100),
  VL_SALDO_DEVEDOR_VENCIDO VARCHAR2(100),
  QT_VENC_MAIS_90_DIAS VARCHAR2(100),
  VL_SALDO_DEVEDOR_VENC_90_DIAS VARCHAR2(100),
  VL_RETORNO_PREVISTO VARCHAR2(100),
  CD_RISCO VARCHAR2(100),
  DT_BASE VARCHAR2(100),
  DH_CARGA VARCHAR2(100),
  VL_RETORNO_VENCIDO VARCHAR2(100),
  CD_CLASSIFICACAO VARCHAR2(100));

  linha arq_bb_type;

--- Abre arquivo txt para leitura
--v_File_Handle := Utl_File.Fopen('C:\Cleber', 'teste.txt', 'r');
-- o nome do arquivo tem que vir dinamico. cada mês é um nome diferente porque vem pela data do mês.

-- Loop para Ler o Arquivo Txt
--Loop
Begin
  --linha.delete; --sempre limpa o vetor
  -- Ler o arquivo
  --Utl_File.Get_Line(v_File_Handle, Vlinha);

  vLinha := '"ESTOQUE DE DÍVIDAS";"001 - BB";"SP";"FUNCAFE CPR";"AGRÍCOLA";"FUNCAFÉ";6;1166502,91;2,00;65911,56;0,00;0,00;281162,54;"PRÓPRIO BB";"09/11/2009";"30/10/2009"';

  vLinhaAux := vLinha;

  while (instr(vLinhaAux,';') > 0) loop
    valor := replace(replace(trim(substr(vLinhaAux,1,instr(vLinhaAux,';')-1)),' '),chr(10));

    if (vCont = 1) Then
      linha.CD_BANCO := valor;
    elsif (vCont = 2) then
      linha.SG_UF := valor;
    elsif (vCont = 3) then
      linha.CD_MODALIDADE_ATIVA := valor; --aqui você trata todos os seus campos
    elsif (vCont = 4) then
      linha.CD_FINALIDADE := valor;
    elsif (vCont = 5) then
      linha.CD_FONTE_RECURSO := valor;
    elsif (vCont = 6) then
      linha.QT_CONTRATOS := valor;
    elsif (vCont = 7) then
      linha.VL_SALDO_DEVEDOR := valor;
    elsif (vCont = 8) then
      linha.QT_CONTRATOS_VENCIDOS := valor;
    elsif (vCont = 9) then
      linha.VL_SALDO_DEVEDOR_VENCIDO := valor;
    elsif (vCont = 10) then
      linha.QT_VENC_MAIS_90_DIAS := valor;
    elsif (vCont = 11) then
      linha.VL_SALDO_DEVEDOR_VENC_90_DIAS := valor;
    elsif (vCont = 12) then
      linha.CD_RISCO := valor;
    elsif (vCont = 13) then
      linha.DT_BASE := valor;
    elsif (vCont = 14) then
      linha.DH_CARGA := valor;
    elsif (vCont = 15) then
      linha.CD_CLASSIFICACAO := valor;
    end if;

    vLinhaAux := substr(vLinhaAux,instr(vLinhaAux,';')+1,length(vLinhaAux));
    vCont := vCont + 1;

  end loop;

  dbms_output.put_line(linha.Cd_Classificacao);
  dbms_output.put_line(linha.cd_banco);
  dbms_output.put_line(linha.Sg_Uf);
  dbms_output.put_line(linha.Cd_Modalidade_Ativa);
  dbms_output.put_line(linha.Cd_Finalidade);
  dbms_output.put_line(linha.Cd_Fonte_Recurso);

  /*

-- grava o arquivo na tabela
Insert Into t_Bb_Ativa (Cd_Classificacao,
Cd_Banco,
Sg_Uf,
Cd_Modalidade_Ativa,
Cd_Finalidade,
Cd_Fonte_Recurso,
Qt_Contratos,
Vl_Saldo_Devedor,
Qt_Contratos_Vencidos,
Vl_Saldo_Devedor_Vencido,
Qt_Venc_Mais_90_Dias,
Vl_Saldo_Devedor_Venc_90_Dias,
Vl_Retorno_Previsto,
Cd_Risco,
Dt_Base,
Dh_Carga,
Vl_Retorno_Vencido)
Values (linha(1).Cd_Classificacao,
linha(1).cd_banco,
linha(1).Sg_Uf,
linha(1).Cd_Modalidade_Ativa,
linha(1).Cd_Finalidade,
linha(1).Cd_Fonte_Recurso,
linha(1).Qt_Contratos,
linha(1).Vl_Saldo_Devedor,
linha(1).Qt_Contratos_Vencidos,
linha(1).Vl_Saldo_Devedor_Vencido,
linha(1).Qt_Venc_Mais_90_Dias,
linha(1).Vl_Saldo_Devedor_Venc_90_Dias,
linha(1).Vl_Retorno_Previsto,
linha(1).Cd_Risco,
linha(1).Dt_Base,
linha(1).Dh_Carga,
linha(1).Vl_Retorno_Vencido);

Commit;*/


Exception
--When No_Data_Found Then
-- arquivo não encontrado
-- exit;
When Value_Error Then
-- linha do arquivo muito grande para armazenar.
Raise_Application_Error(-20214, 'Value_Error '||sqlerrm);
When Utl_File.Invalid_FileHandle Then
-- arquivo não válido
Raise_Application_Error(-20215, 'Invalid_filehandle '||sqlerrm);
When Utl_File.Invalid_Operation Then
-- arquivo não está aberto para leitura
Raise_Application_Error(-20216, 'invalid_operation '||sqlerrm);
When Utl_File.Read_Error Then
-- erro durante a leitura
Raise_Application_Error(-20217, 'Read_Error '||sqlerrm);
end;

--End Loop;
--Utl_File.FClose(v_file_handle);

--end; 

[cleberzumba]

beleza....podemos conversar amanhã?...vou ter q ir nessa.
Te aguardo amanhã aqui...valeu abraço!!!

[cleberzumba]

Brother.....está funcionando....mas nos colocamos o valor do arquivo fixo na procedure. essa procedure é startada e ela tem q pegar o arquivo e carregar os dados. não posso colocar o valor que está no arquivo fixo na rotina.

[diegolenhardt]

Sim claro, ali eu coloquei só como teste cara, basta você voltar o seu Loop,
e ler o vLinha

[cleberzumba]

Pô to fazendo aqui mas não está dando certo...olha só o meu codigo a baixo:

Selecionar tudo

PROCEDURE SIGER_LER_ARQUIVO_PC IS

    Vlinha Varchar2(2000); 
    v_File_Handle Utl_File.File_Type; 
    vLinhaAux Varchar2(2000); 
    vCont Number := 1; 
    valor Varchar2(500);   

  type  arq_bb_type is record
     (CD_BANCO                                  VARCHAR2(100),
      SG_UF                                     VARCHAR2(100),
      CD_MODALIDADE_ATIVA                       VARCHAR2(100),
      CD_FINALIDADE                             VARCHAR2(100),
      CD_FONTE_RECURSO                          VARCHAR2(100),
      QT_CONTRATOS                              VARCHAR2(100),
      VL_SALDO_DEVEDOR                          VARCHAR2(100),
      QT_CONTRATOS_VENCIDOS                     VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENCIDO                  VARCHAR2(100),
      QT_VENC_MAIS_90_DIAS                      VARCHAR2(100),
      VL_SALDO_DEVEDOR_VENC_90_DIAS             VARCHAR2(100),
      VL_RETORNO_PREVISTO                       VARCHAR2(100),
      CD_RISCO                                  VARCHAR2(100),
      DT_BASE                                   VARCHAR2(100),
      DH_CARGA                                  VARCHAR2(100),
      VL_RETORNO_VENCIDO                        VARCHAR2(100),
      CD_CLASSIFICACAO                          VARCHAR2(100));
      
  linha              arq_bb_type;
  
  
begin
--- Abre arquivo txt para leitura 
    v_File_Handle := Utl_File.Fopen('D:\Sistemas_MAPA\SIGER\Arquivos\Arquivo BB', 'Estoque de Dividas.txt', 'r'); 
-- o nome do arquivo tem que vir dinamico. cada mês é um nome diferente porque vem pela data do mês. 

-- Loop para Ler o Arquivo Txt 
--Loop 
     Begin 

        --linha.delete; --sempre limpa o vetor 
        -- Ler o arquivo 
        Utl_File.Get_Line(v_File_Handle, Vlinha); 
              
        vLinhaAux := replace(vLinha,'"',''); 

        while (instr(vLinhaAux,';') > 0) loop 

            valor := replace(replace(trim(substr(vLinhaAux,1,instr(vLinhaAux,';')-1)),' '),chr(10)); 
            
            if (vCont = 1) then
               linha.CD_BANCO := valor; 
            elsif (vCont = 2) then 
               linha.SG_UF := valor; 
            elsif (vCont = 3) then 
               linha.CD_MODALIDADE_ATIVA := valor; --aqui você trata todos os seus campos 
            elsif (vCont = 4) then 
               linha.CD_FINALIDADE := valor;         
            elsif (vCont = 5) then 
               linha.CD_FONTE_RECURSO := valor;         
            elsif (vCont = 6) then 
               linha.QT_CONTRATOS := valor;         
            elsif (vCont = 7) then 
               linha.VL_SALDO_DEVEDOR := valor;         
            elsif (vCont = 8) then 
               linha.QT_CONTRATOS_VENCIDOS := valor;         
            elsif (vCont = 9) then 
               linha.VL_SALDO_DEVEDOR_VENCIDO := valor;         
            elsif (vCont = 10) then 
               linha.QT_VENC_MAIS_90_DIAS := valor;         
            elsif (vCont = 11) then 
               linha.VL_SALDO_DEVEDOR_VENC_90_DIAS := valor;         
            elsif (vCont = 12) then  
               linha.VL_RETORNO_PREVISTO := valor;
            elsif (vCont = 13) then       
               linha.CD_RISCO := valor;                                                                                
            elsif (vCont = 14) then     
               linha.DT_BASE := valor;                                                                                
            elsif (vCont = 15) then     
               linha.DH_CARGA := valor;  
            elsif (vCont = 16) then
               linha.VL_RETORNO_VENCIDO := valor;
            elsif (vCont = 17) then               
               linha.CD_CLASSIFICACAO := valor;
            end if; 
            
            vLinhaAux := substr(vLinhaAux,instr(vLinhaAux,';')+1,length(vLinhaAux)); 
            vCont := vCont + 1; 
           
       end loop;  
        
        -- grava o arquivo na tabela 
        Insert Into t_Bb_Ativa (Cd_Banco, 
                                Sg_Uf, 
                                Cd_Modalidade_Ativa, 
                                Cd_Finalidade, 
                                Cd_Fonte_Recurso, 
                                Qt_Contratos, 
                                Vl_Saldo_Devedor, 
                                Qt_Contratos_Vencidos, 
                                Vl_Saldo_Devedor_Vencido, 
                                Qt_Venc_Mais_90_Dias, 
                                Vl_Saldo_Devedor_Venc_90_Dias, 
                                Vl_Retorno_Previsto, 
                                Cd_Risco, 
                                Dt_Base, 
                                Dh_Carga, 
                                Vl_Retorno_Vencido,
                                Cd_Classificacao) 
        Values (linha.cd_banco, 
                linha.Sg_Uf,
                linha.Cd_Modalidade_Ativa,
                linha.Cd_Finalidade,
                linha.Cd_Fonte_Recurso,
                linha.Qt_Contratos,
                linha.Vl_Saldo_Devedor,
                linha.Qt_Contratos_Vencidos,
                linha.Vl_Saldo_Devedor_Vencido,
                linha.Qt_Venc_Mais_90_Dias,
                linha.Vl_Saldo_Devedor_Venc_90_Dias,
                linha.Vl_Retorno_Previsto,
                linha.Cd_Risco,
                linha.Dt_Base,
                linha.Dh_Carga,
                linha.Vl_Retorno_Vencido,
                linha.Cd_Classificacao);
        
        Commit; 
              
       
      Exception 
           --When No_Data_Found Then 
                -- arquivo não encontrado 
               -- exit;
           When Value_Error Then 
                -- linha do arquivo muito grande para armazenar.
                Raise_Application_Error(-20214, 'Value_Error '||sqlerrm);  
           When Utl_File.Invalid_FileHandle Then 
                -- arquivo não válido
                Raise_Application_Error(-20215, 'Invalid_filehandle '||sqlerrm); 
           When Utl_File.Invalid_Operation Then 
                -- arquivo não está aberto para leitura
                Raise_Application_Error(-20216, 'invalid_operation '||sqlerrm); 
           When Utl_File.Read_Error Then 
                -- erro durante a leitura
                Raise_Application_Error(-20217, 'Read_Error '||sqlerrm);
      end; 
              
      --End Loop; 
      Utl_File.FClose(v_file_handle);         

end;

[cleberzumba]

voltei o loop, mas deu dados não encontrado.

[cleberzumba]

funcionou....uhuuuuuuuuuuuuuuuuu....

só um problema. quando tem acento, tipo 'Dívida'...ele destorce a letra e coloca um ponto de interrogação de cabeça para baixo.

e oura....o nome do arquivo não posso colocar fixo na rotina, ele muda toda vez q a rotina for startada...como eu faço?

[diegolenhardt]

O problema do acento deve estar relacionado ao nls_language, charset, etc

existem funcoes na net para tirar acentos, pode tentar usar uma dessas também,

o nome do arquivo eu tinha postado aí como você pode fazer,

com to_char no sysdate, pegando DD_MM_RRRR, etc

[cleberzumba]

ok..e o nome do arquivo não posso colocar fixo na rotina, ele muda toda vez q a rotina for startada...como eu faço?

[diegolenhardt]

Qual a regra para essa alteracao do nome do arquivo?

dê exemplos,

[cleberzumba]

vou verificar isso e te falo.

[cleberzumba]

cara, mas são tres arquvos e só tem um while, um type....como eu faço para os outros dois arquivos?

[diegolenhardt]

Nesse seu procedimento, crie uma parametro de entrada que será o nome do arquivo lido,

aí você chama a procedure 3 vezes, cada vez passando o arquivo a ser importado, =)

caso os procedimentos sejam diferentes para cada arquivo, crie 3 procedures, ou então trate dentro da procedure pelo nome do arquivo passado no parametro..

[cleberzumba]

o procedimento é o mesmo para os tres arquivos, mas são tres tabelas diferentes e de nomes diferentes. mas eu posso colocar tres insert um abaixo do outro no mesmo while?...isso resolve. acho q dá, não?

[diegolenhardt]

Mas não seria um insert para cada Arquivo???

Se sim, cada While vai ler um arquivo diferente, consequentemente vai inserir em tabela diferente, o que poderia fazer seria fazer via execute immediate para somente mudar o nome da tabela conforme o arquivo lido,

[cleberzumba]

então é isso. vê se você concorsa comigo. tres while, tres type e tres insert. porque são tabelas e arquivos diferentes. certo?

[diegolenhardt]

Isso aí, eu ainda sugiro que crie 3 procedimentos, um para cada, digamos que de erro no primeiro , vai interferir nos outros, então com 3 procedures, poem 1 job chamando as 3 procedures e ta feito, eu costumo colocar sempre um When Others Then no final inserindo o sqlerrm, com o nome da procedure que disparou, e tambem no final caso tudo de certo insert na mesma tabela com um OK,

Selecionar tudo

  Insert Into LogImportacao (nmTarefa, dsErro, dtErro) Values ('PATUALIZATABELASSIGEF', 'OK', Sysdate);

  Commit;

Exception
  When Others Then
    dsErro := SQLERRM;
    Rollback;
    Insert Into LogImportacao (nmTarefa, dsErro, dtErro) 
                       Values ('PATUALIZATABELASSIGEF', dsErro, Sysdate);
    Commit;
End;

=]

[cleberzumba]

valeu brother....está dando tudo certo.

[cleberzumba]

brother tu pode me ajudar sobre esse assunto?

[diegolenhardt]

diga lá