Configure Cron Job for Schedulers

Cron

"Cron" is a UNIX tool which works as time-based job scheduler . The cron jobs can be configured by writing cron expressions which , the scheduler can understand. In this article we will show sample examples of cron expressions to understand it's configuration. 'CronTrigger' class uses the scheduling capabilities of cron to trigger the scheduler functions.

The Cron Expression

The cron expressions are  strings of texts made up of six or seven values separated by white space. The values are actually field positions which refer to a particular unit of time. The values are restricted to a specific syntax and numbers along with some special characters.

Position/TimeUnit	Required	Restricted Values	Restricted Special Characters
1  Seconds	Yes	0-59	, - * /
2  Minutes	Yes	0-59	, - * /
3  Hours	Yes	0-23	, - * /
4  Day of month	Yes	1-31	, - * ? / L W
5  Month	Yes	1-12 or JAN-DEC	, - * /
6  Day of week	Yes	1-7 or SUN-SAT	, - * ? / L #
7  Year	No	empty, 1970-2099	, - * /

• '*' (all values):-wildcard used to mean all values in possible in the time unit.
  
  
• '?' (any specific value, only for day-of-month and day-of-week ):- like * but limited to the mentioned fields only
• '-'  (ranges of values):- like 1-2 would mean 1-2 units of time.
• ',' (separated specific values):- 1,5 would mean cron will run only on 1st and 5th unit of time.
• '/' (incremental value):- "0/10" would mean cron will run only on every 10th unit of time.
• 'L' (last unit of time) :- "L in Day of month" would mean cron will run only on the Last day of the month. Similarly last day of year, week.. etc .
• 'W' (nearest weekday) :- "10W" at the day-of-month field, would mean : "the nearest weekday to the 10th of the month"..

 note:- 'LW' at day-of-month field would mean last weekday of the month.

• '#'(specific day of month) -  "1#2" in the day-of-week field would mean : "the second Monday of the month".(i.e.daynumber#occurance in the month)
  

The Cron Expression Examples

 

Cron Expression	Time Meaning
0 0  13 * * ?	Run at 13pm daily.
0 20 9 ? * *	Run at 9:20am daily.
0 25 9 * * ?	Run at 9:25am daily.
0 25 6 * * ? *	Run at 6:25am daily.
0 5 11 * * ? 2013	Run at 11:5am daily, throughout the year 2013
0 * 15 * * ?	Run every minute, starting at 3pm and ending at 3:59pm, daily.
0 0/10 17 * * ?	Run every 10 minutes starting at 5pm and ending at 5:55pm, daily.
0 0/10 17,18 * * ?	Run every 10 minutes starting at 5pm and ending at 5:55pm, AND Run every 10 minutes starting at 6pm and ending at 6:55pm, daily.
0 0-10 10 * * ?	Run every 1 minute starting at 10am and ending in 10 minutes, daily.
0 10,20 10 ? 4 Fri	Run at 10:10am and at 10:20am every Friday in the month of April.
0 25 20 ? * MON-FRI	Run at 8:25pm every weekday.
0 20 9 10 * ?	Run at 9:20am on the 10th day of every month
0 20 9 L * ?	Run at 9:20am on the last day of every month
0 20 9 L-3 * ?	Run at 9:20am on the 3rd-to-last last day of every month
0 20 9 ? * 6L	Run at 9:20am on the last Friday of every month
0 20 9 ? * 6L	Run at 9:20am on the last Friday of every month
0 20 9 ? * 6L 2002-2005	Run at 9:20am on every last friday of every month during the years 2002, 2003, 2004 and 2005
0 20 9 ? * 6#3	Run at 9:20am on the third Friday of every month
0 0 13 1/15 * ?	Run at 13pm every 15 days every month, starting on the first day of the month.
0 12 12 12 12 ?	Run every December 12th at 12:12am.

Happy coding :)

How to select DISTINCT on a CLOB field.

Lets consider a table 'PREFERENCES' with two values 'ID' and 'PREFERENCES'. Where id is a Number and preferences is a CLOB field.

      ID   PREFERENCES
---------- ---------------------------------------------
         1 Oracle 
         2 Oracle 
         6 MSSQL
         7 MySQL
         8 Oracle

 

Now  if you want to find out how many distinct values are there in the Preference column.You might try to use;

SELECT DISTINCT PREFERENCES FROM PORTALPREFERENCES;

This will generate the following  SQL error:

ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 -  "inconsistent datatypes: expected %s got %s"> *Cause:    *Action: Error at Line: 3 Column: 17

If you then use

SELECT DISTINCT TO_CHAR(PREFERENCES) FROM PORTALPREFERENCES;

This will also generate error :-
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6608, maximum: 4000) 22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)" *Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where            the LOB size was bigger than the buffer limit for CHAR and RAW            types.            Note that widths are reported in characters if character length            semantics are in effect for the column, otherwise widths are            reported in bytes. *Action:   Do one of the following            1. Make the LOB smaller before performing the conversion,            for example, by using SUBSTR on CLOB            2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

as per the Error Description If you use

SELECT DISTINCT DBMS_LOB.SUBSTR(PREFERENCES) FROM PORTALPREFERENCES

Still, an SQL Error will be thrown , which is ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 06502. 00000 -  "PL/SQL: numeric or value error%s" *Cause:    *Action:

There are lot of ways to solve this problem.One of the simplest way is to increase the Buffer in the same sql . As displayed below.

SELECT DISTINCT   DBMS_LOB.SUBSTR(PREFERENCES,4000,1) FROM PORTALPREFERENCES;

This runs without error and gets you all the unique values of preferences column.

Happy Coding!!