Bind Variables

Use bind variables to control data passed into or retrieved from the database.

SQL and PL/SQL statements may contain bind variables, indicated by colon-prefixed identifiers. These parameters indicate where separately specified values are substituted in a statement when executed, or where values are to be returned after execution.

Three different kinds of bind variables exist in the Oracle database:
  • IN bind variables
  • OUT bind variables
  • IN OUT bind variables

IN binds are values passed into the database. OUT binds are used to retrieve data from the database. IN OUT binds are passed in and may return a different value after the statement executes.

Using bind variables is recommended in favor of constructing SQL or PL/SQL statements through string concatenation or template literals. Both performance and security can benefit from the use of bind variables. When bind variables are used, the Oracle database does not have to perform a resource and time consuming hard-parse operation. Instead, it can reuse the cursor already present in the cursor cache.

Note:

Bind variables cannot be used in DDL statements such as CREATE TABLE, nor can they substitute the text of a query, only data.

Topics

  • 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.
  • RETURNING INTO Clause
    The use of the RETURNING INTO clause is described.
  • Batch Operations
    In addition to calling the connection.execute() function, it is possible to use connection.executeMany() to perform batch operations.

Parent topic: Calling PL/SQL and SQL from the MLE JavaScript SQL Driver

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

RETURNING INTO Clause

The use of the RETURNING INTO clause is described.
The RETURNING INTO clause allows you to
  • Fetch values changed during an update
  • Return auto-generated keys during a single-row insert operation
  • List rows deleted

Example 7-10 Using the RETURNING INTO Clause

This example shows how to retrieve the old and new values after an update operation. These values can be used for further processing.

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

const result = session.execute(
    `UPDATE
        hr.employees
    SET
        last_name = upper(last_name)
    WHERE
        employee_id between :firstEmpID and :lastEmpID
    RETURNING
        old last_name
        new last_name
    INTO
        :oldLastName,
        :newLastName`,
    {
        firstEmpID: {
            dir: oracledb.BIND_IN,
            val: firstEmpID,
            type: oracledb.NUMBER
        },
        lastEmpID: {
            dir: oracledb.BIND_IN,
            val: lastEmpID,
            type: oracledb.NUMBER
        },
        oldLastName: {
            type: oracledb.STRING,
            dir: oracledb.BIND_OUT
        },
        newLastName: {
            type: oracledb.STRING,
            dir: oracledb.BIND_OUT
        }
    }
);

if (result.rowsAffected > 1){
    console.log(
        `update() completed successfully:
        - old values: ${JSON.stringify(result.outBinds.oldLastName)}
        - new values: ${JSON.stringify(result.outBinds.newLastName)}`
    );
} else {
    throw new Error(
        `found no row to update in range ${firstEmpID} to ${lastEmpID}`
    );
}
}};
/
This example features both IN and OUT bind variables:
  • firstEmpID and lastEmpID specify the data range to be updated
  • oldLastName is an array containing all the last names as they were before the update
  • newLastName is another array containing the new values

Parent topic: Bind Variables

Batch Operations

In addition to calling the connection.execute() function, it is possible to use connection.executeMany() to perform batch operations.

Using connection.executeMany() is like calling connection.execute() multiple times but requires less work. This is an efficient way to handle batch changes, for example, when inserting or updating multiple rows. The connection.executeMany() method cannot be used for queries.

connection.execute() expects an array containing variables to process by the SQL statement. The bindData array in Example 7-11 contains multiple JavaScript objects, one for each bind variable defined in the SQL statement. The for loop constructs the objects and adds them to the bindData array.

In addition to the values to be passed to the batch operation, the MLE JavaScript SQL driver needs to know about the values' data types. This information is passed as the bindDefs property in the connection.executeMany() options parameter. Both old and new last names in Example 7-11 are character strings with the changeDate defined as a date.

Just as with the connection.execute() function, connection.executeMany() returns the rowsAffected property, allowing you to quickly identify how many rows have been batch processed.

Example 7-11 Performing a Batch Operation

This example extends Example 7-9 by inserting the old and new last names into an audit table. 

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

let result = session.execute(
    `UPDATE
        hr.employees
    SET
        last_name = upper(last_name)
    WHERE
        employee_id between :firstEmpID and :lastEmpID
    RETURNING
        old last_name,
        new last_name
    INTO
        :oldLastName,
        :newLastName`,
    {
        firstEmpID: {
            dir: oracledb.BIND_IN,
            val: firstEmpID,
            type: oracledb.NUMBER
        },
        lastEmpID: {
            dir: oracledb.BIND_IN,
            val: lastEmpID,
            type: oracledb.NUMBER
        },
        oldLastName: {
            type: oracledb.STRING,
            dir: oracledb.BIND_OUT
        };
        newLastName: {
            type: oracledb.STRING,
            dir: oracledb.BIND_OUT
        }
    }
);

if (result.rowsAffected > 1){
    // store the old data and new values in an audit table
    let bindData = [];
    const changeDate = new Date();
    for (let i = 0; i < result.outBinds.oldLastName.length, i++){
        bindDate.push(
            {
                oldLastName: result.outBinds.oldLastName[i],
                newLastName: result.outBinds.newLastName[i],
                changeDate: changeDate
            }
        );
    }
    // use executeMany() with the newly populated array
    result = session.executeMany(
        `insert into EMPLOYEES_AUDIT_OPERATIONS(
            old_last_name,
            new_last_name,
            change_date
        ) values (
            :oldLastName,
            :newLastName,
            :changeDate
        )`,
        bindData,
        {
            bindDefs: {
                oldLastName: {type: oracledb.STRING, maxSize: 30},
                newLastName: {type: oracledb.STRING, maxSize: 30},
                changeDate: {type: oracledb.DATE}
            }
        }
    );

} else {
    throw new Error(
        `found no row to update in range ${firstEmpID} to ${lastEmpID}`
    );
}
}};
/

After the initial update statement completes, the database provides the old and new values of the last_name column affected by the update in the result object's outBinds property. Both oldLastName and newLastName are arrays. The array length represents the number of rows updated.

Parent topic: Bind Variables