Thursday, September 14, 2017

Demo App for REST enabled SQL


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 ) 

Starting from that example there a file in that same github folder.
This is a full test page now based in Oracle JET , ORDS and Code Mirror that can be placed on your ORDS server ( for CORS reasons ) and served up.  There's a series of inputs on the left, output on the right hand side and finally some examples on the bottom of performing the call in cURL, JQuery , or SQLcl.




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
        }
    ]
}

Wednesday, September 06, 2017

ORDS 17.3 Beta - Introducing REST enabled SQL

Download

Got get it on the normal ORDS download page

Versioning 


First and most obvious is ORDS is now on the same versioning scheme as SQL Developer, SQLcl and Oracle Cloud.  That is <year>.<quarter>.<patch> and the same tail we've always had which is <julian day>.<HH24>.<MI>.  That makes this beta ords.17.3.0.248.08.45.zip On to the features.


REST Enabled SQL


Once again the core sql engine from SQL Developer that was wrapped into the command line  SQLcl has been used for another feature. This same library is now used in many places in Oracle including the install of Grid Infra for anyone running RAC databases to the Developer Cloud Service to add Hudson build options for database deployments.

The new feature we are naming REST enabled SQL which in reality is more of REST enabled SQLcl. The feature is OFF by default and can be activated with the following line added to the defaults.xml file.

<entry key="restEnabledSql.active">true</entry>


Once that option is enabled, there is a now an endpoint enabled for EVERY REST enabled schema such as http://localhost:9090/ords/klrice/_/sql . This endpoint is a POST only and can be authenticated to in 2 manners.

  1. Web Server level authenticated user with "SQL Developer" role will be able to access any REST enabled schema.  Yes, that means any REST enabled schema so ensure to use this properly.
  2. DB Authentication. This method will as implies only be allowed to access the same DB Schema it is authenticated to. So HR can access  http://localhost:9090/ords/hr/_/sql  only.


Then it's as simple as calling the REST point, authenticating and tossing any amount of sql at it. Either a singular sql statement or an entire scripts.


$ curl -X "POST" "http://localhost:9090/ords/hr/_/sql"  \
       -H "Content-Type: application/sql"               \
       -u HR:oracle                                     \
   -d $'select count(1) abc from user_objects;select * from dual;' 


{
    "env": {
        "defaultTimeZone": "America/New_York"
    },
    "items": [
        {
            "response": [],
            "result": 0,
            "resultSet": {
                "count": 1,
                "hasMore": false,
                "items": [
                    {
                        "abc": 35
                    }
                ],
                "limit": 1500,
                "metadata": [
                    {
                        "columnName": "ABC",
                        "columnTypeName": "NUMBER",
                        "isNullable": 1,
                        "jsonColumnName": "abc",
                        "precision": 0,
                        "scale": -127
                    }
                ],
                "offset": 0
            },
            "statementId": 1,
            "statementPos": {
                "endLine": 1,
                "startLine": 1
            },
            "statementText": "select count(1) abc from user_objects",
            "statementType": "query"
        },
        {
            "response": [],
            "result": 0,
            "resultSet": {
                "count": 1,
                "hasMore": false,
                "items": [
                    {
                        "dummy": "X"
                    }
                ],
                "limit": 1500,
                "metadata": [
                    {
                        "columnName": "DUMMY",
                        "columnTypeName": "VARCHAR2",
                        "isNullable": 1,
                        "jsonColumnName": "dummy",
                        "precision": 1,
                        "scale": 0
                    }
                ],
                "offset": 0
            },
            "statementId": 2,
            "statementPos": {
                "endLine": 3,
                "startLine": 3
            },
            "statementText": "select * from dual",
            "statementType": "query"
        }
    ]
}




The fine print.

Supported Commands


There's a number of things in this SQLcl library that are disabled as they touch the host operating systems or reach out to the network. Appendix D of the ORD Documentation lists these but to give a flavor for what they are things like
  • host
  • spool
  • @, @@, start
  • connect
  • cd
  • ....
Basically, if the command can touch/read/write the file system in any way, nope. If the command can reach out over the network, nope.

Number of Rows returns

Also the number of rows returnable is governed by a flag in defaults.xml to prevent a runaway query. Exporting a bazillion rows is not a use for this feature. 
<entry key="jdbc.maxRows">1500</entry>



Coming Next...

This feature not only supports a 'plain' sql script but there's a JSON language to sending more robust requests. This is a short example that shows some of the powerful features sending in a select with an offset, a limit, bind variables and a  SCN number.