RESULT_SET

This procedure executes an XML or JSON query and generates a result set in XML or JSON.

The Result Set Interface can return data views that are difficult to express in SQL.

Complex RSI queries, especially those involving large SDATA, can be long-running. The longer a query runs, the more UNDO data is generated to support consistent read operations. This is because Oracle needs to preserve the data as it was at the start of the query to ensure the query’s consistency. On Windows systems, additional factors such as memory management and resource allocation can influence how Oracle manages UNDO. While the exact mechanisms can vary, it’s observed that RSI operations on Windows tend to have higher UNDO requirements. This increased demand can lead to ORA-08176 errors if the UNDO configuration is not adequately sized. To mitigate ORA-08176 errors in RSI query workloads on Windows, adjust UNDO Settings. Increase the UNDO_TABLESPACE size or optimize UNDO_MANAGEMENT parameter to accommodate the higher demands from larger SDATA processing.

See Also: Oracle Text Application Developer’s Guide for details on how to use the Result Set Interface

Syntax

CTX_QUERY.RESULT_SET (
   index_name            IN VARCHAR2,
   query                 IN VARCHAR2,
   result_set_descriptor IN CLOB,
   result_set            IN OUT NOCOPY CLOB,
   part_name             IN VARCHAR2 DEFAULT NULL,
   format                IN NUMBER DEFAULT CTX_QUERY.XML_FORMAT
);

index_name

Specify the index against which to execute the query.

query

Specify the query string.

result_set_descriptor

Specify the result set descriptor in XML or JSON. It describes what the result set should contain.

result_set

Specify the output result set. If this variable is NULL on input, a session-duration temporary lob will be allocated and returned to the user. The user is responsible for deallocating this temporary lob.

part_name

Specify the index partition name. If the index is global, part_name must be NULL. If the index is partitioned and part_name is not NULL, then the query will only be evaluated for the given partition. If the index is partitioned and part_name is NULL, then the query will be evaluated for all partitions.

format

Specify the format for the result set descriptor. Use CTX_QUERY.XML_FORMAT for XML format and CTX_QUERY.JSON_FORMAT for JSON format. The default is CTX_QUERY.XML_FORMAT.

The Input Result Set Descriptor

The result set descriptor is an XML message or JSON object which describes what to calculate for the result set. The elements present in the result set descriptor and the order in which they occur serve as a simple template, specifying what to include in the output result set. That is, there should be the list of hit rowids, then a count, then a token count, and so on. The attributes of the elements specify the parameters and options to the specific operations, such as number of hits in the list of rowids, estimate versus exact count, and so on.

The XML Format Input Result Set Descriptor

The result set descriptor itself is XML conforming to the following DTD:

