ORA-01036: Illegal Variable Name - Fix Bind Variables

ORA-01036: Illegal Variable Name/Number

Error Overview

Error Text: ORA-01036: illegal variable name/number

The ORA-01036 error is raised when Oracle cannot match a bind variable placeholder in a SQL or PL/SQL statement to the variable supplied at execution time. This typically means the placeholder name or count is wrong, the USING clause is missing or has too few/many arguments, or the bind variable syntax is incorrect for the API being used (JDBC, OCI, DBMS_SQL, or EXECUTE IMMEDIATE).

Common Causes

1. Bind Variable Count Mismatch in USING Clause

• The number of :n placeholders in the SQL string does not match the number of variables in the USING clause
• Duplicate placeholder names counted once by the parser but the application passes multiple values

2. Incorrect Bind Variable Syntax for the Interface

• JDBC uses ? as a positional placeholder; sending a named :name placeholder or vice versa
• OCI applications using 0 or negative numbers as bind variable indices

3. Named Bind Variable Typo

• Placeholder :empid in SQL but application binds :emp_id (underscore mismatch)
• Case-sensitive bind variable names in some OCI/third-party drivers

4. Extra Colon in the SQL String

• Accidentally including a colon before a non-bind word: :FROM, :WHERE
• Dynamic SQL built by string concatenation where a colon is embedded in a literal

5. Bind Variable in a Context That Does Not Support Bind Variables

• Using bind variables in DDL statements passed to EXECUTE IMMEDIATE
• Bind variables in table or column name positions (not supported)

Diagnostic Queries

Check the Offending SQL Text in Shared Pool

-- Find the SQL statement associated with the error
SELECT
  sql_id,
  sql_text,
  parse_calls,
  executions,
  bind_data
FROM v$sql
WHERE sql_id = '&sql_id';

View Bind Variable Details for a Specific Statement

-- Inspect bind variable metadata for a SQL_ID
SELECT
  sql_id,
  name,
  position,
  datatype_string,
  last_captured,
  value_string
FROM v$sql_bind_capture
WHERE sql_id = '&sql_id'
ORDER BY position;

Audit Trail for ORA-01036 Failures

SELECT
  event_timestamp,
  db_user_name,
  sql_text,
  return_code
FROM unified_audit_trail
WHERE return_code = 1036
  AND event_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY event_timestamp DESC;

Find Sessions Using Dynamic SQL with Bind Variables

-- Active sessions currently executing EXECUTE IMMEDIATE
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.module,
  q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
  AND UPPER(q.sql_text) LIKE '%EXECUTE%IMMEDIATE%'
ORDER BY s.last_call_et DESC;

Step-by-Step Resolution

1. Fix Bind Variable Count Mismatch in EXECUTE IMMEDIATE

-- WRONG: Two placeholders but only one variable in USING
-- DECLARE
--   v_sql VARCHAR2(200) := 'SELECT * FROM employees WHERE department_id = :1 AND salary > :2';
-- BEGIN
--   EXECUTE IMMEDIATE v_sql USING 50;  -- Missing second bind value
-- END;

-- CORRECT: Match USING arguments to placeholder count
DECLARE
  v_sql    VARCHAR2(200) := 'SELECT * FROM employees WHERE department_id = :1 AND salary > :2';
  v_dept   NUMBER := 50;
  v_sal    NUMBER := 40000;
  v_cnt    NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees WHERE department_id = :1 AND salary > :2'
    INTO v_cnt
    USING v_dept, v_sal;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_cnt);
END;
/

2. Fix Named vs Positional Bind Variable Confusion

-- Oracle native: named bind variables (recommended)
DECLARE
  v_sql VARCHAR2(300) :=
    'UPDATE employees SET salary = :new_sal WHERE employee_id = :emp_id';
BEGIN
  EXECUTE IMMEDIATE v_sql USING 60000, 101;  -- positional order matches left-to-right
END;
/

