Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


sábado, 5 de dezembro de 2009

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

Por Eduardo Legatti

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




16 comentários:

David Siqueira disse...

Fala Eduardo, como tem passado?
Bem é meio "chover no molhado" dizer que o Post esta excelente, quando se trata de um profissional do seu nivel nem temos o que comentar. Mas falando um pouco do conteúdo do Post, eu gostei muito, visto que tenho pouco contato tanto com a DBMS_LOB quanto com a sua utilização, e com seus passos com ceteza ficrá mais simples caso eu necessite implementa-la ou até mesmo usa-la a titulo de testes.
Forte abraço tudo de bom!!!!

Helio disse...

Eduardo,

Muito bom o post. Só gostaria de esclarecer uma coisa: existe como eu pegar de um diretório local? De uma pasta 'C:/temp' por exemplo.
Estou com a necessidade de guardar na tabela um arquivo pdf e que não estará no servidor do bd e sim na máquina local.

Já pesquisei bastante e não consegui chegar em uma conclusão a respeito.

Fico no aguardo. Obrigado!

Eduardo Legatti disse...

Olá Helio,

Localmente? Via PL/SQL acho que você não vai encontrar nada a respeito. A única forma de você fazer isso vai ser utilizando ferramentas gráficas PL/SQL Developer, TOAD, etc...

Abraços e até mais ...

Ellen disse...

Olá Eduardo,

Seu Post foi realmente muito útil, obrigada por compartilhar!!!
Consegui entender muitas coisas sobre UTL_FILE, porém estou com uma dúvida: você disse que eu posso usar o UTL_FILE_DIR ou o objeto DIRECTORY, existe como no mesmo banco eu utilizar essas duas opções? Pergunto isso pois como eu tenho objetos DIRECTORY criados, nao consigo abrir os arquivos somente com o endereço ou UTL_FILE_DIR

Eduardo Legatti disse...

Olá Ellen,

Não há nenhuma restrição em utilizar as duas opções no mesmo banco de dados. Eu apenas quis demonstrar um e outro ;-)

Abraços e até mais ...

Paulo Souza disse...

Olá Eduardo,

Já existe algum modo de armazenar um arquivo local em tabela do Oracle?

Após muitas pesquisas, optei por perguntar a respeito.

Desde já, grato pela atenção dispensada.

Eduardo Legatti disse...

Olá Paulo,

Se você está se referindo em enviar um arquivo da sua máquina (local) para uma tabela no servidor Oracle, então somente utilizando ferramentas gráficas.

Abraços e até mais ...

Anônimo disse...

Boa tarde, qual o impacto em armazenar arquivos blob em disco ou em banco para o backup?
Obrigada

Eduardo Legatti disse...

Olá Anônimo,

Vejo que a sua rotina de backup será um pouco mais complexa e terá uma passo a mais, pois, além de realizar o backup do banco de dados, você terá que realizar o backup dos arquivos externos. Pessoalmente, não gosto da idéia de gravar arquivos em disco fora do banco de dados. Por exemplo, perdemos a recuperabilidade desta informação (point in time recovery) caso haja necessidade de algum recover do banco de dados. Hoje em dia, com o Oracle ScureFiles e mantendo o conteúdo do LOB (BLOB/CLOB) em tablespaces específicas, não vejo motivos para realizar essa separação. para maiores informações veja os artigos Um pouco sobre SecureFile LOBS no Oracle 11g e Migrando para o SecureFile LOBS no Oracle 11g com o DBMS_REDEFINITION

Abraços

Legatti

Edmar Carvalhaes disse...

Oi amigo tudo bem? Eu preciso disto mas é para a versão do SQL Server, sabe onde eu vou achar isto? Eu preciso exatmento disto.

Eduardo Legatti disse...

Olá Edmar,

No SQL Server, você pode dar uma pesquisada sobre Linked Servers ou até mesmo sobre BULK INSERT, no caso de enviar conteúdo de arquivos externos para dentro do banco de dados.

Exemplo de um arquivo .csv:

BULK INSERT meubanco.dbo.Employees
FROM 'C:\Data\meuarquivo.txt'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n'
);

Abraços,

Legatti

Unknown disse...

Boa tarde Eduardo, Primeiramente quero te parabenizar pelo excelente post, muito bom.
Mas fiquei com uma duvida, uma tabela onde guardo as imagens referente ao roteiro de entrega em meu banco de dados oracle 10.2.0, queria exportar essas imagens para um lugar físico na máquina que está o banco de dados, ex c:\fotos, existe essa possibilidade ?

Eduardo Legatti disse...

Olá Guilherme,

No artigo de Janeiro/2010 eu abordo como salvar o conteúdo de uma coluna BLOB para um arquivo em disco.

http://eduardolegatti.blogspot.com.br/2010/01/salvando-o-conteudo-de-um-campo-blob.html

Abraços e até mais

Legatti

Eduardo Legatti disse...

Olá Wilson,

Alguns anos atrás tive que fazer algo parecido e não teve jeito, ou seja, a solução era códigos em JAVA mesmo. Acredito que usei um pacote chamado PLSQL_OSCOMMAND_1.0.0RC1 que acredito que é o que você deve ter achado.

Outra coisa que você pode tentar, mas também a solução é usando códigos JAVA é verificar os links abaixo.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:439619916584
http://mportes.blogspot.com.br/ (Leitura de Arquivos pelo PL/SQL e Java)

Abraços

Legatti

BRUNO disse...

Olá,
Tenho uma procedure que gera o arquivo txt, acredito quer por estar instalado em ambiente Linux, o arquivo salvo fica no padrão Unix(LF), haveria alguma forma de converter o arquivo para Windows(CRLF) no momento de gerar o arquivo?

Eduardo Legatti disse...

Olá Bruno,

Acredito que você terá que fazer uma conversão no próprio Linux após a geração do arquivo. Tente pesquisar por "UNIX / Linux Convert DOS Newlines" e pelo utilitário unix2dos do próprio Linux.

Abraços,

Legatti

Postagens populares