Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
|
Using interMedia Text to Search and Retrieve Data from XML Documents, 13 of 22
Text Query Expression allows you to do the following:
This example shows the set up and usage of the text query expression in a SELECT statement:
-- run as ctxsys begin Ctx_Thes.Drop_Thesaurus ( 'default' ); exception when others then /* not an error if... DRG-11701: thesaurus default does not exist */ if instr ( SQLERRM, 'DRG-11701' ) != 0 then null; else raise_application_error ( -20000, SQLERRM ); end if; end; / begin Ctx_Thes.Create_Thesaurus ( name => 'default', casesens => false ); Ctx_Thes.Create_Phrase ( tname => 'default', phrase => 'crime' ); Ctx_Thes.Create_Phrase ( tname => 'default', phrase => 'murder', rel => 'NT', relname => 'crime' ); Ctx_Thes.Create_Phrase ( tname => 'default', phrase => 'death', rel => 'RT', relname => 'murder' ); Ctx_Thes.Create_Phrase ( tname => 'default', phrase => 'kill', rel => 'RT', relname => 'murder' ); Ctx_Thes.Create_Phrase ( tname => 'default', phrase => 'strangling', rel => 'NT', relname => 'murder' ); Ctx_Thes.Create_Phrase ( tname => 'default', phrase => 'thirteen' ); Ctx_Thes.Create_Phrase ( tname => 'default', phrase => '13', rel => 'SYN', relname => 'thirteen' ); end; /
Set Define Off begin execute immediate 'drop table christie'; exception when others then /* not an error if... ORA-00942: table or view does not exist */ if instr ( SQLERRM, 'ORA-00942' ) != 0 then null; else raise_application_error ( -20000, SQLERRM ); end if; end; / create table christie ( id number, title varchar2(700) ); insert into christie ( title ) values ( '<T>Thirteen At Dinner</T> - <A>Agatha Christie</A>' ); insert into christie ( title ) values ( '<T>The 4:50 from Paddington</T> - <A>Agatha Christie</A>' ); insert into christie ( title ) values ( '<T>Blue Geranium</T> - <A>Agatha Christie</A>' ); insert into christie ( title ) values ( '<T>The fiction of Agatha Christie</T> - <A>John Smith</A>' ); insert into christie ( title ) values ( '<T>Caribbean with quite a few intervening words between it and Mystery</T> - <A>Agatha Christie</A>' ); commit; update christie set id = rownum; commit; alter table christie add constraint christie_pk primary key ( id ) using index; create unique index christie_title on christie ( title ); drop index christie_title; begin Ctx_Ddl.Drop_Preference ( 'my_basic_lexer' ); exception when others then /* not an error if... preference does not exist */ if instr ( SQLERRM, 'DRG-10700' ) != 0 then null; else raise_application_error ( -20000, SQLERRM ); end if; end; / begin Ctx_Ddl.Drop_Section_Group ( 'my_basic_section_group' ); exception when others then /* not an error if... section group does not exist */ if instr ( SQLERRM, 'DRG-12203' ) != 0 then null; else raise_application_error ( -20000, SQLERRM ); end if; end; / begin Ctx_Ddl.Create_Preference ( 'my_basic_lexer', 'basic_lexer' ); Ctx_Ddl.Set_Attribute ( 'my_basic_lexer', 'index_themes', 'false' ); Ctx_Ddl.Set_Attribute ( 'my_basic_lexer', 'index_text', 'true' ); Ctx_Ddl.Create_Section_Group ( group_name => 'my_basic_section_group', group_type => 'basic_section_group' ); Ctx_Ddl.Add_Field_Section ( group_name => 'my_basic_section_group', section_name => 'title', tag => 't', visible => true ); Ctx_Ddl.Add_Field_Section ( group_name => 'my_basic_section_group', section_name => 'author', tag => 'a', visible => true ); end; / create index christie_title on christie ( title ) indextype is ctxsys.context parameters ( 'lexer my_basic_lexer section group my_basic_section_group' ); begin Ctx_Ddl.Drop_Preference ( 'my_basic_lexer' ); Ctx_Ddl.Drop_Section_Group ( 'my_basic_section_group' ); end; /
Set Define Off begin execute immediate 'drop table ctx_mutab'; exception when others then /* not an error if... ORA-00942: table or view does not exist */ if instr ( SQLERRM, 'ORA-00942' ) != 0 then null; else raise_application_error ( -20000, SQLERRM ); end if; end; / create table ctx_mutab ( query_id number constraint ctx_mutab_pk primary key, document clob ); begin execute immediate 'drop sequence ctx_mutab_seq'; exception when others then /* not an error if... ORA-02289: sequence does not exist */ if instr ( SQLERRM, 'ORA-02289' ) != 0 then null; else raise_application_error ( -20000, SQLERRM ); end if; end; / create sequence ctx_mutab_seq start with 1;
This accepts a Text Query Expression, runs the query, and for each hit displays the output of Ctx_Doc.Markup.
Set Define Off create or replace procedure Qry_And_Markup ( p_qry in varchar2 default null ) is v_query_id number; v_document clob; v_amount number; v_nof_hits integer := 0; begin if p_qry is not null then for j in ( select score(0) s, id from christie where contains ( title, p_qry, 0 ) > 0 order by s desc ) loop select ctx_mutab_seq.nextval into v_query_id from dual; Ctx_Doc.Markup ( index_name => 'christie_title', textkey => to_char ( j.id ), text_query => p_qry, restab => 'ctx_mutab', query_id => v_query_id, starttag => Show.Start_Tag, endtag => Show.End_Tag ); select document into v_document from ctx_mutab where query_id = v_query_id; v_amount := 4000; Show.Table_Row ( p_cell_1 => to_char ( j.s ), p_cell_2 => Dbms_Lob.Substr ( lob_loc => v_document, amount => v_amount, offset => 1 ) ); v_nof_hits := v_nof_hits + 1; end loop; if v_nof_hits < 1 then Show.Table_Row ( p_cell_1 => 'No hits' ); end if; else for j in ( select title from christie ) loop Show.Table_Row ( p_cell_1 => j.title ); end loop; end if; end Qry_And_Markup; / Show Errors
|
Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|