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

From: kazelot <kazelot_at_thenut.eti.pg.gda.pl>
Date: 20 Mar 2002 02:14:53 -0800
Message-ID: <8a47e227.0203200214.79ab9978@posting.google.com>

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

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 Received on Wed Mar 20 2002 - 04:14:53 CST