REST-Enabled SQL is an ORDS endpoint that runs SQL statements sent in an HTTP request and returns the results as JSON. Tools and scripts can query a schema without a database driver or a REST module for each query. Because it runs any SQL the schema could run, it must be authenticated; this guide uses an OAuth client instead of the schema's password.
Before You Start
You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.
ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.
REST-Enabled SQL must be turned on in the ORDS configuration with restEnabledSql.active set to true; it was already on for this installation.
Syntax
POST https://host:port/ords/schema_alias/_/sql
Content-Type: application/sql -- the body is SQL text, one or more statements
Content-Type: application/json -- the body is {"statementText": ..., "binds": [...], "limit": n}A Client with the SQL Developer Role
The OAuth client gets the built-in SQL Developer role, which REST-Enabled SQL accepts. Its secret is shortened in the output.
Example:
declare
v_client ords_types.t_client_credentials;
begin
v_client := ords_security.register_client(
p_name => 'sql_runner',
p_grant_type => 'client_credentials',
p_support_email => 'dba@nimbus.example',
p_client_secret => ords_types.oauth_client_secret());
ords_security.grant_client_role(p_client_name => 'sql_runner',
p_role_name => 'SQL Developer');
commit;
dbms_output.put_line('client_id: ' || v_client.client_key.client_id);
dbms_output.put_line('client_secret: ' || v_client.client_secret.secret);
end;
/Output:
client_id: e5TT5Ok6Wb2YD0JO78rzMw.. client_secret: -_1e8t... (shortened) PL/SQL procedure successfully completed.
The client then gets a token from /ords/nimbus/oauth/token, as for any OAuth client.
Run a Query
Example:
curl -k -X POST https://localhost:8443/ords/nimbus/_/sql \ -H "Authorization: Bearer ACCESS_TOKEN" \ -H "Content-Type: application/sql" \ --data-binary "select airport_code, city from airports where country_code = 'IN' order by 1"
Output:
{
"env": {
"defaultTimeZone": "UTC"
},
"items": [
{
"statementId": 1,
"statementType": "query",
"statementPos": {
"startLine": 1,
"endLine": 1
},
"statementText": "select airport_code, city from airports where country_code = 'IN' order by 1",
"resultSet": {
"metadata": [
{
"columnName": "AIRPORT_CODE",
"jsonColumnName": "airport_code",
"columnTypeName": "CHAR",
"columnClassName": "java.lang.String",
"precision": 3,
"scale": 0,
"isNullable": 0
},
{
"columnName": "CITY",
"jsonColumnName": "city",
"columnTypeName": "VARCHAR2",
"columnClassName": "java.lang.String",
"precision": 40,
"scale": 0,
"isNullable": 0
}
],
"items": [
{
"airport_code": "BLR",
"city": "Bengaluru"
},
{
"airport_code": "BOM",
"city": "Mumbai"
},
{
"airport_code": "DEL",
"city": "Delhi"
}
],
"hasMore": false,
"limit": 10000,
"offset": 0,
"count": 3
},
"response": [],
"result": 0
}
]
}Each statement comes back with its text, the column metadata, and the rows.
Bind Variables and a Row Limit
A JSON request carries the statement, its bind values, and a limit:
Example:
{
"statementText": "select destination, distance_km from routes where origin = :origin order by distance_km desc",
"binds": [ { "name": "origin", "data_type": "VARCHAR2", "value": "SIN" } ],
"limit": 2
}Output (sent with Content-Type: application/json):
{
"env": {
"defaultTimeZone": "UTC"
},
"items": [
{
"statementId": 1,
"statementType": "query",
"statementPos": {
"startLine": 1,
"endLine": 1
},
"statementText": "select destination, distance_km from routes where origin = :origin order by distance_km desc",
"binds": [
{
"name": "origin",
"data_type": "VARCHAR2",
"value": "SIN"
}
],
"resultSet": {
"metadata": [
{
"columnName": "DESTINATION",
"jsonColumnName": "destination",
"columnTypeName": "CHAR",
"columnClassName": "java.lang.String",
"precision": 3,
"scale": 0,
"isNullable": 0
},
{
"columnName": "DISTANCE_KM",
"jsonColumnName": "distance_km",
"columnTypeName": "NUMBER",
"columnClassName": "java.math.BigDecimal",
"precision": 5,
"scale": 0,
"isNullable": 0
}
],
"items": [
{
"destination": "SYD",
"distance_km": 6294
},
{
"destination": "DXB",
"distance_km": 5845
}
],
"hasMore": true,
"limit": 2,
"offset": 0,
"count": 2
},
"response": [],
"result": 0
}
]
}A Script of Several Statements
Example:
select count(*) as offers from lounge_offers; update lounge_offers set price_usd = price_usd + 1 where offer_id = 1; rollback; select price_usd from lounge_offers where offer_id = 1;
Output:
{
"env": {
"defaultTimeZone": "UTC"
},
"items": [
{
"statementId": 1,
"statementType": "query",
"statementPos": {
"startLine": 1,
"endLine": 1
},
"statementText": "select count(*) as offers from lounge_offers",
"resultSet": {
"metadata": [
{
"columnName": "OFFERS",
"jsonColumnName": "offers",
"columnTypeName": "NUMBER",
"columnClassName": "java.math.BigDecimal",
"precision": 0,
"scale": -127,
"isNullable": 1
}
],
"items": [
{
"offers": 4
}
],
"hasMore": false,
"limit": 10000,
"offset": 0,
"count": 1
},
"response": [],
"result": 0
},
{
"statementId": 2,
"statementType": "dml",
"statementPos": {
"startLine": 2,
"endLine": 2
},
"statementText": "update lounge_offers set price_usd = price_usd + 1 where offer_id = 1",
"response": [
"\n1 row updated.\n\n"
],
"result": 1
},
{
"statementId": 3,
"statementType": "transaction-control",
"statementPos": {
"startLine": 3,
"endLine": 3
},
"statementText": "rollback",
"response": [
"\nRollback complete.\n\n"
],
"result": 0
},
{
"statementId": 4,
"statementType": "query",
"statementPos": {
"startLine": 4,
"endLine": 4
},
"statementText": "select price_usd from lounge_offers where offer_id = 1",
"resultSet": {
"metadata": [
{
"columnName": "PRICE_USD",
"jsonColumnName": "price_usd",
"columnTypeName": "NUMBER",
"columnClassName": "java.math.BigDecimal",
"precision": 7,
"scale": 2,
"isNullable": 1
}
],
"items": [
{
"price_usd": 59
}
],
"hasMore": false,
"limit": 10000,
"offset": 0,
"count": 1
},
"response": [],
"result": 0
}
]
}Statements run in order in one session: the update is rolled back, so the final query still shows the price of 59.
Without a Token
Output (HTTP 401 Unauthorized):
{
"code": "Unauthorized",
"message": "Unauthorized",
"type": "tag:oracle.com,2020:error/Unauthorized",
"instance": "tag:oracle.com,2020:ecid/iJ6NoEzUKQTjTVOuSD8Zaw"
}
HTTP 401When the test was done, the client was deleted:
Example:
begin ords_security.delete_client(p_name => 'sql_runner'); commit; end; /
Things to Know
- A client with the SQL Developer role can run any statement the schema can, including DDL; give the role only to trusted tools.
- Callers can also authenticate with the schema's database user name and password through basic authentication; OAuth avoids sharing that password.
- For fixed queries used by applications, REST modules are safer than REST-Enabled SQL.
Related Guides
Conclusion
REST-Enabled SQL runs SQL sent over HTTP and returns JSON results, with bind variables, limits, and multi-statement scripts. Authenticate it with an OAuth client that has the SQL Developer role, keep that client to trusted tools, and use REST modules for application APIs.