<!DOCTYPE ctx_result_set_descriptor [
<!ELEMENT ctx_result_set_descriptor (hitlist?, group*, count?, collocates?)>
<!ELEMENT hitlist (rowid?, score?, sdata*, snippet*, sentiment?)>
<!ELEMENT group (count?, group_values?)>
<!ELEMENT count EMPTY>
<!ELEMENT rowid EMPTY>
<!ELEMENT score EMPTY>
<!ELEMENT sdata EMPTY>
<!ELEMENT group_values (value*)>
<!ELEMENT value EMPTY>
<!ELEMENT sentiment (item*)>
<!ELEMENT item EMPTY>
<!ELEMENT collocates EMPTY>
<!ATTLIST sentiment classifier CDATA "DEFAULT_CLASSIFIER">
<!ATTLIST item topic CDATA #REQUIRED>
<!ATTLIST item type (about|exact) "exact">
<!ATTLIST item agg (TRUE|FALSE) "FALSE">
<!ATTLIST item radius CDATA "50">
<!ATTLIST item max_inst CDATA "5">
<!ATTLIST item starttag CDATA #IMPLIED>
<!ATTLIST item endtag CDATA #IMPLIED>
<!ATTLIST collocates radius CDATA "20">
<!ATTLIST collocates max_words CDATA "10">
<!ATTLIST collocates use_tscore (TRUE|FALSE) "TRUE">
<!ATTLIST collocates use_hits CDATA "10">
<!ATTLIST group sdata CDATA #REQUIRED>

<!ATTLIST group topn CDATA #IMPLIED>
<!ATTLIST group bucketby CDATA #IMPLIED>
<!ATTLIST group sortby CDATA #IMPLIED>
<!ATTLIST group order CDATA #IMPLIED>
<!ATTLIST value id CDATA #IMPLIED>
<!ATTLIST hitlist start_hit_num CDATA #REQUIRED>
<!ATTLIST hitlist end_hit_num CDATA #REQUIRED>
<!ATTLIST hitlist order CDATA #IMPLIED>
<!ATTLIST count exact (TRUE|FALSE) "FALSE">

<!ATTLIST sdata name CDATA #REQUIRED>
<!ATTLIST snippet radius CDATA #IMPLIED>
<!ATTLIST snippet max_length CDATA #IMPLIED>
<!ATTLIST snippet starttag CDATA #IMPLIED>
<!ATTLIST snippet endtag CDATA #IMPLIED>
]>

The following is a description of the possible XML elements for the result set descriptor:

(order = "SCORE DESC, MYDATE, MYPRICE DESC")
The possible child elements for `hitlist` are:

The Output Result Set XML

The output result set XML is XML conforming to the following DTD:

