Hi All,

I am getting an ORA-00600 when using a MEMBER OF condition using a
nested table.

If I create a list from the data in added to the neted table the error
does not occur.

OK so I could just use the method that works but this just adds a
level of detail to the code and means I am not using what is supposed
to be a more efficient method of selecting data.

Can anyone see what might be causing the issue?

The nested table is created as follows
CREATE OR REPLACE TYPE Type_Varchar2_4000 AS TABLE OF VARCHAR2(4000)


The procedure I have created to demonstrate the issue is as follows.

CREATE OR REPLACE PACKAGE BODY REPORT_PKG IS
PROCEDURE testmemberofprc(
po_ref_cursor OUT RefCursor)
IS
--TYPE alc_tab IS TABLE OF aggregatelevelright.aggregatelevelcode
%TYPE INDEX BY PLS_INTEGER;
ntt type_varchar2_4000;

v_sql VARCHAR2(2000);
v_errcode VARCHAR2(2000);

BEGIN

ntt := type_varchar2_4000(
'GOR01', 'GOR02', 'GOR03', 'GOR04', 'GOR05',
'GOR06', 'GOR07', 'GOR08', 'GOR09', 'GOR10',
'GOR11', 'FOR', 'LON', 'MOR', 'SWAN', 'NORTH',
'Norwich', 'SOUTH', 'Scotland', 'York', 'ALL');

v_sql := 'SELECT * FROM IOW_FULL_VW WHERE aggregatelevelcode
MEMBER OF :ntt ';

OPEN po_ref_Cursor FOR v_sql USING ntt;

EXCEPTION
WHEN OTHERS
THEN
v_errcode := Sqlerrm;

BEGIN

v_sql := 'SELECT * FROM IOW_FULL_VW WHERE aggregatelevelcode
IN (' ||
'''GOR01'', ''GOR02'', ''GOR03'', ''GOR04'', ''GOR05'', '||
'''GOR06'', ''GOR07'', ''GOR08'', ''GOR09'', ''GOR10'', '||
'''GOR11'', ''FOR'', ''LON'', ''MOR'', ''SWAN'',
''NORTH'','||
'''Norwich'', ''SOUTH'', ''Scotland'', ''York'', ''ALL'')';

OPEN po_ref_Cursor FOR v_sql;

EXCEPTION WHEN OTHERS THEN
v_errcode := Sqlerrm;

RAISE;
END;

RAISE;
END testmemberofprc;
END REPORT_PKG;

On 29 Jan, 11:24, Kevin S <Sear... (AT) googlemail (DOT) com> wrote:
Oracle version? O/S version?

ORA-00600 generally means raise a SR with Oracle.

HTH

-g

Doh!

Sorry people.

Oracle version 10.2.0.1.

o/s is unix but not sure which.

On Jan 29, 9:48*am, Kevin S <Sear... (AT) googlemail (DOT) com> wrote:
show parameter query_rewrite_enabled.

If you are using query rewrite see metalink note: 405938.1 workarounds
in there not for your version of course. The proper answers are
"patch to something current, and if you still have the problem, call
Oracle support" or "don't do that."

jg
--
@home.com is bogus.
http://www.usdoj.gov/usao/md/Public-...Intrusion.html

On Jan 29, 1:47*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
Joel found the only error report that appears to be a probably match
in my opinion also.

HTH -- Mark D Powell --