Pre-General Availability: 2017-09-04
This appendix provides different HTTPS POST Request examples that use ORDS standalone setup with secure HTTPS access.
The payload data of the HTTPS POST request message can be in one of the following formats:
application/sql Content-TypeFor POST requests with Content-Type as application/sql , the payload is specified using SQL, SQL*Plus and SQLcl statements. The payload can be single line, multiple line statements, or a file comprising of multi-line 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 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 multi line 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
}
]
}
application/json Content-TypeUsing a JSON document as the payload, enables you to define more complex requests as shown in the following examples:
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
statementText holds the SQL statement(s)
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 limit value in a POST JSON request for 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 "@offset_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 thejdbc.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 offset value in a POST JSON request. This value specifies the first row that need to 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 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. So, you cannot ensure that the rows returned will match with the previous request. To avoid such 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.
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 C-1 Example Where ORDS time zone is 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 (ORDS) and 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. ORDS and REST Enabled SQL service return Oracle Database DATE and TIMESTAMP values in the Zulu format using the time zone in which ORDS is running. So the zoneless values stored in the Oracle Database can be interpreted and generated.
Oracle recommends to run ORDS 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
}
}
]
}
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"
}
]
}