22.8 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

Signature 1

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            VARCHAR2 );

Signature 2

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            NUMBER );

Signature 3

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            DATE );

Signature 4

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            TIMESTAMP );

Signature 5

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            TIMESTAMP WITH TIME ZONE );

Signature 6

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       in            t_column_name,
    p_value      IN            TIMESTAMP WITH LOCAL TIME ZONE );

Signature 7

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       in            t_column_name,
    p_value      in            INTERVAL YEAR TO MONTH );

Signature 8

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       in            t_column_name,
    p_value      in            INTERVAL DAY TO SECOND );

Signature 9

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            BLOB );

Signature 10

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            bfile );

Signature 11

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            CLOB );

Signature 12

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            SYS.ANYDATA );

Signature 13

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 );

Signature 14

Note:

This signature is only available if SDO_GEOMETRY (Oracle Locator) is installed in the database.

PROCEDURE ADD_PARAMETER (
    p_parameters IN OUT NOCOPY t_parameters,
    p_name       IN            t_column_name,
    p_value      IN            mdsys.sdo_geometry );

Parameters

Table 22-5 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;