Dbms_job.change procedure

I would strongly suggest you move to DBMS_SCHEDULER.  The frequency is a lot more flexible.

For example you could set: BYMONTH=JAN,APR,JUL,OCT

Thank you for your quick response.
At the moment, cannot move to dbms_scheduler.  Do you suggest a solution to above issue.


Thanks
Sid

try removing the triple single quotes:
trunc(NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3)-1,'SATURDAY'))+(06/24)+(00/1440)

>>run on the 1st of every quarter even if 1st of the month is Saturday

The trunc statement seems to return the first Saturday of the next Quarter.  Your description seems to say something different.

BEGIN
 DBMS_JOB.CHANGE
 (20,
 null,
 null,
trunc(NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3)-1,'SATURDAY'))+(06/24)+(00/1440
 );
 commit;
 end;
 /

Error:
BEGIN
*
ERROR at line 1:
ORA-23319: parameter value "01-JUL-17" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 60
ORA-06512: at "SYS.DBMS_JOB", line 238
ORA-06512: at "SYS.DBMS_JOB", line 194
ORA-06512: at line 2

Hi slightwv,

The procedure executed, but the output of the shows 08-JUL-17, when expected output is 01-JUL-17.
Your help is appreciated.

EXECUTE DBMS_JOB.CHANGE(121, null, null,'trunc(NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE , ''Q''),3)-1,''SATURDAY''))+(06/24)+(00/1440)');

PL/SQL procedure successfully completed.

 select job,next_date from dba_jobs
  2  where job=20;

       JOB NEXT_DATE
---------- ---------
       20 08-JUL-17

You changed the interval not the next_date.

4th parameter is interval.  3rd parameter is next_date.

If you wanted next_date, then it does require a date not a string so it is the previous example I posted:
trunc(NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3)-1,'SATURDAY'))+(06/24)+(00/1440)


The doc link for CHANGE:
http://docs.oracle.com/database/121/ARPLS/d_job.htm#ARPLS66571

next_date
Next date when the job will be run.

interval
Date function; evaluated immediately before the job starts running.

Hi,

We want this to run on 1st of every quarter even if 1st falls on a Saturday. the next date it should run is on 1st July 2017 which falls on a Saturday and then every quarter after that.


EXECUTE DBMS_JOB.CHANGE(20, null,TO_DATE('01-JUL-17','DD-MON-YY'),'trunc(NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE , ''Q''),3)-1,''SATURDAY''))+(06/24)+(00/1440)');

PL/SQL procedure successfully completed.

select next_date from dba_jobs where job=20;

NEXT_DATE
---------
01-JUL-17

I have manually hard coded next date, but I want this to run the run quarter. I would like it to run on October 1st  and next quarter without hard coding the date.
Your help & guidance is appreciated.

I think this is what you need:
trunc(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3))

I tried what you said and now the time does not show as it should be for the job.

IT SHOULD SHOW THIS
-----------------------------------------
TO_CHAR(NEXT_DATE,
------------------
01-JUL-17 06:00:00

SHOWS THIS WHICH IS WRONG
TO_CHAR(NEXT_DATE,
------------------
01-JUL-17 00:00:00

If I select that using SQL, I get the correct result:
select to_char(trunc(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3)),'MM/DD/YYYY HH24:MI:SS') from dual

07/01/2017 00:00:00

Sorry I misread the above post.  If you want 6AM, just use the 6/24 math from your original post:
trunc(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3))+6/24

I haven't changed anything from my end for the time to show 6am. I am pasting my latest execute statement and the select statement and time is showing 00:00:00

EXECUTE DBMS_JOB.CHANGE(20,null,null,'trunc(NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE , ''Q''),3)-1,''SATURDAY''))+(06/24)+(00/1440)');
PL/SQL procedure successfully completed.

NOT SHOWING 6AM
-----------------------------------------
select job, to_char(next_date, 'DD-MON-YY HH24:MI:SS') from dba_jobs where job=20;

JOB TO_CHAR(NEXT_DATE
---------- ------------------ --------------------------------------------------
       121 01-JUL-17 00:00:00

Please advise on time showing 6am for the job.
Your help and guidance is appreciated.

As I posted above:
The 4th parameter to dbms_job.change is the interval NOT the next_date.

The statement you just posted changed the interval.

Thank you for your help. It is working now