DBMS_DATA_ACCESS Package

The DBMS_DATA_ACCESS package provides routines to generate and manage Table Hyperlinks for data sets.

Parent topic: Autonomous Database Supplied Package Reference

DBMS_DATA_ACCESS Overview

Describes the use of the DBMS_DATA_ACCESS package.

DBMS_DATA_ACCESS supports these operations:

  • Generation of a Table Hyperlink
  • Manual invalidation of a Table Hyperlink
  • Listing of active Table Hyperlinks

Parent topic: DBMS_DATA_ACCESS Package

DBMS_DATA_ACCESS Security Model

Security on this package can be controlled by granting EXECUTE on this package to selected users or roles.

When a user has been granted EXECUTE on DBMS_DATA_ACCESS they are able to create, list or invalidate the Table Hyperlinks that are created by the user. In addition, by default the ADMIN user has the following privileges:
  • The ADMIN user with PDB_DBA role has EXECUTE privilege on DBMS_DATA_ACCESS.
  • The ADMIN user with the PDB_DBA role is able to list or invalidate any Table Hyperlink in an Autonomous Database instance.

Parent topic: DBMS_DATA_ACCESS Package

Summary of DBMS_DATA_ACCESS Subprograms

This section covers the DBMS_DATA_ACCESS subprograms provided with Autonomous Database.

Subprogram Description

ADD_MEMBER Procedure

CREATE_URL Procedure

This procedure generates a Table Hyperlink or a Table Hyperlink Group.

EXTEND_URL Procedure

This procedure extends the life of a Table Hyperlink.

GET_PREAUTHENTICATED_URL Procedure

This procedure generates a Table Hyperlink.

This procedure is deprecated. Instead use the CREATE_URL Procedure.

EXTEND_URL Procedure

This procedure extends the life of a Table Hyperlink.

INVALIDATE_URL Procedure

This procedure invalidates a Table Hyperlink.

LIST_ACTIVE_URLS Function

This function lists all the currently active Table Hyperlinks.

LIST_MEMBERS Procedure

REMOVE_MEMBER Procedure

Parent topic: DBMS_DATA_ACCESS Package

ADD_MEMBER Procedure

This procedure adds an existing Table Hyperlink to the Table Hyperlink Group as a member.

Syntax

DBMS_DATA_ACCESS.ADD_MEMBER(
    id        IN VARCHAR2,
    member_id       IN BOOLEAN DEFAULT FALSE,
    result          OUT CLOB);

Parameters

Parameter Description

id

Specifies the identifier for the Table Hyperlink Group.
member_id

Specifies the identifier for the Table Hyperlink to add to the group.

result

Provides JSON to indicate whether invalidation is a success or a failure (CLOB).

Example

DECLARE
    status CLOB;
    BEGIN
       DBMS_DATA_ACCESS.ADD_MEMBER(
        id => 'Vd1Px7QWASdqDbnndiuwTAyyEstv82PCHqS_example',
        member_id => 'Zdd1Px7QWASdqDbnndiuwTAyyEstv82PCHlS_example',
        result => status);           
       dbms_output.put_line(status);
    END;
/

Parent topic: Summary of DBMS_DATA_ACCESS Subprograms

CREATE_URL Procedure

This procedure generates a Table Hyperlink or a Table Hyperlink Group. A Table Hyperlink Group provides access to multiple Table Hyperlinks with a single URL. This procedure is overloaded.

Syntax

DBMS_DATA_ACCESS.CREATE_URL( 
    schema_name           IN VARCHAR2,
    schema_object_name    IN VARCHAR2,
    application_user_id   IN VARCHAR2,
    expiration_minutes    IN NUMBER,
    expiration_count      IN NUMBER,
    service_name          IN VARCHAR2,
    column_lists          IN CLOB,
    inherit_acl           IN BOOLEAN  DEFAULT FALSE,
    result                OUT CLOB);

DBMS_DATA_ACCESS.CREATE_URL( 
    sql_statement         IN CLOB,
    application_user_id   IN VARCHAR2,
    default_bind_values   IN CLOB,
    expiration_minutes    IN NUMBER,
    expiration_count      IN NUMBER,
    service_name          IN VARCHAR2,
    column_lists          IN CLOB,
    inherit_acl           IN BOOLEAN  DEFAULT FALSE,
    result                OUT CLOB);

