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.
- 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.
- 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.
