ORA-00984: column not allowed here error that occurs when you try to use a column that is not allowed in a particular area of the sql. The ORA 00984: column not allowed here error occurs when a column is not allowed in the VALUES clause of the insert query, the column name is used in VALUES clause or missing quotes in the insert statement. The VALUES clause should contain values that will be stored in the table. If the column name is specified in the VALUES clause, the insert statement will fail to save the values. The ORA-00984: column not allowed here error occurs if double quotes are used in character or date value. In varchar or date data type values, single quotes should be used.
In the Oracle insert statement, the character string or date value should be enclosed by single quotes. If single quotes are not used in the values or double quotes are used, the insert statement will fail to recognize the string or date value. If the quotations in the insert statement VALUES clause are missing, it will be treated as a column name. The insert statement was unable to store the values in the table. The error ORA-00984: column not allowed here will be thrown.
When this ORA-00984 error occurs
The error ORA-00984: column not allowed here will be thrown if the single quotation in the string value or date value is missing in the VALUES clause of the insert statement. If the double quotation mark is used in the insert statement for a string or date value, the error ORA-00984: column not allowed here will occur.
insert into emp values (1, name);
Error starting at line : 2 in command -
insert into emp values (1, name)
Error at Command Line : 2 Column : 28
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"Root Cause
In oracle, a string value or a date value is created by enclosed with a single quotation. If a single quotation is missed in a string value and a date value or enclosed with double quotation, oracle will not recognise the string and date. It will be interpreted as a column name. Because a column name is not permitted in the insert statement’s VALUES clause, Oracle will thrown this error
Solution 1
If the VALUES clause of the insert statement has a column name, delete it and replace it with a value. The insert statement was unable to save a column name. If you pass the value of a column in the insert statement, the error ORA-00984: column not allowed here will be resolved.
Problem
CREATE TABLE EMP(
id int,
name VARCHAR2(100)
)
insert into emp values (1, name);
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"Solution
insert into emp values (1, 'kim');
1 row inserted.Solution 2
If a single quote is missing in the insert statement’s VALUES clause for a string or date value, the insert statement will fail to recognize the string or date value. The insert statement will fail to store the value in the table. The ORA-00984: column not allowed here will be thrown. The error will be fixed if a single quote mark is placed around a string or date value.
Problem
insert into emp values (1, kim);
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"Solution
insert into emp values (1, 'kim');
1 row inserted.Solution 3
In Oracle, the string value or date value should be surrounded by a single quotation. If the string value or date value is surrounded by double quotation marks, Oracle will not recognize it as a string value or date value. The error message ORA-00984: column not allowed here will be shown. The issue will be fixed if the double quotation in the string value or date value is replaced with a single quotation.
Problem
insert into emp values (1, "kim");
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"Solution
insert into emp values (1, 'kim');
1 row inserted.Solution 4
The date column in the insert statement should be surrounded by a single quotation. The below example shows with a date example.
Problem
insert into emp values (1, 'kim',2001-01-01 13:15:41);
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"Solution
insert into emp values (1, 'kim','2001-01-01 13:15:41');
1 row inserted.