15.2 Create a Custom End-User Context

Use the CREATE END USER CONTEXT command to define a new, custom end-user context object in the database.

The context definition specifies a set of named attributes, their data types, default values, and optional PL/SQL event handlers. The definition acts as a template that the database instantiates at runtime within each end-user security context. You can assign each attribute a default value or associate it with a PL/SQL handler function for lazy loading on first read.

Required privilege

• The CREATE END USER CONTEXT system privilege to create an end-user context in the executing user's own schema.
• The CREATE ANY END USER CONTEXT system privilege to create an end-user context in any schema.

Syntax

CREATE [OR REPLACE] END USER CONTEXT [IF NOT EXISTS] [schema.]name
    USING JSON SCHEMA '{JSON_Schema}';

JSON_Schema ::= "type": "object", properties
properties  ::= "properties": { attribute [, attribute]... }
attribute   ::= attr_name : {
                    "type": attr_type,
                    property_name_value
                }
attr_type   ::= {"integer" | "string" | "object" | "null"}
property_name_value ::=
    {"o:onFirstRead" : "hdlowner.package.function" |
     "default" : value}

Parameters

Parameter	Description
schema	The schema (owner) name of the end-user context definition. If omitted, the current schema is used.
name	The name of the end-user context definition.
attr_name	The name of the attribute within the context.
attr_type	The data type of the attribute. Supported types are integer, string, and object. Use object for nested attributes.
value	The default value of the attribute. A JSON number for integer attributes; a double-quoted JSON string for string attributes.
hdlowner	The schema that owns the PL/SQL handler function for the attribute.
package	The PL/SQL package that contains the handler function.
function	The PL/SQL function name that is executed when the specified event (o:onFirstRead) occurs on the attribute.

Usage notes and restrictions

• When IF NOT EXISTS is specified:
  • If the context exists, the statement is a no-op. No error is raised.
  • If the context does not exist, it is created.
• When IF NOT EXISTS is omitted:
  • If the context already exists, an error is raised.
  • If the context does not exist, it is created.
• When OR REPLACE is specified:
  • If the context already exists, its attribute-value settings are replaced.
  • If the context does not exist, it is created.
• When OR REPLACE is omitted:
  • If the context already exists, an error is raised.
  • If the context does not exist, it is created.
• OR REPLACE and IF NOT EXISTS cannot be combined in the same statement. If combined, an error is raised.
• Event handlers:
  • Each attribute can specify either a default value or an event handler, not both.
  • The o:onFirstRead event causes the handler function to be called the first time the attribute is read during a session. The handler function must then set the attribute value.
  • A context can have at most one unique PL/SQL handler function. If multiple attributes reference event handlers, they must all reference the same schema.package.function; otherwise, an error is raised.

Note:

• Context instantiation is transparent. When an end user or application references a custom context for the first time, the database automatically instantiates it from the definition. No explicit instantiation command is required.
• Context instances are session-specific. They are only visible and modifiable within the current session.

For syntax diagrams and additional details, see CREATE END USER CONTEXT in Oracle AI Database SQL Language Reference.

Example 15-1 Create a custom context with lazy-loaded and static attributes

The following example creates an end-user context hr.hcm_context with two attributes. The emp_id attribute is loaded lazily through a PL/SQL callback. The org_id attribute has a static default value of 1.

CREATE END USER CONTEXT hr.hcm_context USING JSON SCHEMA '{
    "type": "object",
    "properties": {
        "emp_id": {
            "type": "integer",
            "o:onFirstRead": "hr.hcm_core.init_user_context"
        },
        "org_id": {
            "type": "integer",
            "default": 1
        }
    }
}';

When emp_id is first accessed, the database invokes hr.hcm_core.init_user_context to set the value. The org_id attribute is always initialized with the value 1.

Example 15-2 Replace an existing context definition

To replace the definition of an existing end-user context, use OR REPLACE.

CREATE OR REPLACE END USER CONTEXT hr.hcm_context USING JSON SCHEMA '{
    "type": "object",
    "properties": {
        "emp_id": {
            "type": "integer",
            "o:onFirstRead": "hr.hcm_core.init_user_context"
        },
        "org_id": {
            "type": "integer",
            "default": 1
        },
        "region_id": {
            "type": "string",
            "default": "EMEA"
        }
    }
}';

Example 15-3 Create a context only if it does not already exist

CREATE END USER CONTEXT IF NOT EXISTS hr.hcm_context USING JSON SCHEMA '{
    "type": "object",
    "properties": {
        "org_id": {
            "type": "integer",
            "default": 1
        }
    }
}';