Sid Khanna
asked on
Dbms_job.change procedure
Hi ,
I am having trouble scheduling a job to run on the 1st of every quarter even if 1st of the month is Saturday.
Here is what I am working with:
BEGIN
DBMS_JOB.CHANGE
(20,
null,
null,
trunc(NEXT_DAY(ADD_MONTHS( TRUNC(SYSD ATE , '''Q'''),3)-1,'SATURDAY')) +(06/24)+( 00/1440)
);
commit;
end;
/
ERROR at line 9:
ORA-01899: bad precision specifier
ORA-06512: at line 2
Your help is greatly appreciated.
Sid
I am having trouble scheduling a job to run on the 1st of every quarter even if 1st of the month is Saturday.
Here is what I am working with:
BEGIN
DBMS_JOB.CHANGE
(20,
null,
null,
trunc(NEXT_DAY(ADD_MONTHS(
);
commit;
end;
/
ERROR at line 9:
ORA-01899: bad precision specifier
ORA-06512: at line 2
Your help is greatly appreciated.
Sid
ASKER
Thank you for your quick response.
At the moment, cannot move to dbms_scheduler. Do you suggest a solution to above issue.
Thanks
Sid
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(SYSD ATE , 'Q'),3)-1,'SATURDAY'))+(06 /24)+(00/1 440)
trunc(NEXT_DAY(ADD_MONTHS(
>>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.
The trunc statement seems to return the first Saturday of the next Quarter. Your description seems to say something different.
ASKER
BEGIN
DBMS_JOB.CHANGE
(20,
null,
null,
trunc(NEXT_DAY(ADD_MONTHS( TRUNC(SYSD ATE , 'Q'),3)-1,'SATURDAY'))+(06 /24)+(00/1 440
);
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
DBMS_JOB.CHANGE
(20,
null,
null,
trunc(NEXT_DAY(ADD_MONTHS(
);
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_M ONTHS(TRUN C(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
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_M
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(SYSD ATE , 'Q'),3)-1,'SATURDAY'))+(06 /24)+(00/1 440)
The doc link for CHANGE:
http://docs.oracle.com/database/121/ARPLS/d_job.htm#ARPLS66571
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(
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.
ASKER
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(N EXT_DAY(AD D_MONTHS(T RUNC(SYSDA TE , ''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.
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','
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(SYS DATE , 'Q'),3))
trunc(ADD_MONTHS(TRUNC(SYS
ASKER
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
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(T RUNC(SYSDA TE , 'Q'),3)),'MM/DD/YYYY HH24:MI:SS') from dual
07/01/2017 00:00:00
select to_char(trunc(ADD_MONTHS(T
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(SYS DATE , 'Q'),3))+6/24
trunc(ADD_MONTHS(TRUNC(SYS
ASKER
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,nu ll,'trunc( NEXT_DAY(A DD_MONTHS( TRUNC(SYSD ATE , ''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.
EXECUTE DBMS_JOB.CHANGE(20,null,nu
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.
The 4th parameter to dbms_job.change is the interval NOT the next_date.
The statement you just posted changed the interval.
ASKER
Thank you for your help. It is working now
For example you could set: BYMONTH=JAN,APR,JUL,OCT