DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00984 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00984: Column Not Allowed Here — Causes, Fixes & Prevention

ORA-00984 is an Oracle SQL error that occurs when a column name is used in a position where Oracle does not permit column references. This most commonly happens in the VALUES clause of an INSERT statement, in DEFAULT definitions within DDL statements, or in other contexts where only literals, bind variables, or SQL functions are syntactically valid. Understanding the exact context where column names are forbidden is the fastest way to resolve this error.

Top 3 Causes and Fixes

Cause 1: Column Name Used in INSERT VALUES Clause

The most frequent cause. Developers mistakenly place a column name directly inside the VALUES clause instead of providing an actual value.

Wrong:

-- This causes ORA-00984 because 'salary' is a column name, not a value
INSERT INTO salary_log (emp_id, logged_salary, log_date)
VALUES (emp_id, salary, SYSDATE);
Enter fullscreen mode Exit fullscreen mode

Fixed — Use a literal or bind variable:

INSERT INTO salary_log (emp_id, logged_salary, log_date)
VALUES (1001, 5000000, SYSDATE);
Enter fullscreen mode Exit fullscreen mode

Fixed — Use INSERT INTO ... SELECT instead:

INSERT INTO salary_log (emp_id, logged_salary, log_date)
SELECT emp_id, salary, SYSDATE
FROM employees
WHERE department_id = 10;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Referencing a Column in a DEFAULT Clause (DDL)

Oracle's DEFAULT clause only accepts constant values, built-in SQL functions (SYSDATE, USER, SYSTIMESTAMP), or sequences (Oracle 12c+). Referencing another column is not allowed.

Wrong:

CREATE TABLE order_header (
  order_id       NUMBER,
  order_date     DATE DEFAULT SYSDATE,
  expected_date  DATE DEFAULT order_date  -- ORA-00984!
);
Enter fullscreen mode Exit fullscreen mode

Fixed — Use an allowed DEFAULT expression:

CREATE TABLE order_header (
  order_id       NUMBER,
  order_date     DATE    DEFAULT SYSDATE,
  expected_date  DATE    DEFAULT SYSDATE,
  created_by     VARCHAR2(50) DEFAULT USER,
  status         VARCHAR2(20) DEFAULT 'NEW'
);
Enter fullscreen mode Exit fullscreen mode

Fixed — Use a BEFORE INSERT trigger for column-dependent defaults:

CREATE OR REPLACE TRIGGER trg_order_header_bi
BEFORE INSERT ON order_header
FOR EACH ROW
BEGIN
  IF :NEW.expected_date IS NULL THEN
    :NEW.expected_date := :NEW.order_date + 7;  -- 7 days after order_date
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Cause 3: Dynamic SQL String Concatenation Error

When building dynamic SQL, developers sometimes accidentally concatenate a column name directly into the VALUES clause instead of wrapping it as a string literal or using bind variables.

Wrong:

DECLARE
  v_col  VARCHAR2(30) := 'DEPARTMENT_ID';
  v_sql  VARCHAR2(500);
BEGIN
  -- Results in: VALUES (DEPARTMENT_ID, 99) => ORA-00984
  v_sql := 'INSERT INTO audit_log (col_name, col_value) VALUES ('
            || v_col || ', 99)';
  EXECUTE IMMEDIATE v_sql;
END;
/
Enter fullscreen mode Exit fullscreen mode

Fixed — Use bind variables in dynamic SQL:

DECLARE
  v_col  VARCHAR2(30) := 'DEPARTMENT_ID';
  v_sql  VARCHAR2(500);
BEGIN
  v_sql := 'INSERT INTO audit_log (col_name, col_value) VALUES (:1, :2)';
  EXECUTE IMMEDIATE v_sql USING v_col, 99;
  COMMIT;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • ✅ In INSERT ... VALUES(...), use only literals, bind variables (:var), or SQL functions (SYSDATE, SYS_GUID(), etc.)
  • ✅ To copy data from another table, always use INSERT INTO ... SELECT
  • ✅ In DEFAULT clauses, only use constants, built-in functions, or sequences (12c+)
  • ✅ For column-to-column default logic, use a BEFORE INSERT trigger
  • ✅ In dynamic SQL, always prefer bind variables over string concatenation

Prevention Tips

1. Enforce INSERT coding standards in code reviews.
Establish a team rule: if the source of data is another table or a PL/SQL variable, always use INSERT INTO ... SELECT or bind variables — never put identifiers directly in the VALUES clause. Include this check in your code review checklist and leverage SQL syntax validation in tools like SQL Developer or Toad before execution.

2. Document DDL constraints and use triggers for complex defaults.
Keep a clear internal reference documenting what Oracle allows inside DEFAULT clauses. For any business logic that requires one column's default to depend on another column's value, standardize on using BEFORE INSERT triggers or Oracle virtual columns (11g+). This prevents developers from attempting unsupported syntax and keeps default logic maintainable.

Related Oracle Errors

Error Code Description
ORA-00904 invalid identifier — column name doesn't exist or is misspelled
ORA-00936 missing expression — required expression is absent in a clause
ORA-01747 invalid column specification — malformed column reference

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