RESULT_SET

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

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

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
);

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

The Input Result Set Descriptor

The result set descriptor is an XML message 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 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:

• ctx_result_set_descriptor

  This is the root element for the result set descriptor. The parent element is none, as are the available attributes.

  The possible child elements are:

  • Zero or more hitlist elements.

  • Zero or more group elements.

  • At most one count element.

• group

  The group element causes the generated result set to include a group breakdown. In other words, a breakdown of the results by SDATA section values. The group element is also used to obtain facet counts for faceted navigation support. The parent element is ctx_result_set_descriptor, and the available attributes are:

  • sdata

    Specifies the name of the SDATA section to use for grouping. It is required.

  • bucketby

    Determines how group values are bucketed for counting. At the time of this release, the supported valid attribute value is single. It displays each unique facet value along with its count.

  • topn

    Restricts the maximum number of facet values that are returned. It sorts by descending group count by default. Valid attribute values are positive integers larger than zero.

  • sortby

    Valid attribute values are value and count. Value sorts using the value themselves, as appropriate for each data type. Count (default) sorts using the counts for each group.

  • order

    Order can be ascending or descending.

  Possible child elements of group are:

  • At most one count element.

• hitlist

  The hitlist element controls inclusion of a list of hit documents. The parent element is ctx_result_set_descriptor, and the available attributes are:

  The possible attribute elements for hitlist are:

  • start_hit_num

    This specifies the starting document hit to be included in the generated result set. This can be set to any positive integer less than or equal to 16000. For example, if start_hit_num is 21, then the result set will include document hits starting from the 21st document hit. This element is required.

  • end_hit_num

    This specifies the last document hit to be included in the generated result set. This can be set to any positive integer less than or equal to 48000. For example, if end_hit_num is 40, then the result set will include document hits up to the 40th document hit. This element is required.

  • order

    This is an optional attribute that specifies the order for the documents in the generated result set. The value is a list similar to a SQL ORDER BY statement, except that, instead of column names, they can either be SCORE or SDATA section names. In the following example, MYDATE and MYPRICE are the SDATA section names:

(order = "SCORE DESC, MYDATE, MYPRICE DESC")

The possible child elements for hitlist are:

• At most one rowid element.

• At most one score element.

• One or more sdata element.

• At most one snippet element.

• count

  This element causes the generated result set to include a count of the number of hit documents. The parent elements are:

  • ctx_result_set_descriptor

  • group

  The available attributes for count are:

  • exact

    This is to estimate mode. Set to true or false. It is required, and the default is false.

  The possible child elements for count are none.

• rowid

  This child element causes the generated result set to include rowid information for each hit. The parent element is hitlist. There are no attributes and no possible child elements.

• score

  This child element causes the generated result set to include score information for each hit.

  • The parent element is hitlist.

  • There are no available attributes, and no possible child elements.

• sdata

  This child element causes the generated result set to include sdata values for each hit.

  • The parent element is hitlist.

  • The available attribute is name. This specifies the name of the sdata section. It is required.

  • There are no child elements.

• sentiment

  This element controls the inclusion of sentiment classification results for each document returned as a part of the hitlist. There can be only one sentiment element in the hitlist element.

  The parent element is hitlist.

  The attribute available for this element is classifier, which specifies the sentiment classifier that is used to perform sentiment analysis. If no classifier is specified, then the CTXSYS.DEFAULT_SENTIMENT_CLASSIFIER is used. If a specified classifier is not available, then an error is displayed.

• item

  This element specifies keywords or concepts for which sentiment information must be fetched for the returned set of documents. Each sentiment element must contain at least one child item element. The maximum is 10 child item elements. If you specify an empty item element (without any attributes), it indicates that sentiment score for entire document must be returned.

  The parent element is sentiment.

  The available attributes for item are:

  • topic

    This specifies the topic for which sentiment analysis must be performed.

  • type

    If this attribute value is set to ABOUT, then the classifier treats the specified topic as a concept rather than a keyword. The default is EXACT.

  • agg

    Determines whether the sentiment score must be aggregated and presented as a single score for the entire document. The possible values are TRUE or FALSE. TRUE indicates that the per text segment scores will be aggregated and text segments will not be returned in the output resultset, only the aggregated score will be returned. The default value is FALSE.

  • radius

    This specifies the radius of the surrounding text to be identified during sentiment classification for that keyword. The default value is 50.

  • max_inst

    This specifies how many instances of text excerpts related to the specified topic must be analyzed for sentiment classification. The default value is 5.

  • starttag

    This specifies the starting tag for topic highlighting.

  • endtag

    This specifies the ending tag for topic highlighting.

