Results 1 to 8 of 8

Thread: 'IN' is not a valid integer value

  1. #1
    Join Date
    Jul 2007
    Posts
    4

    '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;

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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

  3. #3
    Join Date
    Oct 2005
    Posts
    2,557
    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

  4. #4
    Join Date
    Jul 2007
    Posts
    2
    Update your Toad for 8.6.1 at least

  5. #5
    Join Date
    Jul 2007
    Posts
    2
    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

  6. #6
    Join Date
    Oct 2005
    Posts
    2,557
    At least what? That has nothing whatsoever to do with the code.

  7. #7
    Join Date
    Jul 2007
    Posts
    2
    You could just modify your INSERT to be:

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

  8. #8
    Join Date
    Aug 2007
    Posts
    1

    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
  •