dbms_job
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: dbms_job

  1. #1
    Join Date
    Sep 2000
    Posts
    305
    HI THERE IS ONE PROBLEM

    I AM USUING DBMS_JOB PACKAGE WHEN I CREATE A JOB FOR EVERY THREE MINUTES IT IS WORKING BUT WHEN I SPECIFY FOR A DAY i.e SUNDAY IT IS NOT WORKING IT IS GIVING ERROR

    ORA-23319: parameter value "TRUNC(NEXT_DAY(SYSDATE,"TUESDAY"))" is not appropriate

    THIS IS MY PL/SQL BLOCK

    DECLARE
    BEGIN
    DBMS_JOB.ISUBMIT(14,'SP_TEST_UPDATE;',SYSDATE,'TRUNC(NEXT_DAY(SYSDATE,"TUESDAY"))');
    COMMIT;
    END;

    PLEASE HELP ME THIS IS URGENT

    THANKS IN ADVANCE

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi

    Try this DBMS_JOB.ISUBMIT(14,'SP_TEST_UPDATE;',SYSDATE,'TRUNC(NEXT_DAY(SYSDATE,'TUESDAY'))');


    The interval parameter must evaluate to a time in the future. Legal intervals include:

    'sysdate + 7'
    Run once a week.

    'next_day(sysdate,''TUESDAY'')'
    Run once every Tuesday.

    'null'
    Run only once.




    You have to use single quotes not double..on tuesday

    regards
    Hrishy



    [Edited by hrishy on 04-16-2002 at 04:51 AM]

  3. #3
    Join Date
    Mar 2002
    Posts
    25
    Hi

    try selecting the next date into a variable and then pass the variable when executing. eg.
    DECLARE
    x integer;
    next_date date;
    BEGIN
    select TRUNC(NEXT_DAY(SYSDATE,'TUESDAY'))
    into next_date from dual;
    DBMS_JOB.SUBMIT(x,'SP_TEST_UPDATE;',SYSDATE,next_date);
    COMMIT;
    END;
    /

    I hope this solves your problem.


  4. #4
    Join Date
    Sep 2000
    Posts
    305
    first of all thanks to you

    but what is the difference between dbms_job.submit and dbms_job.isubmit?

    thanks again
    Shailendra


  5. #5
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello

    There is no Isubmit proceedure at all only dbms_job.submit proceedure.

    regards
    Hrishy

  6. #6
    Join Date
    Sep 2000
    Posts
    305
    moderator please have a look on this thread and reply it

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by shailendra
    ORA-23319: parameter value "TRUNC(NEXT_DAY(SYSDATE,"TUESDAY"))" is not appropriate

    THIS IS MY PL/SQL BLOCK

    DECLARE
    BEGIN
    DBMS_JOB.ISUBMIT(14,'SP_TEST_UPDATE;',SYSDATE,'TRUNC(NEXT_DAY(SYSDATE,"TUESDAY"))');
    COMMIT;
    END;
    Don't use double quotes arround TUESDAY. Use two single quotes instead, like ''TUESDAY''. Note - '' are two characters (two single quotes), not one character like in your case (one double quote character).

    Besides, as allready told, better use SUBMIT instead of ISUBMIT (although ISUBMIT is totaly valid procedure inside DBMS_JOB package). The main difference between the two is that in SUBMIT oracle chooses its own job_id from the sequence and returns it to you as an output parameter, while with ISUBMIT you define the job_id. In this case you must be aware of which job ids are allrewady in use, otherwise your job will be rejected.

    So, taking both things into account, your job submition should look something like:

    Code:
    DECLARE 
      my_job BINARY_INTEGER;
    BEGIN 
      DBMS_JOB.SUBMIT(my_job, 'SP_TEST_UPDATE;', SYSDATE, 'TRUNC(NEXT_DAY(SYSDATE,''TUESDAY''))'); 
      COMMIT; 
    END;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Sep 2000
    Posts
    305
    thanks jmodic

    I have last question that when I have truncat the date then why it is firing right now I want to execute it on every tuesday night

    what do I do?

    Thanks again

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You specified your parameter NEXT as SYSDATE, that means Oracle will execute this job immediately after it is submitted. You have used TRUNC() only in your INTERVAL parameter. So if you want it to be run on midnight next Tuesday and then any Tueasday at midnight, you should use this:

    DBMS_JOB.SUBMIT(my_job, 'SP_TEST_UPDATE;', TRUNC(NEXT_DAY(SYSDATE,'TUESDAY')), 'TRUNC(NEXT_DAY(SYSDATE,''TUESDAY''))');
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Sep 2000
    Posts
    305
    HI STILL THERE IS SOME PROBLEM CAN ANYBODY HELP ME

    I HAVE SET THIS JOB FOR FRIDAY BUT IT SHOULD GET EXECUTED AS SOON AS FRIDAY STARTED OR YOU CAN SAY THURSDAY END.

    BUT IT IS NOT HAPPENING IT EXECUTED IN THE NOON AT 12:00:11 BUT I WANTED IT IN THE NIGHT

    PLEASE TELL ME THE SOLUTION AS SOON AS POSSIBLE

    THANKS

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