Browsing "Older Posts"

APEX-AT-WORK no image

DBMS_SCHEDULER examples

Von Tobias Arnhold → 6.25.2009
May be interesting to some of you which haven't had the time testing and working with the DBMS_SCHEDULER. I collected some examples to show what is possible with that amazing tool.

DBMS_SCHEDULER is an internal Oracle package (since Version 10g) which provides database driven jobs.
It's divided into 3 parts:
  • Time schedule part - dbms_scheduler.create_schedule
  • Program declaration part - dbms_scheduler.create_program
  • Job (conflation) part -dbms_scheduler.create_job
Examples of the dbms_scheduler.create_schedule part:

begin
-- daily from Monday to Sunday at 22:00 (10:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_DAILY_2200',
start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)
repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
comments=>'Runtime: Every day (Mon-Sun) at 22:00 o'clock');

-- run every hour, every day
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_HOUR',
start_date => trunc(sysdate)+18/24,
repeat_interval => 'freq=HOURLY;interval=1',
comments => 'Runtime: Every day every hour');

-- run every 5 minute, every day
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_5_MINUTES',
start_date => trunc(sysdate)+18/24,
repeat_interval => 'freq=MINUTELY;interval=5',
comments => 'Runtime: Every day all 5 minutes');

-- run every minute, every day
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_MINUTE',
start_date => trunc(sysdate)+18/24,
repeat_interval => 'freq=MINUTELY;interval=1',
comments => 'Runtime: Every day every minute');

-- run every Sunday at 18:00 (06:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_EVERY_SUN_1800',
start_date=> trunc(sysdate)+18/24,
repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',
comments=>'Runtime: Run at 6pm every Sunday');
end;

Example of the dbms_scheduler.create_program part:

begin
-- Call a procedure of a database package
dbms_scheduler.create_program
(program_name=> 'PROG_COLLECT_SESS_DATA',
program_type=> 'STORED_PROCEDURE',
program_action=> 'pkg_collect_data.prc_session_data',
enabled=>true,
comments=>'Procedure to collect session information'
);
end;

Example of the dbms_scheduler.create_job part:

begin
-- Connect both dbms_scheduler parts by creating the final job
dbms_scheduler.create_job
(job_name => 'JOB_COLLECT_SESS_DATA',
program_name=> 'PROG_COLLECT_SESS_DATA',
schedule_name=>'INTERVAL_EVERY_5_MINUTES',
enabled=>true,
auto_drop=>false,
comments=>'Job to collect data about session values every 5 minutes');
end;

Examples to change dbms_scheduler settings:

begin
-- change start time
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'INTERVAL_EVERY_5_MINUTES',
attribute => 'start_date',
value => to_date('22.06.2009 12:15','dd.mm.yyyy hh24:mi')
);

-- change repeat interval
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'INTERVAL_EVERY_MINUTE',
attribute => 'repeat_interval',
value => 'freq=MINUTELY;interval=2'
);
end;

Example to run job immediate:

begin
dbms_scheduler.run_job('JOB_COLLECT_SESS_DATA',TRUE);
end;

Example to restart job:

begin
dbms_scheduler.disable('JOB_COLLECT_INST_INFO');
dbms_scheduler.enable('JOB_COLLECT_INST_INFO');
end;

Select job status:

-- All jobs
select * from user_scheduler_jobs;

-- Get information to job
select * from user_scheduler_job_log order by log_date desc;

-- Show details on job run
select * from user_scheduler_job_run_details;


Further information about the DBMS_SCHEDULER:
http://psoug.org/reference/OLD/dbms_scheduler.html
Oracle documentation about the DBMS_SCHEDULER

These scripts are tested in an Oracle XE environment (10.2.0.1).
I will extend this post whenever I need new scripts.

Update 11.09.2010:

Run a job without program and schedule plan (all in one command):

dbms_scheduler.create_job (
job_name => 'TEST_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'begin /* some process code */ commit; end;',
number_of_arguments => 0,
start_date => sysdate +1/24/59, -- sysdate + 1 minute
job_class => 'ADMIN', -- Priority Group
enabled => TRUE,
auto_drop => TRUE,
comments => 'Testrun');


@Sue
Dynamic Scheduled jobs from inside APEX are no problem. But be careful if you use "execute immediate"!
APEX-AT-WORK no image

ExtJS navigation tree

Von Tobias Arnhold → 6.19.2009
Hi folks,

I added an ExtJS navigation tree to my example application. To get the JSON data out of the database I created a small package which is dynamically creating this data string on every page load.

Take a look at the updated source code: AAW - ExtJS navigation tree

I got some request about the application installation file (application source code). So I decided to publish it to everybody how asks for it via email: tobias-arnhold@hotmail.de
I would use the same procedure as Denes does. You get an developer account and can download the current version yourself.

PS, feedback is always welcome! :D
APEX-AT-WORK no image

Several Interactive Reports in one ExtJS page

Von Tobias Arnhold → 6.11.2009
I created an example page for the ExtJS layout http://apex.oracle.com/pls/otn/f?p=65555 based on the solution of Roel Hartman "Using two Interactive Reports on one page"
Just follow the tree link: "Integrate IRs into one ExtJS page"

I encountered a problem when I accessed the example page with IE8. After the first page load the following error could occur:
ORA-00001: unique constraint
(APEX_030200.WWW_FLOW_COLLECTION_MEMBERS_PK) violated
After another reload the error disappears and the application works fine.

It's time for a new revolution - Update of my example app

Von Tobias Arnhold → 6.09.2009
For those who are interested in APEX, ExtJS and AJAX take a look on my new/updated example application:
http://apex.oracle.com/pls/otn/f?p=65555

Feel welcome to post some feedback!



I don't know if you have noticed it yet? ;D I updated my blog layout as well.