andrew.tulley.co.uk

British National Grid Converter

The link below is to a tool I created which enables you to convert geographic co-ordinates between British National Grid format and longitude/latitude format.

It uses Oracle Locator functionality to perform the conversion and JQuery (see jquery.com) to create the Ajaxified interface.

I post this as it might (possibly, maybe) be a useful tool in its own right to somebody and it also gives a taste of what can very quickly be achieved with the excellent JQuery Javascript library.

http://apex.oracle.com/pls/otn/f?p=12071

If you have a file on the file system of your database server which you want to allow users of your Apex application to click a link to download, you can do the following.

1) Create a directory object pointing to the location of the files (through SQL Workshop for example):

CREATE DIRECTORY mydir AS ‘c:\path\to\my\directory”

2) Place the following PL/SQL anonymous block in an On Load Before Header Page Process on an Apex page:

DECLARE
l_dest_blob BLOB;
l_source_bfile BFILE := bfilename('mydir','foobar.txt') ;
l_src_offset NUMBER := 1;
l_dest_offset NUMBER := 1;
l_src_osin NUMBER;
l_dst_osin NUMBER;

BEGIN
DBMS_LOB.createtemporary(l_dest_blob,TRUE);

/* Opening the source BFILE is mandatory */
DBMS_LOB.fileopen(l_source_bfile, dbms_lob.file_readonly);

/* Save the input source/destination offsets */
l_src_osin := l_src_offset;
l_dst_osin := l_dest_offset;

/* Use LOBMAXSIZE to indicate loading the entire BFILE */
DBMS_LOB.loadblobfromfile(
l_dest_blob
, l_source_bfile
, dbms_lob.lobmaxsize
, l_src_offset
, l_dest_offset);

OWA_UTIL.mime_header('application/octet', FALSE);
HTP.p('Content-length: ' || dbms_lob.getlength(l_dest_blob));

HTP.p('Content-Disposition: attachment; filename="downloaded_file.txt"');

OWA_UTIL.http_header_close;

WPG_DOCLOAD.download_file(l_dest_blob);

EXCEPTION
WHEN OTHERS THEN
HTP.p(sqlerrm||'...'||dbms_utility.format_error_backtrace);

END;

3) Make a hyperlink or button on another page which points to the page on which you just created the On Load process. Clicking this link will now cause the user’s browser to prompt for the file c:\path\to\my\directory\foobar.txt to be downloaded (the browser will prompt for the file to be saved as “downloaded_file.txt”).

I’ve been working with Windows Batch Scripts recently. The aim has been to create a set of scripts which will automate the installation and configuration of certain applications. I was taking in user input using the SET /P MYVARIABLE=Please enter a value: syntax at the beginning of the batch script and needed to replace existing text in a configuration file with whatever the user entered at the prompt.

You can achieve this, after a fashion, using batch scripts but it’s a bit of a pain. There is a much better, easier and quicker way to do exactly the same thing using Perl. Chances are you will already have a copy of Perl available if you have the Oracle Database installed. It can all be achieved from one line typed directly into the command prompt (no need even to create a Perl script!). Example below.

The file before

The Perl command

This will replace all instances of the string “banana” in the file test.txt with the string “apple”. The search will be case insensitive. If perl.exe is not already in your PATH, you will need to call it by explicitly specifying the path where it is located or by first adding the directory where it can be found to your PATH environment variable.

The file afterwards

Show me some data, Baby

Have a table with a lot of data in and want to work with a random subset of that data? The answer is the Sample Clause of the SELECT statement. The syntax is often described as something like the following:

SELECT *
FROM my_table SAMPLE (percentage_of_rows);

For example:

SQL> CREATE TABLE ANDY.objs AS SELECT * FROM all_objects;

Table created.

SQL> SELECT COUNT(*) FROM ANDY.objs;

COUNT(*)
———-
71277

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7107

"Some data" is not always the same "some data"

The above is demonstrably returning a random 10% of data from the ANDY.objs table. Great. Very useful. However, an (arguably) interesting thing happens if we run the exact same SELECT statement again:

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7020

And again:

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7209

We get a different number of rows each time. It’s still close enough to 10% of the data, you might say. Maybe so but it does highlight an interesting point: The Sample Clause does not bring back x% of rows (as is often thought) but rather gives each row an x% chance of being returned.

From the Oracle Documentation:
"This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table."

The moral of this tale

Use the Sample Clause to work with a random subset of data but don’t rely on it to bring back exactly the expected number of rows. Complete details on the Sample Clause can be found here and more examples can be found here.

Apex pages on a diet

Duncan Mein last year wrote about enabling content compression using Oracle Web Cache to speed up use of both Apex apps and Oracle Discoverer.

Enabling compression in this way is a risk-free, quick win and there’s really no reason not to do it.

Feeling the burn

To enable compression for your Apex apps (including Application Builder itself), do the following:

1. Access the URL http://your.server.com:9400/ (this is the default port for accessing Web Cache administration).
2. Click "Caching, Personalization, and Compression Rules".
3. Under "For all sites" select the last radio button and click "Insert Below".
4. Add a rule for Get and Get with Querystring of type regular expression.
5. Add the regular expression of: /pls/apex/.*$
   NOTE: You need to replace /pls/apex/ with the DAD (Database Access Descriptor) that you use to access Apex.
