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

From: Sudhir <nshanmugam_at_gmail.com>
Date: Wed, 3 Feb 2010 17:13:32 -0800 (PST)
Message-ID: <7587c917-4e08-4f50-a016-356ae8a79fce_at_r24g2000yqd.googlegroups.com>

On Feb 3, 7:07 am, Randolf Geist <mah..._at_web.de> wrote:
> On Feb 2, 3:26 pm, Sudhir <nshanmu..._at_gmail.com> wrote:
>
> > 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/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...

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... Received on Wed Feb 03 2010 - 19:13:32 CST