Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Is it possible to use a variable (table of numbers) with 'in' operator?

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

From: TurkBear <jgreco1_at_mn.rr.com>
Date: Wed, 20 Mar 2002 08:47:59 -0600
Message-ID: <268h9us0cv5mriicuct0o6di8ihldtvl58@4ax.com>

Metalink has this from Oracle Support as a reply to folks with that error code:



From: Oracle, sivakumar saravanan 18-Jan-02 15:37 Subject: Re : Error while selecting from a nested table

Hi Don,

Local collection types not allowed in SQL statements and this is identified as an internal documentation bug.

select sid into retval from TABLE(CAST(my_tab AS tab)) where id = 2; This statement is not allowed. This could be leading to PLS-462 where (my_tab as tab) is not allowed

Regards,
Siva
OSS



kazelot_at_thenut.eti.pg.gda.pl (kazelot) wrote:

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

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!  Check out our new Unlimited Server. No Download or Time Limits! -----== Over 80,000 Newsgroups - 19 Different Servers! ==----- Received on Wed Mar 20 2002 - 08:47:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US