6. You will be able to check that these settings have taken effect by taking a look at the HTTP response headers that your browser receives when you access one of your Apex pages.

   

   The above screenshot is from a tool called Live HTTP Headers available for Firefox. It’s an extremely useful tool, showing you exactly what is being set in  both the Request and Response headers. It can be installed from here. If you start playing with Live HTTP Headers, you may notice that in every request your browser sends to the server, it includes the following:

   

   This indicates to the server that your browser does indeed support gzip compression (which is what you have just configured within Oracle Web Cache). If your browser did not send the Accept-Encoding header as above, then the server would not send back compressed content but rather just plain old uncompressed content which would still be displayed correctly.

   You can see more general information about HTTP Request headers here and about HTTP Response headers here.

Taking the pills

The configuration that you performed above via Oracle Web Cache Administration is just a front-end for modification to the file webcache.xml found in:

<Oracle Middle Tier Home>\webcache\webcache.xml

As such, it is possible to perform these steps via script. This may be useful if, for example, you are trying to create a silent method for installation or configuration of your application. If you are using Windows, you can use the use the batch script and accompanying text file below to perform this configuration entirely via the command prompt (you could work this into a larger installation / configuration script depending upon your requirement).

enable_web_cache_compression.bat – You will need to modify the first line of this file to point to your Oracle Middle Tier Home.

new_webcache_entry.txt – You will only need to modify this file if your DAD is something other than /pls/apex/

Once you have saved these files to any directory and made the changes to these files as mentioned above you can just run enable_web_cache_compression from a Command Prompt to perform the relevant configuration.

DISCLAIMER: You should only use these files as a guide to one possible way for configuring Web Cache to use compression. You should only consider using them once you have looked at their contents and understand what they do. And, as always, you should have a backup of everything before you start modifying configuration files.

Why Browser Caching is Good

Browser caching is often a very useful thing. Accessing a web site you visit often, it can greatly speed things up if much of the contents can be read from your local browser’s cache rather than having to be re-fetched from the server.

Why Browser Caching is Bad

Sometimes, however, caching is not what you want. For example, you may have data which is displayed in the course of a user using your application which you would not want to be cached because you would not want that potentially sensitive information to be stored locally or accessible to other users (simply clicking Back in a browser will often display a cached version of the page). In the scenario where you have logged into an application, viewed some sensitive information, clicked Log Out but left the Browser window open, another user who sits down at the same computer may well be able to click "Back" in their browser to look at what you were just looking at (even though they will not be able to interact with the application in any way because they are not logged in).

How to Stop Caching

There are meta-tags (e.g. <meta http-equiv="Pragma" content="no-cache"/>) which you can specify in the HTML of your web pages whose raison d’etre would seem to be to prevent caching. However, as this Microsft article indicates, these, in fact, have no effect in Internet Explorer 6 (or 7 or 8). One alternative solution that this article suggests is to use SSL / HTTPS. This would indeed work but what if you don’t want to use HTTPS? The other choice is to set HTTP headers to instruct the browser not to cache.

How to Set HTTP Headers to Stop Caching

If using OHS (Oracle HTTP Server, i.e. Apache) you can modify your httpd.conf file to set such headers for only certain URLs so that, for example, your Apex pages are never cached. You would need to add something similar to the following to your httpd.conf file (found in %ORACLE_HOME%\Apache\Apache\conf\httpd.conf):

<LocationMatch "/pls/apex/f">
<IfModule mod_headers.c>
Header set Cache-Control "max-age=0, no-cache, no-store, must-revalidate"
Header set Pragma "no-cache"
Header set Expires "Wed, 11 Jan 1984 05:00:00 GMT"
</IfModule>
</LocationMatch>

You would need to modify the "/pls/apex/f" value if your DAD is something other than the default of "/pls/apex". The above entry will have the effect of preventing caching for all of your Apex pages accessed via this DAD (including those pages associated with Application Builder). You can add in this text pretty much anywhere in your httpd.conf file as long as it is after the call to "AddModule mod_headers.c", Once you have made the change you will need to restart Apache (by running, for example, %ORACLE_HOME%\opmn\bin\opmnctl restartproc ias-component=HTTP_Server from a Command Prompt).

This works in Internet Explorer 6, 7 and 8 as well as in Firefox.

The below shows how to use %ROWTYPE for INSERTs and UPDATEs. I have not seen UPDATE table SET ROW syntax very often.

————————————–

SQL> create table cds(id number, artist_name varchar(255), album_name varchar2(255));

Table created.

SQL> create sequence cds_seq;

Sequence created.

SQL> DECLARE
2 l_new_cd cds%ROWTYPE;
3 BEGIN
4 l_new_cd.id := cds_seq.nextval;
5 l_new_cd.album_name := ‘The Future’;
6 l_new_cd.artist_name := ‘Leonard Cohen’;
7 INSERT INTO cds
8 VALUES l_new_cd;
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> col artist_name format a20
SQL> col album_name format a20
SQL> select * from cds;

