Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!192.87.166.28.MISMATCH!tudelft.nl!txtfeed1.tudelft.nl!newsfeed.kpn.net!pfeed08.wxs.nl!newsfeed.xs4all.nl!newsfeed6.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Date: Tue, 02 Feb 2010 08:47:31 +0100
From: Shakespeare <whatsin@xs4all.nl>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; nl; rv:1.9.1.7) Gecko/20100111 Thunderbird/3.0.1
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: ERROR IS: ORA-01007: variable not in select list
References: <081da676-803e-4304-80d3-c30fc6df8588@d27g2000yqn.googlegroups.com>
In-Reply-To: <081da676-803e-4304-80d3-c30fc6df8588@d27g2000yqn.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 61
Message-ID: <4b67d89a$0$22934$e4fe514c@news.xs4all.nl>
NNTP-Posting-Host: 82.95.215.210
X-Trace: 1265096858 news.xs4all.nl 22934 [::ffff:82.95.215.210]:49337
X-Complaints-To: abuse@xs4all.nl
Xref:  news.cambrium.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