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

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 23 Sep 2007 15:44:18 -0700
Message-ID: <1190587449.837081@bubbleator.drizzle.com>

Dereck L. Dietz wrote:
> "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.

You copied the demo that shows was does NOT work. Try working with the demos so that you understand them and then use the one that DOES work.

We'll both be happier. <g>

-- 
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

Received on Sun Sep 23 2007 - 17:44:18 CDT