Pacote UTL_FILE

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	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	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

/*
       A     Append Text
       AB    Append Byte Mode
       R     Read Text
       RB    Read Byte Mode
       W     Write Text
       WB    Write Byte Mode
     */

Exemplos:
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;

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.
Fonte: http://www.devmedia.com.br/articles/viewcomp.asp?comp=13129