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 or a Table Hyperlink Group. |
|
This procedure extends the life of a Table Hyperlink. |
|
This procedure generates a Table Hyperlink. This procedure is deprecated. Instead use the CREATE_URL Procedure. |
|
This procedure extends the life of a Table Hyperlink. |
|
This procedure invalidates a Table Hyperlink. |
|
This function lists all the currently active Table Hyperlinks. |
|
- ADD_MEMBER Procedure
This procedure adds an existing Table Hyperlink to the Table Hyperlink Group as a member. - 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. - GET_PREAUTHENTICATED_URL Procedure
This procedure generates a Table Hyperlink. - EXTEND_URL Procedure
This procedure extends the life of a Table Hyperlink or of a Table Hyperlink Group. - INVALIDATE_URL Procedure
This procedure invalidates a Table Hyperlink or a Table Hyperlink Group. - LIST_ACTIVE_URLS Function
This function lists all the currently active Table Hyperlinks and Table Hyperlink Groups. - LIST_MEMBERS Procedure
This procedure lists the members of a Table Hyperlink Group. - REMOVE_MEMBER Procedure
This procedure removes a member from a Table Hyperlink Group.
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 |
---|---|
|
Specifies the identifier for the Table Hyperlink Group. |
member_id |
Specifies the identifier for the Table Hyperlink to add to the group. |
|
Provides JSON to indicate whether invalidation is a
success or a failure ( |
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
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 |
---|---|
|
Specifies a JSON array of the
See the following description for details on the format of the JSON array. |
|
Specifies the owner of the object. |
|
Specifies the schema object (table or view). |
|
Specifies the |
|
This optional parameter specifies an
application user ID value. When the Table Hyperlink or Table
Hyperlink Group 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. |
|
Specifes the default value(s) of one or more bind
variables (for a This allows a table hyperlink consumer to access the table hyperlink data with default bind values, without providing the bind values as query parameters. |
|
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 Default value: when
|
|
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 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 |
|
This parameter is optional. Set the value to
When the parameter is not supplied or the parameter
value is set to If the producer database does not have ACLs
configured, the The default value is |
|
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 for the member
See Generate a Table Hyperlink with a Select Statement for details. |
schema_name |
No |
Schema name for the member. Provide a See Generate a Table Hyperlink for a Table or a View for details. |
schema_object_name |
Providing either |
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, 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
. -
The
sql_statement
parameter value must be aSELECT
statement. TheSELECT
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 thedefault_bind_values
parameter. You can override a default bind variable value specified withdefault_bind_values
by explicitly supplying the bind variable value as a query parameter.
- View the returned data in table format with no coloring
(default), by appending the
-
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 andprivate_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
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 |
---|---|
|
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. |
|
Specifes the default value(s) of one or more bind
variables (for a This allows a table hyperlink consumer to access the table hyperlink data with default bind values, without providing the bind values as query parameters. |
|
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 |
|
Set the value of this parameter to
When the parameter is not supplied or the parameter
value is set to If the producer database does not have ACLs
configured, the The default value is |
|
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 andprivate_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 |
---|---|
|
Specifies the ID of the Table Hyperlink or the Table Hyperlink Group 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. |
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 |
---|---|
|
Specifies the identifier for the Table Hyperlink or the Table Hyperlink Group to invalidate. |
|
This parameter is optional. By default, when you run Valid values: |
|
Provides JSON to indicate whether invalidation is a
success or a failure ( |
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 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. -
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 andprivate_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 |
---|---|
|
Specifies the identifier for the Table Hyperlink Group. |
|
Provides JSON to indicate whether invalidation is a
success or a failure ( |
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 |
---|---|
|
Specifies the identifier for the Table Hyperlink Group. |
member_id |
Specifies the identifier for the group member to remove from the Table Hyperlink Group. |
|
Provides JSON to indicate whether invalidation is a
success or a failure ( |
Usage Notes
- The
member_id
value cannot be a Table Hyperlink Group ID (is_group_url
must befalse
). -
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