Oracle Cloud Infrastructure Documentation

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

GET_PREAUTHENTICATED_URL Procedure

This procedure generates a Table Hyperlink.

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.

Parent topic: DBMS_DATA_ACCESS Package

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.

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,
    result                OUT CLOB);

DBMS_DATA_ACCESS.GET_PREAUTHENTICATED_URL( 
    sql_statement         IN CLOB,
    application_user_id   IN VARCHAR2,
    expiration_minutes    IN NUMBER,
    expiration_count      IN NUMBER,
    service_name          IN VARCHAR2,
    column_lists          IN CLOB,
    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.

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.

result

JSON that indicates the outcome of the operation.

Usage Note

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

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 = :county',
    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?county=First

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

EXTEND_URL Procedure

This procedure extends the life of a Table Hyperlink.

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

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

INVALIDATE_URL Procedure

This procedure invalidates a Table Hyperlink.

Syntax

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

Parameters

Parameter Description

id

Specifies the owner of the object.

kill_sessions

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

Valid values: TRUE | FALSE.

result

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

LIST_ACTIVE_URLS Function

This function lists all the currently active Table Hyperlinks.

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": "89fa6081-ec6b-4179-9b06-a93af8fbd4b7",
        "schema_name": "SCOTT",
        "schema_object_name": "EMPLOYEE",
        "created_by": "ADMIN",
        "application_user_id": "AMIT",
        "expiration_time": "2023-01-14T23:41:01.029Z",
        "expiration_count": 100,
        "access_count": 9,
        "created": "2023-01-10T19:41:01.285Z"
    },
    {
        "id": "263d2cd7-3bc0-41a7-8cb9-438a2d843481",
        "sql_statement": "select name from v$pdbs",
        "created_by": "ADMIN",
        "application_user_id": "AMIT",
        "expiration_time": "2023-01-15T00:04:30.578Z",
        "expiration_count": 100,
        "access_count": 0,
        "created": "2023-01-10T20:04:30.607Z"
    }
]

Usage Note

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