Olá,
Nos artigos de Novembro/2007 e Dezembro/2009 e eu demonstrei de forma prática como gerar arquivos externos ao banco de dados através das packages UTL_FILE e DBMS_LOB, a utilização do objeto DIRECTORY, bem como a apresentação de uma função para geração de hash MD5 através da package DBMS_OBFUSCATION_TOOLKIT para geração de senhas e pequenos textos. Neste artigo, irei abordar novamente a utilização dos objetos DIRECTORY e da package DBMS_LOB quanto a geração de hash MD5. A diferença é que agora irei demonstrar como gerar um hash MD5 para dados do tipo LOB (BLOB, CLOB) através da package DBMS_CRYPTO disponível à partir do Oracle 10g.
O objetivo do artigo será demonstrar uma função de banco de dados que chamarei de md5_blob que receberá como parâmetro a coluna de uma tabela do tipo de dado BLOB. Em alguns casos ela poderá ser muito útil, como foi pra mim, quando precisei verificar em uma tabela a quantidade de documentos binários duplicados (*.doc, *.xls, *.pdf, *.jpg, etc.) que estavam armazenados em uma coluna do tipo BLOB.
Segue abaixo os comandos executados para criar um ambiente de forma a simular uma tabela contendo documentos binários. No caso em questão, irei armazenar um arquivo chamado imagem.jpg.
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:04:59 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create or replace directory arquivo_dir as '/tmp';
Diretório criado.
-- 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.
SQL> grant write,read on directory arquivo_dir to scott;
Concessão bem-sucedida.
SQL> grant execute on dbms_crypto to scott;
Concessão bem-sucedida.
-- Listando o arquivo que será carregado para a tabela tab_imagem
[oracle@linux tmp]$ ls -l
total 4
-rwxr--r-- 1 oracle oinstall 820 Jul 2 10:09 imagem.jpg
$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:14:32 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 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.
Pronto. O arquivo imagem.jpg foi carregado para a tabela conforme demonstrado abaixo.
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
Segue abaixo a função de banco de dados que será utilizada para gerar o hash MD5 da coluna do tipo BLOB.
SQL> create or replace function md5_blob (p_blob in blob)
2 return varchar2
3 is
4 begin
5 return dbms_crypto.hash (src => p_blob,
6 typ => dbms_crypto.hash_md5);
7 end;
8 /
Função criada.
Após a criação da função, poderemos ver abaixo através da instrução SELECT que o hash MD5 do conteúdo da coluna IMAGEM da tabela TAB_IMAGEM foi gerado com sucesso.
SQL> select id,nome,md5_blob(imagem) from tab_imagem;
ID NOME MD5_BLOB(IMAGEM)
---------- --------------- --------------------------------
1 imagem.jpg 4B812BDC6240D0770A12DA89E4630BF2
1 linha selecionada.
Apenas para comprovar que o hash MD5 que foi gerado é válido, utilizarei o comando md5sum no Linux para verificar que o valor do hash é o mesmo que foi gerado pelo Oracle.
$ md5sum imagem.jpg
4b812bdc6240d0770a12da89e4630bf2 imagem.jpg
Em relação à colunas do tipo CLOB, a função é praticamente idêntica, o que muda apenas é o tipo do parâmetro de entrada que deve ser definido para CLOB.
SQL> create or replace function md5_clob (p_clob in clob)
2 return varchar2
3 is
4 begin
5 return dbms_crypto.hash (src => p_clob,
6 typ => dbms_crypto.hash_md5);
7 end;
8 /
Função criada.
Nenhum comentário:
Postar um comentário