The APEX_EXEC
package encapsulates data processing and querying capabilities and provides an abstraction from the data source to Application Express components and plug-ins. It contains procedures and functions to execute queries or procedural calls on local and remote data sources as well as web source modules. It can be used for plug-in development as well as for procedural PL/SQL processing in applications or within packages and procedures.
All procedures require an APEX session to be set up. In a pure SQL or PL/SQL context, use the APEX_SESSION
package to initialize a new session.
The typical call sequence depends on the used data source.
REST Enabled SQL Data Sources
Prepare bind variables with [optional]
Create a variable of APEX_EXEC.T_PARAMETERS
type
Add bind values with APEX_EXEC.ADD_PARAMETER
Execute the remote query call
Call APEX_EXEC.OPEN_REMOTE_SQL_QUERY
Get column indexes for result column names
Call APEX_EXEC.OPEN_WEB_SOURCE_QUERY
Loop until the result set is exhausted
Call APEX_EXEC.NEXT_ROW
Retrieve column values for each column by position
call APEX_EXEC.GET_VARCHAR2
, APEX_EXEC.GET_NUMBER
, APEX_EXEC.GET_DATE
, ...
Finally ALWAYS close the query context - IMPORTANT
Call APEX_EXEC.CLOSE
Web source module
Prepare web source parameters variables with [optional]
Create a variable of APEX_EXEC.T_PARAMETERS
type
Add bind values with APEX_EXEC.ADD_PARAMETER
Prepare filters to be passed to the web source (if supported by the web source)
Create a variable of APEX_EXEC.T_FILTERS
type
Add bind values with APEX_EXEC.ADD_FILTER
Prepare order by expressions to be passed to the web source (if supported by the web source)
Create a variable of APEX_EXEC.T_ORDER_BYS
type
Add bind values with APEX_EXEC.ADD_ORDER_BY
Execute the remote query call
Call APEX_EXEC.OPEN_WEB_SOURCE_QUERY
Pass in filters, order bys and parameters previously prepared
Get column indexes for result column names
Call APEX_EXEC.GET_COLUMN_POSITION
Loop until the result set is exhausted
Call APEX_EXEC.NEXT_ROW
Retrieve column values for each column by position
Call APEX_EXEC.GET_VARCHAR2
, APEX_EXEC.GET_NUMBER
, APEX_EXEC.GET_DATE
, ...
Finally ALWAYS close the query context - IMPORTANT
Call APEX_EXEC.CLOSE
Note:
Always add an exception handler to your procedure or function to ensure that APEX_EXEC.CLOSE
is being called in any case. This is important to release server resources like database cursors, temporary lobs and others.