UTL_FILE

 

O pacote UTL_FILE é um conjunto de componentes que tem como função permitir o acesso ou geração de arquivos externos ao banco de dados. Um exemplo seria importarmos scripts em SQL ou PL/SQL para o nosso sistema. O pacote é constituído de 18 componentes, nos quais são distribuídos da seguinte maneira: 7 Exception, 2 Function, 8 Procedure e 1 Type.

Para utilizarmos o pacote, teremos que interromper os serviços do banco de dados e modificar o arquivo initXE.ora (ORACLE 10G Express Edition), onde iremos acrescentar o parâmetro UTL_FILE_DIR, afim de determinar quais os diretórios do sistema está livre para o acesso.

Primeiramente, vamos informar ao banco de dados que podemos gravar e recuperar dados do sistema operacional, através dos seguintes passos:

--Conectar como DBA

CONNECT / AS SYSDBA

--Fechar o banco de dados

SHUTDOWN;

--Iniciar o banco de dos sem abrir

STARTUP MOUNT;

--Alterar o parâmetro UTL_FILE_DIR:

ALTER SYSTEM

    SET UTL_FILE_DIR = ‘*’

SCOPE = SPFILE;

            Agora vamos finalizar e inicializar novamente o banco de dados para assumir os novos parâmetros:

SHUTDOWN

STARTUP

SHOW PARAMETER UTL_FILE_DIR

 Ainda conectado com o usuário SYS (CONNECT / AS SYSDBA) dê privilégio de EXECUTE no pacote UTL_FILE para o usuário HR:

GRANT EXECUTE ON UTL_FILE TO HR;

 Podemos deixar para que todos os usuários tenham o privilégio:

GRANT EXECUTE ON UTL_FILE TO PUBLIC;

Podemos consultar todos os componentes do pacote: DESC UTL_FILE.

COMANDOS UTL_FILE

Procedimentos da package UTL_FILE

Funcionalidade

FOPEN

abre um arquivo para entrada ou saída, criando um arquivo de saída caso o arquivo especificado não exista

IS_OPEN

indica se determinado arquivo está ou não aberto

FCLOSE

fecha um arquivo

FCLOSE_ALL

fecha todos os arquivos abertos

GET_LINE

lê uma linha de um arquivo aberto

PUT

escreve uma linha no arquivo. Não acrescenta automaticamente o caractere de fim de linha

PUT_LINE

escreve uma linha no arquivo, acrescentando automaticamente o caractere de fim de linha

NEW_LINE

inclui o caractere de fim de linha no arquivo, o que irá gerar uma nova linha em branco

FFLUSH

escreve, fisicamente, todas as pendências para um arquivo

 

Exceções package UTL_FILE

Controle

INVALID_PATH

diretório ou nome de arquivo inválido

INVALID_MODE

o parâmetro de modo de abertura é inválido

INVALID_FILEHANDLE

especificador de arquivo inválido

INVALID_OPERATION

o arquivo não pode ser aberto ou a operação é inválida

READ_ERROR

ocorreu um erro do sistema operacional durante a leitura de um arquivo

WRITE_ERROR

ocorreu um erro do sistema operacional durante a gravação de um arquivo

INTERNAL_ERROR

erro não especificado no PL/SQL

NO_DATA_FOUND

nesse caso, é disparada quando o fim do arquivo é encontrado em processamento de leitura seqüencial de um arquivo de texto

 

 Exemplo: Roteiro para geração de arquivo texto:

DECLARE
 arquivo_saida                    UTL_File.File_Type;

Cursor Cur_Linha is
 --Definição do select que traz a linha completa concatenada a ser gravada
BEGIN
    arquivo_saida := UTL_File.Fopen('caminho completo’,’nm arquivo', 'w');

    For Reg_Linha in Cur_linha Loop
        UTL_File.Put_Line(arquivo_saida, Reg_linha.linha);
    End Loop;

    UTL_File.Fclose(arquivo_saida);
    Dbms_Output.Put_Line('Arquivo gerado com sucesso.');
EXCEPTION

      WHEN UTL_FILE.INVALID_OPERATION THEN
               Dbms_Output.Put_Line(‘Operação inválida no arquivo.');
               UTL_File.Fclose(arquivo_saida);
      WHEN UTL_FILE.WRITE_ERROR THEN
               Dbms_Output.Put_Line(‘Erro de gravação no arquivo.');
               UTL_File.Fclose(arquivo_saida);
      WHEN UTL_FILE.INVALID_PATH THEN
               Dbms_Output.Put_Line(‘Diretório inválido.');
               UTL_File.Fclose(arquivo_saida);
      WHEN UTL_FILE.INVALID_MODE THEN
               Dbms_Output.Put_Line(‘Modo de acesso inválido.');
               UTL_File.Fclose(arquivo_saida);
      WHEN Others THEN
               Dbms_Output.Put_Line('Problemas na geração do arquivo.');
               UTL_File.Fclose(arquivo_saida);
END;

 

Exemplo: Roteiro para leitura de arquivo texto:

DECLARE
    arquivo_ler                 UTL_File.File_Type;
    Linha                          Varchar2(100);
BEGIN
    arquivo_ler := UTL_File.Fopen('caminho completo’,’nm arquivo', 'r');
    Loop
        UTL_File.Get_Line(arquivo_ler, Linha);
  -- Manipulação da linha lida
    End Loop;

    UTL_File.Fclose(arquivo_ler);
    Dbms_Output.Put_Line('Arquivo processado com sucesso.');
EXCEPTION
    WHEN No_data_found THEN
               UTL_File.Fclose(arquivo_ler);
               Commit;
    WHEN UTL_FILE.INVALID_PATH THEN
               Dbms_Output.Put_Line(‘Diretório inválido.');
               UTL_File.Fclose(arquivo_ler);
    WHEN Others THEN
               Dbms_Output.Put_Line ('Problemas na leitura do arquivo.');
               UTL_File.Fclose(arquivo_ler);
END;

 
EXEMPLO PRÁTICO DE UTL_FILE

PASSOS:

1.    Conectar com o usuário SYSTEM: CONNECT SYSTEM/SENHA;
2.    Criar diretório: CREATE DIRECTORY DIRETORIO AS 'C:\DIRETORIO';
3.    Ir no Windows Explorer e criar uma pasta chamada DIRETÓRIO na unidade C;
4.    Conceder privilégio de leitura e gravação no diretório criado:

GRANT READ, WRITE ON DIRECTORY DIRETORIO TO HR;

5.    Conectar com o usuário HR: CONNECT HR/HR;
6.    Criar o procedimento abaixo:

DECLARE

 V_LINHA VARCHAR2(2000) := '';

 V_ARQUIVO UTL_FILE.FILE_TYPE;

BEGIN

 V_ARQUIVO := UTL_FILE.FOPEN('DIRETORIO', 'ARQUIVO.TXT', 'W');

 FOR i in 1..5 LOOP

 V_LINHA := 'LINHA ' || i || '!!';

 UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);

END LOOP;

 UTL_FILE.FCLOSE(V_ARQUIVO);

 END;

7.    Vá a unidade C:\DIRETÓRIO e visualize o conteúdo do arquivo.
 
Espero que tenham gostado. Próximo artigo vamos criar uma arquivo XML utilizando uma PL/SQL para extrair os dados do banco de dados. Até lá!