Column not allowed here oracle ошибка

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 am trying to create a trigger that will update an employee audit table when a row is changed. Using a sequence number to assign a unique identifier to each row as it is created. Need to capture the user ID, date of the change, and the action (update), plus the before image of the row.

CREATE SEQUENCE emp_audit_seq START WITH 10; 		     

Create table emp (
   empno       NUMBER(4)	 Primary Key,
   ename       VARCHAR2(10),
   job            VARCHAR2(9),
   mgr           NUMBER(4),
   hiredate     DATE,
   sal             NUMBER(7,2),
   comm        NUMBER(7,2),
   deptno       NUMBER(2));

CREATE TABLE emp_audit   (
     audit_uid		NUMBER(15) 	Primary Key,
     change_date		DATE,
     change_user		VARCHAR2(30),
     action		        CHAR(1),
     empno		        NUMBER(4),
     ename                  VARCHAR2(10),		
     job			VARCHAR2(9),
     mgr			NUMBER(4),
     hiredate		DATE,
     sal			NUMBER(7,2),
     comm		        NUMBER(7,2),
     deptno		        NUMBER(2));

CREATE OR REPLACE TRIGGER trig_emp_audit
  BEFORE UPDATE ON emp
  FOR EACH ROW
BEGIN
  INSERT INTO emp_audit
    VALUES(emp_audit_seq.nextval, change_date, change_user, action, :old.empno, :old.ename, :old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm, deptno);
END;
/

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER TRIG_EMP_AUDIT:

LINE/COL ERROR
-------- -----------------------------------------------
2/3      PL/SQL: SQL Statement ignored
3/149    PL/SQL: ORA-00984: column not allowed here

Can anyone assist in helping me find what I am doing wrong with the trigger?

Edited by: LostNoob on Aug 25, 2012 2:24 PM