ORA-01722 during Procedure execution

[malru]

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

[davey23uk]

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

[malru]

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?

[bhattnirav]

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.

[PAVB]

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.

[malru]

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

[davey23uk]

you need to compare your data and structures and work out why its failing - its called debugging