-- Also valid: use the same named placeholder twice
-- Oracle counts distinct names, not occurrences
DECLARE
  v_sql VARCHAR2(300) :=
    'SELECT * FROM orders WHERE created_by = :user_id OR modified_by = :user_id';
  TYPE t_cur IS REF CURSOR;
  c     t_cur;
BEGIN
  -- :user_id appears twice but only ONE value needed
  OPEN c FOR v_sql USING 42;
  CLOSE c;
END;
/

3. Fix JDBC Positional Placeholder (?) Issues

When calling from Java via JDBC, use ? placeholders only:

// WRONG: Named Oracle bind variables in JDBC PreparedStatement
// String sql = "SELECT * FROM employees WHERE department_id = :dept_id";
// PreparedStatement ps = conn.prepareStatement(sql);
// ps.setInt(1, 50);  // ORA-01036 — JDBC cannot resolve :dept_id

// CORRECT: Use ? in JDBC
String sql = "SELECT * FROM employees WHERE department_id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, 50);
ResultSet rs = ps.executeQuery();

4. Fix Accidental Colon in Dynamic SQL String

-- WRONG: Colon embedded in a keyword becomes a bind placeholder
-- DECLARE
--   v_sql VARCHAR2(200) := 'SELECT :col FROM employees';  -- :col is a bind, not a column reference
-- BEGIN
--   EXECUTE IMMEDIATE v_sql USING 'SALARY';  -- Column names cannot be bound
-- END;

-- CORRECT: Column names must be in the SQL string itself (use concatenation carefully)
DECLARE
  v_col VARCHAR2(30) := 'SALARY';
  v_sql VARCHAR2(200);
  v_val NUMBER;
BEGIN
  -- Validate column name against data dictionary first
  SELECT COUNT(*) INTO v_val
  FROM user_tab_columns
  WHERE table_name = 'EMPLOYEES'
    AND column_name = UPPER(v_col);

  IF v_val = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid column: ' || v_col);
  END IF;

  v_sql := 'SELECT ' || DBMS_ASSERT.SIMPLE_SQL_NAME(v_col) ||
           ' FROM employees WHERE employee_id = :1';
  EXECUTE IMMEDIATE v_sql INTO v_val USING 101;
  DBMS_OUTPUT.PUT_LINE(v_col || ' = ' || v_val);
END;
/

5. Fix DBMS_SQL Bind Variable Mismatch

-- When using the DBMS_SQL package, bind by exact name
DECLARE
  v_cursor  INTEGER;
  v_sql     VARCHAR2(300) :=
    'SELECT employee_id, salary FROM employees WHERE department_id = :dept_id AND salary > :min_sal';
  v_emp_id  NUMBER;
  v_salary  NUMBER;
  v_result  INTEGER;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

  -- Bind by NAME — must match placeholder exactly (case-insensitive)
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id',  50);
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':min_sal',  40000);

  DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_emp_id);
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, v_salary);

  v_result := DBMS_SQL.EXECUTE(v_cursor);

  LOOP
    EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;
    DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_emp_id);
    DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_salary);
    DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_salary);
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END IF;
    RAISE;
END;
/

6. Fix Bind Variables in DDL (Not Supported)

-- WRONG: Bind variables are not permitted in DDL
-- EXECUTE IMMEDIATE 'CREATE TABLE :tname (id NUMBER)' USING 'my_table';

-- CORRECT: Use string concatenation with proper sanitization for DDL object names
DECLARE
  v_table_name VARCHAR2(30) := 'MY_TABLE';
BEGIN
  -- Always validate/sanitize dynamic object names
  EXECUTE IMMEDIATE
    'CREATE TABLE ' || DBMS_ASSERT.SQL_OBJECT_NAME(v_table_name) ||
    ' (id NUMBER GENERATED ALWAYS AS IDENTITY, name VARCHAR2(100))';
END;
/

Prevention Strategies

1. Use Named Bind Variables Consistently

