24.16 EXECUTE_REMOTE_PLSQL Procedure

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

Syntax

PROCEDURE 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

Table 24-13 EXECUTE_REMOTE_PLSQL Procedure 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 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.

Examples

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;