Re: Is it possible to use a variable (table of numbers) with 'in' operator?

In article <8a47e227.0203200214.79ab9978_at_posting.google.com>, kazelot_at_thenut.eti.pg.gda.pl says...
>
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type myTableType
>> 2 as table of number
>> 3 /
>> Type created.
>[...]
>> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select *
>> 2 from all_users
>>3 where user_id in ( select * from TABLE( cast( in_list(:str) as myTableType )
>> ) )
>> 4 /
>

the type MUST be visible to the SQL engine. You must use the CREATE TYPE, it'll not work to hide the type in the package.

>Thanks for the suggestion. I have tried it. The only difference (I hope ;-)
>is that I put the stuff into a package.
>
>Here it is:
>--------------
>CREATE OR REPLACE PACKAGE tried
>AS
> TYPE tab_numbers IS TABLE OF NUMBER (10);
>
> FUNCTION try_1st (at_mozliwosci IN tab_numbers)
> RETURN NUMBER;
>END tried;
>/
>
>CREATE OR REPLACE PACKAGE BODY tried
>AS
> FUNCTION try_1st (at_mozliwosci IN tab_numbers)
> RETURN NUMBER
> IS
> ll_fk1 NUMBER;
> ll_cnt NUMBER;
>
> CURSOR cur_1 (at_tab tab_numbers, al_cnt NUMBER)
> IS
> SELECT mw.FK1
> FROM moz_wyp mw
> WHERE mw.FK2 IN
> (SELECT *
> FROM TABLE(CAST (at_tab AS tab_numbers)))
> GROUP BY FK1
> HAVING COUNT (*) = al_cnt; --AT_TAB.COUNT DOESN'T WORK HERE
> BEGIN
> ll_cnt := at_mozliwosci.COUNT;
>
> FOR rec_1 IN cur_1 (at_mozliwosci, ll_cnt)
> LOOP
> -- DO SOMETHING
> NULL;
> END LOOP;
>
> RETURN 1;
> EXCEPTION
> WHEN OTHERS
> THEN
> RETURN -ABS (SQLCODE);
> END;
>END tried;
>/
>--------------
>
>I got this funny error:
>PLS-00642: Message 642 not found; product=plsql; facility=PCM
>
>I couldn't find any description. Any idea what it means or where I go wrong?
>
>Oh, I almost forgot, my version of oracle is:
>SQL> select * from v$version;
>
>BANNER
>----------------------------------------------------------------
>Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
>PL/SQL Release 8.1.7.2.0 - Production
>CORE 8.1.7.2.1 Production
>TNS for 32-bit Windows: Version 8.1.7.2.0 - Production
>NLSRTL Version 3.4.1.0.0 - Production
>
>
>TIA,
>kazelot

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp