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

PACOTE UTL_FILE DO ORACLE

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.

UTL_FILE

Artigos relacionados