Pre-General Availability: 2018-03-02

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