Generate Layout Perdcomp

[dourado]

Good afternoon,

I'm starting in PL / SQL and I received a mission, generating a TXT file according to the Layout of the Perdcomp (Federal Revenue Program)
I know which tables are the data I need, so I thought about creating a new table according to the layout, but how do you fill it with my data?


I need to generate in txt with the field delimiter being (;) but only to know how to feed this table at first will already be of great help, then I see the generation of txt through Excel

Can you explain this procedure? If it is through procedure, cursor, or however, can you give me an example?

The layout is in the link below, thank you from
http://www.fileserve.com/file/4V6BACa

[stcoutinho]

Gold,

I understand that you will develop a PL / SQL procedure that will work with the UTL_File to generate the text file.

For this, you will develop a four main step procedure:

1) Statement of variables (file_type type) and cursor with the queries it will display the results;
2) Opening the text file in the operating system (FOPEN) and data cursor opening; (Open)
3) Data cursor processing / data recording in the text file (loop - fetch- put_line - end loop);
4) Closing the text file (fclose) and data cursor (close);

Below, it follows a small example of how to write this procedure:

Select all

 
CREATE OR REPLACE PROCEDURE SP_EXPORT_PERDCOMP 
IS  
   v_xxa_output_file        UTL_FILE.FILE_TYPE;      ---- Este campo serve para definir o arquivo texto 
   v_texto                  VARCHAR2(2000) := NULL;  ---  Este campo apresentara a linha que será gravada no arquivo texto 
    
   CURSOR CV_PERDCOMP IS   --- Este cursor deve ter a sua querie para extrair os dados para o PERDCOMP 
      SELECT [CAMPO_1]||   --- estou usando o concatenador "||" para que as colunas saiam juntas 
             [CAMPO_2]||   --- talvez você precise usar funcoes do oracle (TO_CHAR,ROUND,etc) para que  
             ....          --- os dados saiam exatamente nas colunas/posicoes certas, conforme a  
             [CAMPO_N]     --- definicao do lay-out. 
        FROM [TABELA_1] 
             .... 
             [TABELA_N] 
       WHERE [CONDICOES];           
 
BEGIN  
       v_xxa_output_file := UTL_FILE.FOPEN                  --- Operacao de abertura do arquivo texto  
                                           ('/TMP/',        --- Diretorio onde sera gravado arquivo texto 
                                            'PERDCOMP.TXT', --- Nome do arquivo texto 
                                            'W’,            --- Indica arquivo aberto para gravacao 
                                            32767);         --- Tamanho Buffer 
 
      OPEN CV_PERDCOMP;  --- Abertura do cursor de dados 
       
      LOOP 
        FETCH CV_PERDCOMP INTO v_texto;   --- Armazenando 1 linha do select na variavel v_texto 
        EXIT WHEN CV_PERDCOMP%NOTFOUND;   --- Saio do LOOP se não existir mais registro para funcionar 
         
        UTL_FILE.PUT_LINE (v_xxa_output_file,v_texto); --- Gravo a linha no arquivo texto; 
     END LOOP; 
      
     CLOSE CV_PERDCOMP; --- Fechamento do cursor de dados 
      
     UTL_FILE.FCLOSE(v_xxa_output_file); --- Fechamento do arquivo texto 
      
EXCEPTION 
    [COLOCAR AQUI O TRATAMENTO DE POSSIVEIS ERROS QUE OCORRAM DURANTE A EXECUCAO DA PROCEDURE 
     EX: ERRO DE ABERTURA DO ARQUIVO, DIRETORIO INVALIDO, FALTA DE PERMISSOES,ETC] 
 
END SP_EXPORT_PERDCOMP; 

I am not providing A complete script for the solution of your problem. Only a procedure skeleton you will need to develop.

I recommend that you take a look at Oracle's manuals to check the required requirements to use this package (UTL_FILE).

