Subscribe

Share

Database, SQL and PL/SQL

Get Your REST; POST Your SQL

Use REST-enabled SQL in Oracle REST Data Services.

By Jeff Smith

January/February 2018

Representational State Transfer (REST) is today’s dominant software architectural style for creating modern, scalable web services, and JavaScript Object Notation (JSON) is the most popular data-interchange format for RESTful web services that use the REST architecture.

Oracle REST Data Services accepts RESTful web service uniform resource identifiers (URIs) and directs them to the appropriate SQL statement or PL/SQL block, returning the output in either the JSON or comma-separated values (CSV) format. Oracle REST Data Services is easy to install and configure, and it empowers auto-generated REST endpoints for tables, views, and PL/SQL.

Until now, the SQL or database workload that was mapped to a URI that Oracle REST Data Services ultimately ran on an HTTPS call has always been predefined. This article introduces a new feature, REST-enabled SQL, in Oracle REST Data Services version 17.4 that enables the execution of ad hoc queries and scripts. This article will show you how to configure Oracle REST Data Services for this feature and demonstrate a REST-enabled SQL scenario.

Installation and Configuration You can download version 17.4 of Oracle REST Data Services from Oracle Technology Network. Installation of this Oracle REST Data Services version is no different from earlier versions; however, the REST-enabled SQL feature is not enabled by default.

The REST-enabled SQL feature can be enabled by default for all connection pools servicing Oracle Database instances, or it can be enabled for a particular database. To enable it for all databases, add the following line to the DEFAULTS.XML file:

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

Otherwise, add the line above to the appropriate XYZ_PU.XML connection pool configuration file. If your environment includes multiple databases configured for Oracle REST Data Services, it is recommended that you enable REST-enabled SQL only on connection pools where the feature is required.

Restart Oracle REST Data Services, and the new REST-enabled SQL endpoint (_/sql/) will then be available on REST-enabled schemas.

Security

REST-enabled SQL supports two security schemes: web server authentication and database user authentication. Web server authentication allows SQL workloads to be executed on any REST-enabled schema for a web server user that has been granted the “SQL Developer” role.

Starting with Oracle REST Data Services 17.4, database user authentication means that HTTPS requests can also be authenticated via a database user and password. For REST-enabled SQL, the database user can authenticate SQL workloads only for that REST-enabled schema’s _/sql/ endpoint.

The following scenarios will use the new database user authentication scheme. To make this available for your database, you need to REST-enable a schema first.

With Oracle REST Data Services installed and configured for your database, run this PL/SQL block as the user who is to be REST-enabled, substituting “HR” and “hr” with the name of the schema you want to REST-enable:

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'hr',
                       p_auto_rest_auth => FALSE);
    commit;
END;

Note that the p_url_mapping_pattern value must be in lowercase.

Scenario: Running a Simple SQL Statement

As the database user for the schema you just REST-enabled, run a simple SQL statement and examine the JSON response.

To access the REST-enabled SQL endpoint, use the following URI to issue an HTTPS POST request to the web server on which Oracle REST Data Services is running:

https://webserver:port/ords/schema/_/sql/

To issue a POST request, you will need to use a command-line interface such as CURL or an application such as Postman.

Here is the CURL command to issue a POST request that contains a simple SELECT query against the HR schema:

curl -i -X POST --user HR:password -H "Content-Type: application/sql" -k -d 
select department_id, max(salary) from employees group by department_id' 
http://localhost:8888/ords/hr/_/sql

Oracle REST Data Services confirms that the “/schema/” portion of the URI (“hr,” in this case) represents a schema that is REST-enabled. It then attempts to log in to the database as the HR user using the supplied password and, if it is successful, it runs the attached SQL statement as that user in the database.

The results from the execution of the SQL statement are then transformed to JSON and returned to the calling client, which in this case is the CURL session.

Below, the response for this request has been formatted for easier readability:

