Scheduled job for Oracle 9 returning ORA-01858

190 pts.
Tags:
ORA-01858
Oracle 9
Oracle Scheduler
Stored Procedures
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following