EXECUTE_REMOTE_PLSQL Procedure Signature 1

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 execute.
`p_auto_bind_items`	Default `TRUE`. 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`.
`p_sql_parameters`	(Optional) Additional bind variables.

Example

Executes a PL/SQL block on a remote database. 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;