Oracle : How to use repeat_interval in create_job of dbms_scheduler?

[bashish]

Hi,

Trying to execute a procedure repeatadly using job, but fails to execute.

The procedure, I am trying to execute is

create procedure p1 as
begin
insert into tab1 values ('ashish');
commit;
end;
/

where tab1 table is defined as

create table tab1( a varchar2(100) );

and the job I created as

begin
dbms_scheduler.create_job(
job_name => 'TEST_JOB'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin p1(); end;'
,repeat_interval => 'FREQ=MINUTELY; INTERVAL=2'
,enabled => TRUE
,auto_drop => FALSE
,comments => 'Test job');
end;
/


any help?

Thanks

[davide++]

Hi all.

I don't know the package dbms_scheduler.
I usually use the functions of package dbms_job to define an Oracle job (ie a procedure that is executed repeatadly between a fixed time interval).

To create a new Oracle job you can use dbsm_job.submit; the main parameters are
JOB: ID of the new job (it's returned by submit)
WHAT: store procedure to execute
NEXT_DATE: the next date when job will be run (usually SYSDATE)
INTERVAL: next time to execute the job
INTERVAL defines the time period between two executions of the job; it's a date function, for example
SYSDATE + 1 defines one day interval.
SYSDATE + 1/24 defines one hour interval.
SYSDATE + 1/1440 defines one minute interval (1440 = 24 * 60)
To start the executions of new Oracle job you can use dbms_job.run: this procedure take the job ID (that you has got by submit) as parameter, and force the execution of the job identified by job ID; you have to use both functions to activate the Oracle job.

See Oracle documentation for more information about other functions of dbms_job, that you can use to manage yours Oracle job.