Re: Can MEMBER OF condition use pl/sql tables

From: William Robertson <williamr2019_at_googlemail.com>
Date: Fri, 30 Jan 2009 23:52:17 -0800 (PST)
Message-ID: <88d6c74c-5fe2-4634-9152-6c7289ad713d_at_m22g2000vbp.googlegroups.com>



On Jan 29, 11:49 am, Kevin S <Sear..._at_googlemail.com> wrote:
> William,
>
> Running the code below will illustrates the issue I am trying to
> resolve which takes the form of a compile time error
> PLS-00330: Invalid use of type name or subtype name.
>
> I am presuming this is to do with my use of a pl/sql table instead of
> a nested table.
>
> In addition to a pl/sql table, I have also tried it with the
> collection declarations/types commented out in the code and got the
> same message
>
> Thanks for your interest.
>
> Kevin
>
> CREATE OR REPLACE PACKAGE BODY REPORT_PKG IS
>
>   PROCEDURE testmemberofprc(
>             po_ref_cursor            OUT RefCursor)
>   IS
>     TYPE alc_tab IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
>     --TYPE alc_tab IS TABLE OF VARCHAR2(4000);
>     --TYPE alc_tab IS TABLE OF VARCHAR2(4000) NOT NULL;
>     --TYPE alc_tab IS VARRAY(10) OF VARCHAR2(4000) NOT NULL;
>
>     t_alc alc_tab;
>
>     v_index NUMBER:=0;
>     v_sql VARCHAR2(2000);
>     v_errcode VARCHAR2(2000);
>
>   BEGIN
>
>     t_alc(v_index) := 'GOR01';
>     v_index := v_index +1;
>     t_alc(v_index) := 'GOR02';
>     v_index := v_index +1;
>     t_alc(v_index) := 'GOR03';
>
>     v_sql := 'SELECT * FROM IOW_FULL_VW WHERE aggregatelevelcode
> MEMBER OF :t_alc ';
>
>     OPEN po_ref_Cursor FOR v_sql USING t_alc;
>
>   EXCEPTION
>   WHEN OTHERS
>        THEN
>        v_errcode := Sqlerrm;
>        RAISE;
>   END testmemberofprc;
>
> END REPORT_PKG;
True, that won't work with any locally defined collection type. That's a restriction of SQL within PL/SQL though, not of MEMBER OF. There are also some restrictions around what can be passed to dynamic SQL like this as bind variables. Does it have to be dynamic? Why can't you have a generic VARCHAR2_NTT scalar collection type and reuse it in all situations like this? Received on Sat Jan 31 2009 - 01:52:17 CST

Original text of this message