Importar TXT separado por blocos

[carlynhos77]

Ola, boa noite...

Pesquisei vários códigos, tentei com vários exemplos, mas não consegui deslanchar nisso.. preciso importar arquivo retorno bancário, se alguém puder me ajudar, vejam a ideia:

Retorno:

Selecionar tudo

A20000857340012       PREF MUN ALTO PARAIS341BANCO ITAU S.A.     2020102600016405CODIGO DE BARRAS                                                     
G3415315000000000248420201023202010278161000000320000137202010260021202000005658400000003200000009000000000153157   10030531566880231020C   2         
G3415315000000000248420201023202010278164000000030000137202010300003202000005656200000000300000009000000000253157   10028531566880231020C   2         
G3415315000000000248420201023202010278165000000006450137202010300001202000005656100000000064500009000000000353157   10027531566880231020C   2         
Z00000500000000000035645

Selecionar tudo

create or replace procedure "P_CARREGA_TXT" (p_arquivo [b]?????[/b], p_id_cidade NUMBER) 

IS

v_convenio VARCHAR2(13);
v_data_geracao VARCHAR(10);

v_banco NUMBER;  posicao de 2 a 4
v_agencia NUMBER; posicao de 5 a 8
v_conta NUMBER; posicao 9 a 21
v_data_validade DATE; posicao 22 a 31
v_data_pagamento DATE; posicao 32 a 41
v_id_registro NUMBER);  42 a 52

*posições ficticias

BEGIN

   No form da aplicação, eu irei localizar o arquivo retorno e remeto ele pra procedure

   abre o arquivo para ser inserido na tabela 

    nesse ponto, fazer a exclusão de dados da tabela, se tiver uma importação anterior do arquivo, com base na variavel "v_data_geracao"

    depois, a ideia é um código que carrega as duas primeiras variáveis, com base na primeira linha, pois o valor delas serão fixas... 

    a partir da segunda linha, carrega as demais variáveis e salvar num banco d dados

    FOR ... insert (variaveis) into (campos )

     commit
    end for

   END;

A ideia inicial é essa, salvar um simples arquivo retorno dentro de uma tabela...a organização do código é o q imagino

agradeço a ajuda

[tiago_pimenta]

Procura por leitura de arquivo txt... Começa tentando gravar na tabela, 1 linha só, ou seja, crie um arquivo com 1 linha só e depois vai melhorando !!!

[dr_gori]

Isso mesmo que o carlynhos77 disse.
Começa carregando uma linha. Procure aqui no forum por UTL_FILE.FOPEN.

Apenas para contexto, se for usar UTL_FILE,
* Tenha em mente que o arquivo deve estar em algum diretório do banco de dados.
* O banco precisa ter as permissões necessárias nesse diretório.

Existem várias formas de ler um arquivo, inclusive SQL*Loader, External Tables, e várias outras.
Aí você decide qual é a melhor. Depois de decidir, a gente ajuda se precisar.

[carlynhos77]

ola, boa tarde, agradeço a atenção de todos...


Então, eu peguei um exemplo e adaptei, vejam:

Selecionar tudo

create or replace procedure "P_LER_GRAVAR_RETORNO_PAGTO_BANCO" ( p_id_usuario NUMBER, p_g_id_cidade NUMBER, 
                                                                 p_Nome_Local VARCHAR2,
                                                                 P_Nome_Arq VARCHAR2)
is

--VARIAVEIS PRIMEIRA LINHA DO ARQUIVO RETORNO
    p_convenio VARCHAR2(10);  -- A
    p_banco VARCHAR2(3);  -- B
    p_data_geracao VARCHAR2(10);  -- C
    p_nsa VARCHAR2(5);  -- D

    vLinha    Varchar2(2000);
    uArquivo  Utl_File.File_Type;

