Hi All,

I am running the script on Oracle8i under AIX.

I have a pl/sql script(procedure) which should run for every week ie on monday. I need to put this script file(procSample.sql located at /usr/myProc directory for ex.) in a cron job. I dont want to go for dbms_jobs utility provided by oracle. My req is to put it in cron.

Can any one suggest me in doing this one in detail.
Thanks in advance

Hi,
I dont know how to use crontab exactly on AIX. But I think it is realy the same as SCO Unix.

unload your crontab by using "crontab -l > mycron"
Now edit the file mycron and append following row.
30 1 * * 1 /usr/myProc/procSample.sql
It means : execute the procSample.sql every Monday at 1:30 .

Now save the file and load the crontab with the command
"crontab mycron" on Unix Shell.

Check the effect by typing "crontab -l" again.

Remember that there is no empty row at the end of your mycron file.
Otherwise an error occur.

More help you will get by typing "man crontab" on your AIX-Unix Shell.

...only it won't work since you can't ask shell to execute an sql script. You'll need to invoke sqlplus to run the script.

1) type "crontab -e" to edit cron table: this will launch your visual editor (I guess default would be vi)
2) create an entry in it similar to this:
30 1 * * 1 . /home/yourloginid/.profile;sqlplus @/usr/myProc/procSample.sql
3) save the cron table
4) enjoy.

Please note that you have to execute your profile explicitly before attempting to run sqlplus (assuming your profile contains all necessary Oracle environment variables).

Hi n_i,

Thanks for your reply but i have one doubt over here.

You gave the following script:

30 1 * * 1 . /home/yourloginid/.profile;sqlplus @/usr/myProc/procSample.sql


Is it not necessary to mention UserId, Password and Connection String to connect to oracle in the above line?
How it will be connected?

Well, yes. Obviously you need to connect. You could either specify it on the command line or use "/nolog" option and connect from your script...

Hi n_i

can u pls make it more clear...

in my pl/sql i sud write /nolog ? thats what u r mentioning or
see the following

30 1 * * 1 . /home/yourloginid/.profile;sqlplus /nolog @/usr/myProc/procSample.sql


i didnt get you properly

You can connect to a database by two methods, as you probably know:

1) specify userid, password, and service name on the sqlplus command line:

sqlplus userid/pass@svcname @script.sql

2) specify /nolog on the sqlplus command line, like this:
sqlplus /nolog @script.sql

and then use CONNECT command within the script

See SQLplus reference manual for more details