Shell Programming and Scripting

Greetings,

I am doing some work on using Oracle with Cron and was wondering if you could help. I am relatively new both to Oracle and Unix. Please be patient :-)

Here is what I am trying to do:

I wish to know the contents of employees table in scott account everyday at 11 am, save a copy at my directory /home/alikun and send another to a user at email address removed by moderator

This is what I did but couldn't get results:

1. Created a vi script, scottcron.sql, containing the query:

spool /home/alikun
select * from employees;
exit;

2. wrote a separate script, scottshell, to execute the script above:

/home/alikun/Sqlplus scott/tiger @/home/alikun/scottrcron.sql

3. Finally, the cron script, mycron, as follows:

00 11 * * * “/home/alikun/scottshel” | mail -s “hi zalikun, this is your file” email address removed by moderator

crontab mycron

Please note: I did a bit of research on this and it seems that I would need two other things:

a) ORACLE_HOME=
b) B) ORACLE_SID=

How can I find the path for ORACLE_HOME in a), and the SID for b) on my unix system?.

Thanks all so very much,
Alikun

Hi, I don't know if the email address posted was a real one or not, but I have removed it anyway.

Now I am no DBA, but I think that you should be aware of the SID before you can run any SQL commands/scripts in any Oracle database. Once you know the SID, you can get ORACLE_HOME from the oratab file on your system. The location of the oratab file can change depending on the OS and the installtion. On HP systems, usually present in /etc and on Sun, in /var/opt/oracle. This may ofcourse differ.

If you want to run the script for every oracle instance that is running, just do a 'ps -ef | grep pmon | grep -v grep' to get the list of instances and proceed to run the script on those.

Blowtorch, thanks a lot for input; indeed oratab turned out to be under /etc. Yes, i knew the oracle sid was enterprise edition; was, however, unsure of how to reference it - seems to be EE.

No, the email address was not a real one, but do appreciate your sensitivity to privacy issues ;-)

Hello, I have a small example on how I call SQLPlus and PL/SQL procedures through cron. My example is located at:
<a href="http://timarcher.com/?q=node/48">http://timarcher.com/?q=node/48</a>

Hopefully it helps you!

Alikun

You also need to set spool off
$ sql> spool off

$ cat scottcron.sql
spool /home/alikun
select * from employees;
spool off
exit;