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 againThings 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
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.
