-
Scheduling Stored Procedure in Cron
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?
-
Are you crazy?
...
What sort of editing should I do before scheduling this in cron?
Better use DBMS_JOB and/or DBMS_SCHEDULER PL/SQL procedures.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
-
dbms_job is what you want on 8i
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|