ERROR IS: ORA-01007: variable not in select list
Sudhir
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...
Shakespeare
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
Sudhir
> 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
>
> - Show quoted text -
I tried renaming to I_tcnum, but still I'm getting the same error
ddf
>
> - 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
Randolf Geist
> 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; --<== 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/1430226684
Sudhir
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...