ADD_PARAMETER Procedure

This procedure adds a SQL parameter to the parameter collection. To use SQL parameters, prepare the array first, then use it in the execution call.

Syntax

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            varchar2 );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            number );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            date );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            timestamp );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            timestamp with time zone );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            timestamp with local time zone );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            interval year to month );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            interval day to second );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            blob );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            bfile );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            clob );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_value      in            sys.anydata );

procedure add_parameter (
    p_parameters in out nocopy t_parameters,
    p_name       in            t_column_name,
    p_data_type  in            t_data_type,
    p_value      in            t_value );

Parameters

Table 14-4 ADD_PARAMETER Procedure Parameters

Parameter Description

p_parameters

SQL parameter array.

p_name

Parameter name.

p_value

Parameter value.

Example

declare
    l_parameters     apex_exec.t_parameters;
begin
    apex_exec.add_parameter( l_parameters, 'ENAME',    'SCOTT' );
    apex_exec.add_parameter( l_parameters, 'SAL',      2000 );
    apex_exec.add_parameter( l_parameters, 'HIREDATE', sysdate );

    apex_exec.execute_remote_plsql(
        p_server_static_id => '{static ID of the REST Enabled SQL Service}',
        p_auto_bind_items  => false,
        p_plsql_code       => q'#begin insert into emp values (:ENAME, :SAL, :HIREDATE ); end;#',
        p_sql_parameters   => l_parameters );
end;