DBMS_DATA_ACCESS.CREATE_URL( 
    sqls                  IN CLOB,
    application_user_id   IN VARCHAR2,
    expiration_minutes    IN NUMBER,
    expiration_count      IN NUMBER,
    service_name          IN VARCHAR2,
    inherit_acl           IN BOOLEAN   DEFAULT FALSE,
    result                OUT CLOB);

Parameters

Parameter Description

sqls

Specifies a JSON array of the SELECT statements or schema objects.

See the following description for details on the format of the JSON array.

schema_name

Specifies the owner of the object.

schema_object_name

Specifies the schema object (table or view).

sql_statement

Specifies the SELECT statement query text. Bind variable support is available for NUMBER and VARCHAR2 column types.

application_user_id

This optional parameter specifies an application user ID value. When the Table Hyperlink or Table Hyperlink Group is accessed, the value of application_user_id specified during Table Hyperlink generation is available through:

sys_context('DATA_ACCESS_CONTEXT$', 'USER_IDENTITY')

You can define VPD Policies that make use of this value in the Application Context to restrict the rows visible to the application user.

default_bind_values

Specifes the default value(s) of one or more bind variables (for a sql_statement specified with bind variables).

This allows a table hyperlink consumer to access the table hyperlink data with default bind values, without providing the bind values as query parameters.

expiration_minutes

This optional parameter specifies the duration in minutes of validity of the Table Hyperlink or of the Table Hyperlink Group.

The maximum allowed expiration time is 90 days (129600 minutes). If the value is set to greater than 129600, the value used is 129600 minutes (90 days).

If expiration_minutes is specified as a non-null value, expiration_count must not be set to a non-null value. Both cannot be non-null at the same time.

Default value: when expiration_minutes is not provided or when expiration_minutes is provided as NULL, the value is set to 90 days (129600 minutes).

expiration_count

This optional parameter specifies the number of accesses allowed on the Table Hyperlink or on the Table Hyperlink Group.

There is no default value.

If expiration_count is not specified and expiration_minutes is not specified, expiration_minutes is set to 90 days (129600 minutes).

If expiration_count is specified as a non-null value, expiration_minutes must not be set to a non-null value. Both cannot be non-null at the same time.

service_name

The database service to use for data retrieval when using the Table Hyperlink. Specify the service-level guarantee and resources used to service this Table Hyperlink. For example, access to an object or SQL statement can be mapped to services HIGH or MEDIUM, whereas access to another object or SQL statement can be mapped to the LOW service. Supported values are HIGH, MEDIUM, LOW.

The default value is LOW.

column_lists

A JSON value that specifies options by column. The supported options specified in the column_lists parameter are one or more of:

  • order_by_columns: specifies the columns that support sorting.

  • filter_columns: specifies the columns that support filtering

  • default_color_columns: specifies to only use the default coloring for the specified columns.

  • group_by_columns: specifies that group by is allowed for the specified columns (viewing the data by grouping the specified column is allowed).

The column_lists parameter is JSON that contains a list of JSON arrays of columns defining Table Hyperlink functionality. Use this parameter to specify the columns for one or more of the options: order_by_columns, filter_columns, default_color_columns, or group_by_columns.

The form is:

"column_lists" : {
        "order_by_columns": [order_by_columns_list],
        "filter_columns": [filter_columns_list],
        "default_color_columns": [default_color_columns_list],
        "group_by_columns": [group_by_columns_list]
},

For example: 

"column_lists" : {
            "order_by_columns": ["NAME", "DEPARTMENT"],
            "filter_columns": ["ID", "NAME", "DEPARTMENT"],
            "default_color_columns": ["DEPARTMENT"],
            "group_by_columns": ["DEPARTMENT"]
},

Default values:

If column_lists is not specified for order_by_columns and filter_columns options, sorting and filtering is enabled for all columns.

If column_lists is not specified for group_by_columns, the group by option is not enabled for any column. By default, columns defined to enable as group_by_columns are also be enabled as filter_columns.

inherit_acl

This parameter is optional. Set the value to TRUE to inherit ACLs. When this parameter is TRUE, an incoming Table Hyperlink or Table Hyperlink Group's consumer's IP address is validated with the ACL lists on the producer database before allowing access to data.

When the parameter is not supplied or the parameter value is set to FALSE, ACL checks are not applied.

If the producer database does not have ACLs configured, the inherit_acl value is ignored and data access is allowed without any ACL checks.

The default value is FALSE.

result

JSON that indicates the outcome of the operation.

The format of the JSON array you supply as the sqls parameter is:

