Oracle SQL: Bind variables

Bind variables cannot be used in DDL statements (ORA-01027: bind variables not allowed for data definition operations)

The init parameters

• cursor_sharing
• cursor_bind_capture_destination

The SQL*Plus statement variable declares a bind variable.

explain plan assumes bind variables to be a varchar2 data type, see this caveat.

Adaptive cursor sharing allows to create multiple execution plans for a statement that has bind variables (where these execution plans adapt to the values of the bind variables at execution time).

Bind variable values are stored in the private SQL area

Error messages such as

• ORA-01008: not all variables bound
• ORA-43907: This argument must be a literal or bind variable.
• SP2-0552: Bind variable … not declared.

Bind variable in SQL*Plus

dbms_sql.bind_variable

dba_hist_sqlbind, dba_hist_sql_bind_metadata, dba_sqlset_binds, dba_sqltune_binds

v$sql_bind_data, v$sql_bind_metadata, v$sql_bind_capture

PGA

The persistent area of the private SQL area.

x$kksbv, x$kqlfbc, x$kxsbd

Jonathan Lewis notes that histograms and bind variables xist for diametrically opposed reasons.