Um pouco do pacote UTL_FILE, DBMS_LOB e objeto DIRECTORY. Gerando e carregando conteúdo de arquivos externos ao Oracle ...

Olá,

Neste artigo irei demonstrar através de exemplos práticos como gerar arquivos externos ao banco de dados, bem como ler o conteúdo dos mesmos através da package UTL_FILE disponível desde a versão 7.3 do banco de dados Oracle. Em resumo, a package UTL_FILE permite que programas escritos em PL/SQL acessem arquivos do sistema operacional para leitura e escrita. Irei também demonstrar como carregar arquivos binários para dentro de tabelas de banco dados fazendo uso da package DBMS_LOB. Por falar em arquivos binários, o SQL*Plus da versão 11g já nos permite visualizar dados de colunas do tipo BLOB o que, nas versões anteriores, não era possível fazendo com que o SQL*Plus emitisse a mensagem "SP2-0678: Coluna ou tipo de atributo não pode ser exibido pelo SQL*Plus". Com isso, não precisaremos mais ficar removendo as colunas do tipo de dado BLOB em uma consulta SQL.

Enfim, será abordado tanto o uso do já obsoleto parâmetro UTL_FILE_DIR, quanto a criação do objeto DIRECTORY responsável por armazenar o caminho (diretório) de sistema operacional onde reside o banco de dados Oracle, de forma a permitir a escrita e leitura de arquivos.

No caso de se utilizar o parâmetro UTL_FILE_DIR, o comando abaixo deverá ser emitido pelo DBA (se estiver usando spfile) ou o arquivo de inicialização alterado manualmente init[SID].ora. Após a alteração, o banco de dados precisará ser reiniciado.

alter system set utl_file_dir = '[caminho_diretorio]' scope=spfile;

Vamos então aos exemplos práticos. Como demonstrado abaixo, o banco de dados está com o parâmetro UTL_FILE_DIR setado com o caminho "/tmp" que é um diretório na qual o usuário de sistema operacional "oracle" possui permissões de leitura/escrita. Para essas demonstrações, estou utilizando o sistema operacional Linux CentOS release 5.2 e o banco de dados Oracle 11g (11.1.0.7.0).

