Scheduling Stored Procedure in Cron
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Scheduling Stored Procedure in Cron

Hybrid View

  1. #1
    Join Date
    Jun 2008
    Posts
    2

    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?

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Cool 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

  3. #3
    Join Date
    Jun 2008
    Posts
    2
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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
  •  



Click Here to Expand Forum to Full Width