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

Dereck L. Dietz wrote:
"DA Morgan" <damorgan@xxxxxxxxx> wrote in message news:1190564835.98331@xxxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx (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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages