10g的一个bug：'IN' IS NOT A VALID INTEGER VALUE

今天在调试一个procedure的时候，需要有这样的一个语句：

insert into table_a select rowid from table_b where...

调试过程中报'IN' IS NOT A VALID INTEGER VALUE错。

以下是解决过程：

[@more@]解决方法：
1.以sys登陆，将view：ALL_ARGUMENTS 改名以做backup。
2.运行以下sql：
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-1000337/，如需转载，请注明出处，否则将追究法律责任。

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