Home » SQL & PL/SQL » SQL & PL/SQL » Scheduling stored procedure (merged)
Scheduling stored procedure (merged) [message #330619] Mon, 30 June 2008 10:45 Go to next message
youngb912
Messages: 47
Registered: October 2007
Location: New York
Member
Hello Seniors,

I need help with a stored procedure. Here is the email I got from the CFO this morning. I have no experience with stored procude. I am currently doing myself favor by reading on it. You immediate help at this time will be appreciated!

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'<<eof > $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


With the above work? What should I do different?
Re: Create & Schedule Store Procedure through Cron [message #330624 is a reply to message #330619] Mon, 30 June 2008 11:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Tell your CFO that you are reading up on stored procedures and that it will take time before your assignment is done.
Ask him to send you on a training.
Don't pretend to have knowledge that you actually don't have.
Re: Create & Schedule Store Procedure through Cron [message #330626 is a reply to message #330624] Mon, 30 June 2008 11:23 Go to previous messageGo to next message
youngb912
Messages: 47
Registered: October 2007
Location: New York
Member
Thanks for the input. Your point is well taken. Any help that will help me get this done will be highly appreciated.
Re: Create & Schedule Store Procedure through Cron [message #330627 is a reply to message #330619] Mon, 30 June 2008 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>With the above work?
You are told that it looks OK.
You get to the office on 1 July & discover it has failed.
What do you do next?
Re: Create & Schedule Store Procedure through Cron [message #330633 is a reply to message #330627] Mon, 30 June 2008 12:04 Go to previous messageGo to next message
youngb912
Messages: 47
Registered: October 2007
Location: New York
Member
I am sorry if I didn't follow the guideline. It was not intentional. I will like to make changes to my current post but don't want to double post. Please forgive me and help in anyway possible.
icon4.gif  Scheduling stored procedure [message #330652 is a reply to message #330619] Mon, 30 June 2008 13:16 Go to previous message
youngb912
Messages: 47
Registered: October 2007
Location: New York
Member
Hello All,

I am reposting because I was told my previous post didn't follow the guideline. I have written to the moderator to remove that post.

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'<<eof > $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?
Previous Topic: ORA-01486
Next Topic: How to load special characters into oracle database
Goto Forum:
  


Current Time: Fri Dec 09 11:45:29 CST 2016

Total time taken to generate the page: 0.07239 seconds