ID ARTIST_NAME ALBUM_NAME
———- ——————– ——————–
1 Leonard Cohen The Future

SQL> DECLARE
2 l_updated_cd cds%ROWTYPE;
3 BEGIN
4 l_updated_cd.id := cds_seq.nextval;
5 l_updated_cd.album_name := ‘Oracular Spectacular’;
6 l_updated_cd.artist_name := ‘MGMT’;
7 UPDATE cds
8 SET ROW = l_updated_cd
9 WHERE album_name = ‘The Future’;
10* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from cds;

ID ARTIST_NAME ALBUM_NAME
———- ——————– ——————–
1 MGMT Oracular Spectacular
————————————–

Do you think MGMT’s album was named after a particularly impressive OUG?

This entry builds on the session timeout functionality described in part 1 and part 2. Be sure to check those posts out first.

One of the comments I received about my last post on session timeout was to do with Oracle Maps/MapViewer. Oracle Maps is a nifty web 2.0 application which enables Google-style maps to be embedded into your web applications, including Apex applications. (Click here for info on Oracle Maps/MapViewer.)

The problem mentioned in the comment was that if you are interacting with your Oracle Maps map for more than 18 minutes then the popup indicating that your session is about to expire will still appear even though you have certainly not been inactive in the application over that time (you have been busy panning, zooming and interacting with your map).

The below is one way to get round this problem. We will add events to the Map object so that the session is kept alive by simply interacting with the map itself (and hence the popup indicating that your session is about to timeout will not popup inappropriately.)

Step 1 – Create an HTML region on your mapping Apex page

Add an HTML region to the Apex page which contains your embedded map.

Set its template to "No template".

Set the source of this region to be the entire contents of the file found here.

Step 2 – Register the new javascript functions with the Oracle Maps map object

If you already have an Oracle Maps map embedded into your application you will have some javascript which sets up the various options for this object (setting its initial X and Y centre position, setting its initial zoom level, adding map decorations etc.). You most likely have this javascript in a .js file which is referenced from your Apex page. What you need to do is add the following code to your javascript. It sets a listener so that whener the user of your application changes the zoom level of the map or recentres the map, their Last Activity Date is updated on the server.

mapview.addEventListener("before_zoom_level_change"
           ,        resetUserLastActivityDateIfNotResetInLastMinute);

mapview.addEventListener("recenter"
           ,        resetUserLastActivityDateIfNotResetInLastMinute);

And there you have it. Simply interacting with the map will now keep the session alive.

I came across a very useful tool recently called Qumana. If you have your own blog you might find it very useful too. It essentially allows you to manage your blog entries (whether they be on Wordpress, Blogger or a few more) via a thick, downloadable client.

This has the advantage that you have far more control over the layout of your posts. It also makes it far easier to upload images in your posts to whichever site your blog is with and, what’s pretty cool, is that you can create your posts in Word, complete with images, and then just copy and paste into Qumana and click “Publish”. It’s very handy and saves a lot of time.

It’s free and can be found at: http://www.qumana.com/

This entry builds on the session timeout functionality described in part 1. Be sure to check that out first. In this one I describe how to get your application to popup a warning window just before a user’s session is about to timeout due to inactivity (and give them the option to remain logged in).

STEP 1 – Configure Page Zero

If you do not already have one, create a Page 0 in your application.

Add an HTML region to page 0, calling it “Session Timeout JS”.

Be sure to set its “Template” to “No Template”.

Set its conditions to be “Current Page is NOT in Expression 1” and set “Expression 1” to be the id of your user-friendly “Your session has timed out” page (Please see the previous blog entry in this series for details about this page):

Paste all of the text found in the file here into this region’s source.

When you have pasted the code, you will notice that one of the lines reads:

$x(’pFlowId’).value+’&p_next_flow_page_sess=’+$x(’pFlowId’).value+’:3′;

You should change the value of 3 in this line to be whatever the number of your user-friendly “Your session has timed out” page is. (Please see the previous post in this series for details about this page.)

STEP 2 – Setup the Application Process

Select Shared Components > Logic > Application Processes

And click create. Give your Application Process a name of “SET_LAST_ACTIVITY_DATE”. You must not give it a different name as this is referenced from within the javascript you pasted into the region on page zero in the previous step. Set its “Point” to “On Demand: Run this application when requested by a page process”.

Specify the “Process Text” as:

BEGIN

   APP_USERS.set_last_activity_date;

   htp.prn(’1′);

EXCEPTION

   WHEN OTHERS THEN

      htp.p(’0′);

END;

Step 3 – Finished. Have another cup of tea.

That’s it. Done.

If you want to test it to make sure it works (but don’t want to have to wait 18 minutes!) you could temporarily alter the timeout values in the javascript on page zero (notice below I have changed them to 1000*20 [i.e. 20 seconds] and 1000*40 [i.e. 40 seconds]).

Now log in to the application and wait.

After 20 seconds you will see a warning:

If you do nothing, after a further 20 seconds the popup will close and you will be logged out of the application.

If you click the link, the popup will close and you will remain logged in.