• collocates

  This element controls the generation of related keywords or concepts associated with the collection of documents retrieved by the query.

  The parent element is ctx_result_set_descriptor.

  The available attributes for collocates are:

  • radius

    This specifies the radius of the surrounding text to be identified for collocates. The default value is 20.

  • max_words

    This specifies the maximum number of collocates to return for the given query. The default value is 10.

  • use_tscore

    This specifies whether to use T-score for scoring the collocates. The possible values are TRUE or FALSE, with the default being TRUE.

    Set this attribute to TRUE to identify collocates that are common tokens. Set this attribute to FALSE to identify collocates that emphasize unique words.

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:

• ctx_result_set

  This is the root element for the generated result set. There are no attributes. The parent is none. The possible child elements are:

  • At most one hitlist element.

  • Zero or more groups elements.

• groups

  This delimits the start of a group breakdown section. The parent element is ctx_result_set. The available attributes are:

  • sdata

    This is the name of the sdata section used for grouping.

  The possible child elements are:

  • Zero or more group elements.

• group

  This delimits the start of a GROUP BY value. The parent element is the groups element. The available attributes are:

  • value

    This is the value of the sdata section.

  The possible child elements are at most one count element.

• hitlist

  This delimits the start of hitlist information. The parent element is ctx_result_set, while the children are zero or more hit elements. There are no attributes.

• hit

  This delimits the start of the information for a particular document within a hitlist. The parent element is hitlist, and there are no available attributes. The possible child elements are:

  • Zero or one rowid elements.

  • Zero or one score element.

  • Zero or one sdata element.

  • Zero or one snippet element.

• rowid

  This is the rowid of the document, so the content is the rowid of the document. The parent element is the hit element. There are no child elements, and no available attributes.

• score

  This is the score of the document. The parent element is the hit element. The content is the numeric score. There are no available attributes, and no possible child elements.

• sdata

  This is the SDATA value or values for the document. The parent element is the hit element, and the available attribute is name, which is the name of the sdata section. There are no possible child elements available. The content is the SDATA section value, which, for DATE values, is in the format “YYYY-MM-DD HH24:MI:SS”, depending upon the actual values being stored.

• count

  This is the document hit count. The parent element is the ctx_result_set element or the group element. It contains the numeric hit count, has no attributes, and no possible child elements.

• sentiment

  This delimits the sentiment element for the hitlist document. Its child element is item and parent is hitlist. It contains no attributes in the output result set.

• item

  This delimits the item element for the hitlist document. Parent element is sentiment and child elements are segment, score, and doc. It has one attribute called topic.

• segment

  This delimits an instance of segment element in a hit. Parent element is item. Child elements are segment_text and segment_score. It contains no attributes.

• segment_text

  This specifies the text segment for the given item topic. Parent element is segment. It has no child elements or attributes.

• segment_score

  This specifies the sentiment score for the segment. Parent element is segment. It has no child elements or attributes.

• score

  This specifies the sentiment score for the document or for the parent item topic. When present within collocation it specifies the collocation score for the particular collocation keyword. Parent element is doc or collocation. It has no child elements or attributes

• doc

  This denotes the sentiment score is for the entire document. Its parent element is item and child element is score. It has no attributes.

• collocates

  This delimits the collocates element for the result set output. Parent element is ctx_result_set and child element is collocation. It has no attributes.

• collocation

  This denotes a single collocation. Parent element is collocates and child elements are word and score. It has no attributes.

• word

  This specifies the collocates token. Its parent element is collocation. It has no child elements or attributes.

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:

• top 5 hits displaying, score, rowid, author SDATA section value, and pubDate SDATA section value, order by pubDate SDATA section value DESC and score DESC

• total doc hit count for the text query

• counts group by pubDate SDATA section values

• counts group by author SDATA section values

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>

Limitations and Restrictions

The following limitations and restrictions apply for RESULT_SET.

• The Result Set Interface (RSI) is not supported with Virtual Private Database. (VPD is supported with the regular CONTAINS query, but not with RSI.)

• In order to execute the function, you must be able to query the base table.

• If a VPD policy is active on the base table, the documents portion of the result set will not show any documents to which you are not entitled.

• When a VPD policy is being used, aggregate measures such as count may not be accurate.

Related Topics

• Oracle Text Application Developer’s Guide for information on the XML Result Set Interface
• Oracle Text Application Developer’s Guide for more information on faceted navigation.