Scheduled job for Oracle 9 returning ORA-01858
We have an Oracle 9.2.0.1.0 database and get the ORA-01858: a non-numeric character was found where a numeric was expected error.
Our production jobs are a series of stored procedures which are set up to run automatically by being called through another stored procedure, the Scheduler. While the Scheduler is running, two of them have the above error. However, when these two jobs are run as stand alone procedures they run successfully. Do you have any suggestions why this is happening and how to stop it?
Software/Hardware used:
Software/Hardware used:
ASKED:
April 10, 2008 3:05 PM
UPDATED:
August 4, 2010 5:20 AM
Answer Wiki:
Is the Scheduler passing parameters into the stored procedures? Are any values defined differently depending on the execution steps? If so, I would start with checking those values. Since the two do not error in stand alone mode, I would think the errors are not initiated from a database query. But, do not eliminate any option from trouble-shooting.
If possible, use dbms_output to write out all variable values at various stages within the code. PL/SQL doesn't have wonderful debugging tools, so writing out values is crude but helpful. Maybe someone else has some better tools to recommend.
Stored procedure may be having the date conversion functions like To_Date and To_Char. Please verify the NL_DATE_FORMAT in NLS_ENV column of DBA_JOBS , May be date conversion done in stored procedure is not compatible with the NL_DATE_FORMAT in NLS_ENV column of DBA_JOBS. So job is showing error. Recreate the job with proper NL_DATE_FORMAT.
--sangeeta
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
