This discussion is archived
1 Reply Latest reply: Feb 4, 2013 5:40 AM by Roger Ford RSS

MARKUP (HIGHLIGHT) and MULTI_COLUMN_DATASTORE

984246 Newbie
Currently Being Moderated
I am passing a text index to MARKUP's index_name parameter, that is a dummy field for MULTI_COLUMN_DATASTORE. Though, MARKUP only returns the CLOB of the dummy field, not the rest of the columns.

What is the best way to MARKUP a "document" that is stored in a table, in multiple columns (columns are either VARCHAR2 in CLOB). I need every column MARKUP'd (or HIGHLIGHT'd).

Alternatively, I can do the markup-ing myself, but in that case I'd like to know, whether there's a way to get all the terms that Oracle searched with. I use the ora-text operator FUZZY a lot, so I'd need to get the additional terms that are generated (expanded?) by the FUZZY operator.

Edited by: 981243 on Feb 4, 2013 5:22 AM
  • 1. Re: MARKUP (HIGHLIGHT) and MULTI_COLUMN_DATASTORE
    Roger Ford Expert
    Currently Being Moderated
    That doesn't sound right. Markup should fetch the text through the datastore, so you should get all the columns. See example below - the index is created on "firstname" but we still see text highlighted in the "surname" column.
    SQL> drop table cust_catalog;
    
    Table dropped.
    
    Elapsed: 00:00:00.04
    SQL> create table cust_catalog (
      2    id number(16) primary key,
      3    firstname varchar2(80),
      4    surname varchar2(80),
      5    birth varchar2(25),
      6    age numeric );
    
    Table created.
    
    Elapsed: 00:00:00.00
    SQL> INSERT ALL
      2  INTO cust_catalog VALUES ('1','John','Smith','Glasgow','52')
      3  INTO cust_catalog VALUES ('2','Emaily','Johnson','Aberdeen','55')
      4  SELECT * FROM DUAL;
    
    2 rows created.
    
    Elapsed: 00:00:00.01
    SQL> EXEC CTX_DDL.DROP_PREFERENCE   ('my_datastore')
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> EXEC CTX_DDL.CREATE_PREFERENCE ('my_datastore', 'MULTI_COLUMN_DATASTORE')
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> EXEC CTX_DDL.SET_ATTRIBUTE ('my_datastore', 'COLUMNS', 'firstname, surname')
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> CREATE INDEX context_idx ON cust_catalog (firstname)
      2  INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('datastore my_datastore');
    
    Index created.
    
    Elapsed: 00:00:00.10
    SQL> set serverout on
    SQL> declare
      2    markuptext clob;
      3  begin
      4    ctx_doc.set_key_type( 'PRIMARY_KEY' );
      5    ctx_doc.markup( 'context_idx', '1', 'john OR smith', markuptext );
      6    dbms_output.put_line( markuptext );
      7  end;
      8  /
    <FIRSTNAME>
    <<<John>>>
    </FIRSTNAME>
    <SURNAME>
    <<<Smith>>>
    </SURNAME>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points