Re: Can MEMBER OF condition use pl/sql tables
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