'IN' is not a valid integer value

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;

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

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

Update your Toad for 8.6.1 at least

Quote:

---

Originally Posted by 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;

---

Update your Toad for 8.6.1 at least

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

You could just modify your INSERT to be:

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

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

:)