I have a similar problem to thread 18264, only I couldn't get it to work.
I have a stored procedure which is called by a shell script program. When I run the stored procedure alone or through the shell script, it works fine with output text file.
However, if the shell script is executed by a scheduled job (a remote user), the stored procedure will never get executed. The DBA checks permissions on all the related tables and permissions are properly given.
Can anyone tell me why the stored procedure is not working when called by a scheduled job? How to fix this problem?
Typically these problems are cause by the environment being very minimal in cron jobs.
For example if you were using oracle, failing to initialize the environment variables used by Oracle such as ORACLE_HOME, ORACLE_SID, PATH and LD_LIBRARY_PATH will cause database code to fail.
Look in /var/mail under the owner of the cron job.
/var/mail/<owner>
It may be, as noted, an environmental setting which
could be a path issue. Normally you'll get an email
from cron if syslog.conf is configured to send users
messages about cron jobs. Typically if it's a path issue
you will see an error indicating a script or some command
was not found when you check the context of the email.
Thanks to Porter and bdsffl. I showed your suggestions to our DBA and unix admin, and finally got the environment variable resolved. A simple fix by our UA:
1) open up the .profile file
2) copy the line with environment setting (in our case: all_paths_list) to shell script which calls the stored procedure.