BEGIN
  
    -----------------------
    --- Abre arquivo txt --
    -----------------------
    uArquivo := Utl_File.FOpen[color=#FF0000](P_Nome_Local , P_Nome_Arq  , 'r');[/color]
    ---------------------------------
    -- Loop para Ler o Arquivo Txt --
    ---------------------------------
    Utl_File.Get_Line(uArquivo , vLinha);

    if Substr( vLinha , 1,1) = 'A' or Substr( vLinha , 1,1) = 'Z' then
        p_convenio := Substr( vLinha , 3,20 );  -- A
        p_banco := Substr( vLinha , 43, 3 );  -- B
        p_data_geracao := to_char(Substr( vLinha , 66,8 ),'dd/mm/yyyy');  -- C
        p_nsa := Substr( vLinha , 74,6 );  -- D
    end if;

    DELETE FROM ARRECADACAO_RETORNO_BANCOS
    WHERE SEQUENCIAL_NSA = p_nsa and
    id_cidade = p_g_id_cidade;

    Loop
      Begin

        if Substr( vLinha , 1,1) = 'G' then
            Insert Into ARRECADACAO_RETORNO_BANCOS
                ( CODIGO_CONVENIO -- 1
                , CODIGO_BANCO
                , DATA_GERACAO
                , SEQUENCIAL_NSA
                , DATA_PAGAMENTO
                , DATA_CREDITO
                , VALOR_PAGAMENTO
                , CODIGO_EMPRESA
                , DATA_VENCIMENTO
                , CODIGO_PARCELA
                , CODIGO_IMPOSTO
                , ID_ANO
                , ID_REGISTRO
                , NUMERO_AUTENTICACAO
                , DATA_LEITURA
                , USUARIO
                , ID_CIDADE
                , mês_PAGAMENTO
                , ANO_PAGAMENTO
                , CODIGO_BARRAS
                )
            Values
                ( p_convenio
                , p_banco
                , p_data_geracao
                , p_nsa
                , to_char(Substr( vLinha , 22,8 ),'dd/mm/yyyy')
                , to_char(Substr( vLinha , 30,8 ),'dd/mm/yyyy')
                , to_char(Substr( vLinha , 42,11 ),'0.00')
                , Substr( vLinha , 53,4 )
                , to_char(Substr( vLinha , 57,8 ),'dd/mm/yyyy')
                , Substr( vLinha , 65,2 )
                , Substr( vLinha , 67,2 )
                , Substr( vLinha , 69,4 )
                , Substr( vLinha , 73,9 )
                , Substr( vLinha , 118,23 )
                , to_char(sysdate,'dd/mm/yyyy')
                , p_id_usuario
                , p_g_id_cidade
                , to_char(Substr( vLinha , 22,8 ),'mm')
                , to_char(Substr( vLinha , 22,8 ),'yyyy')
                , Substr( vLinha , 22,60 )
                );
            commit;
        end if;    

      End;
    End Loop;
    Utl_File.FClose(uArquivo); 

END;       

Porém, não consegui fazer rodar ainda, porque no meu form, eu adicionei um item ( procurar arquivo), criei um processamento para chamar a procedure:

Selecionar tudo

P_LER_GRAVAR_RETORNO_PAGTO_BANCO (p_id_usuario => :G_ID_USUARIO, 
                                  p_g_id_cidade => :G_ID_CIDADE, 
                                  p_Nome_Local => 'D:\',
                                  P_Nome_Arq  => :P28_SELECIONE);

Acontece que na procedure esta pedindo o LOCAL DO ARQUIVO e o NOME, mas como passar isso? visto q eu so tenho um item para selecionar

[dr_gori]

Pergunta:
Você está carregando o arquivo como ?
Esse é o primeiro passo: Pra usar UTL_FILE o arquivo precisa estar em um diretório do banco de dados.
Pelo que eu estou entendendo, você está permitindo que o usuário escolha o arquivo. Sem problema, mas de alguma forma esse arquivo precisa ser salvo no servidor Oracle.

Explica pra gente como que isso ta acontecendo aí.

[carlynhos77]

Ola, boa tarde...

Na verdade, minha codificação ta errada, para forma q preciso... o arquivo não será salvo na tabela, ele estará no micro, baixado todos os dias

o usuário irá selecionar ele num item de pagina (procurar arquivo), e após manda rodar a procedure

[dr_gori]

Então:
O arquivo ta no computador pessoal do usuário.
A primeira coisa é: Como você está fazendo pra salvar esse arquivo no servidor ?
Você está usando Oracle Forms ?

[carlynhos77]

Ola, a idéia não salvar no servidor, esses arquivos retornos ficam numa pasta qualquer no computador, após serem baixados... o servidor só irá selecionar o arquivo e mandar processar, o conteudo do arquivo será salva na tabela, mas o arquivo em si, não sera salvo... pelo que vi agora, eu utilizei uma procedure q exige q o arquivo esteja no servidor do banco de dados ne, mas não é isso que eu quero....

Vamos dizer assim, eu seleciono o arquivo retorno, processo ele, e depois ele é descartado...

[dr_gori]

Tudo bem, mas nesse caso quem tem que processar não é o PLSQL do banco Oracle.
Quem tem que LER o arquivo e processar é a linguagem que você está usando.

Por isso eu perguntei: Você está usando Oracle Forms ? Ou outra coisa ?
Lembre-se: PLSQL é executado no servidor ORACLE, não na sua máquina local.

[carlynhos77]

ola, boa noite... consegui resolver, o codigo ficou assim

Selecionar tudo

create or replace procedure "P_LER_GRAVAR_RETORNO_PAGTO_BANCO" ( p_id_usuario NUMBER, p_g_id_cidade NUMBER, 
                                                                 p_clob_arquivo CLOB)
is

--VARIAVEIS PRIMEIRA LINHA DO ARQUIVO RETORNO
    p_convenio VARCHAR2(20);  -- A
    p_banco VARCHAR2(3);  -- B
    p_data_geracao DATE;  -- C
    p_nsa VARCHAR2(6);  -- D
    p_id_empresa VARCHAR(10);

    vLinha    Varchar2(4000);
    v_quebra_linha_atual NUMBER;
    v_quebra_linha_anterior NUMBER := 0;
    --v_num_ocorrencia NUMBER := 1;

BEGIN
  
    -- Extrai a primeira linhat --
    ---------------------------------
    v_quebra_linha_atual := dbms_lob.instr(p_clob_arquivo, chr(13)||chr(10), 1, 1);
    vLinha := dbms_lob.substr(p_clob_arquivo, v_quebra_linha_atual, 1 );

    htp.p(v_quebra_linha_atual);
    htp.p(vLinha);

    if Substr( vLinha , 1,1) = 'A' or Substr( vLinha , 1,1) = 'Z' then
        p_convenio := Substr( vLinha , 3,20 );  -- A
        p_banco := Substr( vLinha , 43, 3 );  -- B
        p_data_geracao := to_date(Substr( vLinha , 66,8 ),'yyyymmdd');  -- C
        p_nsa := Substr( vLinha , 74,6 );  -- D
    end if;

    DELETE FROM ARRECADACAO_RETORNO_BANCOS
    WHERE SEQUENCIAL_NSA = p_nsa and
    id_cidade = p_g_id_cidade;

    --Percorre as linhas restantes
    Loop
        --Obtém a posição da próxima quebra de linha
       -- v_num_ocorrencia := v_num_ocorrencia + 1;
       v_quebra_linha_anterior := v_quebra_linha_atual;
        v_quebra_linha_atual := dbms_lob.instr(p_clob_arquivo, chr(13)||chr(10), v_quebra_linha_anterior + 1, 1);

        --Se não existe mais quebra de linha quer dizer que encerrou o arquivo
        IF v_quebra_linha_atual = 0 THEN
            RETURN;
        END IF;


        vLinha := dbms_lob.substr(p_clob_arquivo, (v_quebra_linha_atual-v_quebra_linha_anterior), v_quebra_linha_anterior+2 );
        htp.p(v_quebra_linha_atual);
        htp.p(vLinha);

        if Substr( vLinha , 1,1) = 'G' then
            Insert Into ARRECADACAO_RETORNO_BANCOS
                ( CODIGO_CONVENIO -- 1
                , CODIGO_BANCO
                , DATA_GERACAO
                , SEQUENCIAL_NSA
                , DATA_PAGAMENTO
                , DATA_CREDITO
                , VALOR_PAGAMENTO
                , CODIGO_EMPRESA
                , DATA_VENCIMENTO
                , CODIGO_PARCELA
                , CODIGO_IMPOSTO
                , ID_ANO
                , ID_REGISTRO
                , NUMERO_AUTENTICACAO
                , DATA_LEITURA
                , USUARIO
                , ID_CIDADE
                , mês_PAGAMENTO
                , ANO_PAGAMENTO
                , CODIGO_BARRAS
                )
            Values
                ( p_convenio
                , p_banco
                , p_data_geracao
                , p_nsa
                , to_date(Substr( vLinha , 22,8 ),'ddmmyyyy')
                , to_date(Substr( vLinha , 30,8 ),'yyyymmdd')
                , to_number(Substr( vLinha , 42,11 ))/100
                , Substr( vLinha , 53,4 )
                , to_date(Substr( vLinha , 57,8 ),'yyyymmdd')
                , Substr( vLinha , 65,2 )
                , Substr( vLinha , 67,2 )
                , Substr( vLinha , 69,4 )
                , Substr( vLinha , 73,9 )
                , Substr( vLinha , 118,23 )
                , sysdate
                , p_id_usuario
                , p_g_id_cidade
                , Substr( vLinha , 24,2 )
                , Substr( vLinha , 26,4 )
                , Substr( vLinha , 22,60 )
                );
            commit;
        end if;    

    End Loop;
    
END;       

agradeço a todos

[dr_gori]

ola, boa tarde, agradeço a atenção de todos...


Então, eu peguei um exemplo e adaptei, vejam:

Selecionar tudo

create or replace procedure "P_LER_GRAVAR_RETORNO_PAGTO_BANCO" ( p_id_usuario NUMBER, p_g_id_cidade NUMBER, 
                                                                 p_Nome_Local VARCHAR2,
                                                                 P_Nome_Arq VARCHAR2)
is

--VARIAVEIS PRIMEIRA LINHA DO ARQUIVO RETORNO
    p_convenio VARCHAR2(10);  -- A
    p_banco VARCHAR2(3);  -- B
    p_data_geracao VARCHAR2(10);  -- C
    p_nsa VARCHAR2(5);  -- D

    vLinha    Varchar2(2000);
    uArquivo  Utl_File.File_Type;

BEGIN
  
    -----------------------
    --- Abre arquivo txt --
    -----------------------
    uArquivo := Utl_File.FOpen[color=#FF0000](P_Nome_Local , P_Nome_Arq  , 'r');[/color]
    ---------------------------------
    -- Loop para Ler o Arquivo Txt --
    ---------------------------------
    Utl_File.Get_Line(uArquivo , vLinha);

    if Substr( vLinha , 1,1) = 'A' or Substr( vLinha , 1,1) = 'Z' then
        p_convenio := Substr( vLinha , 3,20 );  -- A
        p_banco := Substr( vLinha , 43, 3 );  -- B
        p_data_geracao := to_char(Substr( vLinha , 66,8 ),'dd/mm/yyyy');  -- C
        p_nsa := Substr( vLinha , 74,6 );  -- D
    end if;

    DELETE FROM ARRECADACAO_RETORNO_BANCOS
    WHERE SEQUENCIAL_NSA = p_nsa and
    id_cidade = p_g_id_cidade;

    Loop
      Begin

        if Substr( vLinha , 1,1) = 'G' then
            Insert Into ARRECADACAO_RETORNO_BANCOS
                ( CODIGO_CONVENIO -- 1
                , CODIGO_BANCO
                , DATA_GERACAO
                , SEQUENCIAL_NSA
                , DATA_PAGAMENTO
                , DATA_CREDITO
                , VALOR_PAGAMENTO
                , CODIGO_EMPRESA
                , DATA_VENCIMENTO
                , CODIGO_PARCELA
                , CODIGO_IMPOSTO
                , ID_ANO
                , ID_REGISTRO
                , NUMERO_AUTENTICACAO
                , DATA_LEITURA
                , USUARIO
                , ID_CIDADE
                , mês_PAGAMENTO
                , ANO_PAGAMENTO
                , CODIGO_BARRAS
                )
            Values
                ( p_convenio
                , p_banco
                , p_data_geracao
                , p_nsa
                , to_char(Substr( vLinha , 22,8 ),'dd/mm/yyyy')
                , to_char(Substr( vLinha , 30,8 ),'dd/mm/yyyy')
                , to_char(Substr( vLinha , 42,11 ),'0.00')
                , Substr( vLinha , 53,4 )
                , to_char(Substr( vLinha , 57,8 ),'dd/mm/yyyy')
                , Substr( vLinha , 65,2 )
                , Substr( vLinha , 67,2 )
                , Substr( vLinha , 69,4 )
                , Substr( vLinha , 73,9 )
                , Substr( vLinha , 118,23 )
                , to_char(sysdate,'dd/mm/yyyy')
                , p_id_usuario
                , p_g_id_cidade
                , to_char(Substr( vLinha , 22,8 ),'mm')
                , to_char(Substr( vLinha , 22,8 ),'yyyy')
                , Substr( vLinha , 22,60 )
                );
            commit;
        end if;    

      End;
    End Loop;
    Utl_File.FClose(uArquivo); 

END;       

Porém, não consegui fazer rodar ainda, porque no meu form, eu adicionei um item ( procurar arquivo), criei um processamento para chamar a procedure:

Selecionar tudo

P_LER_GRAVAR_RETORNO_PAGTO_BANCO (p_id_usuario => :G_ID_USUARIO, 
                                  p_g_id_cidade => :G_ID_CIDADE, 
                                  p_Nome_Local => 'D:\',
                                  P_Nome_Arq  => :P28_SELECIONE);

Acontece que na procedure esta pedindo o LOCAL DO ARQUIVO e o NOME, mas como passar isso? visto q eu so tenho um item para selecionar