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.
I was debating between your solution and the one below. I appreciate you posting your response online. It was one of the first I found. It was wierd though, I couldn't get TOAD to say the original view was legit, hence leading me to the below solution.
ReplyDeleteI found this as an alternative solution. I created the view in my schema and I don't think it affected the sys.ALL ARGUMENTS view. I think it "could" be safe for production.
http://blog.oraclecontractors.com/?p=424#respond
Steve, Toad fan just happy his toad didnt croak
Thank you so much. It worked.
ReplyDeleteI also tested this; it works great on the freeware version. Saved me a lot of political stress.
ReplyDeleteIt looks like in your example to "CREATE OR REPLACE FORCE VIEW" you're missing the reference to "SUBPROGRAM_ID"
ReplyDeleteAre you suppose to "remove" the column? Because you state in solution #3:
"Alter the ALL_ARGUMENTS view and move the SUBPROGRAM_ID column to the end on the database"
Which this would make more sense:
"Alter the ALL_ARGUMENTS view and move the SUBPROGRAM_ID column to the end of the view"
Cheers
I also created the view in my schema and it worked for me too. Thanks a ton!!! Finally I can view packages in Schema Browser. I have Toad 8.0.
ReplyDelete