oracle 10g的connect by bug修复

在系统升级到10g后，碰到了一些connect by的bug。如：

select">select AvGroupLevel, AvGroupCode, AvGroupCodeParent
2 from AvGroupCodeTable where SUBSTR(NLS_LOWER(DataAreaId),1,3) = SUBSTR(NLS_LOWER('845'),
3 START WITH AvGroupCode = '110'
4 CONNECT BY PRIOR AvGroupCodeParent = AvGroupCode
5 and SUBSTR(NLS_LOWER(DataAreaId),1,3) = SUBSTR(NLS_LOWER('845'),1,3)
6 order by AvGroupLevel DESC
7 ;
and SUBSTR(NLS_LOWER(DataAreaId),1,3) = SUBSTR(NLS_LOWER('845'),1,3)
*
ERROR at line 5:
ORA-00600: internal error code, arguments: [qkacon:FJswrwo], [4], [], [], [], [], [], []

[@more@]

1.alter system set "_optimizer_connect_by_cost_based" = FALSE scope=both;

2.alter system set "_allow_level_without_connect_by" = true scope=both;

3.a) Login as 'sys'
b) Run this script ..
CREATE OR REPLACE VIEW ALL_ARGUMENTS
(OWNER, OBJECT_NAME, PACKAGE_NAME, OBJECT_ID, OVERLOAD,
ARGUMENT_NAME, POSITION, SEQUENCE, DATA_LEVEL,
DATA_TYPE, DEFAULT_VALUE, DEFAULT_LENGTH, IN_OUT, DATA_LENGTH,
DATA_PRECISION, DATA_SCALE, RADIX, CHARACTER_SET_NAME, TYPE_OWNER,
TYPE_NAME, TYPE_SUBNAME, TYPE_LINK, PLS_TYPE, CHAR_LENGTH,
CHAR_USED, SUBPROGRAM_ID)
AS
SELECT
u.name, /* OWNER */
NVL(a.PROCEDURE$,o.name), /* OBJECT_NAME */
DECODE(a.PROCEDURE$,NULL,NULL, o.name), /* PACKAGE_NAME */
o.obj#, /* OBJECT_ID */
DECODE(a.overload#,0,NULL,a.overload#), /* OVERLOAD */
a.argument, /* ARGUMENT_NAME */
a.position#, /* POSITION */
a.SEQUENCE#, /* SEQUENCE */
a.LEVEL#, /* DATA_LEVEL */
DECODE(a.TYPE#, /* DATA_TYPE */
0, NULL,
1, DECODE(a.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, DECODE(a.scale, -127, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, DECODE(a.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, DECODE(a.charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
102, 'REF CURSOR',
104, 'UROWID',
105, 'MLSLABEL',
106, 'MLSLABEL',
110, 'REF',
111, 'REF',
112, DECODE(a.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'OBJECT',
122, 'TABLE',
123, 'VARRAY',
178, 'TIME',
179, 'TIME WITH TIME ZONE',
180, 'TIMESTAMP',
181, 'TIMESTAMP WITH TIME ZONE',
231, 'TIMESTAMP WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR TO MONTH',
183, 'INTERVAL DAY TO SECOND',
250, 'PL/SQL RECORD',
251, 'PL/SQL TABLE',
252, 'PL/SQL BOOLEAN',
'UNDEFINED'),
DEFAULT$, /* DEFAULT_VALUE */
deflength, /* DEFAULT_LENGTH */
DECODE(in_out,NULL,'IN',1,'OUT',2,'IN/OUT','Undefined'), /* IN_OUT */
LENGTH, /* DATA_LENGTH */
PRECISION#, /* DATA_PRECISION */
DECODE(a.TYPE#, 2, scale, 1, NULL, 96, NULL, scale), /* DATA_SCALE */
radix, /* RADIX */
DECODE(a.charsetform, 1, 'CHAR_CS', /* CHARACTER_SET_NAME */
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(a.charsetid),
4, 'ARG:'||a.charsetid),
a.type_owner, /* TYPE_OWNER */
a.type_name, /* TYPE_NAME */
a.type_subname, /* TYPE_SUBNAME */
a.type_linkname, /* TYPE_LINK */
a.pls_type, /* PLS_TYPE */
DECODE(a.TYPE#, 1, a.scale, 96, a.scale, 0), /* CHAR_LENGTH */
DECODE(a.TYPE#,
1, DECODE(bitand(a.properties, 128), 128, 'C', 'B'),
96, DECODE(bitand(a.properties, 128), 128, 'C', 'B'), 0), /* CHAR_USED */
a.PROCEDURE# /* SUBPROGRAM ID */
FROM obj$ o,argument$ a,USER$ u
WHERE o.obj# = a.obj#
AND o.owner# = u.USER#
AND (owner# = USERENV('SCHEMAID')
OR EXISTS
(SELECT NULL FROM v$enabledprivs WHERE priv_number IN (-144,-141))
OR o.obj# IN (SELECT obj# FROM sys.objauth$ WHERE Grantee# IN
(SELECT kzsrorol FROM x$kzsro) AND privilege# = 12))
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3345/viewspace-1005806/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3345/viewspace-1005806/