Thanks for the question, Ritesh.
Asked: March 27, 2019 - 12:32 pm UTC
Last updated: April 01, 2019 - 6:20 am UTC
Version: Oracle 12c
Viewed 1000+ times
You Asked
How can i dynamically retrieve the value for repeat_interval in the following job:-
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'create_Subpartition_Ongoing_LOCDB_M',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN coredb.sp_dutl_create_subpart_ongoing_monthly(''LOCATION_ACTIVITY_DB'',''5''); END;',
start_date => systimestamp,
end_date => null,
repeat_interval => 'FREQ=MONTHLY; bymonthday=1; byhour=4; byminute=10; bysecond=0;',
enabled => TRUE);
END;
So, i will have to make one table and get the values from that table. How can i do that ?
and Connor said...
Inside dbms_scheduler there are two routines
get_attribute
set_attribute
You can use these to retrieve and alter the interval.
SQL> BEGIN
2 DBMS_SCHEDULER.create_job (
3 job_name => 'MY_JOB',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN null; END;',
6 start_date => sysdate+1,
7 repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
8 enabled => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> set serverout on
SQL> declare
2 x1 varchar2(100);
3 begin
4 dbms_scheduler.get_attribute (
5 name => 'MY_JOB',
6 attribute => 'repeat_interval',
7 value => x1);
8 dbms_output.put_line(x1);
9 end;
10 /
freq=hourly; byminute=0; bysecond=0;