Use Table Hyperlink to Access Data

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

Note

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

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.

For example, use a Table Hyperlink:

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 without 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.GET_PREAUTHENTICATED_URL includes the default_bind_values parameter:

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

In the case where default_bind_values defines a value for a variable 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 GET_PREAUTHENTICATED_URL Procedure for more information.

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

Oracle Cloud Infrastructure Documentation

Use Table Hyperlink to Access Data