DBMS_DATA_ACCESS Package
The
DBMS_DATA_ACCESS
package provides routines to generate and manage Table
Hyperlinks for data sets.
- DBMS_DATA_ACCESS Overview
Describes the use of theDBMS_DATA_ACCESS
package. - DBMS_DATA_ACCESS Security Model
Security on this package can be controlled by grantingEXECUTE
on this package to selected users or roles. - Summary of DBMS_DATA_ACCESS Subprograms
This section covers theDBMS_DATA_ACCESS
subprograms provided with Autonomous Database.
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.
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 hasEXECUTE
privilege onDBMS_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 |
---|---|
This procedure generates a Table Hyperlink. |
|
This procedure extends the life of a Table Hyperlink. |
|
This procedure invalidates a Table Hyperlink. |
|
This function lists all the currently active Table Hyperlinks. |
- 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
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 |
---|---|
|
Specifies the owner of the object. |
|
Specifies the schema object (table or view). |
|
Specifies the |
|
Specifies an application user ID
value. When the Table Hyperlink is accessed, the value of
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. |
|
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 Default value: when
|
|
Number of accesses allowed on the Table Hyperlink. There is no default value. If If |
|
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
The default value is |
|
A JSON value that specifies options by column. The
supported options specified in the
The
The form is:
For example:
Default values: If If |
|
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, wherecolumn_name_1
throughcolumn_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 supporteddata_type
parameter values areVARCHAR
andNONE
.
- View the returned data in table format with no coloring
(default), by appending the
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;
/
Parent topic: Summary of DBMS_DATA_ACCESS Subprograms
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 |
---|---|
|
Specifies the ID of the Table Hyperlink to extend. |
|
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
The value for
If Default value is NULL. |
|
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
If Default value is null. |
|
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;
/
Parent topic: Summary of DBMS_DATA_ACCESS Subprograms
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 |
---|---|
|
Specifies the owner of the object. |
|
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: |
|
Provides JSON to indicate whether invalidation is a
success or a failure ( |
Parent topic: Summary of DBMS_DATA_ACCESS Subprograms
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 withPDB_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 withPDB_DBA
role, the list includes only the active Table Hyperlinks generated by the invoker.
Parent topic: Summary of DBMS_DATA_ACCESS Subprograms