-
'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
-
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');
-
Fix for the 'IN' is not a valid integer value
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).
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|