Attribute Name Required Description
name No Specifies the group member name. When no name is provided, the procedure creates a default name.
description No Group member description
sql_statement

Providing either sql_statement or schema_object_name is mandatory

SQL statement for the member

See Generate a Table Hyperlink with a Select Statement for details.

schema_name No

Schema name for the member. Provide a schema_name value only when the table/view provided in schema_object_name is not available in current schema

See Generate a Table Hyperlink for a Table or a View for details.

schema_object_name

Providing either sql_statement or schema_object_name is mandatory

Table/View name for the member

See Generate a Table Hyperlink for a Table or a View for details.

default_bind_variable No Applicable only for sql_statements with bind variables

See Generate a Table Hyperlink with a Select Statement for details.

column_lists No Same as defined for the creation of a non-group Table Hyperlink

See Generate a Table Hyperlink with UI Features Specified on Columns for details.

Usage Notes

  • There is a limit of 128 active Table Hyperlinks on an Autonomous Database instance.

  • When using a Table Hyperlink from a browser, the following options are supported:
    • View the returned data in table format with no coloring (default), by appending the ?view=table query parameter to the Table Hyperlink.
    • View the returned data in table format and select the column or columns you want colored with preset colors based on column values. To do this, append the ?view=table&colored_column_names=column_name_1,column_name_2,...column_name_n query parameter to the Table Hyperlink, where column_name_1 through column_name_n are the names of the columns you want colored.
    • View the returned data in table format and select a specific column data type you want colored with preset colors, by appending the ?view=table&colored_column_types=data_type query parameter. The supported data_type parameter values are VARCHAR and NONE.

    • The sql_statement parameter value must be a SELECT statement. The SELECT statement supports bind variables.

      If bind variables are included in the select statement and values are not set in the default_bind_values parameter, bind variable values must be appended to the generated Table Hyperlink as a query parameter when accessing the data.

      When you include the default_bind_values parameter, when you access the data you can omit the bind variable values when default values are specified in the default_bind_values parameter. You can override a default bind variable value specified with default_bind_values by explicitly supplying the bind variable value as a query parameter.

  • When you generate a Table Hyperlink on an Autonomous Database instance with a private endpoint, the result includes a name private_preauth_url with the value of the form: "https://private-endpoint/adb/p/parurl-token/data".

    When you generate a Table Hyperlink on an Autonomous Database instance with a private endpoint and the private endpoint is configured with Allow public access enabled, the result includes both the preauth_url for the public endpoint and private_preauth_url.

    See Configure Private Endpoints and Use a Private Endpoint with Public Access Allowed for more information.

Examples

Example - Table Hyperlink Generated for a Specific Object

The following example generates a Table Hyperlink for STUDENTS_VIEW: 

DECLARE
   status CLOB;
   BEGIN
   DBMS_DATA_ACCESS.CREATE_URL(
      schema_name => 'USER1',
      schema_object_name => 'STUDENTS_VIEW',
      expiration_minutes => 120,
      service_name => 'HIGH',
      result => status);
   dbms_output.put_line(status);
END;
/

Example - Table Hyperlink Generated for a SQL Statement

The following example generates a Table Hyperlink for a SELECT SQL statement: 

DECLARE
   status CLOB;
   par_url_app_string CLOB;
   BEGIN
       par_url_app_string := 1919292929;
       DBMS_DATA_ACCESS.CREATE_URL(
            sql_statement => 'SELECT student_id, student_name FROM STUDENTS_VIEW ORDER BY student_id',
            application_user_id => par_url_app_string,
            expiration_count => 25,
            result => status);
END;
/

Example - Table Hyperlink Generated for a SQL Statement with a Bind Variable

The following example uses a bind variable in the SELECT statement to generate the Table Hyperlink: 

set serveroutput on 
DECLARE
  status clob; 
BEGIN
  DBMS_DATA_ACCESS.CREATE_URL(
    sql_statement => 'select * from TREE_DATA WHERE COUNTY = :countyNAME',
    expiration_minutes => 3000,
    result => status);
    dbms_output.put_line('status : '||status);
END;
/

To use the generated Table Hyperlink, the bind variable value must be passed. The following example uses the generated Table Hyperlink to access tree data for the first county:

https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/gTlbq...example/data?countyNAME=First

Use Table Hyperlink to Access Data with Bind Variables

The following example uses a bind variable in the SELECT statement and includes the default_bind_values parameter to generate the Table Hyperlink: 

set serveroutput on 
DECLARE
  status clob; 
