Question

Scheduled job for Oracle 9 returning ORA-01858

ORA-01858, Oracle Scheduler, Stored procedures, Oracle 9

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?

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.