Here, I will create a simple program and then assign it to the job.
Here, the SQL for creating the program:
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'"TEST"."TEST_PROGRAM"',
program_action=>'begin
insert into test_table values(test_seq.nextval,sysdate);
commit;
end;',
program_>'PLSQL_BLOCK',
number_of_arguments=>0,
comments=>'just a test program',
enabled=>TRUE);
END;
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'"TEST"."TEST_PROGRAM"',
program_action=>'begin
insert into test_table values(test_seq.nextval,sysdate);
commit;
end;',
program_>'PLSQL_BLOCK',
number_of_arguments=>0,
comments=>'just a test program',
enabled=>TRUE);
END;
I will assign this program to a new job which will be started every 2 minutes:
Here, you can see, that its working:
SQL> truncate table test_table;
Table truncated.
SQL> select * from test_table;
no rows selected
SQL> select i,to_char(datum,'dd.mm.yyyy hh24:mi:ss') "Date" from test_table;
I Date
---------- -------------------
11 29.11.2011 14:54:00
12 29.11.2011 14:55:59
13 29.11.2011 14:57:59
14 29.11.2011 14:59:59
Here the SQL for creating the job:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TEST"."TEST_JOB"',
program_name => '"TEST"."TEST_PROGRAM"',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => systimestamp at time zone 'Europe/Prague',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'just a test job',
auto_drop => FALSE,
enabled => TRUE);
END;
The documentation for the programs:
oracle documentation.
SQL> truncate table test_table;
Table truncated.
SQL> select * from test_table;
no rows selected
SQL> select i,to_char(datum,'dd.mm.yyyy hh24:mi:ss') "Date" from test_table;
I Date
---------- -------------------
11 29.11.2011 14:54:00
12 29.11.2011 14:55:59
13 29.11.2011 14:57:59
14 29.11.2011 14:59:59
Here the SQL for creating the job:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TEST"."TEST_JOB"',
program_name => '"TEST"."TEST_PROGRAM"',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => systimestamp at time zone 'Europe/Prague',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'just a test job',
auto_drop => FALSE,
enabled => TRUE);
END;
The documentation for the programs:
oracle documentation.