BEGIN
  DBMS_DATA_ACCESS.CREATE_URL(
    sql_statement = 'SELECT * FROM TREE_DATA WHERE COUNTY = :countyNAME',
    default_bind_values => '{"countyNAME" : "First"}',
    expiration_minutes => 3000,
    result => status);
    dbms_output.put_line('status : '||status);
END;
/

In this case, case the default bind variable value is used and you do not need to provide the value as a query parameter. For example:

curl https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6X...example/data
curl https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6X...example/data

{"items":[
{"COUNTY":"First","SPECIES":"Pine","HEIGHT":16},
{"COUNTY":"First","SPECIES":"Spruce","HEIGHT":6},
{"COUNTY":"First","SPECIES":"Hawthorn","HEIGHT":19},
{"COUNTY":"First","SPECIES":"Cherry","HEIGHT":20},
{"COUNTY":"First","SPECIES":"Chestnut","HEIGHT":51}],
"hasMore":false,
"limit":100,
"offset":0,
"count":6,
"links":
[
{"rel":"self",
"href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/gTlbq...example/data"}
]}

You can override the default bind variable value by explicitly specifying the value as a query parameter. For example:

curl https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6X...example/data?countyNAME=MAIN

Example - Table Hyperlink Generated for a Specific Object with Group By Columns

The following example generates a Table Hyperlink for a specific table with Group By columns specified:

DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.CREATE_URL(
          schema_name => 'ADMIN',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          service_name          => 'HIGH',
          column_lists          => {"group_by_columns": ["COUNTY", "SPECIES"]}',
          result                => status);

       dbms_output.put_line(status);
    END;
/

Examples

DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.CREATE_URL(
          sqls => '[{"name": "employee", "description": "employee description", "schema_name":"admin", "schema_object_name":"employee"},
              {"name":"tree", "description": "tree description", "sql_statement": "select * from admin.tree_data"}]',
          expiration_count     => 10,
          service_name         => 'HIGH',
          result               => status);
       dbms_output.put_line(status);
    END;
/

Parent topic: Summary of DBMS_DATA_ACCESS Subprograms

GET_PREAUTHENTICATED_URL Procedure

This procedure generates a Table Hyperlink.

There are two forms, one to generate the Table Hyperlink for a specific object (table or view). The overloaded form, using the sql_statement parameter, generates a Table Hyperlink for a SQL statement.

Note:

This procedure is deprecated. Instead use the CREATE_URL Procedure.

Syntax

DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL( 
    schema_name           IN VARCHAR2,
    schema_object_name    IN VARCHAR2,
    application_user_id   IN VARCHAR2,
    expiration_minutes    IN NUMBER,
    expiration_count      IN NUMBER,
    service_name          IN VARCHAR2,
    column_lists          IN CLOB,
    inherit_acl           IN BOOLEAN  DEFAULT FALSE,
    result                OUT CLOB);

DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL( 
    sql_statement         IN CLOB,
    application_user_id   IN VARCHAR2,
    default_bind_values   IN CLOB,
    expiration_minutes    IN NUMBER,
    expiration_count      IN NUMBER,
    service_name          IN VARCHAR2,
    column_lists          IN CLOB,
    inherit_acl           IN BOOLEAN  DEFAULT FALSE,
    result                OUT CLOB);

Parameters

Parameter Description

schema_name

Specifies the owner of the object.

schema_object_name

Specifies the schema object (table or view).

sql_statement

Specifies the SELECT statement query text. Bind variable support is available for NUMBER and VARCHAR2 column types.

application_user_id

Specifies an application user ID value. When the Table Hyperlink is accessed, the value of application_user_id specified during Table Hyperlink generation is available through:

sys_context('DATA_ACCESS_CONTEXT$', 'USER_IDENTITY')

You can define VPD Policies that make use of this value in the Application Context to restrict the rows visible to the application user.

default_bind_values

Specifes the default value(s) of one or more bind variables (for a sql_statement specified with bind variables).

This allows a table hyperlink consumer to access the table hyperlink data with default bind values, without providing the bind values as query parameters.

expiration_minutes

Duration in minutes of validity of Table Hyperlink.

The maximum allowed expiration time is 90 days (129600 minutes). If the value is set to greater than 129600, the value used is 129600 minutes (90 days).

If expiration_minutes is specified as a non-null value, expiration_count must not be set to a non-null value. Both cannot be non-null at the same time.

Default value: when expiration_minutes is not provided or when expiration_minutes is provided as NULL, the value is set to 90 days (129600 minutes).