-- Named bind variables are self-documenting and reusable
DECLARE
  v_sql VARCHAR2(500) :=
    'INSERT INTO audit_log (user_id, action, log_time) ' ||
    'VALUES (:user_id, :action, :log_time)';
BEGIN
  EXECUTE IMMEDIATE v_sql
    USING 42, 'LOGIN', SYSTIMESTAMP;
END;
/

2. Validate Dynamic SQL Bind Count Before Execution

-- Count bind variable placeholders in a SQL string
CREATE OR REPLACE FUNCTION count_bind_vars (p_sql IN VARCHAR2) RETURN NUMBER AS
  v_count NUMBER := 0;
  v_pos   NUMBER := 1;
BEGIN
  WHILE v_pos <= LENGTH(p_sql) LOOP
    IF SUBSTR(p_sql, v_pos, 1) = ':' THEN
      v_count := v_count + 1;
      -- Skip the variable name
      v_pos := v_pos + 1;
      WHILE v_pos <= LENGTH(p_sql)
        AND REGEXP_LIKE(SUBSTR(p_sql, v_pos, 1), '[A-Za-z0-9_]')
      LOOP
        v_pos := v_pos + 1;
      END LOOP;
    ELSE
      v_pos := v_pos + 1;
    END IF;
  END LOOP;
  RETURN v_count;
END;
/

3. Use Bind Variable Peeking Reports for Performance

-- Review bound values captured for key SQL statements
SELECT
  s.sql_text,
  b.name,
  b.position,
  b.datatype_string,
  b.value_string,
  b.last_captured
FROM v$sql s
JOIN v$sql_bind_capture b ON s.sql_id = b.sql_id
WHERE s.executions > 100
  AND b.last_captured > SYSDATE - 1
ORDER BY s.sql_id, b.position;

4. Logging Pattern for Dynamic SQL Debugging

-- Always log the SQL and its bind values before EXECUTE IMMEDIATE
PROCEDURE safe_execute (p_sql IN VARCHAR2, p_bind1 IN NUMBER, p_bind2 IN VARCHAR2) AS
BEGIN
  -- Log for debugging
  INSERT INTO dynamic_sql_log (sql_text, bind1, bind2, run_time)
  VALUES (p_sql, p_bind1, p_bind2, SYSTIMESTAMP);
  COMMIT;

  -- Execute
  EXECUTE IMMEDIATE p_sql USING p_bind1, p_bind2;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('SQL: ' || p_sql);
    DBMS_OUTPUT.PUT_LINE('Binds: ' || p_bind1 || ', ' || p_bind2);
    RAISE;
END;

Related Errors

• ORA-01008 - Not all variables bound
• ORA-00900 - Invalid SQL statement
• ORA-06512 - At line (PL/SQL error stack)
• ORA-01722 - Invalid number (common with bind type mismatch)

Emergency Response

Quick Fixes

1. Print the exact SQL string before execution

   DBMS_OUTPUT.PUT_LINE('SQL: ' || SUBSTR(v_sql, 1, 4000));
   -- Count colons manually to verify bind variable count

2. Simplify to hard-coded values first

   -- Replace bind variables with literals to confirm the statement is otherwise valid
   -- Then re-introduce binds one at a time
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees WHERE department_id = 50'
     INTO v_cnt;

3. Check USING clause argument count matches placeholders

   -- :1 :2 :3 -> three arguments in USING
   EXECUTE IMMEDIATE 'UPDATE t SET a=:1, b=:2 WHERE id=:3'
     USING v_a, v_b, v_id;  -- exactly 3 arguments

Post-Resolution Cleanup

-- Confirm statement executes cleanly with fixed binds
DECLARE
  v_cnt NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM employees WHERE department_id = :1 AND salary > :2'
    INTO v_cnt
    USING 50, 40000;
  DBMS_OUTPUT.PUT_LINE('Fixed. Count = ' || v_cnt);
END;
/

-- Flush shared pool to remove any cached invalid cursor plans
ALTER SYSTEM FLUSH SHARED_POOL;