|
Summary
Using TOAD and Oracle10g you will get the error: 'IN' is not a valid integer value, when you try with Schema Browser to see the a package body.
Why you get this error:
'IN' is not a valid integer value
This is a TOAD bug because when Oracle patched version 10.2.0.1 with version 10.2.0.2 the structure of the ALL_ARGUMENTS view changed, causing Toad 8.6.0 or earlier to state: 'IN' is not a valid integer value when selecting procedures in the Schema Browser or loading them into the Procedure Editor.
Workaround
Login as SYS user and create a backup of the existing ALL_ARGUMENTS view
CREATE OR REPLACE VIEW ALL_ARGUMENTS_BACKUP
(OWNER, OBJECT_NAME, PACKAGE_NAME, OBJECT_ID, OVERLOAD,
SUBPROGRAM_ID, 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)
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.procedure#, /* SUBPROGRAM ID */
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 */
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))
/
GRANT SELECT ON ALL_ARGUMENTS TO PUBLIC WITH GRANT OPTION
/
Create the NEW VIEW this the following 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))
/
|
