Utilizando UTL_FILE

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Bom dia senhores,

Estou precisando exportar dados para um arquivo. Estou fazendo isso com UTL_FILE.
O problema é que eu recebo um query e tenho que executar essa query salvando os dados no arquivo, tudo isso dinamicamente.
A query pode vir com diversas tabelas ou não, com diversas colunas ou não.

Alguém pode me ajudar nessa?
nelson.anchite
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 15
Registrado em: Ter, 07 Out 2014 10:24 am
Contato:

Cara, o UTL_FILE é bem bacana, mas tem uma limitação de tamanho de arquivo. Os dados contidos no arquivo só podem chegar até 32767. O que acha de fazer o uso do SPOOL.

Precisei fazer uma grande rotina com o UTL_FILE e me atendeu muito bem, mas já que você precisa sempre exportar o resultado de uma query talvez seja interessante o uso do SPOOL.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Como seria o SPOOL? não conheço.
nelson.anchite
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 15
Registrado em: Ter, 07 Out 2014 10:24 am
Contato:

Você poderia montar um script alocando suas querys e fazendo o uso desse comando.

http://www.dbanchite.com.br/2015/08/o-a ... apido.html
https://docs.oracle.com/cd/E12825_01/ep ... _spool.htm
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Eu posso fazer isso recebdo a query como parametro? Executo as querys com EXECUTE IMMEDIATE.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Eu fiz aqui, só que ele salvou a query, mas não salvou o resultado da query, os dados.
Utilizei o comando abaixo:

Selecionar tudo

spool c:\Tmp\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;
nelson.anchite
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 15
Registrado em: Ter, 07 Out 2014 10:24 am
Contato:

Executei o comando que você colocou aqui, e gravou certinho o resultado em csv.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

vixi.
Agora não sei.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

executei no SQL Developer. Será que é por isso?
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Seguinte, a utilização do SPOOL é muto mais lento que a utilização do UTL_FILE.
Vou ter que fazer isso pelo UTL_FILE. mAS COMO FAZER ISSO DINAMICAMENTE?
nelson.anchite
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 15
Registrado em: Ter, 07 Out 2014 10:24 am
Contato:

Grave as consultas em uma tabela.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Tem que ser em arquivo.
nelson.anchite
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 15
Registrado em: Ter, 07 Out 2014 10:24 am
Contato:

Gravar as querys em tabelas..E executa-las dentro de uma procedure que usa o UTL_FILE.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Não pode ser assim.
Estou fazendo com a procedure abaixo. Ainda não testei:


Selecionar tudo

create or replace procedure SP_TESTE_ARQUIVO (p_query     in varchar2,  
                                                     p_filename  in varchar2 ) is
begin

       vFILE := utl_file.fopen('/acfs/flx/tmp',  p_filename, 'w', 32767); -- Abre o arquivo para gravação. Se o arquivo não existir será criado. Se o arquivo existir, isso sobrescreverá.
  
       dbms_sql.parse(v_theCursor,  p_query, dbms_sql.native );  -- Analisa a instrução SQL
  

       for i in 1 .. 255 loop
           begin
               dbms_sql.define_column(v_theCursor, i, v_columnValue, 32000);
             
               v_colCnt := i;
           exception
               when others then
                    exit;
           end;
       end loop;


       dbms_sql.define_column(v_theCursor, 1, v_columnValue, 2000); -- Especifica as variáveis que estão a receber os valores SELECT. Da mesma forma como uma cláusula INTO faz para uma consulta estática.

       v_status := dbms_sql.execute(v_theCursor); -- Executa a instrução SQL.


       loop
           exit when (dbms_sql.fetch_rows(v_theCursor) <= 0); -- Recupera as linhas que satisfazem a consulta até o cursor não retornar mais linhas.
           v_separator := '';
           
          for i in 1 .. v_colCnt loop
          
               dbms_sql.column_value(v_theCursor, i, v_columnValue); -- Recupera os valores de cada coluna para serem gravados abaixo.

               utl_file.put(vFILE, v_separator || v_columnValue); -- Grava os dados no arquivo.
               v_separator := ';';
               
           end loop;
           utl_file.new_line(vFILE);
           v_cont := v_cont+1;
           
       end loop;
       dbms_sql.close_cursor(v_theCursor); -- Fecha o cursor.
  
       utl_file.fclose(vFILE); -- Fecha o arquivo.
       DBMS_OUTPUT.PUT_LINE('ARQUIVO: '|| v_cont);
       
       
       
end;
/
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Oi Cleberz,

Tem este tópico no ORACLE FORUM que explica como executar dinamicamente com o uso do DBMS_SQL.

https://community.oracle.com/thread/935498?tstart=0

Talvez atenda às suas necessidades. O exemplo que ele passa parece ser bem "maneiro" - rs

Abraços

Coutinho
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 12 visitantes