using cron to execute sqlscript in oracle

Using cron to run sql script is a good idea to apply with a periodic database job. When running any scripts, cron doesn't know any environment variable. So, in the script, we always
-use full path with execute command.
-define all environment variable.

ex. to see data in customer table in oracle database and log in customer_rpt.log, I create sql script[testsc1.sql] like this

[oracle@oracle1 ~]$ cat testsc1.sql
spool /home/oracle/customer_rpt.log
select * from customer;
quit;
[oracle@oracle1 ~]$ 

next create shell script file to run sql script file.

[oracle@oracle1 ~]$ cat mydbshell.cron
#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1; export ORACLE_HOME;
ORACLE_SID=my_sid; export ORACLE_SID;
/u01/app/oracle/product/10.1.0/db_1/bin/sqlplus user/password @/home/oracle/testsc1.sql
[oracle@oracle1 ~]$ 

note that oracle_home, oracle_sid variable must be defined and sqlplus command called with full path.

Now, we define new cron job by 'crontab -e' in console and make it look like this[this is only example you need to modify it as you wish. to learn more about crontab type 'more crontab' ]

[oracle@oracle1 ~]$ crontab -l
0 4 * * * /home/oracle/mydbshell.cron
[oracle@oracle1 ~]$