| Repeat interval for DBMS_SCHEDULER [message #526898] |
Thu, 13 October 2011 10:35  |
 |
lucienlazar
Messages: 62
Registered: November 2010
Location: Romania
|
Member |
|
|
Hello, I have created a job using DBMS_SCHEDULER and I want it to run every 30 seconds:
begin
dbms_scheduler.create_job(job_name => 'jobu',
job_type => 'PLSQL_BLOCK',
job_action => 'begin jobee; end;',
repeat_interval => 'SYSTIMESTAMP + INTERVAL ''30'' SECOND',
enabled => true,
auto_drop => false,
comments => 'test job');
end;
My question is how can I take the value 30 from a configuration table? Let's say I have a query like select value from config_table where property = 'job_interval' that returns the number 30. How can I set this value to be the repeat interval for my job?
|
|
|
|
|
|
|
|
|
|
| Re: Repeat interval for DBMS_SCHEDULER [message #526941 is a reply to message #526903] |
Thu, 13 October 2011 14:57  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following includes the standard method of selecting a value into a variable from a table, then using that variable as part of a parameter, by concatenating it with the rest of the things that constitute the parameter. You could also concatenate the values to the variable before passing the variable, then just pass the variable name.
SCOTT@orcl_11gR2> create table config_table
2 (value number,
3 property varchar2 (15))
4 /
Table created.
SCOTT@orcl_11gR2> insert into config_table values (30, 'job_interval')
2 /
1 row created.
SCOTT@orcl_11gR2> create or replace procedure jobee
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
SCOTT@orcl_11gR2> declare
2 v_value config_table.value%TYPE;
3 begin
4 select value
5 into v_value
6 from config_table
7 where property = 'job_interval';
8 dbms_scheduler.create_job
9 (job_name => 'jobu',
10 job_type => 'PLSQL_BLOCK',
11 job_action => 'begin jobee; end;',
12 repeat_interval => 'SYSTIMESTAMP + INTERVAL ''' || v_value || ''' SECOND',
13 enabled => true,
14 auto_drop => false,
15 comments => 'test job');
16 end;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select repeat_interval
2 from all_scheduler_jobs
3 where job_name = 'JOBU'
4 /
REPEAT_INTERVAL
--------------------------------------------------------------------------------
SYSTIMESTAMP + INTERVAL '30' SECOND
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
| Re: Repeat interval for DBMS_SCHEDULER [message #526952 is a reply to message #526951] |
Thu, 13 October 2011 17:23 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Since dbms_scheduler does a commit, you will need to use an autonomous procedure and run that from the trigger. A drawback is that if the update to the table is rolled back, the alteration of the job interval is not. Please see the demonstration below.
SCOTT@orcl_11gR2> create table config_table
2 (value number,
3 property varchar2 (15))
4 /
Table created.
SCOTT@orcl_11gR2> insert into config_table values (30, 'job_interval')
2 /
1 row created.
SCOTT@orcl_11gR2> create or replace procedure jobee
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
SCOTT@orcl_11gR2> declare
2 v_value config_table.value%type;
3 begin
4 select value
5 into v_value
6 from config_table
7 where property = 'job_interval';
8 dbms_scheduler.create_job
9 (job_name => 'jobu',
10 job_type => 'PLSQL_BLOCK',
11 job_action => 'begin jobee; end;',
12 repeat_interval => 'SYSTIMESTAMP + INTERVAL ''' || v_value || ''' SECOND',
13 enabled => true,
14 auto_drop => false,
15 comments => 'test job');
16 end;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select repeat_interval
2 from all_scheduler_jobs
3 where job_name = 'JOBU'
4 /
REPEAT_INTERVAL
--------------------------------------------------------------------------------
SYSTIMESTAMP + INTERVAL '30' SECOND
1 row selected.
SCOTT@orcl_11gR2> create or replace procedure update_interval
2 (p_new_interval in config_table.value%type)
3 as
4 pragma autonomous_transaction;
5 begin
6 dbms_scheduler.set_attribute
7 ('jobu',
8 'repeat_interval',
9 'SYSTIMESTAMP + INTERVAL ''' || p_new_interval || ''' SECOND');
10 end update_interval;
11 /
Procedure created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace trigger config_table_aur
2 after update of value on config_table
3 for each row
4 when (new.property = 'job_interval')
5 begin
6 update_interval (:new.value);
7 end;
8 /
Trigger created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> update config_table
2 set value = 60
3 where property = 'job_interval'
4 /
1 row updated.
SCOTT@orcl_11gR2> select repeat_interval
2 from all_scheduler_jobs
3 where job_name = 'JOBU'
4 /
REPEAT_INTERVAL
--------------------------------------------------------------------------------
SYSTIMESTAMP + INTERVAL '60' SECOND
1 row selected.
SCOTT@orcl_11gR2> rollback
2 /
Rollback complete.
SCOTT@orcl_11gR2> select * from config_table
2 /
VALUE PROPERTY
---------- ---------------
30 job_interval
1 row selected.
SCOTT@orcl_11gR2> select repeat_interval
2 from all_scheduler_jobs
3 where job_name = 'JOBU'
4 /
REPEAT_INTERVAL
--------------------------------------------------------------------------------
SYSTIMESTAMP + INTERVAL '60' SECOND
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Repeat interval for DBMS_SCHEDULER [message #526953 is a reply to message #526952] |
Thu, 13 October 2011 17:38 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
A workaround for the rollback issue would be to use dbms_job.submit within the trigger to run the update_interval procedure without pragma autonomous_transaction. Dbms_job.submit will not run until or unless there is a commit. Please see the demonstration below.
SCOTT@orcl_11gR2> create table config_table
2 (value number,
3 property varchar2 (15))
4 /
Table created.
SCOTT@orcl_11gR2> insert into config_table values (30, 'job_interval')
2 /
1 row created.
SCOTT@orcl_11gR2> create or replace procedure jobee
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
SCOTT@orcl_11gR2> declare
2 v_value config_table.value%type;
3 begin
4 select value
5 into v_value
6 from config_table
7 where property = 'job_interval';
8 dbms_scheduler.create_job
9 (job_name => 'jobu',
10 job_type => 'PLSQL_BLOCK',
11 job_action => 'begin jobee; end;',
12 repeat_interval => 'SYSTIMESTAMP + INTERVAL ''' || v_value || ''' SECOND',
13 enabled => true,
14 auto_drop => false,
15 comments => 'test job');
16 end;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select repeat_interval
2 from all_scheduler_jobs
3 where job_name = 'JOBU'
4 /
REPEAT_INTERVAL
--------------------------------------------------------------------------------
SYSTIMESTAMP + INTERVAL '30' SECOND
1 row selected.
SCOTT@orcl_11gR2> create or replace procedure update_interval
2 (p_new_interval in config_table.value%type)
3 as
4 begin
5 dbms_scheduler.set_attribute
6 ('jobu',
7 'repeat_interval',
8 'SYSTIMESTAMP + INTERVAL ''' || p_new_interval || ''' SECOND');
9 end update_interval;
10 /
Procedure created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace trigger config_table_aur
2 after update of value on config_table
3 for each row
4 when (new.property = 'job_interval')
5 declare
6 v_job number;
7 begin
8 dbms_job.submit
9 (v_job,
10 'begin
11 update_interval (' || :new.value || ');
12 end;');
13 end;
14 /
Trigger created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select * from config_table
2 /
VALUE PROPERTY
---------- ---------------
30 job_interval
1 row selected.
SCOTT@orcl_11gR2> select repeat_interval
2 from all_scheduler_jobs
3 where job_name = 'JOBU'
4 /
REPEAT_INTERVAL
--------------------------------------------------------------------------------
SYSTIMESTAMP + INTERVAL '30' SECOND
1 row selected.
SCOTT@orcl_11gR2> update config_table
2 set value = 60
3 where property = 'job_interval'
4 /
1 row updated.
SCOTT@orcl_11gR2> rollback
2 /
Rollback complete.
SCOTT@orcl_11gR2> select * from config_table
2 /
VALUE PROPERTY
---------- ---------------
30 job_interval
1 row selected.
SCOTT@orcl_11gR2> select repeat_interval
2 from all_scheduler_jobs
3 where job_name = 'JOBU'
4 /
REPEAT_INTERVAL
--------------------------------------------------------------------------------
SYSTIMESTAMP + INTERVAL '30' SECOND
1 row selected.
SCOTT@orcl_11gR2> update config_table
2 set value = 45
3 where property = 'job_interval'
4 /
1 row updated.
SCOTT@orcl_11gR2> commit
2 /
Commit complete.
SCOTT@orcl_11gR2> select * from config_table
2 /
VALUE PROPERTY
---------- ---------------
45 job_interval
1 row selected.
SCOTT@orcl_11gR2> exec dbms_lock.sleep (5)
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select repeat_interval
2 from all_scheduler_jobs
3 where job_name = 'JOBU'
4 /
REPEAT_INTERVAL
--------------------------------------------------------------------------------
SYSTIMESTAMP + INTERVAL '45' SECOND
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
