Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Using interMedia Text to Search and Retrieve Data from XML Documents, 13 of 22

## 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

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index