EXECUTE_PLSQL Procedure

27.20 EXECUTE_PLSQL Procedure

This procedure executes PL/SQL code based on the current process or plug-in location settings.

Syntax

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

Parameters

Parameter	Description
`p_plsql_code`	PL/SQL code to be executed. Based on the settings of the current process or process-type plug-in, the code is executed locally or remote.
`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. Note that `EXECUTE_PLSQL` binds all `p_sql_parameters` as VARCHAR2. Bind variables such as `NUMBER` and `DATE` are implicitly converted to VARCHAR2.

Example 1

Executes a PL/SQL block with arbitrary bind variables, so any bind can be used to pass values 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_plsql(
        p_plsql_code      => q'#begin :MY_BIND_OUT_VAR := some_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;

Example 2

Executes a PL/SQL block.

BEGIN
    apex_exec.execute_plsql(
        p_plsql_code => q'#begin :P10_NEW_SAL := salary_pkg.raise_sal( p_empno => :P10_EMPNO ); end;#' );
END;

Parent topic: APEX_EXEC