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...

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

On Feb 2, 1:47=A0am, Shakespeare <what...@xs4all.nl> wrote:
> 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:=3D'Testcase10';
> > j:=3D1;
>
> > loop
> > fetch.....
> > exit..notfound
> > insert into tableA
> > (snum,firstname,lastname,status,add1,add2,addcity,addstate,addzip,tcnum=
,ord=ADer_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:=3Dj+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:=3D'Testcase10';
> ....
> o_sban.sADDRSTATE ,
> o_sban.sADDRZIP ,
> l_tcnum ,
> j
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

I tried renaming to I_tcnum, but still I'm getting the same error
"ERROR IS: ORA-01007: variable
not in select list".

On Feb 2, 9:26=A0am, Sudhir <nshanmu...@gmail.com> wrote:
> On Feb 2, 1:47=A0am, Shakespeare <what...@xs4all.nl> wrote:
>
>
>
>
>
> > 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:=3D'Testcase10';
> > > j:=3D1;
>
> > > loop
> > > fetch.....
> > > exit..notfound
> > > insert into tableA
> > > (snum,firstname,lastname,status,add1,add2,addcity,addstate,addzip,tcn=
um,ord=AD=ADer_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:=3Dj+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 statemen=
t
> > > and table.
> > > When I ran the script I started getting "ERROR IS: ORA-01007: variabl=
e
> > > 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 colum=
n
> > name; your insert statement may interpret it wrong in the values clause=
,
> > and assume you mean the column value.
> > So:
>
> > l_tcnum:=3D'Testcase10';
> > ....
> > o_sban.sADDRSTATE ,
> > o_sban.sADDRZIP ,
> > l_tcnum ,
> > j
>
> > Shakespeare- Hide quoted text -
>
> > - Show quoted text -
>
> I tried renaming to I_tcnum, but still I'm getting the same error
> "ERROR IS: ORA-01007: variable
> not in select list".- Hide quoted text -
>
> - Show quoted text -

Part of the problem is the exception handler you've mangled to report
your 'error text'; you are not seeing the entire text and are now left
wondering which line of the insert statement is at fault.  You should
first read here:

http://oratips-ddf.blogspot.com/2008/03/what-was-that-masked-message.html

and afterwards fix your exception handler.  Once that's done run your
procedure again and see where the error actually occurs so you will
have a much better idea what column is at fault and how best to
correct the problem.


David Fitzjarrell

On Feb 2, 3:26=A0pm, Sudhir <nshanmu...@gmail.com> wrote:
> I tried renaming to I_tcnum, but still I'm getting the same error
> "ERROR IS: ORA-01007: variable
> not in select list".

The most obvious explanation for your error would be that your FETCH
statement is the actual culprit - the number of INTO variables might
not be consistent with your query definition.

This simple test case raises the same error:

declare
  c sys_refcursor;
  s_dummy varchar2(1);
  s_dummy_culprit varchar2(1);
begin
  open c for 'select dummy from dual';
  fetch c into s_dummy, s_dummy_culprit; --<=3D=3D here is the problem
  close c;
end;
/


Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/14=
30226684

On Feb 3, 7:07=A0am, Randolf Geist <mah...@web.de> wrote:
> On Feb 2, 3:26=A0pm, Sudhir <nshanmu...@gmail.com> wrote:
>
> > I tried renaming to I_tcnum, but still I'm getting the same error
> > "ERROR IS: ORA-01007: variable
> > not in select list".
>
> The most obvious explanation for your error would be that your FETCH
> statement is the actual culprit - the number of INTO variables might
> not be consistent with your query definition.
>
> This simple test case raises the same error:
>
> declare
> =A0 c sys_refcursor;
> =A0 s_dummy varchar2(1);
> =A0 s_dummy_culprit varchar2(1);
> begin
> =A0 open c for 'select dummy from dual';
> =A0 fetch c into s_dummy, s_dummy_culprit; --<=3D=3D here is the problem
> =A0 close c;
> end;
> /
>
> Regards,
> Randolf
>
> Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> Co-author of the "OakTable Expert Oracle Practices" book:http://www.apres=
s.com/book/view/1430226684http://www.amazon.com/Expert-Oracle-Practices-Dat=
abase-Administration...

Yes, you are right on target... the problem was the FETCH statement...

Fetch returned more rows than what I used in the insert statement...
After fixing the fetch statement it works like charm...

Thanks everyone...