Cron job failing

[pnrdba]

Hi All
I'm having oracle 9i on Linux 7.2 . I got a procedure which has to run on every saturday. Presently i'm running as..

SQL> Exec weekly_reports.Psr_weekly_cr('mm/dd/yyyy');

(Input parameter should be a sysdate).

Can any one help me out how can i automate this script and put this under cron?? please give help me for complete script.
This should connect to a schema by name XYZ and then execute.

This is most urgent , any help for me,

Thanks in advance

[marist89]

man cron

[sreddy]

Use DBMS_JOB package


http://otn.oracle.com/docs/products/.../jobq.htm#5727

[pnrdba]

Thanks for your prompt response . But my problem, is this procedure in turn calls another procedure and do lots of modfications, then selects from many different tables with join conditions and returns huge number of rows . I couldn't understand where can we use this JOB_QUEUES. Please let me know in detail.

[sreddy]

JOB QUEUE is background process you have to configure it in init.ora parm job_queue_processes=n

http://docs.oracle.com/cd_database_g...169.htm#16275.

It really doen't matter if your procedure calls another procedure which does lots of processing.

If you really want to use crontab. set up environment and then login to SQLPLUS and executer the procedure.

exec_proc.sh
-------------------
#!/bin/ksh
ORACLE_SID=XYZ1; export ORACLE_SID
ORACLE_HOME=/oracle/orahome/product/8.1.7; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin; export PATH

sqlplus userid/passwd@xyz1 @exec_proc.sql

exec_proc.sql
-------------------
set serveroutput on
execute weekly_reports(dd/mm/yy) ;