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.
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.
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.
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
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
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.
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.