expiration_count

Number of accesses allowed on the Table Hyperlink.

There is no default value.

If expiration_count is not specified and expiration_minutes is not specified, expiration_minutes is set to 90 days (129600 minutes).

If expiration_count is specified as a non-null value, expiration_minutes must not be set to a non-null value. Both cannot be non-null at the same time.

service_name

The database service to use for data retrieval when using the Table Hyperlink. Specify the service-level guarantee and resources used to service this Table Hyperlink. For example, access to an object or SQL statement can be mapped to services HIGH or MEDIUM, whereas access to another object or SQL statement can be mapped to the LOW service. Supported values are HIGH, MEDIUM, LOW.

The default value is LOW.

column_lists

A JSON value that specifies options by column. The supported options specified in the column_lists parameter are one or more of:

  • order_by_columns: specifies the columns that support sorting.

  • filter_columns: specifies the columns that support filtering

  • default_color_columns: specifies to only use the default coloring for the specified columns.

  • group_by_columns: specifies that group by is allowed for the specified columns (viewing the data by grouping the specified column is allowed).

The column_lists parameter is JSON that contains a list of JSON arrays of columns defining Table Hyperlink functionality. Use this parameter to specify the columns for one or more of the options: order_by_columns, filter_columns, default_color_columns, or group_by_columns.

The form is:

"column_lists" : {
        "order_by_columns": [order_by_columns_list],
        "filter_columns": [filter_columns_list],
        "default_color_columns": [default_color_columns_list],
        "group_by_columns": [group_by_columns_list]
},

For example: 

"column_lists" : {
            "order_by_columns": ["NAME", "DEPARTMENT"],
            "filter_columns": ["ID", "NAME", "DEPARTMENT"],
            "default_color_columns": ["DEPARTMENT"],
            "group_by_columns": ["DEPARTMENT"]
},

Default values:

If column_lists is not specified for order_by_columns and filter_columns options, sorting and filtering is enabled for all columns.

If column_lists is not specified for group_by_columns, the group by option is not enabled for any column. By default, columns defined to enable as group_by_columns are also be enabled as filter_columns.

inherit_acl

Set the value of this parameter to TRUE to inherit ACLs. When inherit is true, an incoming Table Hyperlink consumer's IP address is validated with the ACL lists on the producer database before allowing access to data.

When the parameter is not supplied or the parameter value is set to FALSE, ACL checks are not applied.

If the producer database does not have ACLs configured, the inherit_acl value is ignored and data access is allowed without any ACL checks.

The default value is FALSE.

result

JSON that indicates the outcome of the operation.

Usage Notes

  • This procedure is deprecated. Instead use the CREATE_URL Procedure.

  • There is a limit of 128 active Table Hyperlinks on an Autonomous Database instance.

  • When you generate a Table Hyperlink on an Autonomous Database instance with a private endpoint, the result includes a name private_preauth_url with the value of the form: "https://private-endpoint/adb/p/parurl-token/data".

    When you generate a Table Hyperlink on an Autonomous Database instance with a private endpoint and the private endpoint is configured with Allow public access enabled, the result includes both the preauth_url for the public endpoint and private_preauth_url.

    See Configure Private Endpoints and Use a Private Endpoint with Public Access Allowed for more information.

Example - Table Hyperlink Generated for a Specific Object

The following example generates a Table Hyperlink for STUDENTS_VIEW: 

DECLARE
   status CLOB;
   BEGIN
   DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
      schema_name => 'USER1',
      schema_object_name => 'STUDENTS_VIEW',
      expiration_minutes => 120,
      service_name => 'HIGH',
      result => status);
   dbms_output.put_line(status);
END;
/

Example - Table Hyperlink Generated for a SQL Statement

The following example generates a Table Hyperlink for a SELECT SQL statement: 

DECLARE
   status CLOB;
   par_url_app_string CLOB;
   BEGIN
       par_url_app_string := 1919292929;
       DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
            sql_statement => 'SELECT student_id, student_name FROM STUDENTS_VIEW ORDER BY student_id',
            application_user_id => par_url_app_string,
            expiration_count => 25,
            result => status);
END;
/

Example - Table Hyperlink Generated for a SQL Statement with a Bind Variable

The following example uses a bind variable in the SELECT statement to generate the Table Hyperlink: 

set serveroutput on 
DECLARE
  status clob; 
BEGIN
  DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
    sql_statement => 'select * from TREE_DATA WHERE COUNTY = :countyNAME',
    expiration_minutes => 3000,
    result => status);
    dbms_output.put_line('status : '||status);
