Prerequisites

- Remote Oracle Database + ORDS 17.3 or later

- ORDS instance having a connection pool configured for target database

 

The Basics

Execute any SQL through a REST Endpoint - e.g. "curl" utility

curl   -H    "Content-Type:application/json"
       -u    "SOCTT:TIGER"
       -d    '{"statementText":"select systimestamp as dt from dual"}'
       http://{ords-hostname}:{ords-port}/ords/{db-schema}/_/sql

 

And the response would be the following self-describing JSON

Screen Shot 2018-12-26 at 10.04.26 PM.png

 

Using Remote Database Connections

(1) Shared Components --> Data Sources --> REST Enabled SQL

( Name, End Point, Authentication)

(2) Use a REST Enabled SQL Service and therefore a remote Oracle database as the Data Source for Oracle APEX components like:

• Classic Reports, Interactive Reports

• Reflow Report, Toggle Columns Report

• Tree Region, JET Charts, CSS Calendar

• PL/SQL Process

Screen Shot 2018-12-27 at 8.55.02 AM.png

 

 

Using Custom PL/SQL Code

  • Access REST enabled SQL sources within custom PL/SQL Code:
    • Execute query and provide a "cursor" object APEX_EXEC.OPEN_REMOTESQL_QUERY
    • Execute a PL/SQL block anonymously APEX_EXEC.EXECUTE_REMOTE_SQL
  • Use REST Enabled SQL as a Plug-In developer:
    • "Region Source supports different Data Sources" Plugin Attribute
    • Use APEX_EXEC PL/SQL package in Plug-In code

 

Reference

https://www.oracle.com/technetwork/developer-tools/apex/overview/apex-181-new-features-otn-4491380.pdf