EXECUTE_REMOTE_PLSQL Procedure

This procedure executes PL/SQL code on a REST Enabled SQL instance.

Syntax

APEX_EXEC.EXECUTE_REMOTE_PLSQL (
    p_server_static_id     IN     VARCHAR2,
    p_plsql_code           IN     VARCHAR2,
    p_auto_bind_items      IN     BOOLEAN      DEFAULT TRUE,
    p_sql_parameters       IN OUT t_parameters )

Parameters

Parameter	Description
`p_server_static_id`	Static ID of the ORDS REST Enabled SQL Instance.
`p_plsql_code`	PL/SQL code to be executed.
`p_auto_bind_items`	Whether to automatically bind page item values for both IN and OUT direction. If the PL/SQL code references bind variables which are not page items, this must be set to FALSE. Default TRUE
`p_sql_parameters`	Additional bind variables; if needed.

Example 1

Executes a PL/SQL block on a remote database.

BEGIN
    apex_exec.execute_remote_plsql(
        p_server_static_id => '{Static ID of the REST Enabled SQL Service}',
        p_plsql_code  => q'#begin :P10_NEW_SAL := salary_pkg.raise_sal( p_empno => :P10_EMPNO ); end;#' );
END;

Example 2

Works with arbitrary bind variables, so any bind can be used to pass values to the REST Enabled SQL service and to get values back.

DECLARE
        l_sql_parameters apex_exec.t_parameters;
        l_out_value      varchar2(32767);
    BEGIN
        apex_exec.add_parameter( l_sql_parameters, 'MY_BIND_IN_VAR',  '{some value}' );
        apex_exec.add_parameter( l_sql_parameters, 'MY_BIND_OUT_VAR', ''             );

        apex_exec.execute_remote_plsql(
            p_server_static_id     => '{Static ID of the REST Enabled SQL Service}',
            p_plsql_code           => q'#begin :MY_BIND_OUT_VAR := some_remote_plsql( p_parameter => :MY_BIND_IN_VAR ); end;#',
            p_auto_bind_items => false,
            p_sql_parameters  => l_sql_parameters );

        l_out_value := apex_exec.get_parameter_varchar2( 
            p_parameters  => l_sql_parameters,
            p_name        => 'MY_BIND_OUT_VAR');

        -- further processing of l_out_value        
END;