47

I am doing a simple Oracle INSERT and I keep getting this error: [Err] ORA-00984: column not allowed here

INSERT INTO MY.LOGFILE
(id,severity,category,logdate,appendername,message,extrainfo)
VALUES
(
"dee205e29ec34",
"FATAL",
"facade.uploader.model",
"2013-06-11 17:16:31",
"LOGDB",
NULL,
NULL
)
jabaldonedo
  • 25,822
  • 8
  • 77
  • 77
George Murphy
  • 937
  • 2
  • 10
  • 16
  • 7
    Use single quotes (apostrophes) around string literals e.g. 'FATAL'. Double quotes are optionally (rarely) used around identifiers like column names. – Tony Andrews Jun 11 '13 at 21:36
  • if you're using variables in PL/SQL, it can also mean you haven't defined the variable e.g. declare v_my_var number := 1; INSERT INTO tbl (col_name) values (my_var); -- note mispelled variable name – ghostJago Feb 20 '20 at 10:00

1 Answers1

106

Replace double quotes with single ones:

INSERT
INTO    MY.LOGFILE
        (id,severity,category,logdate,appendername,message,extrainfo)
VALUES  (
       'dee205e29ec34',
       'FATAL',
       'facade.uploader.model',
       '2013-06-11 17:16:31',
       'LOGDB',
       NULL,
       NULL
       )

In SQL, double quotes are used to mark identifiers, not string constants.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • This is the error I am getting now. ORA-01861: literal does not match format string – George Murphy Jun 11 '13 at 21:45
  • @GeorgeMurphy: please post your table definition. – Quassnoi Jun 11 '13 at 21:48
  • 4
    That's almost definitely because `logdate` is a _date_ @George, you're trying to insert a _character_. You need to convert it to a date using [TO_DATE()](http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions203.htm#SQLRF06132), see something like http://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql/10178346#10178346 or http://stackoverflow.com/questions/11883923/converting-number-to-date-in-oracle/11884028#11884028 or hundreds of others... – Ben Jun 11 '13 at 22:29
  • It is work fine when replace double quotes with single ones – Mohammad Jihad Helal Jan 29 '19 at 08:45