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
Generate Layout Perdcomp
- stcoutinho
- Moderador
- Posts: 850
- Joined: Wed, 11 May 2011 5:15 pm
- Location: são Paulo - SP
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:
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
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:
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
- Moderador
- Posts: 850
- Joined: Wed, 11 May 2011 5:15 pm
- Location: são Paulo - SP
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:
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 ";":
Good luck,
Sergio
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:
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 ";":
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];
Sergio
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:
Where the Select is your query. Then to generate TXT you can use SQLLoader.
Must a query Returning the data you want, in the same structure as your destination table and does:
INSERT INTO TABELA_DESTINO SELECT...
-
- Information
-
Who is online
Users browsing this forum: No registered users and 1 guest