Oracle Autonomous Database (ADB) continues to evolve rapidly with
new capabilities that make data access more flexible, secure, and
developer-friendly. One such recent enhancement is the ability to use bind variables in SQL queries within Table Hyperlinks (also known as Pre-Authenticated URLs).
This feature allows you to define
SQL queries that include bind variables and then supply values for those
variables either:
·
As default bind values
at the time of hyperlink creation, or
·
As runtime URL parameters
when calling the link.
This makes Pre-Authenticated URLs even more powerful, especially
when building dashboards, external integrations, or lightweight query-driven
APIs that depend on dynamic filtering.
In this
article, we’ll walk through:
1. What
this feature is and why it’s useful
2. How
to create a Pre-Authenticated URL using bind variables
3. How
default bind values behave
4. How
to override bind values at runtime using query parameters
Why Bind Variables in Table Hyperlinks Matter
Previously, Table Hyperlinks supported only static SQL statements,
which meant:
·
The logic was fixed
·
Filtering required separate hyperlinks
·
External services had no way to pass in parameters dynamically
With this enhancement, you can now create one hyperlink and
let clients supply any number of dynamic bind values at runtime. This
reduces maintenance, improves flexibility, and allows Table Hyperlinks to
behave more like parameterized REST endpoints — without needing to implement a
full REST service.
Creating a Table Hyperlink With Bind Variables
Let’s start
with an example SQL query that contains two bind variables :b1 and :b2.
demo-user@ATP19C> declare
2 l_status long;
3 begin
4 dbms_data_access.create_url(
5 sql_statement => q'# select * from emp where deptno = :b1 and job = :b2 #'
6 , service_name =>'LOW'
7 , default_bind_values => json_object('b1' value 20 ,'b2' value 'CLERK')
8 , result => l_status );
9 dbms_output.put_line( l_status );
10 end;
11 /
The key points here:
·
The SQL contains bind variables (:b1, :b2).
·
default_bind_values
defines values that will be used if no runtime parameters are supplied.
·
A new pre-authenticated URL gets generated.
Sample output from the above PL/SQL block:
{
"status" : "SUCCESS",
"id" : "gTqfJcv0JfckskdR7eI77QCUrMcMe5N4k1iXeeWstlo6c7uKxXQbyQQPotFTi10E",
"preauth_url" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/SSi8uslpyP4hikoPvJedQ9K6Jd-jGfC4hgY-tvdWWrqeu9TP_kPYoVkBu31kHadXqC3rdDDU4j4/data",
"expiration_ts" : "2026-02-19T14:58:18.187Z"
}
This URL can
now be used from anywhere — scripts, dashboards, apps, or API clients.
Accessing the Hyperlink Without Runtime Parameters
If you call
the generated URL without adding any bind values in the query string, ADB will
apply the default bind variable values (deptno = 20, job
= 'CLERK'):
$ curl --location 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/SSi8uslpyP4hikoPvJedQ9K6Jd-jGfC4hgY-tvdWWrqeu9TP_kPYoVkBu31kHadXqC3rdDDU4j4/data'
{
"items": [
{
"EMPNO": 7369,
"ENAME": "SMITH",
"JOB": "CLERK",
"MGR": 7902,
"HIREDATE": "1980-12-17T00:00:00",
"SAL": 800,
"DEPTNO": 20
},
{
"EMPNO": 7876,
"ENAME": "ADAMS",
"JOB": "CLERK",
"MGR": 7788,
"HIREDATE": "1987-05-23T00:00:00",
"SAL": 1100,
"DEPTNO": 20
}
],
"hasMore": false,
"limit": 1000,
"offset": 0,
"count": 2,
"links": [
{
"rel": "self",
"href": "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/SSi8uslpyP4hikoPvJedQ9K6Jd-jGfC4hgY-tvdWWrqeu9TP_kPYoVkBu31kHadXqC3rdDDU4j4/data"
}
]
}
This confirms
that the default bind values were applied successfully.
Overriding Bind Values at Runtime
The real
strength of this feature lies in the ability to override bind variables
dynamically.
All you need to do is append query parameters to the URL, using the same bind variable names.
1.
Changing a Single Bind Variable
For example, to override only b1:
$ curl --location
'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/SSi8uslpyP4hikoPvJedQ9K6Jd-jGfC4hgY-tvdWWrqeu9TP_kPYoVkBu31kHadXqC3rdDDU4j4/data?b1=10'
{
"items": [
{
"EMPNO":
7934,
"ENAME":
"MILLER",
"JOB":
"CLERK",
"MGR":
7782,
"HIREDATE": "1982-01-23T00:00:00",
"SAL":
1300,
"DEPTNO":
10
}
],
"hasMore":
false,
"limit": 1000,
"offset": 0,
"count": 1,
"links": [
{
"rel":
"self",
"href":
"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/SSi8uslpyP4hikoPvJedQ9K6Jd-jGfC4hgY-tvdWWrqeu9TP_kPYoVkBu31kHadXqC3rdDDU4j4/data?b1=10"
}
]
}
Only the
department number changed; the job remained 'CLERK' because we did not
override b2.
2. Changing
Multiple Bind Variables
You can override as many bind values as
needed:
$ curl --location
'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/SSi8uslpyP4hikoPvJedQ9K6Jd-jGfC4hgY-tvdWWrqeu9TP_kPYoVkBu31kHadXqC3rdDDU4j4/data?b1=20&b2=MANAGER'
{
"items": [
{
"EMPNO":
7566,
"ENAME":
"JONES",
"JOB":
"MANAGER",
"MGR":
7839,
"HIREDATE": "1981-04-02T00:00:00",
"SAL":
2975,
"DEPTNO":
20
}
],
"hasMore":
false,
"limit": 1000,
"offset": 0,
"count": 1,
"links": [
{
"rel":
"self",
"href":
"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/SSi8uslpyP4hikoPvJedQ9K6Jd-jGfC4hgY-tvdWWrqeu9TP_kPYoVkBu31kHadXqC3rdDDU4j4/data?b1=20&b2=MANAGER"
}
]
}
With just one
hyperlink, we can now produce entirely different result sets depending on the
runtime parameters.
Use Cases for Runtime Bind Variables
This enhancement opens the door for several
powerful scenarios:
·
Dynamic filtering
for dashboards
o Single URL can drive
multiple filtered views
·
External system
integrations
o Supply parameters
from REST clients without changing SQL
·
Lightweight report
endpoints
o Customize responses
without creating multiple URLs
·
Secure parameterized
access
o Bind variables
reduce SQL injection risks
·
Simplified API behaviour
o Acts like a
parameterized GET endpoint
If you're building
applications that need controlled, query-based access to ADB data, this feature
dramatically reduces overhead.
Bind variable
support for Table Hyperlinks adds a powerful new dimension to how data can be
accessed and filtered in Autonomous Database. With just one hyperlink, you can:
·
Define parameterized
SQL
·
Provide default bind
values
·
Override those
values at runtime
·
Drive flexible,
dynamic data retrieval from external systems
This makes
Pre-Authenticated URLs a versatile tool for lightweight data APIs, automated
pipelines, and integration workflows.
