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.

Topics

  • Named Bind Variables
    Binding by name requires the bind variable to be a string literal, prefixed by a colon.
  • Positional Bind Variables
    Instead of using named bind parameters, you can alternatively provide bind-variable information as an array.

Parent topic: Bind Variables

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 7-8 Using Named Bind Variables

CREATE OR REPLACE PROCEDURE named_binds_ex_proc(
    "deptName" VARCHAR2,
    "sal" NUMBER
)
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}`);
}
}};
/
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.

Parent topic: Using Bind-by-Name vs Bind-by-Position

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 7-9 Using Positional Bind Variables

This example demonstrates the use of positional bind variables and represents a reimplementation of Example 7-8

CREATE OR REPLACE PROCEDURE positional_binds_ex_proc(
    "deptName" VARCHAR2,
    "sal" NUMBER
)
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}`);
}
}};
/

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.

Parent topic: Using Bind-by-Name vs Bind-by-Position