Re: Dynamic "In" w a Cursor in a Package

On Sep 23, 4:03 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> "DA Morgan" <damor..._at_psoug.org> wrote in message
>
> news:1190564835.98331_at_bubbleator.drizzle.com...
>
>
>
>
>
> > CapCity wrote:
> >> I'm pretty new to Oracle and PL-SQL, I have a SQL Server/T-SQL
> >> background. I've googled for this and found some examples, but no exact
> >> luck yet.
>
> >> We're on version 9.
>
> >> We have a need, in a package, to execute a Select statement using an In
> >> List that will be a paramter. I've been able to successfully use this if
> >> the In list has one item:
>
> >> function fcn(include in varchar2) return ref_cursor is
> >> rc ref_cursor;
>
> >> begin
> >> open rc for
> >> select field1 from table
> >> where field2 in include and
> >> field1 not in (select ...);
>
> >> return rc;
> >> end;
>
> >> field1 is numeric. As I said, works fine if include contains 1 element
> >> (include is a comma delimited string). Once it gets a second item, no
> >> more matches. I presume it is taking it as one item and not using it as a
> >> comma-separated list of items.
>
> >> I've seen an example, at
> >>http://www.oracle-base.com/articles/misc/DynamicInLists.phpbut I
> >> couldn't get it to work. I first got the "can't use a local collection"
> >> error so I switched it to use a type declared in the package. I then got
> >> a "missing right parenthesis" error, which made no sense. All my
> >> parenthesis matched. I added a few dozen right ones and got the same
> >> message.
>
> >> Since this works with one item in the include, I'm using it that way,
> >> parsing out the list and calling this separately for each one. It's as
> >> slow as could be, which I expect. but it's limping out the results. Can
> >> anyone help me so that it will accept a list of values and treat it as
> >> such?
>
> >> Thanks,
>
> > This is possibly one of the most asked about capabilities and I find
> > myself referring people to this demo more often than any other.
>
> > Go towww.psoug.org
> > Click on Morgan's Library
> > Click on Conditions
> > Scroll down to "Complex IN Demo"
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Group
> >www.psoug.org
>
> Oracle 10.2.0.1, WindowsXP Pro SP2
>
> When I enter the following command at the SQL*Plus prompt:
>
> SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER IN ( 'SYS','SYSTEM');
>
> I receive a count of 844.
>
> However, when I attempt the "Complex IN" I keep receiving a count of 0.
>
> DECLARE
> v_user VARCHAR2(30) := '''SYS'',''SYSTEM''';
> v_cnt PLS_INTEGER := 0;
> BEGIN
> SELECT COUNT(*) INTO v_cnt FROM dba_tables WHERE owner IN (v_user);
> DBMS_OUTPUT.PUT_LINE(v_cnt);
> END;
>
> Can the Complex IN work with strings? If so, what am I doing wrong? I was
> able to get a LIKE to work but can't seem to get the IN logic working.
>
> LIKE Code:
>
> DECLARE
> v_user VARCHAR2(30) := 'SYS%';
> v_cnt PLS_INTEGER := 0;
> BEGIN
> SELECT COUNT(*) INTO v_cnt FROM dba_tables WHERE OWNER LIKE v_user;
> DBMS_OUTPUT.PUT_LINE(v_cnt);
> END;
>
> Thanks for any insights.- Hide quoted text -
>
> - Show quoted text -

Derek your SQL statement is a static SQL statement and you are trying to use the single bind variable as part of a static SQL statement to represent multiple variables. The way you are trying to use the variable is not valid.

You will have to use a dynamic SQL statement. See Daniel's referenced example or visit the http://asktom.oracle.com site for other examples.

HTH -- Mark D Powell -- Received on Sun Sep 23 2007 - 17:33:09 CDT