Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: Dmbs_job - Interval

    Hi !
    I have two variables :
    1. vd_date - start of the JOB
    2. frequency - 1 = one time a month
    2 = one time twice a month
    3 = one time in three months
    Example 1 :
    vd_date = 2003.01.08
    frequency = 1
    Job will start : 2003.01.08
    2003.02.08
    2003.03.08


    Example 2 :
    vd_date = 2003.01.08
    frequency = 2
    Job will start : 2003.01.08
    2003.03.08
    2003.05.08

    Example 3 :
    vd_date = 2003.01.08
    frequency = 3
    Job will start : 2003.01.08
    2003.04.08
    2003.07.08

    I've tried a few times but every time it wasn't it
    Thx

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    What exactly is the problem here ?
    I don't see it.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Posts
    5

    The problem is

    how to set an interval using this two variables :
    vd_date
    frequency

    Job has to work like in these examples.

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    I assume you want a procedure to submit a job with a given date and frequency and interval.

    create astored procedure that accepts parameters and code it like :


    begin
    dbms_job.isubmit( lv_jobnumber, parameter_what,parameter_date, parameter_interval);
    commit;
    end;

    Just be sure you use correct formats when entering the date's.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Sep 2003
    Posts
    5

    Yes !!!

    dbms_job.isubmit( :v_jobnumber, parameter_what,parameter_date, parameter_interval);

    Yes, I know it ! But I'm asking how should it look like this
    parameter_interval ??? I've tried use functions
    last_day and trunc in this parameter_interval and it didn't work like in my examples.

  6. #6
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    OK I get it.
    Well the interval is not declared as a DATE datatype in the DBMS_JOB.ISUBMIT procedure. It is declared as a VARCHAR2 and INTERVAL is evaluated by plugging it into the statement ' select INTERVAL into next_date from dual;'
    So tha value should be something like 'sysdate+1' (next day) or trunc(sysdate,'MM') (first day of month) or something like that.

    You can check the syntax by doing 'select INTERVAL from dual' using SQL*plus.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •