Call Sequences for APEX_EXEC

All APEX_EXEC procedures require an existing APEX session to function. In a pure SQL or PL/SQL context, use the APEX_SESSION package to initialize a new session.

See Also: APEX_SESSION

Querying a Data Source with APEX_EXEC

  1. Prepare columns to be selected from the data source:

    1. Create a variable of the APEX_EXEC.T_COLUMNS type.

    2. Add columns with the APEX_EXEC.ADD_COLUMN.

  2. (Optional) Prepare bind variables:

    1. Create a variable of APEX_EXEC.T_PARAMETERS type.

    2. Add bind values with APEX_EXEC.ADD_PARAMETER.

  3. (Optional) Prepare filters:

    1. Create a variable of the type APEX_EXEC.T_FILTERS.

    2. Add bind values with APEX_EXEC.ADD_FILTER.

  4. Execute the data source query in one of the following ways:

    • For REST Data Sources, use APEX_EXEC.OPEN_REST_SOURCE_QUERY.

    • For REST Enabled SQL, use APEX_EXEC.OPEN_REMOTE_SQL_QUERY.

    • Alternatively, use APEX_EXEC.OPEN_QUERY_CONTEXT to pass in the location as a parameter.

  5. Get the result set meta data:

    1. APEX_EXEC.GET_COLUMN_COUNT returns the number of result columns.

    2. APEX_EXEC.GET_COLUMN returns information about a specific column.

  6. Process the result set:

    1. APEX_EXEC.NEXT_ROW advances the result cursor by one row.

    2. APEX_EXEC.GET_NNNN functions retrieve individual column values.

  7. Close all resources with APEX_EXEC.CLOSE.

  8. Add an exception handler and close those resources. For example:

    EXCEPTION
    WHEN others THEN
         apex_debug.log_exception;
         apex_exec.close( l_context );
    RAISE;

See Also:

For code examples of a complete query to a Data Source, review the example sections in the following APIs:

Executing a DML on a Data Source with APEX_EXEC

  1. Define the Data Manipulation Language (DML) columns:

    1. Create a variable of the APEX_EXEC.T_COLUMNS type.

    2. Add columns with APEX_EXEC.ADD_COLUMN.

  2. (Optional) Prepare bind variables:

    1. Create a variable of the APEX_EXEC.T_PARAMETERS type.

    2. Add bind values with APEX_EXEC.ADD_PARAMETER.

  3. Prepare the DML Context in one of the following ways:

    • For REST Data Sources, use OPEN_REST_SOURCE_DML_CONTEXT.

    • For REST Enabled SQL, use OPEN_REMOTE_DML_CONTEXT.

    • For local database, use OPEN_LOCAL_DML_CONTEXT.

  4. Add row values for the DML to perform:

    1. Use APEX_EXEC.ADD_DML_ROW to add a new row.

    2. Use APEX_EXEC.SET_VALUE to provide individual column values.

  5. Execute the DML with APEX_EXEC.EXECUTE_DML.

  6. Walk through RETURNING values and error messages for processed DML rows.

    • APEX_EXEC.NEXT_ROW advances the result cursor by one row.

    • APEX_EXEC.HAS_ERROR indicates whether DML processing for this row was successful or not.

    • APEX_EXEC.GET_DML_STATUS_CODE returns the status code (SQL Error Code) for each DML row. If DML for this row was successful, NULL is returned as the status code.

    • APEX_EXEC.GET_NNN functions retrieve individual column “DML RETURNING” values.

  7. Close all resources with APEX_EXEC.CLOSE.

  8. Add an exception handler and close those resources. For example:

    EXCEPTION
    WHEN others THEN
        apex_exec.close( l_context );
        RAISE;

See Also:

For code examples of a complete DML query, review the example sections in the following APIs:

Executing a Remote Procedure or REST API with APEX_EXEC

  1. (Optional) Prepare bind variables:

    1. Create a variable of APEX_EXEC.T_PARAMETERS type.

    2. Add bind values with APEX_EXEC.ADD_PARAMETER.

  2. Execute the local or remote procedure or REST API in one of the following ways:

    • For REST Data Sources, use APEX_EXEC.EXECUTE_REST_SOURCE.

    • For REST Enabled SQL, use APEX_EXEC.EXECUTE_REMOTE_PLSQL.

    • For local database, use APEX_EXEC.EXECUTE_PLSQL.

    The P_PARAMETERS array which is used to pass bind variables is an IN OUT parameter, so OUT parameters are passed back.

  3. (Optional) Retrieve the OUT parameters. Walk through the variable of the APEX_EXEC.T_PARAMETERS type and use GET_PARAMETER_VALUE to retrieve the OUT parameter value.

See Also:

For code examples of a complete remote procedure or REST API query, review the example sections in the following APIs: