Re: Dynamic query IN()-condition for cursor stored procedure

Think I have to train my searchcannon again, couldn't find the answer on the newsgroups... difficult to find something because 'IN' is a regular word... but... found something useful on a website...

Why someone asked the same question, with same example ? :)... Naah, not school work :), but because of this link and example I think : http://www.psoug.org/reference/conditions.html

hmmm... still have one stupid question : how do you declare a SQL-type ?... All apologies, I'm just a newbie...

but I mean, if you declare it in sql plus, and try it in a statement, it works... but after that sql plus session, you can't use it anymore...

If I want to use that type in a package in an external editor and I compile, i always get an error.

Thank you !

Bark Mark

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1086034541.925051_at_yasure>...
> Bark Mark wrote:
>
> > Hello,
> >
> >
> > I want a dynamic IN-condition for my cursor in a stored procedure...
> > let's say it is comparable with this example :
> >
> > SET SERVEROUTPUT ON
> >
> > DECLARE
> >
> > i PLS_INTEGER;
> > InStr VARCHAR2(20) := '10,30';
> >
> > BEGIN
> > SELECT COUNT(*)
> > INTO i
> > FROM emp
> > WHERE deptno IN (InStr);
> >
> > dbms_output.put_line(i);
> > END;
> > /
> >
> >
> > this needs to return "9", but it returns a "0". If you use it with
> > Instr = '10' it works, in all other cases I'm stuck with it...
> >
> > Hope someone can help asap, it's a little bit urgent...
> >
> >
> > Thanks a lot
> >
> > Bark Mark
>
> Help me out here Bark Mark ... this exact question was asked in this
> forum in mid-January of this year. And I mean EXACT QUESTION because
> in the previously asked question the following example was used:
>
> i PLS_INTEGER;
> x InStrTab := InStrTab('10','30');
>
> which varies from your example:
>
> > i PLS_INTEGER;
> > InStr VARCHAR2(20) := '10,30';
>
> By not enough to be a coincidence. So please explain why this is.
> Because right now my conclusion is that this is school work, what
> with this being finals week, and that you are asking someone here
> to help you cheat.
Received on Wed Jun 02 2004 - 12:17:31 CDT