Pre-General Availability: 2017-09-04
The REST Enabled SQL service is a HTTPS web service that provides access to the Oracle Database SQL Engine. You can POST SQL statements to the service. The service then runs the SQL statements against Oracle database and returns the result to the client in a JSON format.
Statically defined RESTful services use predefined SQL statements that are useful when you need a fixed and repeatable service. The REST Enabled SQL service enables you to define SQL statements dynamically and run them against the database without predefined SQL statements. This makes your data more accessible over REST.
A Typical Use Case: If your Oracle Database is in the Cloud and you wish to make it available through a REST API over HTTP(S).
Predefined REST APIs provide common operations such as returning the results of reports and providing an API for updating common tables in your database. There is a need for client developers to run their own queries or queries that can only be written at run time. In such cases, REST Enabled SQL service is useful.
Note:
If you have Oracle REST Data Services installed and if you do not have SQL*Net (JDBC, OCI) to establish Net connection to the Oracle Database, then REST Enabled SQL service provides an easy mechanism to query and run SQL, SQL*Plus, and SQLcl statements against the REST Enabled Oracle Database schema and makes your application available through REST.Related Topics
This section introduces some common terms that are used throughout this document.
REST Enabled SQL service: A HTTPS web service that provides SQL access to the database. SQL statements can be posted to the service and the results are returned in a JSON format to the client.
HTTPS: Hyper Text Transfer Protocol Secure (HTTPS) is the secure version of HTTP, the protocol over which data is sent between your browser and the website to which you are connected. The ‘S’ at the end of HTTPS stands for Secure. It means all communications between your browser and ORDS are encrypted.
cURL: cURL is a command line tool used to transfer data. It is free and open source software that can be downloaded from the following location: curl_haxx.
By default, the REST Enabled SQL service is turned off. To configure REST Enabled SQL service settings, see Configuring REST Enabled SQL Service Settings.
This section explains how to use cURL commands to test the REST Enabled SQL service.
The REST Enabled SQL service is accessible by using the HTTPS POST method. To test the REST Enabled SQL service, you can use the command-line tool named cURL. This powerful tool is available for most platforms, and enables you to test and control the data that is being sent to and received from a REST Enabled SQL service.
Example 3-11 Example cURL Command
Request: curl -i -X POST --user ORDSTEST:ordstest --data-binary "select sysdate from dual" -H "Content-Type: application/sql" -k https://localhost:8088/ords/ordstest/_/sql
Where:
-i option displays the HTTP headers returned by the server.
-k option allows cURL to proceed and operate even for server connections that are otherwise considered to be insecure.
Response:
HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{
"env":{
"defaultTimeZone":"Europe/London"
},
"items":[
{
"statementId":1,
"statementType":"query",
"statementPos":{
"startLine":1,
"endLine":2
},
"statementText":"select sysdate from dual",
"response":[
],
"result":0,
"resultSet":{
"metadata":[
{
"columnName":"SYSDATE",
"jsonColumnName":"sysdate",
"columnTypeName":"DATE",
"precision":0,
"scale":0,
"isNullable":1
}
],
"items":[
{
"sysdate":"2017-07-21T08:06:44Z"
}
],
"hasMore":false,
"limit":1500,
"offset":0,
"count":1
}
}
]
}
The REST Enabled SQL service is provided only through HTTPS POST operation.
You must REST enable the Oracle database schema on which you want to use the REST Enabled SQL service. To REST enable the Oracle database schema, you can use SQL Developer or PL/SQL API.
ORDSTEST:
SQL> CONNECT ORDSTEST/*****; Connected SQL> exec ords.enable_schema; anonymous block completed SQL> commit; Commit complete. SQL>
This section explains how to authenticate the schema on which you want to use the REST Enabled SQL service.
Before using the REST Enabled SQL service, you must authenticate against the Oracle Database schema on which you want to run the SQL statements.
First Party Authentication (Basic Authentication) : For this authentication, create a user in Oracle REST Data Services (ORDS) with the SQL Developer role. This ORDS user will be able to run SQL for any Oracle database schema that is REST enabled.
Schema Authentication: For this authentication, use the Oracle database schema name in uppercase and the Oracle database schema password (for example, HR and HRPassword). Such user will be able to run SQL for the specified schema.
This section shows the format or pattern used to access the REST Enabled SQL service.
If Oracle REST Data Services (ORDS) is running in a Java EE Application Server, then the REST Enabled SQL service is only accessible through HTTPS. If Oracle REST Data Services (ORDS) is running in standalone mode, then ORDS can be configured to use HTTPS. The examples in this document use this configuration.
The following example URL locates the REST Enabled SQL service for the specified schema alias:
Pattern: https://<HOST>/ords/<SchemaAlias>/_/sql
Example: https://host/ords/ordstest/_/sql
Where: Default port is 443
Content Type and Payload Data Type Supported
The Header Content-Type
application/sql: for SQL statements
application/json: for JSON documents
Payload Data Type
SQL: SQL, PL/SQL, SQL*Plus, SQLcl statements
JSON Document: A JSON document with SQL statements and other options such as bind variables