18 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. APEX_EXEC contains procedures and functions to execute queries or procedural calls on local and remote data sources as well as REST Data Sources. It can be used for plug-in development and procedural PL/SQL processing in applications or within packages and procedures.

All APEX_EXEC procedures require an existing Application Express session to function. 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. REST Data Source

   1. Prepare REST data 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 REST data source (if supported by the data 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 REST data source (if supported by the data 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 always called to release server resources such as database cursors and temporary lobs.

• Global Constants
  
• Data Types
  
• ADD_COLUMN Procedure
  
• ADD_DML_ROW Procedure
  
• ADD_FILTER Procedure
  
• ADD_ORDER_BY Procedure
  
• ADD_PARAMETER Procedure
  
• CLEAR_DML_ROWS Procedure
  
• CLOSE Procedure
  
• COPY_DATA Procedure
  
• EXECUTE_DML Procedure
  
• EXECUTE_PLSQL Procedure
  
• EXECUTE_REMOTE_PLSQL Procedure
  
• EXECUTE_REST_SOURCE Procedure
  
• EXECUTE_WEB_SOURCE Procedure (Deprecated)
  
• GET Functions
  
• GET_COLUMN Function
  
• GET_COLUMN_COUNT Function
  
• GET_COLUMN_POSITION Function
  
• GET_DATA_TYPE Functions
  
• GET Functions
  
• GET_DML_STATUS_CODE Function
  
• GET_DML_STATUS_MESSAGE Function
  
• GET_PARAMETER Functions
  
• GET_ROW_VERSION_CHECKSUM Function
  
• GET_TOTAL_ROW_COUNT Function
  
• HAS_ERROR Function
  
• HAS_MORE_ROWS Function
  
• IS_REMOTE_SQL_AUTH_VALID Function
  
• NEXT_ROW Function
  
• OPEN_LOCAL_DML_CONTEXT Function
  
• OPEN_REMOTE_DML_CONTEXT Function
  
• OPEN_REMOTE_SQL_QUERY Function
  
• OPEN_QUERY_CONTEXT Procedure
  
• OPEN_QUERY_CONTEXT Function
  
• OPEN_REST_SOURCE_DML_CONTEXT Function
  
• OPEN_WEB_SOURCE_DML_CONTEXT Function (Deprecated)
  
• OPEN_REST_SOURCE_QUERY Function
  
• OPEN_WEB_SOURCE_QUERY Function (Deprecated)
  
• PURGE_REST_SOURCE_CACHE Procedure
  
• PURGE_WEB_SOURCE_CACHE Procedure (Deprecated)
  
• SET_NULL Procedure
  
• SET_VALUE Procedure
  
• SET_VALUES Procedure
  
• SET_ROW_VERSION_CHECKSUM Procedure