- Create a PLSQL procedure
- Create a Shell script with proper Oracle mandatory environment variables like ORACLE_HOME, ORACLE_SID and PATH variables. Sometimes you have to add LIBRARY_PATH and etc. Because when you run from the cron it is considered as a new session and where you have to set all environment parameters. Then add the procedure execute statement with sqlplus command.
- Change the permissions of the shell script to executable one.
- Add a crontab entry with proper timings. In below example I have scheduled the script for everyday 2:51 AM in the morning.
- Now you can see the logs in the /home/oracle/cron directory after successful execution.
- 6. You can see the log file content whether it is success or not.