14 APEX_EXEC
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.
- Global Constants
- Data Types
- ADD_COLUMN Procedure
- ADD_FILTER Procedure
- ADD_ORDER_BY Procedure
- ADD_PARAMETER Procedure
- CLOSE Procedure
- EXECUTE_PLSQL Procedure
- EXECUTE_REMOTE_PLSQL Procedure
- EXECUTE_WEB_SOURCE Procedure
- GET Functions
- GET_COLUMN Function
- GET_COLUMN_COUNT Function
- GET_COLUMN_POSITION Function
- GET_PARAMETER Functions
- GET_TOTAL_ROW_COUNT Function
- IS_REMOTE_SQL_AUTH_VALID Function
- NEXT_ROW Function
- OPEN_REMOTE_SQL_QUERY Function
- OPEN_QUERY_CONTEXT Function
- OPEN_WEB_SOURCE_QUERY Function
- PURGE_WEB_SOURCE _CACHE Procedure