Um pouco do Oracle Text …
O Oracle Text é uma feature embutida no banco de dados que utiliza a linguagem SQL para indexar, pesquisar e analisar textos e documentos binários armazenados nas tabelas. Nas versões Oracle 11g, 10g e 9i, essa feature é conhecida como Oracle Text. No Oracle 8i é conhecida como Oracle Intermedia e, no Oracle 8, a mesma é conhecida como Oracle Context. Esta tecnologia oferece uma solução completa para pesquisa de textos na qual a mesma permite filtrar e extrair textos de conteúdos de diferentes formatos de documentos. O Oracle Text suporta mais de 150 formatos de documentos, incluindo os mais populares como os documentos Microsoft Office, além de formatos de arquivo do Adobe PDF, arquivos HTML e XML. Neste artigo irei demonstrar como gravar um documento binário (.doc) no banco de dados Oracle 10g Express Edition e o que fazer para poder realizar pesquisas no seu conteúdo. Para maiores informações sobre esta tecnologia, acesse a página oficial do Oracle Text no site da Oracle.
O arquivo que irei carregar para o banco de dados (arquivo.doc) possui o conteúdo mostrado na figura abaixo.
Então vamos realizar um teste prático …
C:\>sqlplus /@XE as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Qua Jun 11 08:52:26 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
-- Criação de um usuário para teste
SYS> create user scott identified by tiger default tablespace users quota unlimited on users;
Usuário criado.
SYS> grant connect,resource to scott;
Concessão bem-sucedida.
-- Criação de um diretório que indica a localização do arquivo (.doc)
SYS> create or replace directory documentos as 'c:\docs';
Diretório criado.
SYS> grant read on directory documentos to scott;
Concessão bem-sucedida.
SYS> connect scott/tiger@XE
Conectado.
-- Criação de uma tabela de teste
SCOTT> create table teste (
2 id number,
3 nome varchar2(20),
4 documento blob
5 );
Tabela criada.
-- Criação de uma stored procedure para carregar o arquivo para o banco de dados
SCOTT> create or replace procedure carrega_arquivo (p_file_name in teste.nome%type)
2 as
3 v_bfile bfile;
4 v_blob blob;
5 begin
6 insert into teste (id,nome,documento)
7 values (1,p_file_name,empty_blob())
8 return documento into v_blob;
9 v_bfile := bfilename('DOCUMENTOS',p_file_name);
10 dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
11 dbms_lob.loadfromfile(v_blob,v_bfile,dbms_lob.getlength(v_bfile));
12 dbms_lob.fileclose(v_bfile);
13 commit;
14 end;
15 /
Procedimento criado.
-- Carregando o arquivo para a tabela
SCOTT> execute carrega_arquivo('arquivo.doc');
Procedimento PL/SQL concluído com sucesso.
SCOTT> select id,nome from teste;
ID NOME
---------- --------------------
1 arquivo.doc
-- Verificando o tamanho do arquivo binário carregado
SCOTT> select dbms_lob.getlength(documento) bytes from teste;
BYTES
----------
19968
-- Criando um índice do tipo context que permitirá a pesquisa textual
SCOTT> create index i_teste_documento on teste (documento)
2 indextype is ctxsys.context parameters ('sync (on commit)');
Índice criado.
-- Verificando se houve algum erro na criação do índice
SCOTT> select * from ctx_user_index_errors;
não há linhas selecionadasAbaixo, podemos verificar que após a criação do índice I_TESTE_DOCUMENTO na coluna DOCUMENTO, algumas tabelas e índices adicionais foram criados
SCOTT> select table_name from user_tables;
TABLE_NAME
------------------------------
TESTE
DR$I_TESTE_DOCUMENTO$I
DR$I_TESTE_DOCUMENTO$K
DR$I_TESTE_DOCUMENTO$N
DR$I_TESTE_DOCUMENTO$R
SCOTT> select index_name,table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
I_TESTE_DOCUMENTO TESTE
SYS_IL0000014279C00003$ TESTE
DR$I_TESTE_DOCUMENTO$X DR$I_TESTE_DOCUMENTO$I
SYS_IL0000014285C00006$ DR$I_TESTE_DOCUMENTO$I
SYS_IL0000014290C00002$ DR$I_TESTE_DOCUMENTO$R
SYS_IOT_TOP_14288 DR$I_TESTE_DOCUMENTO$K
SYS_IOT_TOP_14293 DR$I_TESTE_DOCUMENTO$N
7 linhas selecionadas.Apenas por curiosidade, podemos perceber que a tabela abaixo armazena o texto puro extraido do documento binário que foi carregado na coluna DOCUMENTOS da tabela TESTE
SCOTT> select token_text from DR$I_TESTE_DOCUMENTO$I;
TOKEN_TEXT
----------------------------------------------------
EDUARDO
ISTO
LEGATTI
ORACLE
POR
TESTE
TEXT
UM
É
9 linhas selecionadas.Realizando a Pesquisa Textual
SCOTT> select id,nome from teste where contains(documento, 'legatti', 1) > 0;
ID NOME
---------- --------------------
1 arquivo.doc
SCOTT> select id,nome from teste where contains(documento, 'Oracle', 1) > 0;
ID NOME
---------- --------------------
1 arquivo.doc
SCOTT> select id,nome from teste where contains(documento, 'Eduardo', 1) > 0;
ID NOME
---------- --------------------
1 arquivo.doc
SCOTT> select id,nome from teste where contains(documento, 'nao existe', 1) > 0;
não há linhas selecionadasNo mais, para manutenção do índice caso necessário, poderemos utilizar alguns procedimentos como CTX_DDL.SYNC_INDEX para sincronização do índice porque nem todos os índices são atualizado automaticamente quando registros são adicionados ou deletados, ou CTX_DDL.OPTIMIZE_INDEX para reconstruir o índice quando necessário.
Voltando ao Passado …
Apenas fazendo uma comparação, se estivéssemos utilizando o já obsoleto Oracle 8 (8.0.5), esta tarefa seria um pouco mais complicada, pois teríamos que instalar e configurar o Oracle Context Cartridge e ainda realizar as tarefas abaixo:
Iniciar o serviço do Oracle Context
windows -> ctxsvr80 -user ctxsys/ctxsys -personality qdml (m -> reindexa em background)
linux -> ctxsrv -user ctxsys/ctxsys -personality m &
-- Criar o índice
execute ctx_ddl.create_policy('documento','teste.documento');
execute ctx_ddl.create_index('documento');