Using Bind-by-Name vs Bind-by-Position

Bind variables are used in two ways: by name by position. You must pick one for a given SQL command as the options are mutually exclusive.

Named Bind Variables

Binding by name requires the bind variable to be a string literal, prefixed by a colon.

In the case of named binds, the bindParams argument to the connection.execute() function should ideally be provided with the following properties of each bind variable defined.

Property	Description
dir	The bind variable direction
val	The value to be passed to the SQL statement
type	The data type

Example: Using Named Bind Variables

CREATE OR REPLACE PROCEDURE named_binds_ex_proc(
    "deptName" VARCHAR2,
    "sal" NUMBER
)
AUTHID CURRENT_USER
AS MLE LANGUAGE JAVASCRIPT
{{
if (deptName === null || sal === null){
    throw new Error(
        `must provide deptName and sal to named_binds_ex_proc()`
    );
}

const result = session.execute(
    `SELECT
        e.first_name ||
        ' '    ||
        e.last_name employee_name,
        e.salary
    FROM
        hr.employees e
        LEFT JOIN hr.departments d ON (e.department_id = d.department_id)
    WHERE
        nvl(d.department_name, 'n/a') = :deptName
        AND salary > :sal
    ORDER BY
        e.employee_id`,
    {
        deptName:{
             dir: oracledb.BIND_IN,
             val: deptName,
             type: oracledb.STRING
        },
        sal:{
             dir: oracledb.BIND_IN,
             val: sal,
             type: oracledb.NUMBER
        }
    }
);
console.log(`Listing employees working in ${deptName} with a salary > ${sal}`);
for (let row of result.rows){
    console.log(`${row.EMPLOYEE_NAME.padEnd(25)} - ${row.SALARY}`);
}
}};
/

BEGIN
    named_binds_ex_proc('Shipping', 6000);
END;
/

Result:

Listing employees working in Shipping with a salary > 6000
Matthew Weiss              - 8000
Adam Fripp                 - 8200
Payam Kaufling             - 7900
Shanta Vollman             - 6500

The bindParams argument to connection.execute() defines two named bind parameters:

• deptName

• sal

In this example, the function’s input parameters match the names of the bind variables, which improves readability but isn’t a requirement. You can assign bind variable names as long as the mapping in bindParams is correct.

Positional Bind Variables

Instead of using named bind parameters, you can alternatively provide bind-variable information as an array.

The number of elements in the array must match the number of bind parameters in the SQL text. Rather than mapping by name, the mapping of bind variable and value is based on the position of the bind variable in the text and position of the item in the bind array.

Example: Using Positional Bind Variables

This example demonstrates the use of positional bind variables and represents a reimplementation of Example: Using Named Bind Variables.

CREATE OR REPLACE PROCEDURE positional_binds_ex_proc(
    "deptName" VARCHAR2,
    "sal" NUMBER
)
AUTHID CURRENT_USER
AS MLE LANGUAGE JAVASCRIPT
{{
if (deptName === null || sal === null){
    throw new Error(
        `must provide deptName and sal to positional_binds_ex_proc()`
    );
}

const result = session.execute(
    `SELECT
        e.first_name ||
        ' '    ||
        e.last_name employee_name,
        e.salary
    FROM
        hr.employees e
        LEFT JOIN hr.departments d ON (e.department_id = d.department_id)
    WHERE
        nvl(d.department_name, 'n/a') = :deptName
        AND salary > :sal
    ORDER BY
        e.employee_id`,
    [
        deptName,
        sal
    ]
);
console.log(`Listing employees working in ${deptName} with a salary > ${sal}`);
for(let row of result.rows){
    console.log(`${row.EMPLOYEE_NAME.padEnd(25)} - ${row.SALARY}`);
}
}};
/

BEGIN
    positional_binds_ex_proc('Marketing', 10000);
END;
/

Result:

Listing employees working in Marketing with a salary > 10000
Michael Martinez          - 13000

In this example, bindParams is an array rather than an object. The mapping between bind variables in the SQL text to values is done by position. The first item in the bindParams array maps to the first occurrence of a placeholder in the SQL text and so on.