How to Fix ORA-00984: Column Not Allowed Here in Oracle

ORA-00984 means a column name appears where Oracle accepts only values, most often in the VALUES list of an INSERT. Text written without quotes is read as a column name, and a VALUES list cannot refer to columns. Put text in single quotes, and use INSERT ... SELECT when the values should come from other rows.

Reproduce the Error

Example:

create table demo_notes (note_id number, note varchar2(40), created date);
insert into demo_notes values (1, Hello, sysdate);
insert into demo_notes values (2, note_id, sysdate);

Output:

Table DEMO_NOTES created.

Error starting at line : 2
In command -
insert into demo_notes values (1, Hello, sysdate)
Error at Command Line : 2 Column : 35
Error report -
SQL Error: ORA-00984: column not allowed here

Error starting at line : 3
In command -
insert into demo_notes values (2, note_id, sysdate)
Error at Command Line : 3 Column : 35
Error report -
SQL Error: ORA-00984: column not allowed here

• Hello without quotes is taken as a column name.
• note_id is a real column, but a VALUES list cannot read columns.

Fix: Quote Text, Select Values from Rows

Example:

insert into demo_notes values (1, 'Hello', sysdate);
-- values from another row come through INSERT ... SELECT
insert into demo_notes (note_id, note, created)
select note_id + 1, note || ' again', created from demo_notes where note_id = 1;
select note_id, note from demo_notes order by note_id;

Output:

1 row inserted.

1 row inserted.

   NOTE_ID NOTE
__________ ______________
         1 Hello
         2 Hello again

Things to Know

• Double quotes mark names, not text: "Hello" would also be read as a column name.
• Column defaults cannot refer to other columns either; use a virtual column or a trigger instead.
• In generated SQL, a value pasted into the text without quotes causes this error; bind variables avoid it.

Related Guides

• How to Fix ORA-00904: Invalid Identifier in Oracle
• How to Create Virtual Columns in Oracle

Conclusion

ORA-00984 means a column name sits where only values are allowed. Quote text with single quotes, and copy values from other rows with INSERT ... SELECT rather than VALUES.