DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-01722 during Procedure execution

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    ORA-01722 during Procedure execution

    Hi,

    There is a procedure in my database(Ora1),It's getting compiled
    successfully,when i execute it,it gives the following error:

    begin prepay('Y','601','Y','G','01/APR/2007','30/APR/2007','601'); end;

    ORA-01722: invalid number
    ORA-06512: at "PREPAY", line 466
    ORA-06512: at line 1


    There is another database(Ora2) in the same server,I created this procedue
    in this database & tried to execute it.In this database(Ora2),this procedure
    executed successfully.

    Why this procedure execution gives error in database "Ora1",why it's not
    giving error in database "Ora2"?

    Kindly give ur suggesstion to rectify this error so that this procedure can
    be executed successfully in database "Ora1".

    Both the databases are running under same version(9.2.0.8)

    Thanks...

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    becuase somewhere you think you are using a number but are acdtually using a varchar (or another data type) you will have to debug it to find it. Luckily oracle tells you the line number of the procedure which is failing so you can see what query is being run

  3. #3
    Join Date
    Feb 2006
    Posts
    162
    Thanks davey...

    I'll check it out but Why the same procedure executed successfully in database "Ora2" not in database "Ora1".What could be the reason for this?

  4. #4
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    e.g. you are updating one column by converting char to number using to_number.

    In one database column contains all the numeric value and in 2nd database some rows may be of alphanumeric.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by malru
    Why the same procedure executed successfully in database "Ora2" not in database "Ora1".What could be the reason for this?
    Ora1 and Ora2 are not identical. Look at the definition of tables affected by prepay procedure.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Feb 2006
    Posts
    162
    Thanks All...

    This problem is occurring not only during particular procedure execution,We're getting this error almost for all procedures during its execution.

    These procedures were executing successfully last week,but now
    only it's giving problem.

    These two errors are occurring during procedure exec,

    Error-1
    ERROR at line 1:
    ORA-01722: invalid number
    ORA-06512: at "CW.INTRATE", line 285
    ORA-06512: at line 1


    Error-2
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "CW.INFORMATION", line 460
    ORA-06512: at line 1

    I doubt that some corruption has occurred in the database.
    How to fix this problem so that procedures will execute successfully?

    We're getting these errors not during procedure compilation but only during execution.

    Thanks...

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you need to compare your data and structures and work out why its failing - its called debugging

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width