Tuesday, October 14, 2008

How to create a job in Oracle?

Creating jobs in data base are very practical when applications are closed or not running but business needs to run a sql script to do manupilations or calculations and send mails to users etc...

Syntax :-
Sys.dbms_job.submit(job: out binary_integer,what:in varchar2,next_date:in date,
Interval: in varchar2,
no_parse: in Boolean,instance : in binary _integer,force: in Boolean);


Example:-

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => '
insert into test
(
select decode(max(num),null,1,(max(num)+1)) from test);'
,next_date => to_date('10/11/2008 23:30:08','mm/dd/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+30)'
,no_parse => FALSE
);
:JobNumber := to_char(X);
END;

This job when once created, it inserts record into test table every time periodically.

No comments: