DBMS_JOB not running, urgent please help
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: DBMS_JOB not running, urgent please help

  1. #1
    Join Date
    Apr 2002
    Posts
    291
    Hi Guys,
    I created a job by using DBMS_JOB.SUBMIT(....);
    when i say print jobno:
    JOBNO
    ------
    21

    Now i want to run this job no 21 on every sunday morning 03:00:00 am. i tried it using dbms.next_date(sysdate,'sysdate+7);
    but where to give sunday and time also??

    Can any one help me how to put my job to run on every sunday ,please.

    It would be very helpful if i can get sequence of steps. becuase i've to do it on production which in turn calls some other packages.
    PNRDBA

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Az interval value, set 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY"")+3/24'
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Sep 2001
    Posts
    62
    Hi Try this,

    DBMS_JOB.SUBMIT (:v_jobnum,'my_procedure;', sysdate,
    'TRUNC(NEXT_DAY(SYSDATE,''SUNDAY'')+3/24');


    Shaz



  4. #4
    Join Date
    Apr 2002
    Posts
    291
    Guys,
    This is not wroking, i tried in both the ways. Here i'm pasting the eror messages:

    PROD>>VARIABLE jobno NUMBER
    PROD>>begin
    2 DBMS_JOB.SUBMIT(:jobno,
    3 'CALL_WEEKLY;',
    4 SYSDATE,'SYSDATE + 7');
    5 commit;
    6 end;
    7 /

    PL/SQL procedure successfully completed.

    PROD>>begin
    2 DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
    3 sysdate,
    4 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY"")+3/24');
    5 end;
    6 /
    begin
    *
    ERROR at line 1:
    ORA-23319: parameter value "NEXT_DAY(TRUNC(SYSDATE),'SUNDAY"")+3/24" is not
    appropriate
    ORA-06512: at "SYS.DBMS_JOB", line 57
    ORA-06512: at "SYS.DBMS_JOB", line 132
    ORA-06512: at line 2


    PROD>>VARIABLE jobno NUMBER
    PROD>>begin
    2 DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
    3 sysdate,
    4 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY"")+3/24');
    5 end;
    6 /
    begin
    *
    ERROR at line 1:
    ORA-23319: parameter value "NEXT_DAY(TRUNC(SYSDATE),'SUNDAY"")+3/24" is not
    appropriate
    ORA-06512: at "SYS.DBMS_JOB", line 57
    ORA-06512: at "SYS.DBMS_JOB", line 132
    ORA-06512: at line 2

    Please figure out my problem, thanks in advance

    PNRDBA

  5. #5
    Join Date
    Sep 2000
    Posts
    305
    execute this command

    begin
    ret number;
    DBMS_JOB.SUBMIT(ret, 'CALL_WEEKLY;',sysdate,
    'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY")+3/24');
    commit;
    end;


    Hope this will work
    Shailendra

  6. #6
    Join Date
    Apr 2002
    Posts
    291
    Thanks for your reply Shailendra,
    But this

    execute this command

    begin
    ret number;
    DBMS_JOB.SUBMIT(ret, 'CALL_WEEKLY;',sysdate,
    'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY")+3/24');
    commit;
    end;

    is also not working....can you please help me with any other solution..

    Guys, Please through some light on this problem. I'm still struggling with this.

    Thanks in advance
    PNRDBA

  7. #7
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Has you got an error?
    Otherwise if nothing happens you should check
    the init.ora param for queuung

    Code:
    job_queue_processes -- greater 0
    job_queue_intervall -- interval in seconde

    Orca

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Pnrdba,

    There has been a typo in Julian's message that you have addopted in all of your following tries. There should be two single quote characters around SUNDAY, before and after it. In Julian's message there are two single quotes before and two double quotes after the name of the day. So the following should work for you (if you have NLS_LANGUAGE set to AMERICAN):
    Code:
    begin
      DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
      sysdate,
      'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');
    end;
    /
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    10x Jurij! Yes, there was a typo.

    To pnrdba: Adopt the code above (by J.M.).
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Apr 2002
    Posts
    291
    Thanks a lot Jurij,
    I'll try it now, and get back to you.

    Thanks once again
    PNRDBA

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