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.