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
Are you using dbms_job or dbms_scheduler?
See here, how to diagnose and fix a broken job:
see here, working examples of scheduled jobs in the download from
the book "Oracle Job Scheduling":
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
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
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
for 30% off directly from the publisher.