Oracle Cloud Infrastructure Documentation

Access Data with Table Hyperlinks or with a Table Hyperlink Group

Table Hyperlink data is retrieved and returned in JSON format and is paginated.

You can access Table Hyperlink data using a browser or using any REST client. The data returned is paginated to allow you to access a maximum of 100 records at a time, with the total data size in the response limited to 1 MB. You can provide the limit query parameter to limit the number of records fetched. Table Hyperlink data retrieval is blocked if Table Hyperlink authentication fails or if the requested Table Hyperlink has expired.

When you create a Table Hyperlink or a Table Hyperlink Group, you can set the consistent parameter to specify that the producer provides data consistently across different pages when a consumer access data. When consistent is set to TRUE, the producer to provide consistent data to a consumer that accesses multiple pages of data. This means that a consumer's first access and subsequent data requests for pages associated with a Table Hyperlink use the same data snapshot (SCN) as the request for the first page.

Note

Table Hyperlink data can be viewed in tabular format when accessed from a browser. See Access Data in Table Format with Table Hyperlinks or Table Hyperlink Groups for details.

Parent topic: Use Table Hyperlinks for Read Only Data Access on Autonomous AI Database

Use Table Hyperlink to Access Data

Table Hyperlink data is retrieved and returned in JSON format and is paginated.

For example, to use a Table Hyperlink and process the response:

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

The Table Hyperlink response includes links for any previous or next pages, when the data includes more than one page. This allows you to navigate in either direction while fetching data. The JSON also includes a self link that points to the current page, as well as a hasMore attribute that indicates if there is more data available to fetch.

The following is the response format:

{
    "items": [],                 <-- Array of records from database
    "hasMore": true OR false,    <-- Indicates if there are more records to fetch or not
    "limit": Number,             <-- Indicates number of records in the page. Maximum allowed number is 100.
    "offset": Number,            <-- Offset indicating the start of the current page
    "count": Number,             <-- Count of records in the current page
    "links": [
        {
            "rel": "self",
            "href": "{Link to table hyperlink url for the current page}"
        },
        {
            "rel": "previous",
            "href": "{Link to table hyperlink url for the previous page}"
        },
        {
            "rel": "next",
            "href": "{Link to table hyperlink url for the next page}"
        }
    ]
}

For example, the following is a sample response from a Table Hyperlink (with newlines added for clarity):

{"items":[
{"COUNTY":"Main","SPECIES":"Alder","HEIGHT":45},
{"COUNTY":"First","SPECIES":"Chestnut","HEIGHT":51},{"COUNTY":"Main","SPECIES":"Hemlock","HEIGHT":17},
{"COUNTY":"Main","SPECIES":"Douglas-fir","HEIGHT":34},{"COUNTY":"First","SPECIES":"Larch","HEIGHT":12},
{"COUNTY":"Main","SPECIES":"Cedar","HEIGHT":21},{"COUNTY":"First","SPECIES":"Douglas-fir","HEIGHT":10},
{"COUNTY":"Main","SPECIES":"Yew","HEIGHT":11},{"COUNTY":"First","SPECIES":"Willow","HEIGHT":17},
{"COUNTY":"Main","SPECIES":"Pine","HEIGHT":29},{"COUNTY":"First","SPECIES":"Pine","HEIGHT":16},
{"COUNTY":"First","SPECIES":"Spruce","HEIGHT":6},{"COUNTY":"Main","SPECIES":"Spruce","HEIGHT":8},
{"COUNTY":"First","SPECIES":"Hawthorn","HEIGHT":19},{"COUNTY":"First","SPECIES":"Maple","HEIGHT":16},
{"COUNTY":"Main","SPECIES":"Aspen","HEIGHT":35},{"COUNTY":"First","SPECIES":"Larch","HEIGHT":27},
{"COUNTY":"First","SPECIES":"Cherry","HEIGHT":20},{"COUNTY":"Main","SPECIES":"Pine","HEIGHT":37},
{"COUNTY":"Main","SPECIES":"Redwood","HEIGHT":78},{"COUNTY":"Main","SPECIES":"Alder","HEIGHT":45},
{"COUNTY":"First","SPECIES":"Chestnut","HEIGHT":51},{"COUNTY":"Main","SPECIES":"Hemlock","HEIGHT":17},
{"COUNTY":"Main","SPECIES":"Douglas-fir","HEIGHT":34},{"COUNTY":"First","SPECIES":"Larch","HEIGHT":12},
{"COUNTY":"Main","SPECIES":"Cedar","HEIGHT":21},{"COUNTY":"First","SPECIES":"Douglas-fir","HEIGHT":10},
{"COUNTY":"Main","SPECIES":"Redwood","HEIGHT":78}],

"hasMore":false,
"limit":100,
"offset":0,
"count":30,
"links":
[
{"rel":"self",
"href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/F5Sn..._example/data"}
]}