C:\>sqlplus system/*****

SQL*Plus: Release 11.1.0.6.0 - Production on Sab Dez 5 13:15:32 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter utl_file_dir

NAME                          TYPE        VALUE
----------------------------- ----------- ----------------------------
utl_file_dir                  string      /tmp

No exempo abaixo irei gerar um arquivo simples que irei chamar de arq_teste.txt contendo algumas linhas de texto. Para isso, usarei a procedure PUT_LINE da package UTL_FILE.

-- Gerando o arquivo de texto externo ao banco de dados
SQL> declare
  2    v_arquivo utl_file.file_type;
  3  begin
  4    v_arquivo := utl_file.fopen('/tmp', 'arq_teste.txt', 'W');
  5    utl_file.put_line(v_arquivo, 'linha 1');
  6    utl_file.put_line(v_arquivo, 'linha 2');
  7    utl_file.put_line(v_arquivo, 'linha 3');
  8    utl_file.put_line(v_arquivo, 'linha 4');
  9    utl_file.put_line(v_arquivo, 'linha 5');
 10    utl_file.fclose(v_arquivo);
 11  exception
 12    when utl_file.invalid_path then
 13       raise_application_error(-20000, 'Atenção! O caminho informado não existe.');
 14  end;
 15  /

Procedimento PL/SQL concluído com sucesso.

Acessando o sistema operacional, podemos ver que o arquivo arq_teste.txt foi criado e seu conteúdo populado conforme o script PL/SQL acima.

[oracle@linux tmp]$ ls -l
total 8
-rw-r--r-- 1 oracle oinstall   40 Dez 5 13:16 arq_teste.txt

[oracle@linux tmp]$ cat arq_teste.txt
linha 1
linha 2
linha 3
linha 4
linha 5

Para que possamos ler o conteúdo do arquivo que foi gerado no sistema operacional através de PL/SQL, farei uso da procedure GET_LINE da package UTL_FILE.

-- Lendo o conteúdo do arquivo de texto gerado
SQL> set serveroutput on
SQL> declare
  2    v_linha varchar2(2000) := '';
  3    v_arquivo utl_file.file_type;
  4  begin
  5    v_arquivo := utl_file.fopen('/tmp', 'arq_teste.txt', 'R');
  6    loop begin
  7      utl_file.get_line(v_arquivo, v_linha);
  8      dbms_output.put_line('linha lida=' || v_linha);
  9      exception
 10        when no_data_found then
 11        utl_file.fclose(v_arquivo);
 12        exit;
 13      end;
 14    end loop;
 15  end;
 16  /
linha lida=linha 1
linha lida=linha 2
linha lida=linha 3
linha lida=linha 4
linha lida=linha 5

Procedimento PL/SQL concluído com sucesso.

Da mesma forma que utilizamos o parâmetro UTL_FILE_DIR, poderíamos ter utilizado o objeto de banco de dados DIRECTORY. O objetivo do objeto directory (diretório) é armazenar o caminho de um diretório de sistema operacional, assim como o parâmetro UTL_FILE_DIR. A grande vantagem é que podemos criar vários objetos que apontam para diferentes diretórios do sistema operacional, além de não termos que ter de efetuar o shutdown/startup da instância após a sua criação como acontece com parâmetro UTL_FILE_DIR. Vale a pena salientar que podemos também configurar múltiplos caminhos com o parâmetro UTL_FILE_DIR bastando, para isso, definirmos mais de uma entrada no arquivo de inicialização da instância como demonstrado no exemplo abaixo:

utl_file_dir="/u01/oradata/prod/utl_dir/"
utl_file_dir="/u02/oradata/prod/utl_dir/"
utl_file_dir="/u03/oradata/prod/utl_dir/"

No mais, o objeto DIRECTORY é global no banco de dados, ou seja, não é de propriedade de nenhum usuário de banco de dados específico (a não ser do próprio SYS). Para que os usuário de banco de dados possam utilizá-lo, será necessário conceder os privilégios READ (leitura) e/ou WRITE (escrita) dependendo de cada uso.

Como já demonstrado nos exemplos acima, irei realizar a mesma operação de escrita e leitura em arquivos do sistema operacional mas, agora, irei fazer uso do objeto DIRECTORY (diretório).

SQL> create or replace directory arquivo_dir as '/tmp';

Diretório criado.

Vale a pena salientar que os demais usuários de banco de dados deverão ter os privilégios apropriados, como demonstrado pelo comando abaixo, afim de terem acesso ao objeto DIRECTORY.

grant read, write on directory [nome_diretorio] to [usuarios...];

Continuando com a demonstração, irei realizar os mesmos procedimentos, mas agora utilizando o objeto directory ARQUIVO_DIR em vez do caminho fixo definido no parâmetro UTL_FILE_DIR.

-- Gerando o arquivo de texto externo ao banco de dados
SQL> declare
  2    v_arquivo utl_file.file_type;
  3  begin
  4    v_arquivo := utl_file.fopen('ARQUIVO_DIR', 'arq_teste.txt', 'W');
  5    utl_file.put_line(v_arquivo, 'linha 1');
  6    utl_file.put_line(v_arquivo, 'linha 2');
  7    utl_file.put_line(v_arquivo, 'linha 3');
  8    utl_file.put_line(v_arquivo, 'linha 4');
  9    utl_file.put_line(v_arquivo, 'linha 5');
 10    utl_file.fclose(v_arquivo);
 11  exception
 12    when utl_file.invalid_path then
 13       raise_application_error(-20000, 'Atenção! O diretório informado não existe.');
 14  end;
 15  /

Procedimento PL/SQL concluído com sucesso.

-- Lendo o conteúdo do arquivo de texto gerado
SQL> set serveroutput on
SQL> declare
  2    v_linha varchar2(2000) := '';
  3    v_arquivo utl_file.file_type;
  4  begin
  5    v_arquivo := utl_file.fopen('ARQUIVO_DIR', 'arq_teste.txt', 'R');
  6    loop begin
  7      utl_file.get_line(v_arquivo, v_linha);
  8      dbms_output.put_line('linha lida=' || v_linha);
  9      exception
 10        when no_data_found then
 11        utl_file.fclose(v_arquivo);
 12        exit;
 13      end;
 14    end loop;
 15  end;
 16  /
linha lida=linha 1
linha lida=linha 2
linha lida=linha 3
linha lida=linha 4
linha lida=linha 5

Procedimento PL/SQL concluído com sucesso.

Caso seja informado um objeto DIRECTORY que não exista, a mensagem de erro abaixo será emitida:

SQL> declare
  2    v_arquivo utl_file.file_type;
  3  begin
  4    v_arquivo := utl_file.fopen('ARQUIVO_DIR_ERRO', 'arq_teste.txt', 'W');
  5    utl_file.put_line(v_arquivo, 'linha 1');
  6    utl_file.put_line(v_arquivo, 'linha 2');
  7    utl_file.put_line(v_arquivo, 'linha 3');
  8    utl_file.put_line(v_arquivo, 'linha 4');
  9    utl_file.put_line(v_arquivo, 'linha 5');
 10    utl_file.fclose(v_arquivo);
 11  exception
 12    when utl_file.invalid_path then
 13       raise_application_error(-20000, 'Atenção! O diretório informado não existe.');
 14  end;
 15  /
declare
*
ERRO na linha 1:
ORA-20000: Atenção! O diretório informado não existe.
ORA-06512: em line 13

Como parte de outra demonstração, irei agora criar uma stored procedure de banco de dados que carregará um arquivo binário (imagem.jpg) para dentro de uma tabela de teste. Para isso, vou criar uma coluna do tipo de dado BLOB e fazer uso de alguns sub-programas disponíveis na package DBMS_LOB. No mais, irei executar os procedimentos abaixo:

-- Criando o usuário de teste
SQL> create user scott identified by tiger
  2  default tablespace users
  3  quota unlimited on users;

Usuário criado.

-- Concedendo privilégios básicos
SQL> grant create session,create table, create procedure to scott;

Concessão bem-sucedida.

-- Concedendo privilégios para uso do diretório
SQL> grant read, write on directory ARQUIVO_DIR to scott;

Concessão bem-sucedida.

Na saída do SQL abaixo, podemos ver que o usuário SCOTT possui os privilégio READ (leitura) e WRITE (escrita) para o objeto directory ARQUIVO_DIR:

SQL> select grantor,grantee,table_name,privilege
  2  from
  3  all_tab_privs
  4  where table_name = 'ARQUIVO_DIR';

GRANTOR             GRANTEE              TABLE_NAME             PRIVILEGE
------------------- -------------------- ---------------------- ----------------------
SYS                 SYSTEM               ARQUIVO_DIR            EXECUTE
SYS                 SYSTEM               ARQUIVO_DIR            READ
SYS                 SYSTEM               ARQUIVO_DIR            WRITE
SYSTEM              SCOTT                ARQUIVO_DIR            READ
SYSTEM              SCOTT                ARQUIVO_DIR            WRITE

-- Conectando com o usuário SCOTT
SQL> connect scott/tiger
Conectado.

-- Criando a tabela de teste
SQL> create table tab_imagem (
  2    id number primary key,
  3    nome varchar2(20),
  4    imagem blob
  5  );

Tabela criada.

-- Listando o arquivo que será carregado para a tabela tab_imagem
[oracle@linux tmp]$ ls -l
total 4
-rw-r--r-- 1 oracle oinstall 809 Dez 5 13:25 imagem.jpg

-- Criando a stored procedure carrega_imagem
SQL> create or replace procedure carrega_imagem (p_nome_arquivo in tab_imagem.nome%type) as
  2   v_bfile bfile;
  3   v_blob blob;
  4  begin
  5    insert into tab_imagem (id,nome,imagem)
  6    values (1,p_nome_arquivo,empty_blob())
  7    return imagem into v_blob;
  8    v_bfile := bfilename('ARQUIVO_DIR',p_nome_arquivo);
  9    dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
 10    dbms_lob.loadfromfile(v_blob,v_bfile,dbms_lob.getlength(v_bfile));
 11    dbms_lob.fileclose(v_bfile);
 12    commit;
 13  end;
 14  /

Procedimento criado.

-- Carregando o arquivo para a tabela tab_imagem
SQL> execute carrega_imagem('imagem.jpg');

Procedimento PL/SQL concluído com sucesso.

-- Verificando o tamanho (em bytes) do arquivo carregado
SQL> select dbms_lob.getlength(imagem) from tab_imagem;

DBMS_LOB.GETLENGTH(IMAGEM)
--------------------------
                       809

SQL> exit
Desconectado de Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Aproveitando que utilizei função DBMS_LOB.GETLENGTH acima, tem muita gente que acha que o valor retornado é a quantidade de caracteres, o que não é verdade. O valor retornado nada mais é do que o tamanho do arquivo em bytes que foi carregado do sistema operacional, conforme mostrado abaixo:

[oracle@linux tmp]$ ls -l
total 4
-rw-r--r-- 1 oracle oinstall 809 Dez 5 13:25 imagem.jpg

Para finalizar, como mencionado no início do artigo, o SQL*Plus das versões anteriores ao Oracle 11g, não permitiam que o usuário visualizasse o conteúdo de um campo do tipo de dado BLOB:

-- Utilizando o SQL*Plus da versão 10g
C:\>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.4.0 - Production on Sab Dez 5 13:28:19 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab_imagem;
SP2-0678: Coluna ou tipo de atributo não pode ser exibido pelo SQL*Plus

Na versão 11g do SQL*Plus, agora é possível visualizarmos o conteúdo de um campo do tipo de dados BLOB como demonstrado abaixo. Contudo, a única vantagem que eu vejo em relação ao SQL*Plus das versões anteriores é de que não precisaremos mais remover as colunas do tipo de dado BLOB na hora de emitirmos um comando SELECT, embora que, visualizar dados binários não faz lá muito sentido ...

-- Utilizando o SQL*Plus da versão 11g
C:\>sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Sab Dez 5 13:32:06 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab_imagem;

        ID NOME             IMAGEM
---------- ---------------- ------------------------------------------------------------
         1 imagem.jpg       47494638396109000600F70000000000B1B1B1AFAFAFA3A3A39999997777
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            27CCCCCC191919171717111111C0C0C00D0D0D090909070707AAAAAAA4A4
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            58FFFFFF4848484444444040403838383232322E2E2EDDDDDD2828282626
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            B30000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            002C000000000900060000080E0049081C48B0A0C1830813260C08003B00