Oracle Procedure Execution Error

[Malru]

Hi,

We're using PL/SQL Developer 5.0 and Oracle version is 9.2.0.8.

We're facing problem during a procedure execution, in PL/SQL Developer it's getting executed successfully,but in SQL* Plus it's giving the following error,

begin prepayment('Y','601','N','G','01/04/2007','30/04/2007','601'); end;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "CW.PREPAYMENT", line 452
ORA-06512: at line 1

What could be the reason for getting this error in SQL* Plus but not in PL/SQL Developer?

Thanks...

 

[Dezsoe Kados]

One possibility is, that the date fields are interpreted in a different way when using SQL*Plus...

 

[Marco Kalter]

My guess is that SQL*Plus is in a different Oracle Home (or even on a different PC). These implicit character to date conversions depend on the NLS_DATE_FORMAT of the Oracle Home.

To be independent of these external factors, use to_date with an explicit date format:

to_date('01/04/2007', 'dd/mm/yyyy')