Getting Started with REST Enabled SQL

POST a query or DML or DDL or even a short script over HTTPS to ORDS and have ORDS run that on your Oracle Database and have the results returned in JSON. That's the plan.

What You Need To Know

REST Enabled SQL uses Schema Authentication as well First Party Authentication.  

This means you can run SQL against an Oracle Database if the following is true.
  • REST Enabled SQL is enabled in ORDS
  • A database user is REST Enabled 
  • The database user name is known
  • The database user password is known
So before turning on REST Enabled SQL in ORDS it is vital that current REST Enabled schemas have strong, secure passwords.

This should be resolved before turning on REST Enabled SQL in ORDS, as once it is turned on, all REST Enabled schemas will be accessible using only their database username and password through ORDS. The same applies for any new REST Enabled schemas.

Setup

Requirements

  • Download ORDS 17.3.
  • Download cURL . Not necessary, but the examples use cURL
  • Download Java 1.8 
  • Access to an Oracle Database

Configuration

Setup ORDS as normal, there is only one flag in ORDS configuration default.xml to turn on REST Enabled SQL feature. It is off by default

Configure ORDS to  REST Enabled SQL service

Example:


Configure Oracle Database schema

Example:
See more here

Accessing the REST Enable SQL Service

The REST Enabled SQL service is available using the following URL
https://servername:port/ords/schemaname/_/sql

But only HTTP POST requests are supported. So browsing to the URL will result in a 

405 Method Not Allowed

The easiest way to perform a HTTP POST is to use a client tool like cURL.

cURL Example:

*The DEMO user password is not what I would call "strong" or "secure" but hey!

HTTP Request

The HTTP POST request provides
  • Authentication : 
    • DEMO :The Oracle schema name in UPPER CASE. 
    • demo   : The terrible password for the DEMO schema. Passwords are case sensitive
  • Request Body : 
    • select 'Hello World!' as myColName from dual
  • Content-Type : 
    • application/sql  
  • URL:
    • http://localhost:8087/ords/demo/_/sql




JSON Response