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.I haven’t used Oracle for a while so I’m a bit rusty.
This is my table:
create table calendar(
username VARCHAR2(12),
content VARCHAR2(100),
dateContent DATE,
type CHAR(3) CHECK (type IN ('PUB', 'PRV')));
But when I try to insert a value like this:
insert into calendar
(username, content, dateContent, type)
values
(chris, assignment due, to_date('01-OCT-2010 13:00','DD-MON-YYYY HH24:MI'), PUB)
/
I am getting:
ORA-00984: column not allowed here
pointing to the type column at the end. I have a feeling I’m not getting something right with the DATE field as I’ve never really used it.
What have I done wrong?
ORA-00984
ORA-00984: колонка не позволительна здесь
Причина:
Имя колонки было использовано в выражении, которое не разрешено, такое как предложение VALUES INSERT оператора.
Действие:
Проверьте синтаксис оператора и используйте имена колонок только там где это требуется.
Learn the cause and how to resolve the ORA-00984 error message in Oracle.
Description
When you encounter an ORA-00984 error, the following error message will appear:
- ORA-00984: column not allowed here
Cause
You tried to execute a SQL statement that included a column name where it was not permitted.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
This error most commonly occurs when you try to include a column name in the VALUES clause of a INSERT statement.
For example, if you had tried to use the column named customers in an INSERT statement as follows:
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1, customer_name);
You would receive the following error message:
You could correct the INSERT statement by including a character value, instead of the column name as follows:
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1, 'IBM');
Or if you needed to include a column name, you could rewrite the INSERT statement with a sub-select as follows:
INSERT INTO supplier (supplier_id, supplier_name) SELECT account_no, customer_name FROM customers WHERE city = 'Newark';
ORA-00984: column not allowed here
Oracle PL/SQL error message: ORA-00984: column not allowed here.
Cause:
A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement.
Solution:
Check the syntax of the statement and use column names only where appropriate.
Example:
CREATE TABLE TEST3( ID NUMBER NOT NULL, NAME VARCHAR2(250) ); INSERT INTO TEST3(ID,NAME) VALUES (5,"123");
Output:
ORA-00984: column not allowed here
Correct:
INSERT INTO TEST3(ID,NAME) VALUES (5,'123');
Output:
1 rows inserted.