Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
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