Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

sql insert error: column not allowed here

SOLVED
Go to solution
Ratzie
Super Advisor

sql insert error: column not allowed here

I am trying to do a load of one column. Into a primary key.

cat TID.txt|while read i
do echo $i
sqlplus -s $LOGONID <insert into tid_tbl ( TID )
values($i);
commit;
quit

EOF
done

I get column not allowed here.
For the TID column.
6 REPLIES
Oviwan
Honored Contributor

Re: sql insert error: column not allowed here

Hi

try this:
insert into tid_tbl ("TID")
values($i);

the error# is:
ORA-00984:column not allowed here

first try the statements in SQL*Plus or whatever you use.

Regards
Patti Johnson
Respected Contributor
Solution

Re: sql insert error: column not allowed here

What is the datatype for TID.

If it is character data then you need to enclose the $i in quotes. Try this.

cat TID.txt|while read i
do echo $i
sqlplus -s $LOGONID <insert into tid_tbl ( TID )
values('$i');
commit;
quit

EOF
done

Patti
Ratzie
Super Advisor

Re: sql insert error: column not allowed here

I have tried both ways and still get same error.
Patti Johnson
Respected Contributor

Re: sql insert error: column not allowed here

Please post a describe of the table and the input file.

I ran your original script with a varchar2 column and got this error.
d
values(d)
*
ERROR at line 2:
ORA-00984: column not allowed here

But when I added the '' it worked.
It treats the $i as a column name without the quote.

Please post the output of your run.

Patti
Ratzie
Super Advisor

Re: sql insert error: column not allowed here

You are correct, insert worked correctly with ' '
I had a typo with the '
Ratzie
Super Advisor

Re: sql insert error: column not allowed here

Thanks Patti