Shows you the steps to generate a Table Hyperlink that you can use to share access for a schema object (table or view).

1. Identify the table or view that you want to share.

   If there are restrictions on the data you want to make available, use the application_user_id parameter when you generate the Table Hyperlink and create a VPD policy to restrict the data that you expose. See Define a Virtual Private Database Policy to Secure Table Hyperlink Data for more information.

2. Run DBMS_DATA_ACCESS.CREATE_URL to generate the Table Hyperlink.

   DECLARE
      status CLOB;
      BEGIN
         DBMS_DATA_ACCESS.CREATE_URL(
             schema_name => 'ADMIN',
             schema_object_name    => 'TREE_DATA',
             expiration_minutes    => 360,
             result                => status);
          dbms_output.put_line(status);
       END;
   /

   The expiration_minutes parameter specifies that the Table Hyperlink expires and is invalidated after 360 minutes.

   See CREATE_URL Procedure for more information.

3. Check the result.

   In this example status contains the result that includes information about the Table Hyperlink.

   {
   "status":"SUCCESS","id":"wPY0uxyx-gioxOndiKVlqVF585xqJs14CIp9M1qH",
   "preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/-pPLdrbUTcLUrZLExyzxyzabc_1w/data",
   "expiration_ts":"2024-10-22T22:37:18.805Z",
   "expiration_count":null
   }

Shows you the steps to generate a Table Hyperlink that provides access to data using a SQL query statement.

1. Identify the table or view that contains the information you want to share, as well as the SELECT statement on the table or view that you want to use.

   If there are restrictions on the data you want to make available, use the application_user_id parameter when you generate the Table Hyperlink and create a VPD policy to restrict the data that you expose. See Define a Virtual Private Database Policy to Secure Table Hyperlink Data for more information.

2. Run DBMS_DATA_ACCESS.CREATE_URL to generate the Table Hyperlink.

   You have two options, depending on whether you want to include default bind variable values (if you include bind variables in the select statement).

   1. Provide a select statement and do not use the optional default_bind_values parameter.

      For example:

      DECLARE
         status CLOB;
         BEGIN
            DBMS_DATA_ACCESS.CREATE_URL(
                sql_statement        => 'SELECT species, height FROM TREE_DATA',
                expiration_minutes   => 360,
                result               => status);
             dbms_output.put_line(status);
          END;
      /

      The sql_statement value must be a SELECT statement. The SELECT statement supports bind variables. If bind variables are included in the select statement, the bind variable values must be appended to the generated Table Hyperlink as a query parameter when accessing the data.
   2. Provide a select statement and include the default_bind_values parameter.

      For example:

      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   => 360,
                result               => status);
             dbms_output.put_line(status);
          END;
      /

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

      The default_bind_values parameter specifies values for one or more bind variables in the sql_statement. When default_bind_values is included with DBMS_DATA_ACCESS.CREATE_URL:

      • For bind variables specified in default_bind_values, you can omit the bind variable values when you access the data. The default value is used for a specified bind variable if an override is not provided in the Table Hyperlink URL as a query parameter when you access the data.

      • You can override a default bind variable value specified in default_bind_values if you append the bind variable value in the Table Hyperlink URL as a query parameter when you access the data.

      • If a bind variable is included in the select statement and a default value for the bind variable is not specified in the default_bind_values parameter, you must append a bind variable value to the generated Table Hyperlink URL as a query parameter when you access the data.

   Bind variable support is available for NUMBER and VARCHAR2 column types.

   In these examples the expiration_minutes parameter specifies that the Table Hyperlink expires and is invalidated after 360 minutes.

3. Check the result.

   In this example status contains the result that includes the Table Hyperlink.

   {
   "status":"SUCCESS","id":"LCvtpALqZgcHGL4Lxyzabcxyza-QVEFngwh1UGhg8jjuFAHOQJLGFi",
   "preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/RIabc_xyz7Kw8ZLyZ2JzCXZQXpc/data",
   "expiration_ts":"2025-01-20T16:42:49.527Z"}
   }

When you generate a Table Hyperlink you can use the column_lists parameter to specify UI features for specified columns.

1. Identify the table or view or select statement that you want to share.

   This example generates a Table Hyperlink using the column_lists parameter with a table. You can also use this parameter when you generate a Table Hyperlink with a SELECT statement.

   See Generate a Table Hyperlink for a Table or a View and Generate a Table Hyperlink with a Select Statement for more information.

