Um pouco do Oracle Text ...

Olá,

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 selecionadas

Abaixo, 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 extraído 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 selecionadas 

No 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');