SDATA Section

The value of an SDATA section is extracted from the document text like other sections, but is indexed as structured data, also referred to as SDATA. Using SDATA sections supports operations such as projection, range searches, and ordering. It also enables SDATA indexing of section data such as embedded tags, and detail table or function invocations. This enables you to perform various combinations of text and structured searches in one single SQL statement.

SDATA operators should be used only as descendants of AND operators that also have non-SDATA children. SDATA operators are meant to be used as secondary, checking or non-driving, criteria. For instance, "find documents with DOG that also have price > 5", rather than "find documents with rating > 4".

Use CTX_DDL.ADD_SDATA_SECTION to add an SDATA section to a section group. Use CTX_DDL.UPDATE_SDATA to update the values of an existing SDATA section. When querying within an SDATA section, you must use the CONTAINS operator. The following example creates a table called items, and adds an SDATA section called my_sec_group, and then queries SDATA in the section.

After you create an SDATA section, you can further modify the attributes of the SDATA section using CTX_DDL.SET_SECTION_ATTRIBUTE.

Create the table items:

CREATE TABLE items 
(id  NUMBER PRIMARY KEY, 
 doc VARCHAR2(4000));
 
INSERT INTO items VALUES (1, '<description> Honda Pilot </description>
                              <category> Cars & Trucks </category>
                              <price> 27000 </price>');
INSERT INTO items VALUES (2, '<description> Toyota Sequoia </description>
                              <category> Cars & Trucks </category>
                              <price> 35000 </price>');
INSERT INTO items VALUES (3, '<description> Toyota Land Cruiser </description>
                              <category> Cars & Trucks </category>
                              <price> 45000 </price>');
INSERT INTO items VALUES (4, '<description> Palm Pilot </description>
                              <category> Electronics </category>
                              <price> 5 </price>');
INSERT INTO items VALUES (5, '<description> Toyota Land Cruiser Grill </description>
                              <category> Parts & Accessories </category>
                              <price> 100 </price>');
COMMIT;

Add SDATA section my_sec_group:

BEGIN
  CTX_DDL.CREATE_SECTION_GROUP('my_sec_group', 'BASIC_SECTION_GROUP');
  CTX_DDL.ADD_SDATA_SECTION('my_sec_group', 'category', 'category', 'VARCHAR2');
  CTX_DDL.ADD_SDATA_SECTION('my_sec_group', 'price', 'price', 'NUMBER');
END;
 

Create the CONTEXT index:

CREATE INDEX items$doc 
  ON items(doc) 
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS('SECTION GROUP my_sec_group');
 

Run a query:

SELECT id, doc
  FROM items
  WHERE contains(doc, 'Toyota 
                       AND SDATA(category = ''Cars & Trucks'') 
                       AND SDATA(price <= 40000 )') > 0;

Return the results:

  ID DOC
---- ----------------------------------------------------------------------
   2 <description> Toyota Sequoia </description>
                                   <category> Cars & Trucks </category>
                                   <price> 35000 </price>

The following example updates the value of the SDATA section price for a document having the rowid of 1, to a new value of 30000.

BEGIN
    SELECT ROWID INTO rowid_to_update FROM items WHERE id=1;

    CTX_DDL.UPDATE_SDATA('items$doc', 
                         'price',
                         SYS.ANYDATA.CONVERTVARCHAR2('30000'),
                         rowid_to_update);
END;

After executing the above query, the price of Honda Pilot is changed from 27000 to 30000.