2. Run DBMS_DATA_ACCESS.CREATE_URL to generate the Table Hyperlink and specify group by columns option for viewing with a browser:

   For example, to specify group by columns:

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

   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.

   For example:

   column_lists => '{ 
           "group_by_columns":["COUNTY", "SPECIES"], 
           "order_by_columns":["COUNTY"] }'

   See CREATE_URL Procedure for more information.

3. Check the result.

   In this example status contains the result that includes information about the Table Hyperlink.

   {
   "status":"SUCCESS",
   "id":"LLUZjJ5Yy8d0txydMiuxCVL_j4abc_xyzV1989GmgRInaBYTw",
   "preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Rt_yRfTxbcabc_xyzWlANP5RmM9Y/data",
   "expiration_ts":"2024-10-25T02:30:39.810Z",
   "expiration_count":null
   }

   See Use Table Hyperlinks to Access Data with the Group By Option in Table Format for an example showing group by columns usage.

4. Run DBMS_DATA_ACCESS.CREATE_URL to generate the Table Hyperlink and specify a UI format option.

   For example, to set the columns that can be sorted use the column_lists order_by_columns option:

   DECLARE
      status CLOB;
      column_lists CLOB;
      BEGIN
         DBMS_DATA_ACCESS.CREATE_URL(
             schema_name => 'ADMIN',
             schema_object_name    => 'TREE_DATA',
             expiration_minutes    => 360,
             column_lists          => '{ "order_by_columns": ["COUNTY", "SPECIES"] }',
             result                => status);
          dbms_output.put_line(status);
       END;
   /

   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.

   See CREATE_URL Procedure for more information.

5. Check the result.

   In this example status contains the result that includes information about the Table Hyperlink.

   {
   "status":"SUCCESS",
   "id":"LLUZjJ5Yy8d0txydMiuxCVL_j4abc_xyzV1989GmgRInaBYTw",
   "preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Rt_yRfTxbcabc_xyzk3EmM9Y/data",
   "expiration_ts":"2024-10-25T02:30:39.810Z",
   "expiration_count":null
   }

   See Use Table Hyperlinks in Table Format with Column Sorting for an example showing order by columns usage.

At any time a user with appropriate privileges can invalidate a Table Hyperlink.

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

1. Obtain the application_user_id value that you specified when you generated the Table Hyperlink.
2. Create VPD policy on the database where you generated the Table Hyperlink.

   CREATE OR REPLACE FUNCTION limit_sal (v_schema IN VARCHAR2, v_objname IN VARCHAR2)
     RETURN VARCHAR2 authid current_user AS
   BEGIN
        RETURN 'employee_id = SYS_CONTEXT(''DATA_ACCESS_CONTEXT$'', ''USER_IDENTITY'')';
   END;
   

   See DBMS_RLS for more information.

3. Register the VPD policy.

   BEGIN
      DBMS_RLS.ADD_POLICY( 
           object_schema        => 'HR',
           object_name          => 'EMPLOYEE',
           policy_name          => 'POL',
           policy_function      => 'LIMIT_SAL');
   END;
   /

   See DBMS_RLS for more information.

Autonomous Database provides views that allow you to monitor Table Hyperlink usage.

Notes for generating a Table Hyperlink with DBMS_DATA_ACCESS.CREATE_URL:

• You can use the optional service_name parameter to specify that the generated Table Hyperlink is serviced with a specific service-level guarantee and resources. For example, use the service_name parameter to specify access to the SQL statement is mapped to the HIGH service.

• You can use the optional inherit_acl parameter if you want to limit access to Table Hyperlink data. Set this parameter to TRUE to inherit ACLs. When this parameter's value is TRUE, an incoming Table Hyperlink consumer's IP address is validated with the ACLs on the producer database before allowing access to data. If the producer database does not have ACLs configured, the inherit_acl value is ignored and data access is allowed without any ACL checks.

  See Configuring Network Access with Access Control Rules (ACLs) for more information.

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

• Use DBMS_DATA_ACCESS.LIST_ACTIVE_URLS to show Table Hyperlinks. See List Table Hyperlinks and Table Hyperlink Groups for details.

• Use DBMS_DATA_ACCESS.EXTEND_URL to extend the life of a Table Hyperlink. See EXTEND_URL Procedure for more information.