END;
/

To use the generated Table Hyperlink, the bind variable value must be passed. The following example uses the generated Table Hyperlink to access tree data for the first county:

https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/gTlbq...example/data?countyNAME=First

Use Table Hyperlink to Access Data with Bind Variables

The following example uses a bind variable in the SELECT statement and includes the default_bind_values parameter to generate the Table Hyperlink: 

set serveroutput on 
DECLARE
  status clob; 
BEGIN
  DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
    sql_statement = 'SELECT * FROM TREE_DATA WHERE COUNTY = :countyNAME',
    default_bind_values => '{"countyNAME" : "First"}',
    expiration_minutes => 3000,
    result => status);
    dbms_output.put_line('status : '||status);
END;
/

In this case, case the default bind variable value is used and you do not need to provide the value as a query parameter. For example:

curl https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6X...example/data
curl https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6X...example/data

{"items":[
{"COUNTY":"First","SPECIES":"Pine","HEIGHT":16},
{"COUNTY":"First","SPECIES":"Spruce","HEIGHT":6},
{"COUNTY":"First","SPECIES":"Hawthorn","HEIGHT":19},
{"COUNTY":"First","SPECIES":"Cherry","HEIGHT":20},
{"COUNTY":"First","SPECIES":"Chestnut","HEIGHT":51}],
"hasMore":false,
"limit":100,
"offset":0,
"count":6,
"links":
[
{"rel":"self",
"href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/gTlbq...example/data"}
]}

You can override the default bind variable value by explicitly specifying the value as a query parameter. For example:

curl https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6X...example/data?countyNAME=MAIN

See GET_PREAUTHENTICATED_URL Procedure for more information.

Example - Table Hyperlink Generated for a Specific Object with Group By Columns

The following example generates a Table Hyperlink for a specific table with Group By columns specified:

DECLARE
   status CLOB;
   BEGIN
      DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL(
          schema_name => 'ADMIN',
          schema_object_name    => 'TREE_DATA',
          expiration_minutes    => 360,
          service_name          => 'HIGH',
          column_lists          => {"group_by_columns": ["COUNTY", "SPECIES"]}',
          result                => status);

       dbms_output.put_line(status);
    END;
/

Parent topic: Summary of DBMS_DATA_ACCESS Subprograms

EXTEND_URL Procedure

This procedure extends the life of a Table Hyperlink or of a Table Hyperlink Group.

Syntax:

DBMS_DATA_ACCESS.EXTEND_URL( 
    id                              IN VARCHAR2,
    extend_expiration_minutes_by    IN NUMBER,
    extend_expiration_count_by      IN NUMBER,
    result                          OUT CLOB);

Parameters

Parameter Description

id

Specifies the ID of the Table Hyperlink or the Table Hyperlink Group to extend.

extend_expiration_minutes_by

Number of minutes by which to extend expiration time of the Table Hyperlink. The expiration time is set to the current expiration time plus the value of extend_expiration_minutes_by.

The value for extend_expiration_minutes_by plus the current expiration time must not exceed 129600 (which corresponds to 90 days).

If extend_expiration_minutes_by is null, extend_expiration_count_by must not be null. Both cannot be null at the same time.

Default value is NULL.

extend_expiration_count_by

The number of accesses on the Table Hyperlink is extended by this count. The expiration count is set to the current expiration count plus the value of extend_expiration_count_by.

If extend_expiration_count_by is null, extend_expiration_minutes_by must not be null. Both cannot be null at the same time.

Default value is null.

result

JSON that indicates the outcome of the operation.

Usage Note

With a Table Hyperlink Group id the procedure extends all the member Table Hyperlinks except the members added with DBMS_DATA_ACCESS.ADD_MEMBER. The members that were added to the Table Hyperlink Group with DBMS_DATA_ACCESS.ADD_MEMBER maintain their independent Table Hyperlink invalidation values and you can extend them individually using DBMS_DATA_ACCESS.EXTEND_URL.

Example - Extend Expiration Minutes of Table Hyperlink

set serveroutput on
declare
  status clob;
  js_status json_object_t;
  js_arr    json_array_t;
  url_id varchar2(4000);
