This blog was originally published on 21 December, 2017.

One of the new features of Application Express 5.2 is the ability to execute SQL not only in the “local” Oracle database (where APEX runs in), but also on a remote Oracle instance. This is based on the new REST Enabled SQL feature introduced with ORDS 17.4. When the REST Enabled SQL feature is active on an ORDS instance, SQL statements can be passed in JSON format using HTTP POST requests. Execution results are being passed back as a self-describing JSON response. Clients thus can communicate with the Oracle database using open protocols and without SQL*Net. 

A prerequisite for using REST Enabled SQL is to have at least ORDS 17.4 installed. Older ORDS versions (3.0.x) do not support REST Enabled SQL.

The Application Express 5.2 Early Adopter Instance meets all requirements for REST enabled SQL. You can try out this new feature and its integration into Application Express using just your schema (or the schema of a second workspace).

Set up

First, we need to set up the schema and the workspace in order to use REST Enabled SQL. We’ll authenticate with the REST Enabled SQL service using the database username and password (not the APEX workspace password). Navigate to SQL Workshop – SQL Commands. You can see your schema name in the upper right corner. If you do not know your schema password, execute an ALTER USER {username} IDENTIFIED BY {password} in order to set the schema password to a known value.

Then we need to enable the REST Enabled SQL service for the given Schema. Run the following PL/SQL block.

begin
    ords.enable_schema;
end;

After that, the schema is all set. Navigate back to Application Builder in order to create an application using the REST Enabled SQL feature.

Create a REST Enabled SQL Reference

In Application Builder, first create a new application without much content – in the create application wizard just accept all defaults. When the application has been created, navigate to Shared Components.

In Shared Components, look up REST Enabled SQL in the lower left corner.

Right now, no references exist. Click the Create button to add a new reference.

Create the new REST Enabled SQL reference from scratch.

Provide a name for the new reference and use the following URL scheme: http://apexea.oracle.com/pls/apex/{your-schema}

On the next page, provide authentication credentials. Use the database schema name and the password you have set in SQL Workshop. Choose Basic Authentication and ignore the warning message. For the Early Adopter instance, Basic Authentication is OK to use. Click the Create ORDS Remote Server to finish.

Application Express will test the REST Enabled SQL service. If everything has been done right (correct URL, schema name and password) you should see a message as follows.

Click the Close button to complete the process. You should see your new REST Enabled SQL reference.

Instead of using your own schema, you can also use a schema the development team has prepared for you. Use the following details when creating the REST Enabled SQL reference.

  • Endpoint UTL: https://apexea.oracle.com/pls/apex/scott_connect
  • Authentication Type: Basic
  • Username: SCOTT_CONNECT (upper case)
  • Password: tiger

The SCOTT_CONNECT schema does not own any tables (so you cannot accidentally drop them), but it has SELECT privileges on tables in the SCOTT_DATA schema. When, later on, creating components on objects in that schema, make sure to pick up the SCOTT_DATA schema in order to see tables.

Use REST Enabled SQL

Next we’ll start using the REST Enabled SQL reference for an APEX component. Navigate to the application pages overview and click the Create Page button. 

Provide a Name for the new page  …

Provide input to the next wizards steps as needed. On the last wizard step, things have changed in Application Express 5.2: Whereas earlier versions just allowed to choose between a table or a SQL query, we now can choose REST Enabled SQL as the components’ Data Source …

The report can be based on a SQL Query as well …

Finally click Create. The report page will be created and you will be redirected to Page Designer, where you can review the settings. Note that the Source section within the property editor on the right also allows to set the data source and to choose a REST Enabled SQL service.

Finally run your page. The report results should look rather normal – just an interactive report based on the EMP table …

… which we can apply filters on …

Enable debug mode in order to get some insight about what is happening during execution. Application Express generates a SQL query, as usual – but instead of simply executing it, the query is being posted to the REST Enabled SQL service as an HTTP POST request.

In Application Express Early Adopter 1, REST Enabled SQL can be used for Interactive Reports, Classic Reports, the CSS Calendar, Oracle JET Charts and for “Execute PL/SQL” page processes. Try it out in your Early Adopter workspace now.