UTL_FILE: O pacote que te permite brincar com arquivos no Oracle
Neste artigo, eu vou fingir manjo de PL/SQL e vou te ensinar o que é o UTL_FILE, para que ele serve, como usar e quais são os casos de uso mais legais. Também vou mostrar alguns exemplos práticos de como brincar com arquivos no sistema operacional usando o UTL_FILE. No final, vou te mostrar as fontes que eu usei para copiar e colar este artigo. Vamos nessa!
O que é o UTL_FILE?
O UTL_FILE é um pacote que o Oracle te dá de graça para você poder ler e escrever arquivos no sistema operacional usando o PL/SQL. Com o UTL_FILE, você pode criar, abrir, fechar, ler, escrever, apagar e renomear arquivos de texto ou binários, usando uns comandinhos bem simples e fáceis de decorar.
O UTL_FILE é um pacote que já vem instalado em qualquer banco de dados Oracle, mas isso não significa que você pode sair usando ele sem mais nem menos. Você precisa ter alguns cuidados e permissões, que eu vou te contar agora.
Como usar o UTL_FILE?
Para usar o UTL_FILE, você precisa seguir alguns passos:
- Escolher um diretório no sistema operacional onde você quer guardar ou pegar os arquivos. Esse diretório precisa ter as permissões certas para o usuário do Oracle que vai usar o UTL_FILE. Se você não tiver permissão, peça para o seu administrador de banco de dados, ou para o seu hacker de confiança.
- Criar um objeto de diretório no banco de dados que aponte para o diretório do sistema operacional que você escolheu. Esse objeto de diretório precisa ter um nome bonito e único, e pode ser criado pelo usuário SYS ou por qualquer usuário que tenha o privilégio de criar qualquer coisa.
- Dar o privilégio READ e/ou WRITE para o objeto de diretório para o usuário do Oracle que vai usar o UTL_FILE. Esse privilégio pode ser dado pelo usuário SYS ou pelo dono do objeto de diretório. Se você não tiver esse privilégio, você vai levar um belo de um erro quando tentar usar o UTL_FILE.
- Abrir um arquivo usando a função UTL_FILE.FOPEN, que recebe como parâmetros o nome do objeto de diretório, o nome do arquivo, o modo de abertura (R para ler, W para escrever, A para anexar) e opcionalmente o tamanho do buffer e o conjunto de caracteres. Essa função retorna um identificador de arquivo, que é um nomezinho que você vai usar nas outras operações.
- Ler ou escrever no arquivo usando os procedimentos e funções do UTL_FILE, como UTL_FILE.GET_LINE, UTL_FILE.PUT_LINE, UTL_FILE.PUT, UTL_FILE.NEW_LINE, UTL_FILE.PUTF, UTL_FILE.FFLUSH, UTL_FILE.FCOPY, UTL_FILE.FREMOVE, UTL_FILE.FRENAME, etc. Esses procedimentos e funções recebem como parâmetro o identificador de arquivo e outros parâmetros que dependem de cada operação.
- Fechar o arquivo usando o procedimento UTL_FILE.FCLOSE, que recebe como parâmetro o identificador de arquivo. É muito importante fechar o arquivo depois de terminar as operações, para não deixar o arquivo aberto e causar problemas. Se você esquecer de fechar o arquivo, o Oracle vai ficar bravo com você e vai te dar um puxão de orelha.
Quais são os casos de uso mais comuns do UTL_FILE?
O UTL_FILE pode ser usado para várias coisas, como:
- Gerar relatórios em formato de texto ou CSV, que você pode mandar por e-mail, FTP ou pombo-correio.
- Importar ou exportar dados entre o banco de dados Oracle e outros sistemas, usando arquivos de texto ou binários como pontes.
- Processar arquivos externos que tenham informações importantes para o banco de dados, como logs, configurações, parâmetros, etc.
- Integrar o banco de dados Oracle com outras aplicações que usem arquivos como forma de comunicação, como sistemas operacionais, linguagens de programação, planilhas, etc.
Exemplos práticos de uso do UTL_FILE
Agora eu vou mostrar alguns exemplos práticos de como usar o UTL_FILE para fazer algumas coisas legais. Para simplificar, eu vou usar o usuário SCOTT, que é um usuário que todo mundo conhece, e o diretório C:\TEMP, que é um diretório que todo mundo usa. Você pode mudar esses exemplos para o seu ambiente e necessidade.
Criar um objeto de diretório no banco de dados
Para criar um objeto de diretório no banco de dados que aponte para o diretório C:\TEMP, você pode usar o seguinte comando:
CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'C:\TEMP';
Esse comando cria um objeto de diretório chamado TEMP_DIR, que pode ser usado pelo UTL_FILE para acessar o diretório C:\TEMP. Note que o nome do objeto de diretório é arbitrário, mas precisa ser único no banco de dados. Você pode chamar de FRED_DIR, se quiser.
Dar o privilégio READ e WRITE para o objeto de diretório
Para dar o privilégio READ e WRITE para o objeto de diretório TEMP_DIR para o usuário SCOTT, você pode usar o seguinte comando:
GRANT READ, WRITE ON DIRECTORY TEMP_DIR TO SCOTT;
Esse comando permite que o usuário SCOTT possa ler e escrever arquivos no diretório C:\TEMP usando o UTL_FILE. Note que você pode dar apenas o privilégio READ ou apenas o privilégio WRITE, dependendo do que você quer fazer. Se você só quer ler, dê só o READ. Se você só quer escrever, dê só o WRITE. Se você quer os dois, dê os dois.
Abrir um arquivo para escrita
Para abrir um arquivo chamado RELATORIO.TXT para escrita no diretório C:\TEMP, você pode usar a seguinte função:
DECLARE
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN('TEMP_DIR', 'RELATORIO.TXT', 'W');
-- demais operações de escrita no arquivo
END;
Essa função atribui o identificador de arquivo v_file para o arquivo RELATORIO.TXT, que será criado no diretório C:\TEMP com o modo de abertura W, que significa escrita. Se o arquivo já existir, ele será sobrescrito. Você pode usar o modo A para anexar ao final do arquivo, se preferir. Mas cuidado para não anexar coisas que não devem ser anexadas.
Escrever no arquivo
Para escrever no arquivo RELATORIO.TXT, você pode usar os procedimentos e funções do UTL_FILE, como por exemplo:
UTL_FILE.PUT_LINE(v_file, 'Este é um relatório muito divertido gerado pelo UTL_FILE');
UTL_FILE.PUT(v_file, 'Data: ');
UTL_FILE.PUTF(v_file, '%s\n', SYSDATE);
UTL_FILE.NEW_LINE(v_file, 2);
UTL_FILE.PUT_LINE(v_file, 'A seguir, os dados da tabela EMP, que é uma tabela muito interessante:');
Esses procedimentos e funções escrevem no arquivo RELATORIO.TXT as linhas e os dados especificados. Note que você pode usar caracteres especiais, como \n para quebra de linha, e formatações, como %s para string, para deixar o arquivo mais bonito.
Fechar o arquivo
Para fechar o arquivo RELATORIO.TXT, você pode usar o seguinte procedimento:
UTL_FILE.FCLOSE(v_file);
Esse procedimento fecha o arquivo RELATORIO.TXT e libera o identificador de arquivo v_file. É muito importante fechar o arquivo depois de terminar as operações, para não deixar o arquivo aberto e causar problemas. Se você esquecer de fechar o arquivo, o Oracle vai ficar bravo com você e vai te dar um puxão de orelha.
Abrir um arquivo para leitura
Para abrir um arquivo chamado CONFIG.TXT para leitura no diretório C:\TEMP, você pode usar a seguinte função:
DECLARE
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN('TEMP_DIR', 'CONFIG.TXT', 'R');
-- demais operações de leitura no arquivo
END;
Essa função atribui o identificador de arquivo v_file para o arquivo CONFIG.TXT, que será aberto no diretório C:\TEMP com o modo de abertura R, que significa leitura. Se o arquivo não existir, um erro será gerado. E você vai ficar triste.
Ler do arquivo
Para ler do arquivo CONFIG.TXT, você pode usar os procedimentos e funções do UTL_FILE, como por exemplo:
DECLARE
v_line VARCHAR2(100);
BEGIN
LOOP
UTL_FILE.GET_LINE(v_file, v_line);
DBMS_OUTPUT.PUT_LINE(v_line);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(v_file);
END;
Esse bloco de código lê cada linha do arquivo CONFIG.TXT e imprime na tela usando o DBMS_OUTPUT. O loop termina quando não há mais dados no arquivo, gerando a exceção NO_DATA_FOUND, que é tratada fechando o arquivo. Se você quiser fazer algo mais útil com as linhas do arquivo, como inserir em uma tabela ou validar algum parâmetro, você pode fazer isso dentro do loop.
Conclusão
Espero que este artigo tenha sido útil e divertido para você, e que você tenha se interessado pelo UTL_FILE, um pacote muito poderoso e versátil do Oracle. Se você quiser saber mais sobre o UTL_FILE, você pode consultar as referências que eu usei para escrever este artigo, ou pesquisar na internet por mais tutoriais e dicas. O UTL_FILE é um pacote que vale a pena conhecer e explorar, pois ele pode facilitar muito a sua vida como desenvolvedor ou administrador de banco de dados Oracle.
Valeuuuuu !
Referências
- Oracle Database PL/SQL Packages and Types Reference
- Oracle-base – UTL_FILE
- PL/SQL Tutorial – UTL_FILE
- Stack Overflow – How to use UTL_FILE
