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

From: ddf <oratune_at_msn.com>
Date: Tue, 2 Feb 2010 06:38:17 -0800 (PST)
Message-ID: <7eba93b4-1e03-467e-ba1a-0efd26a8b9f9_at_t34g2000prm.googlegroups.com>



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,ord­­er_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:

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 Received on Tue Feb 02 2010 - 08:38:17 CST

Original text of this message