Re: ERROR IS: ORA-01007: variable not in select list

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 02 Feb 2010 08:47:31 +0100
Message-ID: <4b67d89a$0$22934$e4fe514c_at_news.xs4all.nl>

Op 2-2-2010 1:13, Sudhir schreef:
> I have a cursor inside a procedure which fetches some records using
> dynamic query.
> Now I need to insert these values into a table long with two more
> values, one is a unique ID and the other is testcase name.
> Unique ID- It can be anything. In this case I use a variable J and
> increment it by 1 each time when I fetch the record.
> Test case name will be a static value that comes as a IN parameter of
> SP.
>
> tcnum:='Testcase10';
> j:=1;
>
> loop
> fetch.....
> exit..notfound
> insert into tableA
> (snum,firstname,lastname,status,add1,add2,addcity,addstate,addzip,tcnum,order_ID)
> values (
>
> o_sban.sNUM ,
> o_sban.FIRSTNAME ,
> o_sban.LASTNAME ,
> o_sban.STATUS ,
> o_sban.sADDRADDR1 ,
> o_sban.sADDRADDR2 ,
> o_sban.sADDRCITY ,
> o_sban.sADDRSTATE ,
> o_sban.sADDRZIP ,
> tcnum ,
> j
> ) ;
>
> j:=j+1;
>
> end loop;
>
> The script used to work without any issue when the column in select
> statement was equal to column in the table.
> Since I need those two values, I added it in the table and modified
> the script accordingly. The datatype are same in both select statement
> and table.
> When I ran the script I started getting "ERROR IS: ORA-01007: variable
> not in select list".
> Can anyone help me on this?
> I appreciate your help...

Try renaming your variable tcnum. Looks like it's the same as the column name; your insert statement may interpret it wrong in the values clause, and assume you mean the column value.
So:

l_tcnum:='Testcase10';
....

o_sban.sADDRSTATE ,
o_sban.sADDRZIP ,
l_tcnum ,

j

Shakespeare Received on Tue Feb 02 2010 - 01:47:31 CST