Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!t34g2000prm.googlegroups.com!not-for-mail From: ddf Newsgroups: comp.databases.oracle.server Subject: Re: ERROR IS: ORA-01007: variable not in select list Date: Tue, 2 Feb 2010 06:38:17 -0800 (PST) Organization: http://groups.google.com Lines: 96 Message-ID: <7eba93b4-1e03-467e-ba1a-0efd26a8b9f9@t34g2000prm.googlegroups.com> References: <081da676-803e-4304-80d3-c30fc6df8588@d27g2000yqn.googlegroups.com> <4b67d89a$0$22934$e4fe514c@news.xs4all.nl> <79eec258-cb22-4366-a57b-59f45e45063c@36g2000yqu.googlegroups.com> NNTP-Posting-Host: 72.192.72.65 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1265121498 10897 127.0.0.1 (2 Feb 2010 14:38:18 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 2 Feb 2010 14:38:18 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: t34g2000prm.googlegroups.com; posting-host=72.192.72.65; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.2),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Feb 2, 9:26=A0am, Sudhir wrote: > On Feb 2, 1:47=A0am, Shakespeare 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