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.
Typical Use Case: Your Oracle Database is in the cloud and you want to make it available through a REST API over HTTPS.
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 these cases, a 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 an network connection to Oracle Database, then a 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.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’ stands for secure. It means that all communications between your browser and Oracle REST Data Services 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 access the REST-Enabled SQL service.
You can use the HTTPS POST method to access the REST-Enabled SQL service. To access 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 connect and control the data that you send to and receive from a REST-Enabled SQL service.
Example 4-1 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:
The-i
option displays the HTTP headers returned by the server.
The -k
option enables 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 method.
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 the PL/SQL API.
ORDSTEST
:
SQL> CONNECT ORDSTEST/*****; Connected SQL> exec ords.enable_schema; anonymous block completed SQL> commit; Commit complete. SQL>
Related Topics
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 with the SQL Developer role. This Oracle REST Data Services 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
). This type of 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 is running in a Java EE Application Server, then the REST-Enabled SQL service is only accessible through HTTPS. If Oracle REST Data Services is running in standalone mode, then Oracle REST Data Services 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: The default port is 443
Content Type and Payload Data Type Supported
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
This section provides different HTTPS POST request examples that use Oracle REST Data Services standalone setup with secure HTTPS access.
The payload data of the HTTPS POST request message can be in one of the following formats:
For POST requests with Content-Type
as application/sql
, the payload is specified using SQL, SQL*Plus, and SQLcl statements. The payload can be a single line statement, multiple line statements, or a file that consists of multiline statements as shown in the following examples:
The following example uses Schema Authentication to run a single SQL statement against the demo
Oracle Database schema:
Request:
curl -i -X POST --user DEMO:demo --data-binary "select sysdate from dual" -H "Content-Type: application/sql" -k https://localhost:8088/ords/demo/_/sql
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 } } ] }
Where:
DEMO
is the Oracle Database schema name.
demo
is the Oracle Database schema password.
select sysdate from dual
is the SQL statement that will run in the DEMO
Oracle Database schema.
Content-Type: application/sql
is the content type. Only application/sql
and application/json
are supported.
https://localhost:8088/ords/demo/_/sql
is the location of the REST- Enabled SQL service for the demo
Oracle Database schema.
For multiline SQL statements, using a file as payload data in requests is useful.
File: simple_query.sql
SELECT 10 FROM dual;
Request:
curl -i -X POST --user DEMO:demo --data-binary "@simple_query.sql" -H "Content-Type: application/sql" -k https://localhost:8088/ords/demo/_/sql
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":1 }, "statementText":"SELECT 10 FROM dual", "response":[ ], "result":0, "resultSet":{ "metadata":[ { "columnName":"10", "jsonColumnName":"10", "columnTypeName":"NUMBER", "precision":0, "scale":-127, "isNullable":1 } ], "items":[ { "10":10 } ], "hasMore":false, "limit":1500, "offset":0, "count":1 } } ] }
You can run one or more statements in each POST request. Statements are separated similar to Oracle Database SQL*Plus script syntax, such as, end of line for SQL*Plus statements, a semi colon for SQL statements, and forward slash for PL/SQL statements.
script.sql
:
CREATE TABLE T1 (col1 INT); DESC T1 INSERT INTO T1 VALUES(1); SELECT * FROM T1; BEGIN INSERT INTO T1 VALUES(2); END; / SELECT * FROM T1;
Request:curl -i -X POST --user DEMO:demo --data-binary "@script.sql" -H "Content-Type: application/sql" -k https://localhost:8088/ords/demo/_/sql
HTTP/1.1 200 OK Content-Type: application/json X-Frame-Options: SAMEORIGIN Transfer-Encoding: chunked { "env":{ "defaultTimeZone":"Europe/London" }, "items":[ { "statementId":1, "statementType":"ddl", "statementPos":{ "startLine":1, "endLine":1 }, "statementText":"CREATE TABLE T_EXAMPLE1 (col1 INT)", "response":[ "\nTable T_EXAMPLE1 created.\n\n" ], "result":0 }, { "statementId":2, "statementType":"sqlplus", "statementPos":{ "startLine":2, "endLine":2 }, "statementText":"DESC T_EXAMPLE1", "response":[ "Name Null\n Type \n---- ----- ---------- \nCOL1 NUMBER(38) \n" ], "result":0 }, { "statementId":3, "statementType":"dml", "statementPos":{ "startLine":3, "endLine":3 }, "statementText":"INSERT INTO T_EXAMPLE1 VALUES(1)", "response":[ "\n1 row inserted.\n\n" ], "result":1 }, { "statementId":4, "statementType":"query", "statementPos":{ "startLine":4, "endLine":4 }, "statementText":"SELECT * FROM T_EXAMPLE1", "response":[ ], "result":1, "resultSet":{ "metadata":[ { "columnName":"COL1", "jsonColumnName":"col1", "columnTypeName":"NUMBER", "precision":38, "scale":0, "isNullable":1 } ], "items":[ { "col1":1 } ], "hasMore":false, "limit":1500, "offset":0, " count":1 } }, { "statementId":5, "statementType":"plsql", "statementPos":{ "startLine":5, "endLine":8 }, "statementText":"BEGIN\n INSERT INTO T_EXAMPLE1 VALUES(2);\nEND;", "response":[ "\nPL\/SQL procedure successfully completed.\n\n" ], "result":1 }, { "statementId":6, "statementType":"query", "statementPos":{ "startLine":9, "endLine":9 }, "statementText":"SELECT * FROM T_EXAMPLE1", "response":[ ], "result":1, "resultSet":{ "metadata":[ { "columnName":"COL1", "jsonColumnName":"col1", "columnTypeName":"NUMBER", "precision":38, "scale":0, "isNullable":1 } ], "items":[ { "col1":1 }, { "col1":2 } ], "hasMore":false, "limit":1500, "offset":0, "count":2 } }, { "statementId":7, "statementType":"ddl", "statementPos":{ "startLine":10, "endLine":10 }, "statementText":"DROP TABLE T_EXAMPLE1", "response":[ "\nTable T_EXAMPLE1 dropped.\n\n" ], "result":1 } ] }
Using a JSON document as the payload enables you to define more complex requests as shown in the following sections:
The following example posts a JSON document (within the simple_query.json
file) to the REST-Enabled SQL service.
File: simple_query.json
{ "statementText":"SELECT TO_DATE('01-01-1976','dd-mm-yyyy') FROM dual;"}
Request: curl -i -X POST --user DEMO:demo --data-binary "@simple_query.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql
The statementText
holds the SQL statement or statements.
The Content-Type
is application/json
.
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":1 }, "statementText":"SELECT TO_DATE('01-01-1976','dd-mm-yyyy') FROM dual", "response":[ ], "result":0, "resultSet":{ "metadata":[ { "columnName":"TO_DATE('01-01-1976','DD-MM-YYYY')", "jsonColumnName":"to_date('01-01-1976','dd-mm-yyyy')", "columnTypeName":"DATE", "precision":0, "scale":0, "isNullable":1 } ], "items":[ { "to_date('01-01-1976','dd-mm-yyyy')":"1976-01-01T00:00:00Z" } ], "hasMore":false, "limit":1500, "offset":0, "count":1 } } ] }
You can specify the limit
value in a POST JSON request for the pagination of a large result set returned from a query.
limit.json
{ "statementText": " WITH data(r) AS ( SELECT 1 r FROM dual UNION ALL SELECT r+1 FROM data WHERE r < 100 ) SELECT r FROM data;", "limit": 5 }
Request: curl -i -X POST --user DEMO:demo --data-binary "@limit.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql
limit
is the maximum number of rows returned from a query.
Note:
The maximum number of rows returned from a query is based on themisc.pagination.maxRows
value set in default.xml
file.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":1 }, "statementText":" WITH data(r) AS ( SELECT 1 r FROM dual UNION ALL SELECT r+1 FROM data WHERE r < 100 ) SELECT r FROM data", "response":[ ], "result":0, "resultSet":{ "metadata":[ { "columnName":"R", "jsonColumnName":"r", "columnTypeName":"NUMBER", "precision":0, "scale":-127, "isNullable":1 } ], "items":[ { "r":1 }, { "r":2 }, { "r":3 }, { "r":4 }, { "r":5 } ], "hasMore":true, "limit":5, "offset":0, "count":5 } } ] }
Related Topics
You can specify the offset
value in a POST JSON request. This value specifies the first row that must be returned and is used for pagination of the result set returned from a query.
offset_limit.json
{ "statementText": " WITH data(r) AS ( SELECT 1 r FROM dual UNION ALL SELECT r+1 FROM data WHERE r < 100 ) SELECT r FROM data;", "offset": 25, "limit": 5 }
Request: curl -i -X POST --user DEMO:demo --data-binary "@offset_limit.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql
Where: offset
is the first row to be returned in the result set. Typically, this is used to provide the pagination for a large result set that returns the next page of rows in the result set.
Note:
Each request made to the REST-Enabled SQL service is performed in its own transaction, which means that you cannot ensure that the rows returned will match the previous request. To avoid these risks, queries that need pagination should use the ORDER BY clause on a primary key.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":1 }, "statementText":" WITH data(r) AS ( SELECT 1 r FROM dual UNION ALL SELECT r+1 FROM data WHERE r < 100 ) SELECT r FROM data", "response":[ ], "result":0, "resultSet":{ "metadata":[ { "columnName":"R", "jsonColumnName":"r", "columnTypeName":"NUMBER", "precision":0, "scale":-127, "isNullable":1 } ], "items":[ { "r":26 }, { "r":27 }, { "r":28 }, { "r":29 } { "r":30 } ], "hasMore":true, "limit":5, "offset":25, "count":5 } } ] }
You can define binds in JSON format. This functionality is useful when calling procedures and functions that use binds as the parameters.
Example 4-2 Binds in POST Request
binds.json
{ "statementText": "CREATE PROCEDURE TEST_OUT_PARAMETER (V_PARAM_IN INT IN, V_PARAM_OUT INT OUT) AS BEGIN V_PARAM_OUT := V_PARAM_IN + 10; END; / EXEC TEST_OUT_PARAMETER(:var1, :var2)", "binds":[ {"name":"var1","data_type":"NUMBER","value":10}, {"name":"var2","data_type":"NUMBER","mode":"out"} ] }
Request: curl -i -X POST --user DEMO:demo --data-binary "@binds.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql
HTTP/1.1 200 OK Content-Type: application/json X-Frame-Options: SAMEORIGIN Transfer-Encoding: chunked { "env":{ "defaultTimeZone":"Europe/London" }, "items":[ { "statementId":1, "statementType":"plsql", "statementPos":{ "startLine":1, "endLine":2 }, "statementText":"CREATE PROCEDURE TEST_OUT_PARAMETER (V_PARAM_IN IN INT, V_PARAM_OUT OUT INT) AS BEGIN V_PARAM_OUT := V_PARAM_IN + 10; END;", "response":[ "\nProcedure TEST_OUT_PARAMETER compiled\n\n" ], "result":0, "binds":[ { "name":"var1", "data_type":"NUMBER", "value":10 }, { "name":"var2", "data_type":"NUMBER", "mode":"out", "result":null } ] }, { "statementId":2, "statementType":"sqlplus", "statementPos":{ "startLine":3, "endLine":3 }, "statementText":"EXEC TEST_OUT_PARAMETER(:var1, :var2)", "response":[ "\nPL\/SQL procedure successfully completed.\n\n" ], "result":0, "binds":[ { "name":"var1", "data_type":"NUMBER", "value":10 }, { "name":"var2", "data_type":"NUMBER", "mode":"out", "result":20 } ] } ] }
Example 4-3 Complex Bind in POST Request
File:complex_bind_example.json
{ "statementText":" declare type t is table of number index by binary_integer; l_in t := :IN; l_out t; begin for i in 1..l_in.count loop l_out(i) := l_in(i) * 2; end loop; :L_OUT := l_out; end; ", "binds":[ { "name":"IN", "data_type":"PL/SQL TABLE", "type_name":"", "type_subname":"", "type_components":[ { "data_type":"NUMBER" } ], "value":[ 2, 4, 7 ] }, { "name":"L_OUT", "data_type":"PL/SQL TABLE", "type_name":"", "type_subname":"", "type_components":[ { "data_type":"NUMBER" } ], "mode":"out" } ] }
Request: curl -i -X POST --user DEMO:demo --data-binary "@complex_bind_example.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql
HTTP/1.1 200 OK Content-Type: application/json X-Frame-Options: SAMEORIGIN Transfer-Encoding: chunked { "env":{ "defaultTimeZone":"Europe/London" }, "items":[ { "statementId":1, "statementType":"plsql", "statementPos":{ "startLine":2, "endLine":12 }, "statementText":"declare \n type t is table of number index by binary_integer; \n l_in t := :IN; \n l_out t; \n begin \n for i in 1..l_in.count loop \n l_out(i) := l_in(i) * 2; \n end loop; \n :L_OUT := l_out; \n end;", "response":[ ], "result":1, "binds":[ { "name":"IN", "data_type":"PL/SQL TABLE", "type_components":[ { "data_type":"NUMBER" } ], "type_name":"", "type_subname":"", "value":[ 2, 4, 7 ] }, { "name":"L_OUT", "data_type":"PL/SQL TABLE", "mode":"out", "type_components":[ { "data_type":"NUMBER" } ], "type_name":"", "type_subname":"", "result":[ 4, 8, 14 ] } ] } ] }
This section shows the examples with batch statements and batch bind values in a POST request.
Example 4-4 Batch statements
{ "statementText":[ "insert into adhoc_table_simple values(1)", "insert into adhoc_table_simple values(2)", "delete from adhoc_table_simple" ] }
Request :curl -i -X POST --user DEMO:demo --data-binary "@batch_example.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql
HTTP/1.1 200 OK Content-Type: application/json X-Frame-Options: SAMEORIGIN Transfer-Encoding: chunked { "env":{ "defaultTimeZone":"Europe/London" }, "items":[ { "statementId":1, "statementType":"dml", "statementPos":{ "startLine":0, "endLine":0 }, "statementText":[ "insert into adhoc_table_simple values(1)", "insert into adhoc_table_simple values(2)", "delete from adhoc_table_simple" ], "response":[ "\n1 row inserted.\n\n", "\n1 row inserted.\n\n", "\n2 rows inserted.\n\n" ], "result":[ 1, 1, 2 ] } ] }
Example 4-5 Batch bind values
{ "statementText":"INSERT INTO ADHOC_TABLE_DATE VALUES(?,?)", "binds":[ { "index":1, "data_type":"NUMBER", "batch":true, "value":[ 3, 6, 9, 13, 17 ] }, { "index":2, "data_type":"DATE", "batch":true, "value":[ "2017-02-21T06:12:20Z", "2017-02-21T06:12:20Z", "2017-02-21T06:12:20Z", "2017-02-21T06:12:20Z", "2017-02-21T06:12:20Z" ] } ] }
Request: curl -i -X POST --user DEMO:demo --data-binary "@batch_bind_example.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql
HTTP/1.1 200 OK Content-Type: application/json X-Frame-Options: SAMEORIGIN Transfer-Encoding: chunked { "env":{ "defaultTimeZone":"Europe/London" }, "items":[ { "statementId":1, "statementType":"dml", "statementPos":{ "startLine":1, "endLine":2 }, "statementText":"INSERT INTO ADHOC_TABLE_DATE VALUES(?,?)", "response":[ "\n1 row inserted.\n\n", "\n1 row inserted.\n\n", "\n1 row inserted.\n\n", "\n1 row inserted.\n\n", "\n1 row inserted.\n\n" ], "result":[ 1, 1, 1, 1, 1 ], "binds":[ { "index":1, "data_type":"NUMBER", "batch":true, "value":[ 3, 6, 9, 13, 17 ] }, { "index":2, "data_type":"DATE", "batch":true, "value":[ "2017-02-21T06:12:20Z", "2017-02-21T06:12:20Z", "2017-02-21T06:12:20Z", "2017-02-21T06:12:20Z", "2017-02-21T06:12:20Z" ] } ] } ] }
Example 4-6 Oracle REST Data services Time Zone Set as Europe/London
Oracle Database DATE and TIMESTAMP data types do not have a time zone associated with them. The DATE and TIMESTAMP values are associated with the time zone of the application. Oracle REST Data Services and the REST- Enabled SQL service return values in a JSON format. The standard for JSON is to return date and timestamp values using the UTC Zulu format. Oracle REST Data Services and the REST- Enabled SQL service return Oracle Database DATE and TIMESTAMP values in the Zulu format using the time zone in which Oracle REST Data Services is running.
Oracle recommends running Oracle REST Data Services using the UTC time zone to make this process easier.
date.json
{ "statementText":"SELECT TO_DATE('2016-01-01 10:00:03','yyyy-mm-dd hh24:mi:ss' ) winter, TO_DATE('2016-07-01 10:00:03','yyyy-mm-dd hh24:mi:ss' ) summer FROM dual;" }
Request: curl -i -X POST --user DEMO:demo --data-binary "@date.json" -H "Content-Type: application/json" -k https://localhost:8088/ords/demo/_/sql
Note:
In this example, both DATE values are specified as 10 a.m. The"summer"
value is returned as 9 a.m. Zulu time. This is due to British Summer Time.HTTP/1.1 200 OK Date: Wed, 26 Jul 2017 14:59:27 GMT Content-Type: application/json X-Frame-Options: SAMEORIGIN Transfer-Encoding: chunked Server: Jetty(9.2.21.v20170120) { "env":{ "defaultTimeZone":"Europe/London" }, "items":[ { "statementId":1, "statementType":"query", "statementPos":{ "startLine":1, "endLine":1 }, "statementText":"SELECT TO_DATE('2016-01-01 10:00:03','yyyy-mm-dd hh24:mi:ss' ) winter, TO_DATE('2016-07-01 10:00:03','yyyy-mm-dd hh24:mi:ss' ) summer FROM dual", "response":[ ], "result":0, "resultSet":{ "metadata":[ { "columnName":"WINTER", "jsonColumnName":"winter", "columnTypeName":"DATE", "precision":0, "scale":0, "isNullable":1 }, { "columnName":"SUMMER", "jsonColumnName":"summer", "columnTypeName":"DATE", "precision":0, "scale":0, "isNullable":1 } ], "items":[ { "winter":"2016-01-01T10:00:03Z", "summer":"2016-07-01T09:00:03Z" } ], "hasMore":false, "limit":1500, "offset":0, "count":1 } } ] }
The following code snippet shows the different data types and the formats supported:
{ "statementText":"SELECT ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? FROM dual", "binds":[ { "index":1, "data_type":"NUMBER", "value":1233 }, { "index":2, "data_type":"NUMERIC", "value":123 }, { "index":3, "data_type":"DECIMAL", "value":123 }, { "index":4, "data_type":"DEC", "value":123 }, { "index":5, "data_type":"NUMBER", "value":123 }, { "index":6, "data_type":"INTEGER", "value":123 }, { "index":7, "data_type":"INT", "value":123 }, { "index":8, "data_type":"SMALLINT", "value":123 }, { "index":9, "data_type":"FLOAT", "value":123 }, { "index":10, "data_type":"DOUBLE PRECISION", "value":123 }, { "index":11, "data_type":"REAL", "value":123 }, { "index":12, "data_type":"BINARY_FLOAT", "value":123 }, { "index":13, "data_type":"BINARY_DOUBLE", "value":123 }, { "index":14, "data_type":"CHAR", "value":"abc" }, { "index":15, "data_type":"CHARACTER", "value":"abc" }, { "index":16, "data_type":"VARCHAR", "value":"abc" }, { "index":17, "data_type":"VARCHAR2", "value":"abc" }, { "index":18, "data_type":"CHAR VARYING", "value":"abc" }, { "index":19, "data_type":"CHARACTER VARYING", "value":"abc" }, { "index":20, "data_type":"NCHAR", "value":"abc" }, { "index":21, "data_type":"NATIONAL CHAR", "value":"abc" }, { "index":22, "data_type":"NATIONAL CHARACTER", "value":"abc" }, { "index":23, "data_type":"NVARCHAR", "value":"abc" }, { "index":24, "data_type":"NVARCHAR2", "value":"abc" }, { "index":25, "data_type":"NCHAR VARYING", "value":"abc" }, { "index":26, "data_type":"NATIONAL CHAR VARYING", "value":"abc" }, { "index":27, "data_type":"NATIONAL CHARACTER VARYING", "value":"abc" }, { "index":28, "data_type":"DATE", "value":"01-Jan-2016" }, { "index":29, "data_type":"TIMESTAMP", "value":"1976-02-01T00:00:00Z" }, { "index":30, "data_type":"TIMESTAMP", "value":"1976-02-01T00:00:00Z" }, { "index":31, "data_type":"TIMESTAMP WITH LOCAL TIME ZONE", "value":"1976-02-01T00:00:00Z" }, { "index":32, "data_type":"TIMESTAMP WITH TIME ZONE", "value":"1976-02-01T00:00:00Z" }, { "index":33, "data_type":"INTERVALYM", "value":"P10Y10M" }, { "index":34, "data_type":"INTERVAL YEAR TO MONTH", "value":"P10Y10M" }, { "index":35, "data_type":"INTERVAL YEAR(2) TO MONTH", "value":"P10Y10M" }, { "index":36, "data_type":"INTERVALDS", "value":"P11DT10H10M10S" }, { "index":37, "data_type":"INTERVAL DAY TO SECOND", "value":"P11DT10H10M10S" }, { "index":38, "data_type":"INTERVAL DAY(2) TO SECOND(6)", "value":"P11DT10H10M10S" }, { "index":39, "data_type":"ROWID", "value":1 }, { "index":40, "data_type":"RAW", "value":"AB" }, { "index":41, "data_type":"LONG RAW", "value":"AB" }, { "index":42, "data_type":"CLOB", "value":"clobvalue" }, { "index":43, "data_type":"NCLOB", "value":"clobvalue" }, { "index":45, "data_type":"LONG", "value":"A" } ] }
Request Specification for application/sql
The body of the request is in plain UTF8 text. Statements can be separated by their usual SQL*Plus terminator.
Specification for application/json
JSONPath | Type | Description | Example | Default Value | Possible Values |
---|---|---|---|---|---|
$.statementText |
String |
Specifies the SQL statements to execute. |
"select 1 from dual" |
Not applicable |
Not applicable |
$.statementText |
Array |
Specifies batch DML statements using an array. One DML statement is specified per string in an array. |
[ "insert into test1 values(1)","update test1 set col1=2" ] |
Not applicable |
Not applicable |
$.offset |
Number |
Specifies the number of rows to offset the query result. This is used for pagination of the result set returned from a query. |
25 |
0 |
Between 0 to misc.pagination.maxRows . |
$.limit |
Number |
Specifies the maximum number of rows returned from a query. Values greater than the value of the |
500 |
misc.pagination.maxRows |
Between 0 to misc.pagination.maxRows . |
$.binds |
Array |
Specifies an array of objects specifying the bind information. |
"binds":[ { "name":"mybind1", "data_type":"NUMBER", "mode":"out" }, { "name":"mybind2", "data_type":"NUMBER", "value":7 } ] |
Not applicable |
Not applicable |
$.binds[*].name |
String |
Specifies the name of the bind, when you are using named notation. |
"mybind" |
Not applicable |
Not applicable |
$.binds[*].index |
Number |
Specifies the index of bind, when you are using positional notation. |
1 |
Not applicable |
Between 1 to n |
$.binds[*].data_type |
String |
Specifies Oracle data type of the bind. |
"NUMBER" |
Not applicable |
For more information, refer to Oracle Built-in Types |
$.binds[*].value |
Any value |
Specifies the value of the bind. |
"value to insert" |
null |
Can be one of the following data-types:
For more information, refer to Oracle Built-in Types |
$.binds[*].mode |
String |
Specifies the mode in which the bind is used. |
"out" |
"in" |
[ "in" , "inout", "out" ] |
$.binds[*].batch |
Boolean |
Specifies whether or not you want to perform a batch bind. If you want to perform a batch bind, then set the value to If the value is set to |
true |
false |
[ true, false ] |
$.binds[*].type_name |
String |
Required when you are using Currently, only an empty string is accepted as the value. |
"" |
Not applicable |
Not applicable |
$.binds[*].type_subname |
String |
Required when you are using Currently, only an empty string is accepted as the value. |
"" |
Not applicable |
Not applicable |
$.binds[*].type_components |
Array |
Specifies an array of data types in the PL/SQL TABLE Required when you are using |
[{"data_type":"NUMBER"}] |
Not applicable |
Not applicable |
$.binds[*].type_components[*].data_type |
String |
Specifies Oracle data type of a column in the PL/SQL TABLE. Required when you are using |
"NUMBER" |
Not applicable |
For more information, refer to Oracle Built-in Types |
JSONPath | Data type | Description | Example Values | Possible values |
---|---|---|---|---|
$.env |
Object |
Specifies the information about the Oracle REST Data Services environment. |
Not applicable |
Not applicable |
$.env.defaultTimeZone |
String |
Specifies the timezone in which Oracle REST Data Services server is running on. |
"Europe/London" |
Not applicable |
$.items |
Array |
Specifies that there is one item for each statement executed. |
Not applicable |
Not applicable |
$.items[*].statementId |
Number |
Specifies the sequence number of the statement. |
1 |
Not applicable |
$.items[*].statementType |
String |
Specifies the type of statement. |
"query" |
[ "query" , "dml", "ddl", "plsql" , "sqlplus" , "ignore", "transaction-control", "session-control", "system-control", "jdbc", "other" ] |
$.items[*].statementPos |
Object |
Specifies information about the position of a specified statement. |
Not applicable |
Not applicable |
$.items[*].statementPos.startLine |
Number |
Specifies start line of the statement. |
Not applicable |
Not applicable |
$.items[*].statementPos.endLine |
Number |
Specifies end line of the statement. |
Not applicable |
Not applicable |
$items[*].statementText |
String |
Specifies the SQL statement to be executed. |
"select 1 from dual" |
Not applicable |
$items[*].statementText |
Array |
Specifies batch DML statements can be specified using an array. One DML statement specified per string in an array. |
[ "insert into test1 values(1)","update test1 set col1=2" ] |
Not applicable |
$.items[*].response |
Array |
Specifies array of Strings. The response generated when running the statement. |
[ "\n1 row inserted.\n\n" ] |
Not applicable |
$.items[*].result |
Number |
Specifies the result generated when running the statement. For DML statements, this will be the number of rows affected. |
5 |
Not applicable |
$.items[*].result |
Array |
Specifies the result generated when running each of the batch statements. For DML statements, this will be the number of rows affected. |
[ 1, 1, 2 ] |
Not applicable |
$.items[*].resultSet |
Object |
Specifies information about the result set generated from a query. |
Not applicable |
Not applicable |
$.items[*].resultSet.metadata |
Array |
Specifies each object in the array provides information about the metadata of a column. |
Not applicable |
Not applicable |
$.items[*].resultSet.metadata[*].columnName |
String |
Specifies the name of the column used in the Oracle Database. |
Not applicable |
Not applicable |
$.items[*].resultSet.metadata[*].jsonColumnName |
String |
Specifies the name of the column used in
|
Not applicable |
Not applicable |
$.items[*].resultSet.metadata[*].columnTypeName |
String |
Specifies the Oracle Database data type of the column. |
Not applicable |
Not applicable |
$.items[*].resultSet.metadata[*].precision |
Number |
Specifies the precision of the column. |
Not applicable |
Not applicable |
$.items[*].resultSet.metadata[*].scale |
Number |
Specifies the scale of the column. |
Not applicable |
Not applicable |
$.items[*].resultSet.metadata[*].isNullable |
Number |
Specifies whether the column is nullable or not. 0, if the column is not nullable. 1, if the column is nullable. |
Not applicable |
Not applicable |
$.items[*].resultSet.items |
Array |
Specifies the list of all rows returned in the result set. |
Not applicable |
Not applicable |
$.items[*].resultSet.items[*].<columnname> |
Any type |
Specifies the value of a particular column and row in the result set. |
Not applicable |
Not applicable |
$.items[*].resultSet.hasMore |
Boolean |
Specifies whether result set has more rows. Value is set to The rows in the result set depend on |
false |
[ true , false ] |
$.items[*].resultSet.count |
Number |
Specifies the number of rows returned. |
Not applicable |
Not applicable |
$.items[*].resultSet.offset |
Number |
Specifies the number of rows to offset the query result. This is used for pagination of the result set returned from a query. |
25 |
Between 0 to |
$.items[*].resultSet.limit |
Number |
Specifies the maximum number of rows returned from a query. Values greater than |
500 |
Between 0 to |
$.items[*].binds |
Array |
Specifies an array of objects specifying the bind information. |
"binds":[ { "name":"mybind1", "data_type":"NUMBER", "mode":"out" }, { "name":"mybind2", "data_type":"NUMBER", "value":7 } ] |
Not applicable |
$.items[*].binds[*].name |
String |
Specifies the name of the bind, when you are using named notation. |
"mybind" |
Not applicable |
$.items[*].binds[*].index |
Number |
specifies iIndex of bind, when you are using positional notation. |
1 |
1 - n |
$.items[*].binds[*].data_type |
String |
Specifies the Oracle data type of the bind. |
"NUMBER" |
For more information, refer to Oracle Built-in Types |
$.items[*].binds[*].value |
Any type |
Specifies the value of the bind. |
"value to insert" |
Can be one of the following data types:
For more information, refer to Oracle Built-in Types |
$.items[*].binds[*].result |
Any type |
Specifies the result of an OUT bind. |
Not applicable |
Not applicable |
$.items[*].binds[*].mode |
String |
Specifies the mode in which the bind is used. |
"out" |
[ "in" , "inout", "out" ] |
$.items[*].binds[*].batch |
Boolean |
Specifies whether or not you want to perform a batch bind. If you want to perform a batch bind, then set the value to If a batch bind is to be performed, then the value is set to If the value is set to |
true |
[ true, false ] |
$.items[*].binds[*].type_name |
String |
Required when using Currently, only an empty string is accepted as the value. |
"" |
Not applicable |
$.items[*].binds[*].type_subname |
String |
Required when using Currently, only an empty string is accepted as the value. |
"" |
Not applicable |
$.items[*].binds[*].type_components |
Array |
Array of data types in the PL/SQL TABLE Required when using |
[{"data_type":"NUMBER"}] |
Not applicable |
$.items[*].binds[*].type_components[*].data_type |
String |
The Oracle data type of a column in the PL/SQL TABLE. Required when using |
"NUMBER" |
For more information, refer to Oracle Built-in Types |
This section lists all the supported SQL, SQL*Plus and SQLcl statements for REST-Enabled SQL service.
This section describes the SQL statements that the REST- Enabled SQL service supports.
REST- Enabled SQL service supports all SQL commands. If the specified Oracle Database schema has the appropriate privileges, then you can run them. Oracle REST Data Services makes all queries into in-line views before execution to provide pagination support. Queries are made in-line irrespective of the format in which you provide the query. All the other nonquery SQL statements are executed as they are.
All column names in a query must be unique because the views and in-line views cannot have ambiguous column names.
Cursor expressions are not displayed in view or in-line views.
WITH FUNCTION clause is not supported in in-line views.
The REST- Enabled SQL service supports PL/SQL statements and blocks.
Example 4-7 PL/SQL Statement
DECLARE v_message VARCHAR2(100) := 'Hello World'; BEGIN FOR i IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE (v_message); END LOOP; END; /
This section lists all the SQL*Plus statements that the REST- Enabled SQL service supports.
REST- Enabled SQL service supports most of the SQL*Plus statements except those statements that are related to formatting. The specific Oracle Database schema must have the appropriate privileges to run the SQL*Plus statemments.
SET system_variable value
Note:
system_variable
and value
represent one of the clauses described in Set System Variables section./ (slash)
COPY {FROM database | TO database | FROM database TO database} {APPEND | CREATE | INSERT | REPLACE} destination_table[(column, column, column, ...)] USING query
DEF[INE] [variable] | [variable = text]
DESC[RIBE] {[schema.]object[@connect_identifier]}
EXEC[UTE] statement
HELP | ? [topic]
PASSW[ORD] [username]
PRINT [variable ...]
PRO[MPT] [text]
REM[ARK]
SHO[W] [option]
TIMI[NG] [START text | SHOW | STOP]
UNDEF[INE] variable ...
VAR[IABLE] [variable [type][=value]]
WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE[COMMIT | ROLLBACK | NONE]}
WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
XQUERY xquery_statement
system_variable
and value
:
SET APPI[NFO]{ON | OFF | text}
SET AUTOP[RINT] {ON | OFF}
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
SET CMDS[EP] {; | c | ON | OFF}
SET COLINVI[SIBLE] [ON | OFF]
SET CON[CAT] {. | c | ON | OFF}
SET COPYC[OMMIT] {0 | n}
SET DEF[INE] {& | c | ON | OFF}
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
SET ECHO {ON | OFF}
SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]
SET ESC[APE] {\ | c | ON | OFF}
SET FEED[BACK] {6 | n | ON | OFF | ONLY}]
SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
SET SHOW[MODE] {ON | OFF}
SET SQLBL[ANKLINES] {ON | OFF}
SET SQLP[ROMPT] {SQL> | text}
SET TI[ME] {ON | OFF}
SET TIMI[NG] {ON | OFF}
SET VER[IFY] {ON | OFF}
SET XQUERY BASEURI {text}
SET XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT}
SET XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT}
SET XQUERY CONTEXT {text}
This section lists the possible values for option
which is either a term or a clause used in the SHO[W] option
command.
option
variable:
SHOW system_variable
SHOW EDITION
SHOW ERR[ORS] [ { ANALYTIC VIEW | ATTRIBUTE DIMENSION | HIERARCHY | FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name]
SHOW PDBS
SHOW SGA
SHOW SQLCODE
SHOW COLINVI[SIBLE]
SHOW APPIN[FO]
SHOW AUTOT[RACE]
SHOW BINDS
SHOW BLO[CK TERMINATOR]
SHOW CMDSEP
SHOW COPYTYPECHECK
SHOW COPYCOMMIT
SHOW DEFINE
SHOW DEFINES
SHOW DESCR[IBE]
SHOW ECHO
SHOW EDITION
SHOW ERRORL[OGGING]
SHOW ESC[APE]
SHOW FEEDBACK
SHOW CONCAT
SHOW SHOW[MODE]
SHOW RECYC[LEBIN]
SHOW RELEASE
SHOW SQLBL[ANKLINES]
SHOW SCAN
SHOW SERVEROUT[PUT]
SHOW SPACE
SHOW TABLES
SHOW TIMI[NG]
SHOW USER
SHOW VER[IFY]
SHOW XQUERY
Related Topics
This section lists the SQLcl statements that the REST- Enabled SQL service supports.
REST- Enabled SQL service supports some of the SQLcl statements. The specific Oracle Database schema must have the appropriate privileges to run the SQLcl statements.
CTAS
DDL
SET DDL