MEMBER OF condition causing ORA-00600: internal error code, arguments: [15216], [], [], [], [], [], [], []
Date: Thu, 29 Jan 2009 03:24:26 -0800 (PST)
Message-ID: <22ab7818-f1ac-4244-97a5-69c3de4a26a1_at_v31g2000vbb.googlegroups.com>
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; Received on Thu Jan 29 2009 - 05:24:26 CST