OraFAQ Forum: SQL & PL/SQL » Scheduling stored procedure (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Scheduling stored procedure (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Scheduling stored procedure (merged) [message #330619]

Mon, 30 June 2008 10:45

youngb912
Messages: 56
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?

Report message to a moderator

Re: Create & Schedule Store Procedure through Cron [message #330624 is a reply to message #330619]

Mon, 30 June 2008 11:20

Frank
Messages: 7901
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.

Report message to a moderator

Re: Create & Schedule Store Procedure through Cron [message #330626 is a reply to message #330624]

Mon, 30 June 2008 11:23

youngb912
Messages: 56
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.

Report message to a moderator

Re: Create & Schedule Store Procedure through Cron [message #330627 is a reply to message #330619]

Mon, 30 June 2008 11:24

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Create & Schedule Store Procedure through Cron [message #330633 is a reply to message #330627]

Mon, 30 June 2008 12:04

youngb912
Messages: 56
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.

Report message to a moderator

Scheduling stored procedure [message #330652 is a reply to message #330619]

Mon, 30 June 2008 13:16

youngb912
Messages: 56
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?

Report message to a moderator

Previous Topic:	ORA-01486
Next Topic:	How to load special characters into oracle database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 17:12:44 CST 2024