{
    "env": {
        "defaultTimeZone": "America/New_York"
    },
    "items": [
        {
            "statementId": 1,
            "statementType": "query",
            "statementPos": {
                "startLine": 1,
                "endLine": 2
            },
            "statementText": "select department_id, max(salary) from employees 
group by department_id",
            "response": [],
            "result": 0,
            "resultSet": {
                "metadata": [
                    {
                        "columnName": "DEPARTMENT_ID",
                        "jsonColumnName": "department_id",
                        "columnTypeName": "NUMBER",
                        "precision": 4,
                        "scale": 0,
                        "isNullable": 1
                    },
                    {
                        "columnName": "MAX(SALARY)",
                        "jsonColumnName": "max(salary)",
                        "columnTypeName": "NUMBER",
                        "precision": 0,
                        "scale": -127,
                        "isNullable": 1
                    }
                ],
                "items": [
                    {
                        "department_id": 100,
                        "max(salary)": 12000
                    },
                    {
                        "department_id": 30,
                        "max(salary)": 11000
                    },
                    {
                        "department_id": null,
                        "max(salary)": 7000
                    },
                    {
                        "department_id": 90,
                        "max(salary)": 24000
                    },
                    {
                        "department_id": 20,
                        "max(salary)": 13000
                    },
                    {
                        "department_id": 70,
                        "max(salary)": 10000
                    },
                    {
                        "department_id": 110,
                        "max(salary)": 12000
                    },
                    {
                        "department_id": 50,
                        "max(salary)": 8200
                    },
                    {
                        "department_id": 80,
                        "max(salary)": 14000
                    },
                    {
                        "department_id": 40,
                        "max(salary)": 6500
                    },
                    {
                        "department_id": 60,
                        "max(salary)": 9000
                    },
                    {
                        "department_id": 10,
                        "max(salary)": 4000
                    }
                ],
                "hasMore": false,
                "limit": 500,
                "offset": 0,
                "count": 12
            }
        }
    ]
}

Interpreting the Response

The response to the request includes two components: the metadata and the results. The metadata describes the following:

  • The time zone of the web server on which the Java Virtual Machine for Oracle REST Data Services is running
  • A listing of the statements executed
  • The items included in the results, including the column names and data types that have been transposed to JSON

The results for each statement are then included with each listing (in this example, there is only a single statement to be executed). The results also include the following:

  • Any text included with the response, such as “Table TEST created”
  • For data manipulation language (DML), the number of rows affected
  • Whether there are more results to be paged
  • The hardcoded limit for results, which is defined in your Oracle REST Data Services configuration
  • The number of results included in the items for the current response

Note that any DATE or TIMESTAMP included in a result set will include the TIMEZONE for the corresponding JSON value using the Zulu time zone derived from the Oracle REST Data Services installation.

Possible Uses

REST-enabled SQL provides access to remote data—“remote” being a database where no Oracle NET Services access is provided by HTTPS. Suppose you have a web application that is set up to work with one Oracle Database instance, but you need to access data in another database. Currently, that would require one of the following:

  • A DB_LINK from the first database to the second database
  • The ability to open a second Oracle NET Services connection to the second database

With REST-enabled SQL, you now have an HTTPS alternative. If Oracle REST Data Services is available and configured for the second database, you can just access the data via an HTTPS POST request. You could also write a PL/SQL routine to make the data available as a VIEW that goes across HTTPS.

There is also a new Type 3 JDBC driver that uses REST-enabled SQL endpoints to make JDBC connections available over HTTPS. You can download the Oracle REST Data Services JDBC driver from the Oracle REST Data Services download page. In this blog post, I show how to use this new driver with the SQLcl command-line interface for Oracle Database to connect to a database via Oracle REST Data Services to run your queries and scripts.

Summary

Oracle REST Data Services provides a REST interface for Oracle Database. It can serve custom RESTful services that run any SQL or PL/SQL block, and it supports REST-enabling tables and views for a full create, read, update, and delete (CRUD) API. Oracle REST Data Services recently added support for remote procedure call (RPC) for PL/SQL objects and now, with version 17.4, Oracle REST Data Services provides HTTPS access for running ad hoc database workloads.

GitHub includes a sample web page for trying many different scenarios using the new _/sql/ endpoint. Extract the files to your Oracle REST Data Services or web server HTDOCS folder, and load the HTML page. (This approach is much easier to work with than making CURL requests.)

Next Steps

DOWNLOAD Oracle REST Data Services.

TRY Oracle Database Cloud services.

Photography by Joshua Sortino on Unsplash