DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01036 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01036: Illegal Variable Name/Number — What It Means and How to Fix It

ORA-01036 is thrown by Oracle Database when a bind variable used in a SQL statement has an invalid name, an incorrect number of parameters, or a mismatch between the declared bind variables and those supplied by the application. This error is common in applications built with JDBC, cx_Oracle, ODP.NET, or Pro*C, and it often appears during dynamic SQL execution. Understanding the root cause quickly can save hours of debugging time.

Top 3 Causes and SQL Examples

Cause 1: Bind Variable Name Mismatch

The most frequent cause is a discrepancy between the bind variable name in the SQL string and the name used when binding values in the application code.

Incorrect:

-- SQL uses :emp_id but code binds :empid
String sql = "SELECT last_name FROM employees WHERE employee_id = :emp_id";
pstmt.setIntAtName("empid", 100);  -- ERROR: name does not match
Enter fullscreen mode Exit fullscreen mode

Correct:

String sql = "SELECT last_name FROM employees WHERE employee_id = :emp_id";
pstmt.setIntAtName("emp_id", 100);  -- Matches exactly
pstmt.execute();
Enter fullscreen mode Exit fullscreen mode

PL/SQL correct usage:

DECLARE
  v_name employees.last_name%TYPE;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT last_name FROM employees WHERE employee_id = :emp_id'
  INTO v_name
  USING 100;
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
Enter fullscreen mode Exit fullscreen mode

Cause 2: Parameter Count or Order Mismatch

When using positional binding or dynamic SQL, the number of bind variables in the query must exactly match the number of parameters supplied.

Incorrect (missing one parameter):

DECLARE
  v_sql VARCHAR2(500);
  v_out VARCHAR2(100);
BEGIN
  v_sql := 'SELECT last_name FROM employees
             WHERE department_id = :dept_id
               AND job_id = :job_id';

  -- ERROR: only one value supplied for two bind variables
  EXECUTE IMMEDIATE v_sql INTO v_out USING 90;
END;
/
Enter fullscreen mode Exit fullscreen mode

Correct:

DECLARE
  v_sql VARCHAR2(500);
  v_out VARCHAR2(100);
BEGIN
  v_sql := 'SELECT last_name FROM employees
             WHERE department_id = :dept_id
               AND job_id = :job_id';

  -- Supply values in the same order as bind variables appear
  EXECUTE IMMEDIATE v_sql INTO v_out USING 90, 'AD_VP';
  DBMS_OUTPUT.PUT_LINE(v_out);
END;
/
Enter fullscreen mode Exit fullscreen mode

Cause 3: Invalid Characters in Bind Variable Name

Bind variable names must follow Oracle identifier rules — only letters, digits, and underscores are allowed, and the name must start with a letter. Using hyphens, spaces, or other special characters will trigger ORA-01036.

Incorrect:

DECLARE
  v_sql VARCHAR2(500);
  v_cnt NUMBER;
BEGIN
  -- ERROR: hyphen not allowed in bind variable name
  v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept-id';

  -- ERROR: space between colon and variable name
  v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = : dept_id';

  EXECUTE IMMEDIATE v_sql INTO v_cnt USING 90;
END;
/
Enter fullscreen mode Exit fullscreen mode

Correct:

DECLARE
  v_sql VARCHAR2(500);
  v_cnt NUMBER;
BEGIN
  -- Only letters, digits, and underscores — no spaces after colon
  v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id';
  EXECUTE IMMEDIATE v_sql INTO v_cnt USING 90;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_cnt);
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • Match names exactly — compare the bind variable names in the SQL string with those in your binding code character by character.
  • Count your parameters — ensure the number of values in USING or setXxxAtName() calls equals the number of distinct bind variables in the query.
  • Reused bind variables — if the same bind variable name appears multiple times in EXECUTE IMMEDIATE, supply the value once per occurrence in the USING clause.
  • Check for whitespace — a stray space like : emp_id instead of :emp_id will cause this error immediately.
  • Validate dynamically built SQL — log or print the final SQL string before execution to catch concatenation errors early. 
-- Debugging tip: print the dynamic SQL before executing
DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id';
  DBMS_OUTPUT.PUT_LINE('SQL: ' || v_sql);  -- Inspect before running
  -- EXECUTE IMMEDIATE v_sql INTO ... USING ...;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Standardize bind variable naming conventions across your team (e.g., always use :p_column_name) and enforce them through code reviews. Consistent naming eliminates the majority of ORA-01036 occurrences before code reaches production.

  2. Use a mature ORM or query builder (MyBatis, Hibernate, SQLAlchemy) that handles bind variable management automatically. If you must use raw drivers, pin the driver version across the team and thoroughly test dynamic SQL generation logic with unit tests that verify both the SQL string and the parameter count simultaneously.

Related Errors

Error Code Description
ORA-01008 Not all variables bound — some bind variables have no value supplied
ORA-00904 Invalid identifier — column or object name is incorrect
ORA-06512 PL/SQL stack trace — often appears alongside ORA-01036 to indicate the line number

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