STORE_SQE

This procedure creates either a stored or session-duration query expression (SQE). Only the query definition is stored.

SQEs are used to store the definition of a query without storing any results. Referencing the query with the CONTAINS SQL operator references the definition of the query. In this way, SQEs make it easy for defining long or frequently used query expressions. Creating a session-duration SQE is useful for when you do not want the maintenance overhead of deleting unused or no longer needed SQEs.

Supported Operators

Stored query expressions support all of the CONTAINS query operators. Stored query expressions also support all of the special characters and other components that can be used in a query expression, including other stored query expressions.

Privileges

Users are permitted to create and remove stored query expressions owned by them. Users are permitted to use stored query expressions owned by anyone. The CTXSYS user can create or remove stored query expressions for any user.

Syntax

Syntax 1

CTX_QUERY.STORE_SQE(
           query_name      IN VARCHAR2,
           text_query      IN VARCHAR2,
           duration        IN NUMBER default CTX_QUERY.DURATION_PERSISTENT
);

Syntax 2

CTX_QUERY.STORE_SQE_CLOB_SYNTAX(
           query_name      IN VARCHAR2,
           text_query      IN CLOB,
           duration        IN NUMBER default CTX_QUERY.DURATION_PERSISTENT
);

query_name

Specify the name of the stored query expression to be created.

text_query

Specify the query expression to be associated with query_name.

duration

The possible values are DURATION_SESSION and DURATION_PERSISTENT.

• When duration is to set to DURATION_SESSION, the stored query expression is stored in a PL/SQL package variable and is available for the session.

• When duration is to set to DURATION_PERSISTENT, the stored query expression is stored in a database table, and can be referenced by other database sessions.

• SQEs with the DURATION_SESSION option are not supported when issued from the catalog of a sharded database. Use the DURATION_PERSISTENT option instead.

• The query_name namespace is shared between the persistent and session-duration SQEs. If you try to add a persistent or session-duration SQE with a name that is already used by another persistent or session-duration SQE, then an error will be raised.

duration_persistent

When there is a CLOB query, specify that the duration is stored in a database table. This SQE must be deleted when it is no longer needed.

• The query_name namespace is shared between the persistent and session-duration SQEs. If you try to add a persistent or session-duration SQE with a name that is already used by another persistent or session-duration SQE, then an error will be raised.

Example

begin
  ctx_query.store_sqe('dis1', 'flood', CTX_QUERY.DURATION_SESSION);
  ctx_query.store_sqe('dis2', 'tornado', CTX_QUERY.DURATION_PERSISTENT);
  ctx_query.store_sqe('dis3', 'fire')
end;
/

Restriction

SQEs are supported in logical standby only starting with Oracle AI Database release 26ai and not in previous releases of Oracle AI Database.