DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01006 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01006: Bind Variable Does Not Exist — Causes, Fixes & Prevention

ORA-01006 is a runtime Oracle error thrown when a bind variable referenced in a SQL statement cannot be found or has not been properly registered in the execution context. This error typically surfaces in dynamic SQL environments, application-tier database drivers (JDBC, OCI, cx_Oracle), and PL/SQL blocks that use EXECUTE IMMEDIATE or DBMS_SQL. Because it occurs at runtime rather than parse time, it can catch developers off guard in production systems.

Top 3 Causes

1. Mismatch Between Declared and Supplied Bind Variables

The most common cause: the number of bind variable placeholders in the SQL string does not match the number of values supplied during binding.

Broken example:

DECLARE
  v_sql    VARCHAR2(300);
  v_result NUMBER;
BEGIN
  -- Three bind variables declared: :1, :2, :3
  v_sql := 'SELECT COUNT(*) FROM orders WHERE cust_id = :1 AND status = :2 AND region = :3';
  -- Only two values supplied in USING clause -> ORA-01006
  EXECUTE IMMEDIATE v_sql INTO v_result USING 1001, 'ACTIVE';
END;
/
Enter fullscreen mode Exit fullscreen mode

Fixed example:

DECLARE
  v_sql    VARCHAR2(300);
  v_result NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM orders WHERE cust_id = :1 AND status = :2 AND region = :3';
  -- All three bind variables supplied
  EXECUTE IMMEDIATE v_sql INTO v_result USING 1001, 'ACTIVE', 'WEST';
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_result);
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Missing BIND_VARIABLE Call in DBMS_SQL

When using the DBMS_SQL package, every bind variable placeholder in the parsed SQL must have a corresponding DBMS_SQL.BIND_VARIABLE call before execution.

Broken example:

DECLARE
  v_cur INTEGER;
  v_sql VARCHAR2(300);
  v_rows INTEGER;
BEGIN
  v_sql := 'UPDATE employees SET salary = :sal, dept_id = :dept WHERE emp_id = :eid';
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, v_sql, DBMS_SQL.NATIVE);
  -- Only binding :sal, skipping :dept and :eid -> ORA-01006
  DBMS_SQL.BIND_VARIABLE(v_cur, ':sal', 80000);
  v_rows := DBMS_SQL.EXECUTE(v_cur);  -- Error here
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/
Enter fullscreen mode Exit fullscreen mode

Fixed example:

DECLARE
  v_cur  INTEGER;
  v_sql  VARCHAR2(300);
  v_rows INTEGER;
BEGIN
  v_sql := 'UPDATE employees SET salary = :sal, dept_id = :dept WHERE emp_id = :eid';
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, v_sql, DBMS_SQL.NATIVE);
  -- Bind ALL variables declared in the SQL string
  DBMS_SQL.BIND_VARIABLE(v_cur, ':sal',  80000);
  DBMS_SQL.BIND_VARIABLE(v_cur, ':dept', 20);
  DBMS_SQL.BIND_VARIABLE(v_cur, ':eid',  105);
  v_rows := DBMS_SQL.EXECUTE(v_cur);
  DBMS_OUTPUT.PUT_LINE('Rows updated: ' || v_rows);
  DBMS_SQL.CLOSE_CURSOR(v_cur);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cur) THEN
      DBMS_SQL.CLOSE_CURSOR(v_cur);
    END IF;
    RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Conditional Dynamic SQL Without Matching Bind Logic

When the SQL string is built conditionally, the bind variable list must mirror exactly what ends up in the final SQL string.

Broken example:

DECLARE
  v_sql    VARCHAR2(500);
  v_dept   NUMBER := 10;
  v_job    VARCHAR2(20) := 'SA_REP';
  v_ref    SYS_REFCURSOR;
BEGIN
  v_sql := 'SELECT employee_id FROM employees WHERE department_id = :dept';
  IF v_job IS NOT NULL THEN
    v_sql := v_sql || ' AND job_id = :job';
  END IF;
  -- Always binding only one variable even when :job was added -> ORA-01006
  OPEN v_ref FOR v_sql USING v_dept;
END;
/
Enter fullscreen mode Exit fullscreen mode

Fixed example:

DECLARE
  v_sql  VARCHAR2(500);
  v_dept NUMBER := 10;
  v_job  VARCHAR2(20) := 'SA_REP';
  v_ref  SYS_REFCURSOR;
BEGIN
  v_sql := 'SELECT employee_id FROM employees WHERE department_id = :dept';
  IF v_job IS NOT NULL THEN
    v_sql := v_sql || ' AND job_id = :job';
    OPEN v_ref FOR v_sql USING v_dept, v_job;  -- 2 binds
  ELSE
    OPEN v_ref FOR v_sql USING v_dept;          -- 1 bind
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • Count :variable placeholders in your SQL string and ensure an equal number of values are passed in the USING clause or via BIND_VARIABLE.
  • After editing a SQL string in a stored procedure, always audit the corresponding binding section.
  • Use Named bind variables (:dept_id, :status) instead of positional ones (:1, :2) for clarity.
  • Wrap dynamic SQL logic in helper procedures that validate bind variable counts before execution.

Prevention Tips

  1. Standardize dynamic SQL with a utility wrapper. Build a lightweight PL/SQL wrapper that accepts the SQL string and a bind variable collection, validates counts, and raises a meaningful application error before attempting execution. This turns a cryptic ORA-01006 into a clear diagnostic message.

  2. Enforce testing for all conditional SQL branches. Every optional WHERE clause or conditional SQL fragment must have its own test case — including cases where optional parameters are NULL. Use Oracle's DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in exception handlers during development to get exact line numbers when ORA-01006 does slip through.

Related Errors

  • ORA-01008 – Not all variables bound: similar to ORA-01006 but fires when variables are declared yet no value is provided.
  • ORA-01745 – Invalid host/bind variable name: triggered when the bind variable name violates Oracle naming rules.
  • ORA-06512 – Appears alongside ORA-01006 in the error stack, pointing to the exact PL/SQL line of failure.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)