1 Reply Latest reply: Feb 4, 2013 7:40 AM by Roger Ford-Oracle RSS


      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
          Roger Ford-Oracle
          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
          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  /