What to do when a stored procedure works in SQL*Plus but fails in the scheduled job Oracle Tips by Burleson Consulting

Question: What do you do when a stored procedure works in SQL*Plus but fails in the scheduled job?

Answer: This was the question asked by one of our Oracle Forum members recently.  (If you haven't already, check out the forum at http://dbaforums.org/oracle/ )  This is a common error, and it's caused from different users sign-on hence privileges, or by incorrect environmental variable settings ($ORACLE_HOME).  You can check the exact error with the DBA_SCHEDULER_JOB_RUN_DETAILS command.

Are you using dbms_job or dbms_scheduler? See here, how to diagnose and fix a broken job: http://www.dba-oracle.com/t_fixing_broken_jobs.htm  Also, see here, working examples of scheduled jobs in the download from the book "Oracle Job Scheduling":  http://www.rampant-books.com/book_0501_scheduling.htm

When you execute a job via a cron or with dbms_scheduler, it's important to have the exact same sign-on privileges and environmental settings.  Also, check to see if you need the "grant CREATE EXTERNAL JOB" privilege.  As a test, I would first put the working procedure inside a shell script and get that working:

http://www.rampant-books.com/book_0701_shell_scripting.htm

Then, you can get it working via a scheduled job inside Oracle. . . .

Let us know what works!

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.