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.

1. REST Enabled SQL Data Sources

   1. Prepare bind variables with [optional]

      • Create a variable of APEX_EXEC.T_PARAMETERS type

      • Add bind values with APEX_EXEC.ADD_PARAMETER

   2. Execute the remote query call

      • Call APEX_EXEC.OPEN_REMOTE_SQL_QUERY

   3. Get column indexes for result column names

      • Call APEX_EXEC.OPEN_WEB_SOURCE_QUERY

   4. Loop until the result set is exhausted

      • Call APEX_EXEC.NEXT_ROW

   5. Retrieve column values for each column by position

      • call APEX_EXEC.GET_VARCHAR2, APEX_EXEC.GET_NUMBER, APEX_EXEC.GET_DATE, ...

   6. Finally ALWAYS close the query context - IMPORTANT

      • Call APEX_EXEC.CLOSE

2. Web source module

   1. Prepare web source parameters variables with [optional]

      • Create a variable of APEX_EXEC.T_PARAMETERS type

      • Add bind values with APEX_EXEC.ADD_PARAMETER

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

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

   4. Execute the remote query call

      • Call APEX_EXEC.OPEN_WEB_SOURCE_QUERY

      • Pass in filters, order bys and parameters previously prepared

   5. Get column indexes for result column names

      • Call APEX_EXEC.GET_COLUMN_POSITION

   6. Loop until the result set is exhausted

      • Call APEX_EXEC.NEXT_ROW

   7. Retrieve column values for each column by position

      • Call APEX_EXEC.GET_VARCHAR2, APEX_EXEC.GET_NUMBER, APEX_EXEC.GET_DATE, ...

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