Text Query Expression

Text Query Expression allows you to do the following:

• Express all word-based criteria that are standard in the text retrieval industry, such as, boolean word combinations, proximity, and phrases searches.

• Perform theme based search criteria, for example, (...CONTAINS (my_column,'About (customizing XML presentations)')...). Here, the phrase, 'customizing XML presentations' typically, does not occur in the retrieved document.

  See Also: Oracle8i interMedia Text Reference for more information on Text Query Expression.

interMedia Text Example 4: Using Text Query Expressions

This example shows the set up and usage of the text query expression in a SELECT statement:

• Creating a Thesaurus

• Creating Table christie

• Creating Table ctx_mutab

Creating a Thesaurus

-- 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;
/

Creating Table christie

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; 
/ 

Creating Table ctx_mutab

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;

Accepting Text Query Expression and Running the Query

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