Toad displays error 'IN' is not a valid integer value

Problem Description
You are using TOAD version that is less than 8.6.1 and is connecting to Oracle database release 2(10.2.0.2 or higher) , then while looking source of the stored procedure toad displays error message
TOAD ERROR:
'IN' is not a valid integer value
in a pop-up window.

This problem remains if you try to connect to oracle database 11g with toad version less than 8.6.1.

Cause of the Problem
The problem happens because Oracle made a change to the ALL_ARGUMENTS view at release 10.2.0.2 and this 'broke' TOAD. Until 10.2.0.1 there is no problem. But starting with 10.2.0.2 oracle has added a new column named SUBPROGRAM_ID in the ALL_ARGUMENTS view and this causes toad unusable.

Have a look at ALL_ARGUMENTS view between two releases.
In 10.2.0.1,


SQL> desc all_arguments;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
PACKAGE_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OVERLOAD VARCHAR2(40)
ARGUMENT_NAME VARCHAR2(30)
POSITION NOT NULL NUMBER
SEQUENCE NOT NULL NUMBER
DATA_LEVEL NOT NULL NUMBER
DATA_TYPE VARCHAR2(30)
DEFAULT_VALUE LONG
DEFAULT_LENGTH NUMBER
IN_OUT VARCHAR2(9)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
RADIX NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
TYPE_OWNER VARCHAR2(30)
TYPE_NAME VARCHAR2(30)
TYPE_SUBNAME VARCHAR2(30)
TYPE_LINK VARCHAR2(128)
PLS_TYPE VARCHAR2(30)
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)


In 11g,


SQL> desc all_arguments
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
PACKAGE_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OVERLOAD VARCHAR2(40)
SUBPROGRAM_ID NUMBER
ARGUMENT_NAME VARCHAR2(30)
POSITION NOT NULL NUMBER
SEQUENCE NOT NULL NUMBER
DATA_LEVEL NOT NULL NUMBER
DATA_TYPE VARCHAR2(30)
DEFAULTED VARCHAR2(1)
DEFAULT_VALUE LONG
DEFAULT_LENGTH NUMBER
IN_OUT VARCHAR2(9)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
RADIX NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
TYPE_OWNER VARCHAR2(30)
TYPE_NAME VARCHAR2(30)
TYPE_SUBNAME VARCHAR2(30)
TYPE_LINK VARCHAR2(128)
PLS_TYPE VARCHAR2(30)
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)



Solution of the Problem
1.With toad version less than 8.6.1 don't connect to oracle version 10.2.0.2 or higher. Not a actual solution.

2.Upgrade to toad version. On version 9.5.0 it works fine.

3.Alter the ALL_ARGUMENTS view and move the SUBPROGRAM_ID column to the end on the database. This is not supported according to oracle. However on your test database it is safer to do it. But it is not recommended to do it on your production database though no side effect I ever get.

In your 11.1g database connect as "sys as sysdba" and create the view as below.


CREATE OR REPLACE FORCE VIEW "SYS"."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") 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 */
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));
View created.


After creating now try to connect to oracle database with your existing toad and problem should go away.