Getting Started
The new Oracle REST Data Services SQL over REST. How to enable that is on my last blog post here: http://krisrice.blogspot.com/2017/09/ords-173-beta-introducing-rest-enabled.html
cURL Examples
The simplest way to test this new feature out is with a curl command sending over the SQL.
$ curl -X "POST" "http://localhost:9090/ords/hr/_/sql" \
-H "Content-Type: application/sql" \
-u HR:oracle \
-d $'select * from dual;'
There are a number of other curl based examples now in the example github project : https://github.com/oracle/oracle-db-tools/tree/master/ords/rest-sql . The examples try to cover the various type of output that can be returned. This includes trying to use SPOOL which is a restricted command, DDL, a full sql script, a SQLcl command "DDL" and others.
A better way
cURL is great but a web page is much more dynamic to show off such features. Dermot who created this new feature created a demo page to show off many of the features as he showed off in this tweet ( hint follow him )
A quick "how to get started" using REST Enabled SQL.https://t.co/kn0E1dpnRm pic.twitter.com/pn2jJq4x3r— Dermot ONeill (@dermotoneill) September 7, 2017
Starting from that example there a file in that same github folder.
The most useful thing in this demo page is the Examples drop list. There's everything from a trivial select from dual to doing POST of a fully form JSON document of the command to run such as a select with a bind of a VARRAY
{
"statementText": "SELECT ? as col_ARRAY FROM dual",
"offset": 0,
"limit": 5,
"binds":[
{"index":1,"data_type":"VARRAY", "type_name":"ADHOC_VARRAY_NUMBER","value":[1,5,3]}
]
}
The Output
The returning JSON can vary quite a lot depending on what is being sent. However the basic structure is quite simple. There is an array of "items" which is the statements processed. The variance comes in depending on what is being sent.
For a Query there will be a item[N].resultSet. This will then have a child of metadata which is the columns, datatypes, json scrubbed name, real name. Peered to this is another "items" which has an array of the rows from the select.
For NON-Query there is an item[N].response which contains the text of what the command did.
And it can get more complicated from there.
{
"env": {
"defaultTimeZone": "America/New_York"
},
"items": [
{
"statementId": 1,
....
},
.....
Here's a short example and it's corresponding output:
spool a select 1 from dual; DESC dual; begin null; end; / spool off
{
"env": {
"defaultTimeZone": "America/New_York"
},
"items": [
{
"statementId": 1,
"statementType": "sqlplus",
"statementPos": {
"startLine": 1,
"endLine": 1
},
"statementText": "spool a",
"response": [
"SP2-0738: Restricted command: \n\"spool a\"\nnot available",
"\n"
],
"result": 0
},
{
"statementId": 2,
"statementType": "query",
"statementPos": {
"startLine": 2,
"endLine": 2
},
"statementText": "select 1 from dual",
"response": [],
"result": 0,
"resultSet": {
"metadata": [
{
"columnName": "1",
"jsonColumnName": "1",
"columnTypeName": "NUMBER",
"precision": 0,
"scale": -127,
"isNullable": 1
}
],
"items": [
{
"1": 1
}
],
"hasMore": false,
"limit": 1500,
"offset": 0,
"count": 1
}
},
{
"statementId": 3,
"statementType": "sqlplus",
"statementPos": {
"startLine": 3,
"endLine": 3
},
"statementText": "DESC dual",
"response": [
"Name Null? Type \n----- ----- ----------- \nDUMMY VARCHAR2(1) \n"
],
"result": 0
},
{
"statementId": 4,
"statementType": "plsql",
"statementPos": {
"startLine": 4,
"endLine": 7
},
"statementText": "begin\n null;\nend;",
"response": [
"\nPL/SQL procedure successfully completed.\n\n"
],
"result": 0
},
{
"statementId": 5,
"statementType": "sqlplus",
"statementPos": {
"startLine": 8,
"endLine": 8
},
"statementText": "spool off",
"response": [
"SP2-0738: Restricted command: \n\"spool off\"\nnot available",
"\n"
],
"result": 0
}
]
}
