FUNÇÃO PARA GERAR ARQUIVOS TEXTOS AUTOMATICAMENTE

FUNÇÃO PARA GERAR ARQUIVOS TEXTOS AUTOMATICAMENTE

Olá pessoal, tudo bem?

Abaixo segue uma procedure que criei para geração de arquivos textos automaticamente, claro que baseado em uma query recebida como parâmetro. Esta query é recebida como string, ou seja, dinâmica.

Atenção:
Como a query é dinâmica, observar o uso de aspas simples, pois pode dar erro ao executá-la.

Script:

CREATE OR REPLACE PACKAGE EXPORT_FILE IS

TYPE R_ARCHIVE IS RECORD(LINE VARCHAR2(4000));
TYPE T_ARCHIVE IS TABLE OF R_ARCHIVE INDEX BY BINARY_INTEGER;

PROCEDURE PR_EXPORT_FILE(p_string in long
,p_path in varchar2 default null
,p_name_file in varchar2 default null
,p_caract in varchar2
,p_archive out export_file.t_archive
,p_msg_error out varchar2);

END EXPORT_FILE;
/
CREATE OR REPLACE PACKAGE BODY EXPORT_FILE IS

PROCEDURE PR_EXPORT_FILE(p_string in long
,p_path in varchar2 default null
,p_name_file in varchar2 default null
,p_caract in varchar2
,p_archive out export_file.t_archive
,p_msg_error out varchar2) IS
/*
Funcao que gera arquivos baseado na query recebida como parametro
*/
v_name_table varchar2(30);
v_script varchar2(32767);
v_sql varchar2(32767);
v_line varchar2(32767);
v_file utl_file.file_type;
v_crt varchar2(1);
v_rg_min number;
v_rg_max number;

BEGIN

--Limpa variaveis
v_script := null;
v_sql := null;
v_line := null;

if p_string is not null then
--Recupera codigo do caracter e atualiza
v_crt := substr(p_caract,1,1);

--Guarda em variavel o nome da tabela a ser utilizada
v_name_table := upper('TMPEXPORTFILE' || dbms_session.unique_session_id);

--Cria tabela temporaria a ser utilizada
v_script := 'CREATE TABLE ' || v_name_table || ' AS ' || replace(upper(p_string), 'SELECT', 'SELECT ROWNUM IDROWNUM,');
execute immediate v_script;

--Abre arquivo a ser gerado
if p_path is not null and p_name_file is not null then
v_file := utl_file.fopen(p_path,p_name_file,'W',32767);
end if;

--Recupera as coluna da tabela utilizada
for tb in (select utc.column_name, utc.data_type
from user_tab_columns utc
where utc.table_name = v_name_table
order by utc.column_id)
loop
if v_line is null then
v_line := tb.column_name;
else
v_line := v_line || v_crt || tb.column_name;
end if;
--
if v_sql is null then
v_sql := tb.column_name;
else
if tb.data_type = 'DATE' then
v_sql := v_sql || ' || ''' || v_crt || ''' || to_char(' || tb.column_name || ',''dd/mm/yyyy'')';
elsif tb.data_type = 'DATETIME' then
v_sql := v_sql || ' || ''' || v_crt || ''' || to_char(' || tb.column_name || ',''dd/mm/yyyy hh24:mi:ss'')';
elsif tb.data_type = 'NUMBER' then
v_sql := v_sql || ' || ''' || v_crt || ''' || replace(' || tb.column_name || ',''.'','','')';
else
v_sql := v_sql || ' || ''' || v_crt || ''' || ' || tb.column_name;
end if;
end if;
end loop;

--Grava cabecalho no arquivo e/ou no parametro de saida
p_archive(1).line := v_line;
if p_path is not null and p_name_file is not null then
utl_file.put_line(v_file, v_line);
end if;

--Gravando registros no arquivo e/ou no parametro de saida
v_script := 'SELECT MIN(IDROWNUM), MAX(IDROWNUM) FROM ' || v_name_table;
execute immediate v_script into v_rg_min, v_rg_max;

if nvl(v_rg_min,0) > 0 and nvl(v_rg_max,0) > 0 then
for i in v_rg_min..v_rg_max
loop
v_script := 'SELECT ' || v_sql ||
' FROM ' || v_name_table ||
' WHERE IDROWNUM = ' || i;
execute immediate v_script into v_line;

--Grava linha no parametro de saida
p_archive(i+1).line := v_line;

--Grava linha no arquivo
if p_path is not null and p_name_file is not null then
utl_file.put_line(v_file, v_line);
end if;
end loop;
else
p_msg_error := 'Nao ha registros a serem gerados!';
end if;
end if;

--Fecha arquivo gerado
if p_path is not null and p_name_file is not null then
utl_file.fclose(v_file);
end if;

--Apaga tabela utilizada
v_script := 'DROP TABLE ' || v_name_table;
execute immediate v_script;
--

EXCEPTION
When others then
--Retorna mensagem de erro
p_msg_error := 'Erro: EXPORT_FILE.PR_EXPORT_FILE: ' || sqlerrm;

--Fecha arquivo no caso de erro
if p_path is not null and p_name_file is not null then
utl_file.fclose(v_file);
end if;

--Apaga tabela utilizada
begin
v_script := 'DROP TABLE ' || v_name_table;
execute immediate v_script;
exception
when others then null;
end;
--
END PR_EXPORT_FILE;

END EXPORT_FILE;
/

Exemplo de uso:

declare
--Declaracao da variavel de retorno
p_archive export_file.t_archive;
p_msg_error varchar2(500);
begin
--Chamada da procedure
export_file.pr_export_file('select * from dual'
,null
,null
,chr(9)
,p_archive,
,p_msg_error);

--Lendo a variavel
if p_archive.count > 0 then
for i in p_archive.first..p_archive.last
loop
dbms_output.put_line(p_archive(i).line);
end loop;
end if;
--
end;

Observação: De vez enquando caso ocorra erro verificar se a tabela com inicial “TMPEXPORTFILE” foi apagada.

Bom, é isso aí pessoal, qualquer dúvida entra em contato…

Abs.