<!DOCTYPE ctx_result_set [
<!ELEMENT ctx_result_set (hitlist?, groups*, count? , collocates?)>
<!ELEMENT hitlist (hit*)>
<!ELEMENT hit (rowid?, score?, snippet*, sdata*, sentiment?)>
<!ELEMENT groups (group*)>
<!ELEMENT group (count?)>
<!ELEMENT count (#PCDATA)>
<!ELEMENT rowid (#PCDATA)>
<!ELEMENT score (#PCDATA)>
<!ELEMENT snippet (segment*)>
<!ELEMENT sdata (#PCDATA)>
<!ELEMENT sentiment (item*)>
<!ELEMENT item (segment*, score*, doc?)>
<!ELEMENT segment (segment_text?, segment_score?)>
<!ELEMENT segment_text (#PCDATA)>
<!ELEMENT segment_score (#PCDATA)>
<!ELEMENT doc (score?)>
<!ELEMENT collocates (collocation*)>
<!ELEMENT collocation (word?, score?)>
<!ELEMENT word (#PCDATA)>
<!ATTLIST item topic CDATA #REQUIRED>
<!ATTLIST groups sdata CDATA #REQUIRED>
<!ATTLIST group value CDATA #REQUIRED>

<!ATTLIST group range CDATA #IMPLIED>
<!ATTLIST group single CDATA #IMPLIED>
<!ATTLIST sdata name CDATA #REQUIRED>

The following is a description of the list of possible XML elements for the output result set:

Example

This call to CTX_QUERY.RESULT_SET with the specified XML result_set_descriptor will generate the following information in the form of XML:

declare
  rs clob;
begin
  dbms_lob.createtemporary(rs, true, dbms_lob.session);
  ctx_query.result_set('docidx', 'oracle', '
  <ctx_result_set_descriptor>
   <count/>
   <hitlist start_hit_num="1" end_hit_num="5" order="pubDate desc, score desc">
     <score/>
     <rowid/>
     <sdata name="author"/>
     <sdata name="pubDate"/>
   </hitlist>
   <group sdata="pubDate">
     <count/>
   </group>
   <group sdata="author">
     <count/>
   </group>
  </ctx_result_set_descriptor>
', rs);
  dbms_lob.freetemporary(rs);
exception
  when others then
   dbms_lob.freetemporary(rs);
   raise;
end;
/

The XML output store in the result set output clob will resemble the following:

<ctx_result_set>
  <hitlist>
    <hit>
      <score>3</score><rowid>AAAPoEAABAAAMWsAAC</rowid>
      <sdata name="AUTHOR">John</sdata>
      <sdata name="PUBDATE">2001-01-03 00:00:00</sdata>
    </hit>
    <hit>
      <score>3</score><rowid>AAAPoEAABAAAMWsAAG</rowid>
      <sdata name="AUTHOR">John</sdata>
      <sdata name="PUBDATE">2001-01-03 00:00:00</sdata>
    </hit>
    <hit>
      <score>3</score><rowid>AAAPoEAABAAAMWsAAK</rowid>
      <sdata name="AUTHOR">John</sdata>
      <sdata name="PUBDATE">2001-01-03 00:00:00</sdata>
    </hit>
    <hit>
      <score>3</score><rowid>AAAPoEAABAAAMWsAAO</rowid>
      <sdata name="AUTHOR">John</sdata>
      <sdata name="PUBDATE">2001-01-03 00:00:00</sdata>
    </hit>
    <hit>
      <score>3</score><rowid>AAAPoEAABAAAMWsAAS</rowid>
      <sdata name="AUTHOR">John</sdata>
      <sdata name="PUBDATE">2001-01-03 00:00:00</sdata>
    </hit>
  </hitlist>

  <count>100</count>

  <groups sdata="PUBDATE">
    <group value="2001-01-01 00:00:00"><count>25</count></group>
    <group value="2001-01-02 00:00:00"><count>50</count></group>
    <group value="2001-01-03 00:00:00"><count>25</count></group>
  </groups>

  <groups sdata="AUTHOR">
    <group value="John"><count>50</count></group>
    <group value="Mike"><count>25</count></group>
    <group value="Steve"><count>25</count></group>
  </groups>

</ctx_result_set>

The JSON Format Input Result Set Descriptor

The JSON format result set descriptor consists of $query, $search, and $facet parts. You can use the JSON format result set descriptor to query context indexes and JSON search index. It is of the following format:

{
  "$query": <text query and filter conditions>,
  "$search": <search result specifications>,
  "$facet": <faceted result specifications>
}

Note:

Use wildcard field steps (*) in the contains clause to include other path steps between the paths. For example: address.*.name Use descendent notation (..) in the contains clause to include descendant path steps between the paths. For example: address..name2 In this query, name2 is considered as a descendent of address and matches the address record of the table. You can use a $contains field condition only as a part of a simple $contains query or as a part of the outermost $and condition. You cannot use it as a part of a $or condition or an inner $and condition. Doing so results in an error.

Note:

Wildcard field steps (*) and array steps ([ and ]) are not supported. To support field conditions on string values, a JSON search index with search_on text_value_string is required.

The following is an example of a $query part with the supported clauses:

  "$query" :
  {
    "$and" : [
      { "book.*.summary" : {  "$contains" : "(Music or Song) and Dance"  } },
      { "book.*.review" : {  "$contains" : "(Good or excellent) and interesting"  } },
      { "$or" : [
        { "book.rating" : { "$gte" :
4.5 } },
        { "$and" : [  { "book.price" : { "$lte" : 100 } },  { "book.author" : { "$eq" : "Doe" } } ] }
      ]
    ]
  }

The JSON Format Result Set Output

The JSON format result set output is a JSON object that consists of the following parts:

"$count" : number
"$hit" : [ <hit_object_1>, ..., <hit_object_i> , ... ]
"$facet": [ <facet_object_1>, ..., <facet_object_i>, ...]

The following is a description of the list of possible JSON objects for the output result set:

Example 12-1 Using the JSON format Result Set Interface with CONTEXT Index

This example shows you how to use the JSON format result set interface with CONTEXT index.

Create a table and populate it with values:

drop table zebra_table;
create table zebra_table(id number, details clob);

INSERT INTO zebra_table
VALUES (1,'  Zebra details : <price>2000</price><price>1000</price>
                             <name>Storm</name>
                             <stripes>Dark</stripes><stripes>Light</stripes>
                             <handler>Bob</handler>
                             <sold>true</sold>');

INSERT INTO zebra_table
VALUES (2,'  Zebra details : <rating>5</rating> <price>1000</price>
                             <name>Snowy</name>
                             <stripes>Light</stripes><stripes>Grey</stripes>
                             <handler>Jane Doe</handler>
                             <sold>true</sold>');

INSERT INTO zebra_table
VALUES (3,'  Zebra details : <rating>4.5</rating> <price>3000</price>
                             <name>Zigs</name>
                             <stripes>Grey</stripes><stripes>Dark</stripes>
                             <handler>Jane Doe</handler>
                             <sold>false</sold>');

INSERT INTO zebra_table
VALUES (4,'  Zebra details : <rating>4.5</rating> <price>3000</price>
                             <name>Zigs</name>
                             <stripes>Grey</stripes><stripes>Dark</stripes>
                             <handler>Jane Doe</handler> <sold></sold>');

Create a section group named mysecgrp and enable the optimized_for search attribute for each column to be treated as a facet:

exec ctx_ddl.drop_section_group   ('mysecgrp')
exec ctx_ddl.create_section_group ('mysecgrp', 'BASIC_SECTION_GROUP')

exec ctx_ddl.add_sdata_section    ('mysecgrp', 'rating', 'rating', 'NUMBER')
exec ctx_ddl.set_section_attribute('mysecgrp', 'rating', 'optimized_for', 'search')

exec ctx_ddl.add_sdata_section    ('mysecgrp', 'price', 'price', 'NUMBER')
exec ctx_ddl.set_section_attribute('mysecgrp', 'price', 'optimized_for', 'search')

exec ctx_ddl.add_sdata_section    ('mysecgrp', 'name', 'name', 'VARCHAR2')
exec ctx_ddl.set_section_attribute('mysecgrp', 'name', 'optimized_for', 'search')

exec ctx_ddl.add_sdata_section    ('mysecgrp', 'stripes', 'stripes', 'VARCHAR2')
exec ctx_ddl.set_section_attribute('mysecgrp', 'stripes', 'optimized_for', 'search')

exec ctx_ddl.add_sdata_section    ('mysecgrp', 'handler', 'handler', 'VARCHAR2')
exec ctx_ddl.set_section_attribute('mysecgrp', 'handler', 'optimized_for', 'search')

exec ctx_ddl.add_sdata_section    ('mysecgrp', 'sold', 'sold', 'VARCHAR2')
exec ctx_ddl.set_section_attribute('mysecgrp', 'sold', 'optimized_for', 'search')

Create a CONTEXT index on details and specify the preferences by using the parameters clause:

create index zebra_idx on zebra_table(details)
indextype is ctxsys.context
parameters('section group mysecgrp');

A call to CTX_QUERY.RESULT_SET with the specified JSON result_set_descriptor generates the following information in the form of JSON:

variable rs_output clob;

declare
  qry varchar2(4000);
  rs_descriptor clob;
begin
  qry := 'zebra details';
  rs_descriptor := '
{
  "$search" : { "start" : 1,  "end" : 2, "project" : [ "name", "handler" ] },
  "$facet" : [
                   { "$uniqueCount" : "name" },
                   { "$uniqueCount" : "sold" },
                   { "$uniqueCount" : { "sdata" : "price" } },
                   { "$sum" : { "sdata" : "price",
                                "bucket" :
                                  [ { "$lt" : 3000 }, { "$gte" : 3000 } ]
                              }
                   },
                   { "$avg" : "rating" },
                   {
                     "$count" : { "sdata" : "handler",
                                  "bucket" :
                                    [ { "$lte" : "C" }, { "$gt" : "C" } ]
                                }
                   }
             ]
}
';
  dbms_lob.createtemporary( :rs_output, true );
  ctx_query.result_set( 'zebra_idx', qry, rs_descriptor, :rs_output,
                        format => CTX_QUERY.JSON_FORMAT );
end;
/

select json_query(:rs_output, '$' pretty) from dual;

The following is output:

{
  "$count" : 4,
  "$hit" :
  [
    {
      "score" : 3,
      "rowid" : "AAASxXAABAAAY95AAA",
      "project" :
      {
	"NAME" : "Storm",
	"HANDLER" : "Bob"
      }
    },
    {
      "score" : 3,
      "rowid" : "AAASxXAABAAAY95AAB",
      "project" :
      {
	"NAME" : "Snowy",
	"HANDLER" : "Jane Doe"
      }
    }
  ],
  "$facet" :
  [
    {
      "NAME" :
      [
	{
	  "value" : "Zigs",
	  "$uniqueCount" : 2
	},
	{
	  "value" : "Snowy",
	  "$uniqueCount" : 1
	},
	{
	  "value" : "Storm",
	  "$uniqueCount" : 1
	}
      ]
    },
    {
      "SOLD" :
      [
	{
	  "value" : "true",
	  "$uniqueCount" : 2
	},
	{
	  "value" : "false",
	  "$uniqueCount" : 1
	}
      ]
    },
    {
      "PRICE" :
      [
	{
	  "value" : 1000,
	  "$uniqueCount" : 2
	},
	{
	  "value" : 3000,
	  "$uniqueCount" : 2
	},
	{
	  "value" : 2000,
	  "$uniqueCount" : 1
	}
      ]
    },
    {
      "PRICE" :
      [
	{
	  "bucket" :
	  {
	    "$gte" : 1000,
	    "$lt" : 3000
	  },
	  "$sum" : 4000
	},
	{
	  "bucket" :
	  {
	    "$gte" : 3000,
	    "$lte" : 3000
	  },
	  "$sum" : 6000
	}
      ]
    },
    {
      "RATING" :
      {
	"$avg" : 4.66666666666666666667
      }
    },
    {
      "HANDLER" :
      [
	{
	  "bucket" :
	  {
	    "$gte" : "Bob",
	    "$lte" : "C"
	  },
	  "$count" : 1
	},
	{
	  "bucket" :
	  {
	    "$gt" : "C",
	    "$lte" : "Jane Doe"
	  },
	  "$count" : 3
	}
      ]
    }
  ]
}

Example 12-2 Using the JSON format Result Set Interface with JSON Search Index

This example shows you how to use the JSON format result set interface with JSON search index.

Create a table and populate it with values:

drop table zebra_table;
create table zebra_table(id number, details clob check(details is json));

INSERT INTO zebra_table
VALUES (1,'{ "zebra" : { "price" : [2000,1000],
                       "name" : "Storm",
                       "stripes" : ["Dark","Light"],
                       "handler" : "Bob", "sold" : true }}');

INSERT INTO zebra_table
VALUES (2,'{ "zebra" : { "rating": 5, "price" : 1000,
                       "name" : "Zigzag",
                       "stripes" : ["Light","Grey"],
                       "handler" : "Jane Doe", "sold" : "true" }}');

INSERT INTO zebra_table
VALUES (3,'{ "zebra" : { "rating": 4.5, "price" : 3000,
                       "name" : "Zigs",
                       "stripes" : ["Grey","Dark"],
                       "handler" : "Jane Doe", "sold" : false }}');

INSERT INTO zebra_table
VALUES (4,'{ "zebra" : { "rating": "4.5", "price" : "3000",
                       "name" : "Zigs",
                       "stripes" : ["Grey","Dark"],
                       "handler" : "Jane Doe", "sold" : null }}');

Create a JSON search index on details and specify the preferences by using the parameters clause:

create search index zebra_idx on zebra_table(details) for json
parameters('search_on text_value_string');

A call to CTX_QUERY.RESULT_SET with the specified JSON result_set_descriptor generates the following information in the form of JSON:

variable rs_output clob;

declare
  rs_descriptor clob;
begin
  rs_descriptor := '
{
  "$query" : { "zebra.*.name" : { "$contains" : "sto% or zig%" } },
  "$search" : { "start" : 1,  "end" : 2 },
  "$facet" : [
                   { "$uniqueCount" : "zebra.name" },
                   { "$uniqueCount" : "zebra.sold" },
                   { "$uniqueCount" :
                       { "path" : "zebra.price", "type" : "number" }
                   },
                   { "$sum" : { "path" : "zebra.price",
                                "bucket" :
                                  [ { "$lt" : 3000 }, { "$gte" : 3000 } ]
                              }
                   },
                   { "$avg" : "zebra.rating" },
                   {
                     "$count" : { "path" : "zebra.handler",
                                  "type" : "string",
                                  "bucket" :
                                    [ { "$lte" : "C" }, { "$gt" : "C" } ]
                                }
                   }
             ]
}
';
  dbms_lob.createtemporary( :rs_output, true );
  ctx_query.result_set( 'zebra_idx', null, rs_descriptor, :rs_output,
                        format => CTX_QUERY.JSON_FORMAT );
end;
/

select json_query(:rs_output, '$' pretty) from dual;

The following is output:

{
  "$count" : 4,
  "$hit" :
  [
    {
      "score" : 4,
      "rowid" : "AAASwtAABAAAY95AAB"
    },
    {
      "score" : 4,
      "rowid" : "AAASwtAABAAAY95AAC"

    }
  ],
  "$facet" :
  [
    {
      "zebra.name" :
      [
	{
	  "value" : "Zigs",
	  "$uniqueCount" : 2
	},

	{
	  "value" : "Zigzag",
	  "$uniqueCount" : 1
	},
	{
	  "value" : "Storm",
	  "$uniqueCount" : 1
	}
      ]
    },
    {

      "zebra.sold" :
      [
	{
	  "value" : "true",
	  "$uniqueCount" : 2
	},
	{
	  "value" : "null",
	  "$uniqueCount" : 1
	},
	{
	  "value" : "false",
	  "$uniqueCount" : 1
	}
      ]
    },
    {
      "zebra.price" :
      [
	{
	  "value" : 1000,
	  "$uniqueCount" : 2

	},
	{
	  "value" : 3000,
	  "$uniqueCount" : 2
	},
	{
	  "value" : 2000,
	  "$uniqueCount" : 1
	}
      ]
    },

    {
      "zebra.price" :
      [
	{
	  "bucket" :
	  {
	    "$gte" : 1000,
	    "$lt" : 3000
	  },
	  "$sum" : 4000
	},

	{
	  "bucket" :
	  {
	    "$gte" : 3000,
	    "$lte" : 3000
	  },
	  "$sum" : 6000
	}
      ]
    },
    {
      "zebra.rating" :
      {
	"$avg" : 4.66666666666666666667
      }
    },
    {
      "zebra.handler" :
      [
	{
	  "bucket" :
	  {

	    "$gte" : "Bob",
	    "$lte" : "C"
	  },
	  "$count" : 1
	},
	{
	  "bucket" :
	  {
	    "$gt" : "C",
	    "$lte" : "Jane Doe"
	  },
	  "$count" : 3
	}
      ]
    }
  ]
}

Limitations and Restrictions

The following limitations and restrictions apply for RESULT_SET.

See Also:

Oracle Text Application Developer’s Guide for information on the XML and JSON Result Set Interfaces Oracle Text Application Developer’s Guide for more information on faceted navigation Oracle Database Introduction to Simple Oracle Document Access (SODA) for more information on SODA filter specifications