CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Peter Penzov
Peter Penzov

Reputation: 1668

SQL Error: ORA-00984: column not allowed here

I'm trying to use this SQL query to generate some test data

    INSERT INTO EVENT (ID, SOURCE, TYPE, EVENT_DATE, DESCRIPTION) VALUES (DBMS_RANDOM.Value(5000, 90000), 101, 'WARNING', (SYSDATE - 1/10 + LOOP_COUNTER/1300), DBMS_RANDOM.STRING('X', 5))

CREATE TABLE EVENT(
  ID INTEGER NOT NULL,
  SOURCE VARCHAR2(50 ),
  TYPE VARCHAR2(50 ),
  EVENT_DATE DATE,
  DESCRIPTION VARCHAR2(100 )
)

I get error Error report - SQL Error: ORA-00984: column not allowed here 00984. 00000 - "column not allowed here"

Do you have any idea how I can fix this issue?

Upvotes: 2

Views: 9459

Answers (3)

Aaron Carta
Aaron Carta

Reputation: 48

We recently ran into this error while implementing some complex array binding logic and my team spent ages trying to figure out why were getting this error - we thought the keyword was the problem (our column lists are created by the business for each client and are dynamic per table). Of course, it was something silly.

If you are doing array binding, when you setup the query make sure that all the fields in the values list have a ":" in front of them, this is what caused our error (note CompanyCode field):

string query = "INSERT INTO SURVEY.COMPANY (RPT_INDUSTRY,SUBMISSIONID,COMPANYCODE,COLUMN) VALUES (:RPT_INDUSTRY,:SUBMISSIONID,COMPANYCODE,:COLUMN)"
command.CommandText = query;
command.CommandType = CommandType.Text;
command.BindByName = true;

Upvotes: 0

Dmitriy
Dmitriy

Reputation: 5565

Such error appears when you use unidentified variable in VALUES clause. Probably your variable LOOP_COUNTER is not declared or is written with a typo.

Upvotes: 4

DZDomi
DZDomi

Reputation: 1679

The Column SOURCE is a type of varchar2 but you are providing an integer

Upvotes: 3

Related Questions