All,

Is it possible to use pl/sql tables (Associative Arrays) instead of a
varray when using the MEMBER OF condition?

Thanks

On Jan 27, 10:16*am, Kevin S <Sear... (AT) googlemail (DOT) com> wrote:
WHAT version of ORACLE?????

have you checked the documentation? (i.e., RTFM)
here's 10g
http://download.oracle.com/docs/cd/B...htm#sthref1108

Kevin S schrieb:
Actually, "member of" doesn't work with varrays (the same about
associative arrays) - the only collection type it supports is nested table.

Best regards

Maxim

Maxim,

Good point, I should have said nested table.

What I want to be able to do is use a record created at run time
rather than create something in the schema.

It doesn't seem to big a thing to expect to be able to do but
apparently it is not possible.

Anyone know if there is a technical reason why opr if it was simply
something that was overlooed whern the functionality was introduced?

Anyone have a work around?

Thanks for responding.

KS

On Jan 28, 11:37*am, Kevin S <Sear... (AT) googlemail (DOT) com> wrote:
MEMBER OF works fine with nested table collections declared within PL/
SQL. However the part about records could be complicating things, as
PL/SQL record variables don't have a lot of the functionality of
object types. What have you tried that isn't working?

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;

On Jan 29, 11:49*am, Kevin S <Sear... (AT) googlemail (DOT) com> wrote:
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?