Gerando arquivos DSV com PL/SQL

ATUALIZADO EM 17/5/19

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á o conteúdo das linhas de seu resultado com cada valor de coluna separado por algum caractere definido por você, como por exemplo, o caractere ponto e vírgula (;). Opcionalmente, é possível incluir no arquivo DSV um cabeçalho gerado de forma dinâmica (ver parâmetro "p_inclui_cabecalho" da funçãoo que mostrarei adiante), que contém a descrição das colunas inclusas no SQL. 

     Para gerar o arquivo DSV utilizaremos a função FC_GRAVAR_ARQUIVO_DSV, que retorna a quantidade de linhas gravadas nele 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_dir               in varchar2,
                        p_filename          in varchar2,                        
                        p_separator         in varchar2 default ',',
                        p_inclui_cabecalho  in varchar2 default 'N'
                        )
    return number
    is
       l_output        utl_file.file_type;
       l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(4000);
       l_status        integer;
       l_colCnt        number default 0;
       l_separator     varchar2(10) default '';
       l_cnt           number default 0;       
       l_columns       dbms_sql.desc_tab;
   begin
      dbms_output.enable(null);
      --dbms_output.put_line(p_query);

      l_output := utl_file.fopen( p_dir, p_filename, 'w' );
      
      -- faz parse no SQL:
      dbms_sql.parse(  l_theCursor,  p_query,
                                            dbms_sql.native );
            
      -- verifica se deve incluir cabecalho com nome das colunas do SQL:
      IF p_inclui_cabecalho = 'S' THEN        
          -- recupera nomes das colunas do SQL e qtde total de colunas:  
          dbms_sql.describe_columns(l_theCursor, l_colCnt, l_columns);
      
          -- efetua loop para gravar no arquivo os nomes das colunas
          for i in 1 .. l_columns.count loop
              --dbms_output.put_line(l_columns(i).col_name);
              utl_file.put( l_output, l_columns(i).col_name);
              -- grava separador de colunas enquanto nao for ultima coluna:
              if i < l_columns.count then 
                    utl_file.put( l_output, p_separator );
              end if;
          end loop;
          utl_file.new_line( l_output );
      END IF;
     
      -- percorre todas as colunas:
      for i in 1 .. 255 loop
           begin
               dbms_sql.define_column( l_theCursor, i,
                                       l_columnValue, 4000 );
               l_colCnt := i;
           exception
               when others then
                   if ( sqlcode = -1007 ) then exit;
                   else
                       raise;
                   end if;
           end;
      end loop;
      
      -- executa o SQL:  
      l_status := dbms_sql.execute(l_theCursor);

      -- recupera valores e grava-os em arquivo texto conforme diretorio e filename especificados:
      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;
      
      -- libera recursos fechando o cursor e o arquivo:
      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',';', 'S') from dual;

     Resultado:

          O arquivo employees.dsv contendo um cabeçalho com  o nome de cada coluna utilizada no SQL + 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. É importante ressaltar que o 3º (p_separator) e 4º parâmetros (p_inclui_cabecalho) da função são opcionais. Caso você não forneça valor para eles serão usados os valores default que podem ser observados no cabeçalho da função.

     Essa função é bastante útil também para quem precisa descarregar no arquivo de dados uma grande quantidade de informações, pois ao tentar fazer isso através de ferramentas gráficas, como por exemplo, no "Oracle SQL Developer", se o tamanho dos dados for maior que a RAM disponível, normalmente o software irá travar. 

   
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ó! Espero que a função lhe seja útil.

[]s