There is an Oracle manual called "Oracle Packages and Types, which describes it. You can also search for the theme in Asktom http://asktom.oracle.com).
Hugs and good luck,

Sergio

Sergio

[stcoutinho]

Golden,

Sorry, I had not noticed that you wanted to record in a table, but the logic (excluded the UTL_File) would look like
for this, you will develop A four-step procedure:

1) Statement of variables / cursor with the queries that presents the results;
2) Opening of the cursor (Open)
3) processing of the cursor / data recording in the table (loop - fetch- insert - end loop);
4) Data cursor closure (close);

Below, it follows a small example of how to write this procedure:

Select all

 
CREATE OR REPLACE PROCEDURE SP_EXPORT_PERDCOMP 
IS  
   v_texto                  [NOME_TABELA_PERDCOMP]%ROWTYPE;  ---  Esta variável vai armazenar um registro conforme a estrutua da nova tabela 
    
   CURSOR CV_PERDCOMP IS   --- Este cursor deve ter a sua querie para extrair os dados para o PERDCOMP 
      SELECT [CAMPO_1],    --- talvez você precise usar funcoes do oracle (TO_CHAR,ROUND,etc) para que  
             [CAMPO_2],    --- os dados não sejam "alterados" pelo excel na sua formatacao de colunas  
             ....           
             [CAMPO_N]     
        FROM [TABELA_1] 
             .... 
             [TABELA_N] 
       WHERE [CONDICOES];           
 
BEGIN  
      DELETE  
        FROM [NOME_TABELA_PERDCOMP]; --- Limpando a tabela de destino 
      COMMIT; 
       
      OPEN CV_PERDCOMP;  --- Abertura do cursor de dados 
       
      LOOP 
        FETCH CV_PERDCOMP INTO v_texto;   --- Armazenando 1 linha do select na variavel v_texto 
        EXIT WHEN CV_PERDCOMP%NOTFOUND;   --- Saio do LOOP se não existir mais registro para funcionar 
         
        INSERT  
          INTO [NOME_TABELA_PERDCOMP] 
               ([CAMPO_A], 
                [CAMPO_B], 
                ... 
                [CAMPO_Z]) 
         VALUES (v_texto.[campo_a], 
                 v_texto.[campo_b], 
                 .... 
                 v_texto.[campo_z]); 
         
         COMMIT;              
     END LOOP; 
      
     CLOSE CV_PERDCOMP; --- Fechamento do cursor de dados 
      
EXCEPTION 
    [COLOCAR AQUI O TRATAMENTO DE POSSIVEIS ERROS QUE OCORRAM DURANTE A EXECUCAO DA PROCEDURE] 
 
END SP_EXPORT_PERDCOMP; 

In the previous exempo I passed on the UTL_File, I believe that you would need to change the craving that I passed you to
to behave the delimiters ";":

Select all

 
 CURSOR CV_PERDCOMP IS          --- Este cursor deve ter a sua querie para extrair os dados para o PERDCOMP  
      SELECT [CAMPO_1]||';'||   --- estou usando o concatenador "||" para que as colunas saiam juntas  
             [CAMPO_2]||';'||   --- talvez você precise usar funcoes do oracle (TO_CHAR,ROUND,etc) para que  
             ....               --- os dados saiam exatamente nas colunas/posicoes certas, conforme a  
             [CAMPO_N]          --- definicao do lay-out.  
        FROM [TABELA_1]  
             ....  
             [TABELA_N]  
       WHERE [CONDICOES];         

Good luck,

Sergio

[burga]

Or you can do an "Insert Into Select".

Must a query Returning the data you want, in the same structure as your destination table and does:

Select all

INSERT INTO TABELA_DESTINO SELECT... 

Where the Select is your query. Then to generate TXT you can use SQLLoader.

[dourado]

Thank you guys for the real lesson that gave me here, I'll use it and go back to say the result or if you have more doubts, hehe