begin
  -- Initially sets the expiration time to 60 minutes
  dbms_data_access.get_preauthenticated_url(
    schema_name        => 'SCOTT',     -- Schema name
    schema_object_name => 'EMPLOYEE',  -- Schema object name
    expiration_minutes => 60,          -- Expiration minutes
    service_name       => 'HIGH',
    result             => status);
   js_status := json_object_t.parse(status);
  url_id := js_status.get_string('id');
  dbms_output.put_line('The url id of url: ' || url_id);
  dbms_output.put_line('Initial Expiration Time: ' ||
                       js_status.get_string('expiration_ts'));
  -- Extend the expiration minutes by 1 day, the url would now expire
  -- 24 hours later than the previous expiration time
  dbms_data_access.extend_url(
    id                           => url_id,
    extend_expiration_minutes_by => 1440,
    result                       => status);
   -- List urls created
  status := dbms_data_access.list_active_urls;
  js_arr := json_array_t.parse(status);
  for indx in 0.. js_arr.get_size - 1
  loop
    js_status := TREAT (js_arr.get (indx) AS json_object_t);
    if js_status.get_string('id') = url_id then
      dbms_output.put_line('New Expiration Time : ' ||
                            js_status.get_string('expiration_time'));
      exit;
    end if;
  end loop;
end;
/

Example - Extend Expiration Count of Table Hyperlink

set serveroutput on
declare  status clob;
  js_status json_object_t;
  js_arr    json_array_t;
  url_id varchar2(4000);
begin
  -- Initially sets the expiration count to 100
  dbms_data_access.get_preauthenticated_url(
    schema_name        => 'SCOTT',     -- Schema name
    schema_object_name => 'EMPLOYEE',  -- Schema object name
    expiration_count   => 100,         -- Expiration count
    service_name       => 'HIGH',
    result             => status);
  js_status := json_object_t.parse(status);
  url_id := js_status.get_string('id');
  dbms_output.put_line('The url id of url: ' || url_id);
  dbms_output.put_line('Initial Expiration Count: ' ||
                       js_status.get_string('expiration_count'));
  -- Extends access count by 100 so url would expire after 200 accesses
  dbms_data_access.extend_url(
    id                         => url_id,
    extend_expiration_count_by => 100,
    result                     => status);
  -- List urls created
  status := dbms_data_access.list_active_urls;
  js_arr := json_array_t.parse(status);
  for indx in 0.. js_arr.get_size - 1
  loop
    js_status := TREAT (js_arr.get (indx) AS json_object_t);
     if js_status.get_string('id') = url_id then
      dbms_output.put_line('New Expiration Count : ' ||
                            js_status.get_string('expiration_count'));
      exit;
    end if;
  end loop;
end;
/

Parent topic: Summary of DBMS_DATA_ACCESS Subprograms

INVALIDATE_URL Procedure

This procedure invalidates a Table Hyperlink or a Table Hyperlink Group.

Syntax

DBMS_DATA_ACCESS.INVALIDATE_URL(
    id                  IN VARCHAR2,
    kill_sessions       IN BOOLEAN DEFAULT FALSE,
    result              OUT CLOB);

Parameters

Parameter Description

id

Specifies the identifier for the Table Hyperlink or the Table Hyperlink Group to invalidate.

kill_sessions

This parameter is optional.

By default, when you run DBMS_DATA_ACCESS.INVALIDATE_URL existing sessions that may be in the middle of accessing data using a Table Hyperlink or a Table Hyperlink Group are not killed. When this parameter is set to TRUE, this value specifies that such existing sessions should be killed, so that the invalidation does not leave any ongoing access to a data set.

Valid values: TRUE | FALSE.

result

Provides JSON to indicate whether invalidation is a success or a failure (CLOB).

Usage Note

When the DBMS_DATA_ACCESS.INVALIDATE_URL id parameter is a Table Hyperlink Group, the procedure invalidates the group and all the group members, with the exception of any group members that were added with DBMS_DATA_ACCESS.ADD_MEMBER. After you run DBMS_DATA_ACCESS.INVALIDATE_URL, the members that were added with DBMS_DATA_ACCESS.ADD_MEMBER maintain their independent Table Hyperlink invalidation values and you can invalidate these Table Hyperlinks individually using DBMS_DATA_ACCESS.INVALIDATE_URL.

Example

DECLARE
    status CLOB;
    BEGIN
       DBMS_DATA_ACCESS.INVALIDATE_URL(
        id => 'Vd1Px7QWASdqDbnndiuwTAyyEstv82PCHqS_example',
        result => status);           
       dbms_output.put_line(status);
    END;
/

Parent topic: Summary of DBMS_DATA_ACCESS Subprograms

LIST_ACTIVE_URLS Function

