Scheduling Stored Procedure in Cron

[do8good]

Hello All,

I need help with a stored procedure. I have little experience with stored procedure. Here is what I deal with -

Hello Scott,
The stored procedure to run is:

GLOVIA_PROD.DIVTREND_PROC('EPM',
TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM'),
TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY'));


This should run on the 1st of every month starting July 1, 2008. It should be scheduled to start in the early AM, around 1AM.
If possible, you may adjust the start time to avoid any other processing that might be happening at that time, but it shouldn't start later than 3AM.

What sort of editing should I do before scheduling this in cron? Here is what I have for now -

#./usr/bin/ksh
############################################################################
# proc.sh
############################################################################
#PURPOSE:
#
# Date Reason
# ------ -------- ----------------------------------------------------------
# 07/08 Created for Cerner Corp.
#
############################################################################
#
# MAILTO="dba888@yahoo.com"
# MAILTO="oracle"
#
#
cd /oracle/admin/PROD/admin
#
# Set up environment variables:
#
PROD_SCRIPTS=/oracle/admin/PROD/admin

export PROD_SCRIPTS

#
ORACLE_SID=PROD
ORACLE_HOME=/oracle/product/817
# ORACLE_BASE=/u001/app/oracle
#
export ORACLE_SID ORACLE_HOME
# export ORACLE_BASE
PATH=$ORACLE_HOME/bin:$PROD_SCRIPTS:$PATH
export PATH
#
# TWO_TASK= # just to be sure we're connected local!!!
#
export TWO_TASK
#
# execute procedure:
#
sqlplus ' /as sysdba'< $PROD_SCRIPTS/proc.log
Cerner_PROD.DIVTREND_PROC('EPM',
TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM'),
TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY'));
eof
exit


Please let me know where I am wrong? Please help?

[LKBrwn_DBA]

...
What sort of editing should I do before scheduling this in cron?

Better use DBMS_JOB and/or DBMS_SCHEDULER PL/SQL procedures.

[do8good]

No I am not crazy but your question made my day. I couldn't stop laughing. Okay on a serious note - pls lead me on using DBMS_SCHEDULER to do this.

[davey23uk]

dbms_job is what you want on 8i