Create Job: Oracle 10g

Today I researched something, our Apprentice asked.

To create an automatic job in Oracle 10g we will use the DBMS_SCHEDULER package.

First you create a program, that holds the activity you wish to perform

SQL> BEGIN
2 SYS.DBMS_SCHEDULER.CREATE_PROGRAM
3 (
4 program_name => 'SYS.TEST_JOB_PROG',
5 program_type => 'PLSQL_BLOCK',
6 program_action => 'begin
7 update test_job_table set LOHN=LOHN+100 where name=''Heinz''; -- Zwei single quotes!
8 end;'
9 number_of_arguments => 0,
10 enabled => TRUE,
11 comments => 'Test increase of table value',
12 );
13 /

PL/SQL procedure successfully completed.

Then you create the schedule:

SQL> begin
2 dbms_scheduler.create_schedule(
3 repeat_interval => 'FREQ=HOURLY;BYMINUTE=5,10,15,20,25,30,35,40,45,50,55,0', -- Erläuterungen
4 schedule_name => '"test_job_sched"');
5 end;
6 /

PL/SQL procedure successfully completed.

In the end you put connect the program with the schedule in a job

SQL> begin
2 dbms_scheduler.create_job(
3 job_name=> '"test_job_job"',
4 program_name=>'test_job_prog',
5 schedule_name=>'"test_job_sched"',
6 job_class=>'DEFAULT_JOB_CLASS',
7 auto_drop=>FALSE,
8 enabled=>TRUE);
9 end;
10 /

PL/SQL procedure successfully completed.

Source