Social Icons

31 de jul de 2014

Gerando arquivos DSV com PL/SQL


Olá pessoal,

      No artigo de hoje vou mostrar como gerar um arquivo texto em formato DSV (Delimited Separated Values) de forma simples e rápida, utilizando apenas uma função que criaremos no Banco de Dados. O arquivo será gerado a partir do resultado de uma instrução SQL e terá apenas o conteúdo das linhas de uma tabela com os valores das colunas separados por algum caractere, como por exemplo, o caractere ponto e vírgula (;).


     A função retorna a quantidade de linhas gravadas no arquivo DSV e usa os seguintes packages:
   
          1- DBMS_SQL:
              Pacote utilizado para ler e executar dinâmicamente a instrução SQL que é recebida na função através do parâmetro p_query. Também é utilizada para formatar o conteúdo do arquivo DSV, utilizando um caractere separador de valores recebido no parâmetro p_separator;
   
          2- UTL_FILE:
              Pacote utilizado para gravar o arquivo DSV com o nome especificado no valor do parâmetro p_filename, no diretório especificado no valor do parâmetro p_dir. Um detalhe muito importante é que o diretório onde o arquivo será gravado, deverá ser um dos seguintes valores:
   
              a) Diretório informado no parâmetro de sistema utl_file_dir (ver mais informações no item 3 do artigo Segurança no Oracle 10G - Top 5 Basic Security);
   
              b) Objeto diretório do Oracle Database (ver mais informações no passo 2 do artigo Criando Tabelas Externas).


     Segue abaixo o código da função:
 
create or replace FUNCTION FC_GRAVAR_ARQUIVO_DSV
            p_query     in varchar2,
            p_separator in varchar2 default ',',
            p_dir       in varchar2 ,
            p_filename  in varchar2 )
    return number
    is
       l_output        utl_file.file_type;
       l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(2000);
       l_status        integer;
       l_colCnt        number default 0;
       l_separator     varchar2(10) default '';
       l_cnt           number default 0;
   begin
       l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 
       dbms_sql.parse(  l_theCursor,  p_query,
                                            dbms_sql.native );
 
       for i in 1 .. 255 loop
           begin
               dbms_sql.define_column( l_theCursor, i,
                                       l_columnValue, 2000 );
               l_colCnt := i;
           exception
               when others then
                   if ( sqlcode = -1007 ) then exit;
                   else
                       raise;
                   end if;
           end;
       end loop;
 
       dbms_sql.define_column( l_theCursor, 1,
                               l_columnValue, 2000 );
 
       l_status := dbms_sql.execute(l_theCursor);
 
       loop
           exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
           l_separator := '';
          for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i,
                                      l_columnValue );
               utl_file.put( l_output,
                             l_separator || l_columnValue );
               l_separator := p_separator;
           end loop;
           utl_file.new_line( l_output );
           l_cnt := l_cnt+1;
       end loop;
       dbms_sql.close_cursor(l_theCursor);
 
       utl_file.fclose( l_output );
       RETURN L_CNT;
end;
  
        E abaixo um exemplo de como utilizá-la:

      select FC_GRAVAR_ARQUIVO_DSV('select * FROM HR.EMPLOYEES',
                             ';', 'RELAT_DIR', 'employees.dsv') from dual;

     Resultado:
          O arquivo employees.dsv contendo os registros da tabela HR.EMPLOYEES e campos separados pelo caractere ; será criado no objeto diretório RELAT_DIR. Importante: o dono da função tem que ter o privilégio READ,WRITE neste objeto.
   
Conclusão
     A função sugerida neste artigo é uma ótima ferramenta para quem precisa automatizar a geração de arquivos de dados com o objetivo de integrar sistemas. Quando houver essa necessidade, sugiro criar um scheduler job para executar periodicamente a função e salvar o arquivo no diretório de uma tabela externa, ou ainda, enviar o arquivo por e-mail para o(s) interessado(s) que será(ão) responsável(is) pela integração.


Bom pessoal, por hoje é só!

[]s
 

    
     

2 comentários:

  1. Olá Fábio,

    Isso já ajuda muito, parabéns pelo artigo e obrigado.

    Nesse caso o arquivo é gerado sem as colunas das tabelas, seria possível acrescentar nesse mesmo processo?

    ResponderExcluir
    Respostas
    1. Hudson, me parece que você quis dizer como fazer para incluir uma linha extra que seria um cabeçalho contendo o nome de cada coluna, certo? Se sim, basta acrescentar outro SELECT contendo o nome das colunas e fazer um UNION ALL com o SELECT dos dados. Exemplo:

      SELECT 'first_name' as first_name, 'last_name' as last_name FROM DUAL
      UNION ALL
      select first_name, last_name FROM HR.EMPLOYEES;

      Ok?

      Excluir

 

Meus últimos Links Favoritos

Suporte remoto alunos

Seguidores

Meu One Drive (antigo Sky Drive)