Introduction
In a previous post APEX, ORDS, and REST Enabled SQL, I explained how REST Enabled SQL works, how to configure it in ORDS, and how to set up a REST Enabled SQL Service in APEX.
This post will show you how to use the APEX_EXEC API to execute PL/SQL and run SQL queries in remote databases with REST Enabled SQL. This post assumes you have already configured an APEX REST Enabled Data Service in the 'Local' instance, which points to a REST Enabled SQL Endpoint in a 'Remote' instance. Remember, a REST Enabled SQL Endpoint points to a specific schema in the 'Remote' instance.
About APEX_EXEC
The APEX_EXEC PL/SQL API encapsulates DML and querying capabilities and provides an abstraction from the data source to APEX components and PL/SQL code. APEX_EXEC contains procedures and functions to execute queries or procedural calls on local and remote data sources and REST Data Sources. It can be used for plug-in development and procedural PL/SQL processing in applications or within packages and procedures.
Run a PL/SQL Procedure
This section will show you how to run a PL/SQL procedure in a remote database. For this example, the goal is to create a task in a table in the remote database. A package and procedure exist in the remote database called cndemo_tasks_pk.create_task. This procedure creates a record in the table cndemo_tasks and returns the new task number.
To use REST Enabled SQL to call PL/SQL on a remote database, we should use the APEX_EXEC.EXECUTE_REMOTE_PLSQL API.
Sample Code
Discussion
Of course, just because you can do something does not mean you should. For requirements like the above, I would typically recommend creating an ORDS Handler in the remote instance. A pre-defined REST service provides a consistent system agnostic interface for any system (not just an Oracle one) to create tasks etc. It also keeps you in control of what code is being run against that system.
For certain activities, REST-enabled SQL is incredibly flexible. You do not need pre-defined web services, etc. You are not reliant on the remote database for anything. You can run any DDL or DML against the remote schema if you have access to the REST Enabled SQL Endpoint. These activities could include deploying code to the remote schema, running ad-hoc sql queries, etc.
Error Handling
If an error is raised in the remote PL/SQL Block, then an ORA-20999 Error on Remote Server error is raised along with the ORA error encountered in the remote database. For example, the below error is raised if I do not populate the TASK_NAME bind variable.
ORA-20999: Error on Remote Server: ORA-01400: cannot insert NULL into ("CNDEMO"."CNDEMO_TASKS"."TASK_NAME")
ORA-06512: at "CNDEMO.CNDEMO_TASKS_PK", line 25
ORA-06512: at "CNDEMO.CNDEMO_TASKS_PK", line 20
ORA-06512: at line 7
ORA-06512: at line 14
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_API", line 1561
Running SQL
In this section, I will show you how to run a SQL statement in the remote database to get a list of tasks for a given user. In order to do this, we will use the apex_exec.open_remote_sql_query APEX PL/SQL API.
In the sample code, I have tried to illustrate as many of the options and parameters available in the various APEX_EXEC APIs.
Sample Code
Discussion
This is another example that, in reality, may have been better served by building an ORDS Resource in the remote instance. However, if you need to dynamically change the SQL run in the remote instance or adjust what columns are returned, this is a great option.
Conclusion
The APEX_EXEC PL/SQL API, used in conjunction with REST Enabled SQL, is a great way to run code against remote databases. It is an especially useful combination if you don't know what code you need to run ahead of time or if you cannot create ORDS REST Services in the remote database. It also provides an excellent abstraction layer using a familiar PL/SQL API.