I recently received a windows Vista laptop from CapGemini specially to be used at one of our customer contracts. This was because of the fact some special security tools needed to be installed to be able to work at the customer side. To be able to use the Oracle e-business suite I needed to run Oracle Jinitiator 1.3.1.21 in combiantin with Windows vista and Internet Explorer version 7 (7.0.6000.16809). However every time I tried to logon and started a session to the forms section of Oracle e-business suite internet explorer crashed.
After some searching I found the solution at the website of theappsfactory.com. After doing some steps I have Jinitiator 1.3.1.21 working with Windows vista. Just follow the following steps:
1. Download and install the latest version of Java (such as Version 6, Update 11). You can get it here.
2. Copy the file C:\Program Files\Java\jre6\bin\client\jvm.dll to folder C:\Program Files\Oracle\Jinitiator 1.3.1.21\bin\hotspot. (Overwrite the existing jvm.dll.)
3. In folder C:\Program Files\Oracle\Jinitiator 1.3.1.21\lib, rename "font.properties.zh.5" to "font.properties.zh.6" and "font.properties.zh.5.1" to "font.properties.zh.6.1"
4. If you use multiple languages, use a text editor (such as Notepad) to open file "C:\Program Files\Oracle\Jinitiator 1.3.1.21\lib\font.properties". Search for "\u5b8b\u4f53=SIMSUN.TTF" and change it to "\u5b8b\u4f53=SIMSUN.TTC". This is an optional step.
When running a database server there are always tasks which you would like to run according to a schedule. This can be things like importing and exporting data, running cleanup tasks, check and report things. When thinking about some of you applications you can come up with several applications you have scheduled or would like to schedule. To get things scheduled you can use several options, you can buy yourself a application like Appworx which has a name in the Oracle (eBS) world, you can use crontab to scedule things however you can also use the buildin scheduler in the Oracle 11G database.
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.
When working with SQL to retrieve data from your database you want it in some cases to be differently formatted than the data is in the database. The data in your database might not be as nice as you would like to present to users in a application. For example, you users expect data to be in uppercase or in lowercase while your data is not available in the database in such in way. Oracle has a couple of simple functions you can use to set the data exactly as you wish before creating the output
When you like to select text in all upper case you can use the command UPPER and to select all the text data in lower case you can use the command LOWER. For example we have the following query:
select
location_code,
location_name
from
noaa_weather_obsloc;
This will give:
LOCATION_CODE LOCATION_NAME
------------- ---------------
PAAK Atka Airport
PAAP Port Alexander
PAAQ Palmer
PABA Barter Island
PABE Bethel
5 rows selected
Now if we want to have all the location names in upper case we can use the query:
select
location_code,
upper(location_name)
from
noaa_weather_obsloc;
the upper command in front of location_name will make that all the characters returned from location_code are now in upper case.
LOCATION_CODE UPPER(LOCATION_NAME)
------------- ---------------
PAAK ATKA AIRPORT
PAAP PORT ALEXANDER
PAAQ PALMER
PABA BARTER ISLAND
PABE BETHEL
5 rows selected
The same can be used for lower case, however, now we have to use the command lower to make sure that all the characters are in lower case as in the query below is done.
select
lower(location_code),
location_name
from
noaa_weather_obsloc;
LOWER(LOCATION_CODE) LOCATION_NAME
------------- ---------------
paak Atka Airport
paap Port Alexander
paaq Palmer
paba Barter Island
pabe Bethel
5 rows selected
Now this is quite straight forward. However, You can expect for example a situation where you have a table with all names of people in it. In this case you would for example want to make sure that the every first letter of a word(name) is in uppercase and the rest is in lower case. For this Oracle has the INITCAP option which will set every first letter of a word in uppercase and the rest in lowercase. For example "aLberT EinStEIn" would make "Albert Einstein". Your data should be in a ideal situation never contain a name like this, however when you have them manualy enterd mistakes can be made and you can add this as a extra security to make sure your names are presented in a good way. You can see a example of Oracle SQL INITCAP below
select
INITCAP(location_code),
location_name
from noaa_weather_obsloc;
