Re: Can MEMBER OF condition use pl/sql tables

From: Kevin S <SearleK_at_googlemail.com>
Date: Thu, 29 Jan 2009 03:49:47 -0800 (PST)
Message-ID: <935b90bd-3312-4bf5-b831-a7f57919ccce_at_w1g2000prm.googlegroups.com>



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; Received on Thu Jan 29 2009 - 05:49:47 CST

Original text of this message