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!

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.