2

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;
kenpeter
  • 7,404
  • 14
  • 64
  • 95
  • 1
    by the way - for future reference: "line 14" is pointing you to the `execute immediate` statement, which is where the exception is actually being raised at runtime. – Jeffrey Kemp Dec 01 '17 at 01:35

1 Answers1

4

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

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • 1
    OK, since you are building the dynamic SQL statement as a string itself, you do need to escape those single quotes. But that requires two single-quote characters, not one double-quote character. Copying and pasting your text, it appears you have double-quote characters. – Dave Costa Dec 01 '17 at 00:36
  • Yes, this is how to escape single quote: https://stackoverflow.com/questions/11315340/pl-sql-how-to-escape-single-quote-in-a-string – kenpeter Dec 01 '17 at 00:48