dbms_job

[shailendra]

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

[hrishy]

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]

[clarence]

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.

[shailendra]

first of all thanks to you

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

thanks again
Shailendra

[hrishy]

Hello

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

regards
Hrishy

[shailendra]

moderator please have a look on this thread and reply it

[jmodic]

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:

DECLARE 
  my_job BINARY_INTEGER;
BEGIN 
  DBMS_JOB.SUBMIT(my_job, 'SP_TEST_UPDATE;', SYSDATE, 'TRUNC(NEXT_DAY(SYSDATE,''TUESDAY''))'); 
  COMMIT; 
END;

[shailendra]

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

[jmodic]

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''))');

[shailendra]

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