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

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sun, 23 Sep 2007 20:03:50 GMT
Message-ID: <GEzJi.35329$RX.25339@newssvr11.news.prodigy.net>

"DA Morgan" <damorgan_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.php but 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 to www.psoug.org
> Click on Morgan's Library
> Click on Conditions
> Scroll down to "Complex IN Demo"
> -- 
> Daniel A. Morgan
> University of Washington
> damorgan_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. Received on Sun Sep 23 2007 - 15:03:50 CDT