CODEWITHSUNDEEP
oracle-databaseoracle11g
kenpeter
kenpeter

Reputation: 8274

oracle column not allowed here

I got this error:

Error report -
ORA-00984: column not allowed here
ORA-06512: at line 14
00984. 00000 -  "column not allowed here"

here is the code. I copy and paste the field name (all in cap). The field name should be correct

set serveroutput on

DECLARE
  my_creation_dt date;
  mysql varchar2(6000) := '';
BEGIN
  select creation_dt into my_creation_dt from role_table where security_role = 'admin';

  mysql := 'insert into role_grant_table (PERSON_ID, CREATION_DT, SECURITY_ROLE, 
    SSS_CREATE_DT, UPDATE_WHO, UPDATE_ON) values (1234, SYSDATE, 
    "ADMIN", 
    :my_creation_dt, 
    "myname", 
    SYSDATE)'; -- line 14, column not allowed here

  execute immediate mysql using (my_creation_dt);

END;

Upvotes: 2

Views: 1410

Answers (1)

Dave Costa
Dave Costa

Reputation: 48121

Double-quotes are used to enclose identifiers (usually when they have mixed case or punctuation characters). So Oracle is interpreting "ADMIN" and "myname" as identifiers, which in this context the parser takes to be column names. The error is telling you that referencing a column here is not allowed.

Presumably, you intended those to be the string values to be inserted. Use single-quotes to enclose string literals, i.e. 'ADMIN' and 'myname'.

Upvotes: 4

Related Questions