This function lists all the currently active Table Hyperlinks and Table Hyperlink Groups.

Syntax

DBMS_DATA_ACCESS.LIST_ACTIVE_URLS RETURN CLOB;

Parameters

Parameter Description
RETURN

The return value is a JSON array.

Example

DECLARE
   result CLOB;
   BEGIN
       result := DBMS_DATA_ACCESS.LIST_ACTIVE_URLS;
       DBMS_OUTPUT.PUT_LINE(result);
   END;
[{"id":"pT36lYHFGA4s3UXSNBCRO13v3D4_example1",
"created_by":"SCOTT",
"service_name":"HIGH",
"expiration_time":"2025-07-28T16:38:02.723Z",
"expiration_count":10,
"access_count":0,
"created":"2025-04-29T16:38:02.977Z",
"inherit_acl":true,
"sql_statement":"select * FROM TREE_DATA WHERE COUNTY = :county"}]

Usage Notes

  • The behavior of DBMS_DATA_ACCESS.LIST_ACTIVE_URLS is dependent on the invoker. If the invoker is ADMIN or any user with PDB_DBA role, the function lists all active Table Hyperlinks, regardless of the user who generated the Table Hyperlink. If the invoker is not the ADMIN user and not a user with PDB_DBA role, the list includes only the active Table Hyperlinks generated by the invoker.

  • When you generate and list a Table Hyperlink on an Autonomous Database instance with a private endpoint, the result includes a name private_preauth_url with the value of the form: "https://private-endpoint/adb/p/parurl-token/data".

    When you generate and list a Table Hyperlink on an Autonomous Database instance with a private endpoint and the private endpoint is configured with Allow public access enabled, the result includes both the preauth_url for the public endpoint and private_preauth_url.

    See Configure Private Endpoints and Use a Private Endpoint with Public Access Allowed for more information.

  • When a Table Hyperlink is a group member the DBMS_DATA_ACCESS.LIST_ACTIVE_URLS response entry shows "group_ids" with a non-null value that includes one or more IDs. The IDs show Table Hyperlink Group IDs that the Table Hyperlink (group member) is a member of.

Parent topic: Summary of DBMS_DATA_ACCESS Subprograms

LIST_MEMBERS Procedure

This procedure lists the members of a Table Hyperlink Group.

Syntax

DBMS_DATA_ACCESS.LIST_MEMBERS(
    id              IN VARCHAR2,
    result          OUT CLOB);

Parameters

Parameter Description

id

Specifies the identifier for the Table Hyperlink Group.

result

Provides JSON to indicate whether invalidation is a success or a failure (CLOB).

Example

DECLARE
    status CLOB;
    BEGIN
       DBMS_DATA_ACCESS.LIST_MEMBERS(
         id => 'aGnHVyZ4vBo4_Fq2R0A2G2-y6TdUKRHeveqyGJ3_example',
         result => status);           
      dbms_output.put_line(status);
    END;
/

Parent topic: Summary of DBMS_DATA_ACCESS Subprograms

REMOVE_MEMBER Procedure

This procedure removes a member from a Table Hyperlink Group.

Syntax

DBMS_DATA_ACCESS.REMOVE_MEMBER(
    id              IN VARCHAR2,
    member_id       IN BOOLEAN DEFAULT FALSE,
    result          OUT CLOB);

Parameters

Parameter Description

id

Specifies the identifier for the Table Hyperlink Group.
member_id

Specifies the identifier for the group member to remove from the Table Hyperlink Group.

result

Provides JSON to indicate whether invalidation is a success or a failure (CLOB).

Usage Notes

  • The member_id value cannot be a Table Hyperlink Group ID (is_group_url must be false).

  • If the removed member is an existing Table Hyperlink which was added to the group using DBMS_DATA_ACCESS.ADD_MEMBER, the member is removed from the group but the Table Hyperlink could be accessed directly until it is explicitly invalidated or expires.

  • If a Table Hyperlink Group contains only one member and that member is removed, the group is invalidated.

Example

DECLARE
    status CLOB;
    BEGIN
       DBMS_DATA_ACCESS.REMOVE_MEMBER(
        id => 'Vd1Px7QWASdqDbnndiuwTAyyEstv82PCHqS_example',
        member_id => 'Zdd1Px7QWASdqDbnndiuwTAyyEstv82PCHlS_example',
        result => status);           
       dbms_output.put_line(status);
    END;
/

Parent topic: Summary of DBMS_DATA_ACCESS Subprograms