DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01008 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01008: Not All Variables Bound — Causes, Fixes, and Prevention

ORA-01008 is thrown by Oracle when the number of bind variables declared in a SQL statement does not match the number of variables actually bound before execution. Simply put, you've used placeholders like :1, :2, or :name in your SQL, but failed to supply a value for each one. This error commonly appears in PL/SQL dynamic SQL, JDBC applications, Python (cx_Oracle/oracledb), and OCI programs.

Top 3 Causes

1. Mismatched Number of Bind Variables

The most common cause: your SQL has three placeholders but your code only binds two values.

-- WRONG: Three bind variables in SQL, only two bound in USING clause
DECLARE
  v_id   NUMBER := 101;
  v_name VARCHAR2(50) := 'Alice';
BEGIN
  -- :3 (dept_id) is never bound → ORA-01008
  EXECUTE IMMEDIATE
    'INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (:1, :2, :3)'
    USING v_id, v_name;  -- Missing third bind value!
END;
/

-- CORRECT: All three placeholders are bound
DECLARE
  v_id     NUMBER := 101;
  v_name   VARCHAR2(50) := 'Alice';
  v_dept   NUMBER := 10;
BEGIN
  EXECUTE IMMEDIATE
    'INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (:1, :2, :3)'
    USING v_id, v_name, v_dept;  -- All three bound correctly
  COMMIT;
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Repeated Bind Variable Names Across Different APIs

In Oracle PL/SQL, a named bind variable used multiple times in a statement only needs to be bound once. However, in JDBC (positional binding) or some other drivers, each occurrence must be bound separately. Misunderstanding this behavior is a frequent source of ORA-01008.

-- PL/SQL: Same named variable used twice — bind ONCE (Oracle handles duplication)
DECLARE
  v_dept  NUMBER := 20;
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM employees
      WHERE dept_id = :dept OR manager_dept = :dept'
    INTO v_count
    USING v_dept;  -- Only one USING entry needed in PL/SQL
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

-- JDBC positional binding (Java pseudo-SQL comment):
-- String sql = "SELECT COUNT(*) FROM employees WHERE dept_id = ? OR manager_dept = ?";
-- pstmt.setInt(1, 20);  // First occurrence
-- pstmt.setInt(2, 20);  // Second occurrence — must be bound separately!
SELECT 'Always bind each positional ? separately in JDBC' AS reminder FROM dual;
Enter fullscreen mode Exit fullscreen mode

3. Dynamic SQL Construction Errors

When SQL is built conditionally at runtime, it's easy to add a bind variable to the SQL string but forget to include the corresponding value in the binding call — or vice versa.

-- Dangerous pattern: SQL and bind values built independently
DECLARE
  v_sql      VARCHAR2(1000);
  v_dept     NUMBER := 10;
  v_salary   NUMBER := NULL;  -- Not used this time
  v_count    NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM employees WHERE dept_id = :1';

  -- BUG: v_salary condition added to SQL but binding not updated
  -- v_sql := v_sql || ' AND salary > :2';  -- If uncommented without adding USING value → ORA-01008

  EXECUTE IMMEDIATE v_sql INTO v_count USING v_dept;
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_count);
END;
/

-- Safe pattern: Always synchronize SQL construction with bind value list
DECLARE
  v_sql       VARCHAR2(1000) := 'SELECT COUNT(*) FROM employees WHERE 1=1';
  v_dept      NUMBER := 10;
  v_salary    NUMBER := 5000;
  v_count     NUMBER;
BEGIN
  -- Build SQL and bind values together
  v_sql := v_sql || ' AND dept_id = :1 AND salary >= :2';

  EXECUTE IMMEDIATE v_sql INTO v_count USING v_dept, v_salary;
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_count);
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Count your placeholders: Count every :1, :2, :name token in your SQL string and ensure your USING clause or binding calls match exactly.
  2. Log the SQL before execution: Print or log the full SQL string in development so you can visually inspect variable counts when errors occur.
  3. Use V$SQL_BIND_CAPTURE: Query this view to inspect bind variable metadata for recently executed statements on the database side. 
-- Check bind variable info for a specific SQL statement
SELECT sql_id, name, position, datatype_string, value_string
FROM   v$sql_bind_capture
WHERE  sql_id = 'your_sql_id_here'
ORDER  BY position;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Code review checklist: Always review the SQL string and its binding code together. Make it a team rule that the number of bind tokens must visually match the number of bound values in every pull request.
  • Unit test all parameter combinations: Test dynamic SQL procedures with every meaningful combination of inputs, including NULLs and edge cases, to catch binding mismatches before they reach production.

Related Errors

  • ORA-01006 — Bind variable does not exist: You referenced a variable name in the bind call that doesn't appear in the SQL.
  • ORA-01036 — Illegal variable name/number: The bind variable name violates Oracle naming rules.
  • ORA-00904 — Invalid identifier: Often confused with bind variable errors when column names or aliases are mistyped.

📖 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)