Use Table Hyperlink to Access Data with Bind Variables

If there is a bind variable in the SELECT statement when you generate a Table Hyperlink, you must either use the default_bind_values parameter when you generate the Table Hyperlink to specify a default bind variable value or you must pass a bind variable value as a query parameter to access the Table Hyperlink data.

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

For example, when a Table Hyperlink is generated with the following SQL statement and you do not specify the default_bind_values parameter: 

sql_statement = 'SELECT * FROM TREE_DATA WHERE COUNTY = :countyName'

You must append a bind variable and specify a value as a query parameter to access the data. For example:

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

The bind value does not need to be specified in the URL when the DBMS_DATA_ACCESS.CREATE_URL includes the default_bind_values parameter:

For example, with the following parameters in DBMS_DATA_ACCESS.CREATE_URL: 

sql_statement = 'SELECT * FROM TREE_DATA WHERE COUNTY = :countyNAME',
default_bind_values => '{"countyNAME" : "First"}'

In this case the parameter default_bind_values defines the value for the bind variable and you do not need to provide the value as a query parameter (the default value is supplied): 

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"}
]}

When default_bind_values defines a bind variable value you can override the bind variable value by explicitly specifying a value as a query parameter. For example: 

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

See CREATE_URL Procedure for more information.

Access Data for a Password Protected Table Hyperlink

When you include the password parameter with DBMS_DATA_ACCESS.CREATE_URL to create a password protected Table Hyperlink, along with the URL value, the produces must share the password with consumers. Consumers use both the URL and the password to access the data and supply the password to access the data using Basic authentication over REST.

For example:

HEADER
Authorization: Basic password
GET https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/Vdtv..._example_wxd0PN/data

Use a Table Hyperlink Group to Access Data

Table Hyperlink Group data is retrieved and returned in JSON format and is paginated.

For example, to use a Table Hyperlink Group and process the response:

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

Table Hyperlink Group response includes the member URL information.

For example, the following is a sample response for a Table Hyperlink Group:

{  "member_urls": [
    {      
      "id": "{member_id}",
      "name": "{member_name}",
      "description": "{member_description}" 
    },
    .....
    ]
}

You could next use the following REST API to access the data for a Table Hyperlink Group member, using one of the supplied member_id values. For example: 

Get https://dataaccess.adb.{region_name}.oraclecloudapps.com/adb/p/{group_par_url_token}/data?member_id={member_id}
 
Response
200 OK - Success
 
{
    "items": [],                 <-- Array of records from database
    "hasMore": true OR false,    <-- Indicates if there are more records to fetch or not
    "limit": Number,             <-- Indicates number of records in the page. Maximum allowed number is 100.
    "offset": Number,            <-- Offset indicating the start of the current page
    "count": Number,             <-- Count of records in the current page
    "links": [
        {
            "rel": "self",
            "href": "{Link to Table Hyperlink for the current page}"
        },
        {
            "rel": "previous",
            "href": "{Link to Table Hyperlink for the previous page}"
        },
        {
            "rel": "next",
            "href": "{Link to Table Hyperlink for the next page}"
        }
    ]
}