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


quarta-feira, 11 de junho de 2008

Um pouco do Oracle Text ...

Por Eduardo Legatti

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



15 comentários:

Anônimo disse...

Olá,

O artigo me ajudou muito a compreender como usar o Oracle Text no banco 10g R2.
Muito obrigado!!!!
Anderson Bestteti

Anônimo disse...

Também me ajudou muito.

Obrigado

Júlio

Áureo Rodrigues Noguerol disse...

Muito bom o teu artigo, esta bem completo. parabens

Sergio Coutinho disse...

Eduardo,
Eduardo,
Excelente o seu artigo, especialmente por que resume em poucas palavras - e com bastante exemplos - a potencialidade desta ferramenta.
Estou adotando este recurso em uma aplicação Oracle APEX e me deparei com o seguinte problema: meus campos BLOB podem armazenar informações em idioma Espanhol, Inglês ou Português. Eu preciso de algum cuidado especial para trabalhar com estes três idiomas e o Oracle Text?
Grato pela atenção !
Sergio

Eduardo Legatti disse...

Olá Sérgio,

Acredito que você terá que pesquisar um pouco sobre o assunto, mas nada que seja muito complicado ;-) veja se a documentação poderá lhe ajudar. Indexing a Multi-Language Table

Abraços e até mais ...

Filipe disse...

Olá Eduardo

Quando executei a procedure, o oracle nao pode localizar o arquivo para executar o comando "openfile"
Preciso criar o diretório no local, colocar o arquivo dentro e depois criar o mesmo diretorio no oracle. Seria isso?

Abraço!

Eduardo Legatti disse...

Olá Filipe,

No exemplo do artigo, eu criei uma pasta local C:\DOCS e criei no Oracle um objeto DIRECTORY (documentos) apontando para esta pasta. O arquivo precisa estar na pasta C:\DOCS

Obrigado pela visita.

Abraços e até mais ...

Legatti

Laerte disse...

Olá,

Primeiramente quero parabenizar pelo trabalho aqui no blog. Nesse momento estou quebrando a cabeça para realizar o seguinte: Estou utilizando a função CONTAINS para pesquisar frases e retornar apenas se as palavras que eu passei existirem usando o operador "AND" exemplo:

DADOS:
Engenheiro Sênior
Engenheiro Pleno

WHERE CONTAINS(coluna, 'Engenheiro AND Sênior') > 0;

Agora meu problema é que preciso fazer essa busca ser case-insensitive se o cara digitar "Engenheiro Senior" ele vai ter que retorna o Sênior com acento também! Eu utilizava uma função para remover os acentos e depois a função lower para igualizar os dois dados (o parâmetro e os dados) mas percebi que a função CONTAINS não aceita funções, retorna o erro: Erro de SQL: ORA-20000: Oracle Text error: DRG-10599: a coluna não está indexada.

Já procurei bastante uma solução mas encontrei nada, estou no caminho certo de usar o CONTAINS? O que eu poderia fazer para solucionar esse problema?

Eduardo Legatti disse...

Olá Laerte,

Eu usava muito a função TRANSLATE do Oracle para ignorar os caracteres acentuados, maiúsculos e minúsculos, mas eu não usava o Oracle TEXT. Se o CONTAINS não está deixando você usar functions, verifique se você terá sucesso alterando a sessão do usuário. Dá uma lida no artigo de Abril/2013 chamado de "Ignorando caracteres acentuados, maiúsculos e minúsculos no Oracle (Case Insensitive/Accent Insensitive)" através do link abaixo:

http://eduardolegatti.blogspot.com.br/2013/04/ignorando-caracteres-acentuados.html

Boa sorte!

Abraços e até mais

Legatti

Laerte disse...

Olá, Eduardo,

Obrigado pela resposta anterior utilizando ALTER SESSION resolveu meu problema, mas agora me deparei com outro usando a função CONTAINS. Quando o usuário me passa palavras comuns não reservadas do Oracle tudo ocorre bem, mas por exemplo se o registro contém "!", "AND", "OR", ele acusa erro de syntax, tentei utilizar chave { }, mas também não funcionou.

Dados:
Engenheiro Sênior
Engenheiro Pleno OR

SELECT * FROM TB
WHERE CONTAINS(DESCRICAO, 'Engenheiro and senior and {OR}') > 0;

Retorna resultados mas não o especifico: "Engenheiro Pleno OR".

Agora não sei por quais termos pesquisar, se devo partir pra outra função do Oracle, o que eu poderia fazer nesse caso? Agradeço a resposta anterior.

Eduardo Legatti disse...

Olá Laerte,

Veja se o link abaixo pode te ajudar

https://docs.oracle.com/cd/B28359_01/text.111/b28304/cqspcl.htm#CCREF0400

Acho que você vai ter que debruçar nessa documentação ;-)

Dependendo do tipo de pesquisa você quira fazer, também é possível usar expressões regulares a partir do Oracle 10g através do REGEXP_LIKE

https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

Abraços

Legatti

Wagner disse...

Parabéns pelo blog!!! Se puder me orientar, preciso armazenar arquivos texto (preciso manter a formatação) e fazer a volta (armazenado para arquivo).

Utilizei este seu post. O stored procedure armazenou em blob. Porém não consegui fazer a volta, utilizando outros posts de sua autoria. Pode me orientar?

Sucesso!!!

Eduardo Legatti disse...

Olá Wagner,

Para arquivos textos, acho que o tipo de dado BLOB não seria o mais apropriado, e sim o tipo de dado CLOB. No mais, veja se os artigos abaixo podem te ajudar.

Um pouco do pacote UTL_FILE, DBMS_LOB e objeto DIRECTORY. Gerando e carregando conteúdo de arquivos externos ao Oracle ...
http://eduardolegatti.blogspot.com.br/2009/12/um-pouco-do-pacote-utlfile-dbmslob-e.html

Salvando o conteúdo de um campo BLOB para um arquivo em disco (BLOB_TO_FILE) ...
http://eduardolegatti.blogspot.com.br/2010/01/salvando-o-conteudo-de-um-campo-blob.html

Abraços

Legatti

Wagner disse...

Olá, Eduardo. Com relação ao objeto directory, como faço para que o servidor reconheça a criação/permissão de diretório/arquivo no lado cliente? Parabéns pelo post!!!

Eduardo Legatti disse...

Olá Wagner,

Não tem jeito. O objeto DIRECTORY referencia diretórios somente no lado servidor.

Abraços e até mais...

Legatti

Postagens populares