# Database Discussions > Oracle >  'IN' is not a valid integer value

## mdkora

I am using TOAD 8.5 and oracle 10.2. When i compile a procedure i get this error 'IN' is not a valid integer value. 
I have a simple procedure

CREATE TABLE TABLE1(NO NUMBER, NAME VARCHAR2(10));

CREATE OR REPLACE PROCEDURE PROC(i IN INTEGER) AS
BEGIN
INSERT INTO TABLE1(i,'Name');
END PROC;

----------


## skhanal

The INSERT part is not correct

CREATE OR REPLACE PROCEDURE PROC(i IN INTEGER) AS
BEGIN
INSERT INTO TABLE1 (No, Name) Values (i,'Name');
END PROC;
Reply With Quote

----------


## stecal

Asking for trouble using a keyword such as "name." Plus, you need a trailing slash.

http://download.oracle.com/docs/cd/B...s.htm#LNPLS019

SQL > conn scott/tiger
Connected.
SCOTT > CREATE TABLE TABLE1(NO NUMBER, NAME VARCHAR2(10));

Table created.

SCOTT > 
SCOTT > CREATE OR REPLACE PROCEDURE PROC(i IN INTEGER) AS
  2  BEGIN
  3  INSERT INTO TABLE1(i,'Name');
  4  END PROC;
  5  /

Warning: Procedure created with compilation errors.

SCOTT > show errors
Errors for PROCEDURE PROC:

LINE/COL ERROR
-------- ---------------------------------------------------------------
3/1      PL/SQL: SQL Statement ignored
3/22     PL/SQL: ORA-01747: invalid user.table.column, table.column, or
         column specification

----------


## zezitomane

Update your Toad for 8.6.1 at least

----------


## zezitomane

> I am using TOAD 8.5 and oracle 10.2. When i compile a procedure i get this error 'IN' is not a valid integer value. 
> I have a simple procedure
> 
> CREATE TABLE TABLE1(NO NUMBER, NAME VARCHAR2(10));
> 
> CREATE OR REPLACE PROCEDURE PROC(i IN INTEGER) AS
> BEGIN
> INSERT INTO TABLE1(i,'Name');
> END PROC;



Update your Toad for 8.6.1 at least

----------


## stecal

At least what? That has nothing whatsoever to do with the code.

----------


## joebednarz

You could just modify your INSERT to be:

INSERT INTO table1 VALUES( i, 'Name');

----------


## manjulak18

here is what you do to fix it. 

a) log in as SYS. 
2) go to schema browser and extract DDL for ALL_ARGUMENTS.  Paste it into the 
SQL Editor and change the name of it to ALL_ARGUMENTS_OLD so you have a backup. 
Run the script and verify that the view was created and is valid. 
3) run this: 

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)) 
/ 

4) Toad should behave again.  I really doubt this will cause any problems with 
oracle.  It's just a read-only view.  But If you encounter any other problems, 
you can revert back to the old view in step 2). 

 :Smilie:

----------

