Re: ERROR IS: ORA-01007: variable not in select list
Date: Tue, 2 Feb 2010 06:38:17 -0800 (PST)
On Feb 2, 9:26 am, Sudhir <nshanmu..._at_gmail.com> wrote:
> On Feb 2, 1:47 am, Shakespeare <what..._at_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:='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- 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:
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 Received on Tue Feb 02 2010 - 08:38:17 CST