The Oracle database scheduler has a lot of options I would all like to address in the upcoming time at this weblog, however we start with something simple. As I already stated some time ago I have been working on a computer model for weather analysis which also uses current weather data. In a previous post I explained by using this application for example how you can retrieve remote XML files via the HTTP protocol and store it in your database. Now I will use this again to explain how you can add a PL/SQL statement to a schedule and have it executed on regular times. I would like to retrieve the XML datafile about the weather at Farewell Lake Seaplane Base which has code PAFK. I will not go into details about the statement, however, to retrieve a remote XML file and store it in a Oracle database I would have to execute the following statement:
insert into NOAA_WEATHER_XMLDATA values
(
'PAFK',
sysdate,
(select sys.xmltype.createXML(locs.location_xml_url.getClob()) from noaa_weather_obsloc locs where location_code = 'PAFK')
);
The XML data on the remote server is refreshed every hour so I would like this SQL statement to be executed every hour so I will have every hour a new record in my database containing the current weather conditions at the Farewell Lake Seaplane Base. To be able to schedule this we login to the Oracle Enterprise Manager 11G Database Control environment en go to the "server" tab, here we find at the right of the screen a section about the Oracle Scheduler. Under this section we have "Jobs", "Chains", "Schedules", "Programs", "Job Classes", "Windows", "Window Groups", "Global Attributes" and "Automated Maintenance Tasks". For this example we are only interested in "Jobs".
Clicking on the link will bring us to the "Scheduler Jobs" screen where we have a overview of all the scheduled jobs. As we can see already some jobs are here by default when you install the database. The scheduled job named "WEATHER INSERT PAAK" is a job already created by me in a previous step, however, now we would like to add a job for PAFK instead of PAAK.
As you can also see in the screenshot is that the scheduled job is part of "Job Class". In short a "Job Class" is used within the Oracle scheduler to order groups of scheduled jobs. There is some more to it however we will not go into this at this moment and I will also not go into the details of creating such a "Job Class". When clicking on the "Create" button we will be shown a screen where we can create a new job, as seen in the screenshot below:
Here we have to enter some information to define our new job. As name we set a simple however clear name "WEATHER INSERT PAFK". The schema is set to the JLOUWERS schema because all the tables and other objects of this application are stored in this schema in the Oracle database. Enabled is for sure set to "Yes". If you specify Yes to enable the job, the job runs according to its predetermined schedule; either immediately or at a specific time. If you specify No to disable the job, the job is created but does not run until you enable the it.
The description is again free to fill and we have set "Insert weather data from station PAFK".
The logging level of the Oracle scheduled job is set to "Log job runs only" this will make sure that we will log that a job is started and completed. Other options are for example "No logging" where completely nothing is stored about the job history, this is not advisable. You can also select the option "Log everything" which can be very helpfully when you are debugging some things however for a day to day procedure this is not advisable.
The Job Class is set to "WEATHER DATA" and as already discussed in this post I will not go into details on this in this post. The "Auto Drop" is set to false because we do not want the job to be removed after it is finished, we also set the Restartable function to true because we would like to retry things if they go wrong.
As already discussed we would like to have a insert statement executed every time so we have chosen for the "Command Type" PL/SQL Block. There are however more types which you can use. We will however not discuss those in this post. To have all the things working as we would like we have to extend the PL/SQL code a bit more to the following:
begin
insert into NOAA_WEATHER_XMLDATA values
(
'PAFK',
sysdate,
(select sys.xmltype.createXML(locs.location_xml_url.getClob()) from noaa_weather_obsloc locs where location_code = 'PAFK')
);
commit;
end;
This is the complete syntax we need to have it really really done in the database. The scheduler expects a begin and a end command and we like to have it committed to the database so we add a commit command to the syntax of the PL/SQL block. For those who would like to know if this is also possible to create using PL/SQL, yes all that is done via this GUI can also be done with a PL/SQL script. This would look like the code below:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"JLOUWERS"."WEATHER INSERT PAFK"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
insert into NOAA_WEATHER_XMLDATA values
(
''PAFK'',
sysdate,
(select sys.xmltype.createXML(locs.location_xml_url.getClob()) from noaa_weather_obsloc locs where location_code = ''PAFK'')
);
commit;
end;',
start_date => systimestamp at time zone 'Europe/Vienna',
job_class => '"WEATHER DATA"',
comments => 'Insert weather data from station PAFK',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"JLOUWERS"."WEATHER INSERT PAFK"', attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"JLOUWERS"."WEATHER INSERT PAFK"' );
END;
You can view the code which will be executed by clicking the "Show SQL" button. Here you have the ability to review the code before it is executed against your database.
Clicking the "OK" button will make that the job is posted to the database. If we do this the job will however only run ones so we also have to add a schedule to it. As discussed before we would like to have it run every hour. We will set it to run 15 minutes after the hour with not end-date to it. If we click the "Schedule" tab we will be able to set all this. We will set that it has to be repeated with a interval of 1 hour and that the start time is 15 minutes past six and we will not set a end-date or time. Meaning this will run forever, at least until we stop it.
Also for this we can do this via PL/SQL by using sys.dbms_scheduler.set_attribute. This can be handy if you for example would like to create a job and schedule automatically for every new weather station you enter in your application. The code to set such a information is show here:BEGIN
sys.dbms_scheduler.set_attribute( name => '"JLOUWERS"."WEATHER INSERT PAFK"', attribute => 'repeat_interval', value => 'FREQ=HOURLY');
sys.dbms_scheduler.set_attribute( name => '"JLOUWERS"."WEATHER INSERT PAFK"', attribute => 'start_date', value => to_timestamp_tz('2009-03-15 18:15:00 Europe/Vienna', 'YYYY-MM-DD HH24:MI:SS TZR'));
END;
After all the things we set we now can see the job in the Oracle Scheduler Jobs overview and we see that it is scheduled for running at "Mar 15, 2009 6:15:00 PM +01:00". The fact you also see a "Last Run Date" enterd is that during the creation of this blogpost I did some test running the job.
No comments:
Post a Comment