New URL for this blog

I recently downloaded and installed the really rather brilliant open source blogging software from wordpress.org. I recommend having a play about with it if you have a web server with PHP and MySQL available to you. This will be the last entry to atulley.wordpress.com. The new home for this blog is:

http://andrew.tulley.co.uk

Replacing text in a file using Perl

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

SQL: SELECTing a random subset of data

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: Speeding things up with Oracle Web Cache compression

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.

How to stop your Apex pages being cached

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.

Using %ROWTYPE for INSERTs and UPDATEs

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?

Implementing session timeout in your ApEx app – Part 3

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.

Qumana Blogging Tools

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/

Implementing session timeout in your ApEx app – Part 2

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.

Implementing session timeout in your ApEx app – Part 1

This entry talks about one method
for causing users to timeout of your
application after a period of inactivity.

STEP 1 – Create your USERS table

From SQL Workshop, run the following:

CREATE TABLE users (id NUMBER, username VARCHAR2(30), last_active_date DATE);

And then, once the table is created, populate it with a user:

INSERT INTO users (id
,username
,last_active_date)
VALUES (1
,'ANDY'
,'01-JAN-2008');

(Change the value of ‘ANDY’ to be the username of a user that exists. For example, if you are using ApEx Standard Authentication then change it to be the username of an ApEx user.)

STEP 2 – Create the APP_USERS package

From SQL Workshop, run the following to create the package specification:

CREATE OR REPLACE PACKAGE app_users
IS
c_session_timeout_minutes CONSTANT number(2,0) := 20;
PROCEDURE set_last_activity_date;
FUNCTION is_session_valid
RETURN boolean;
END app_users;

From SQLWorkshop, run the following to create the package body:

CREATE OR REPLACE PACKAGE BODY app_users
IS
PROCEDURE Set_Last_Activity_Date
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_current_user VARCHAR2(30);
BEGIN
l_current_user := v('APP_USER');
UPDATE users
SET last_active_date = sysdate
WHERE username = l_current_user;
COMMIT;
END;

FUNCTION Is_Session_Valid
RETURN boolean
IS
l_current_user VARCHAR2(30);
l_is_session_valid VARCHAR2(1);
BEGIN
l_current_user := v('APP_USER');
SELECT CASE
WHEN (last_active_date + APP_USERS.c_session_timeout_minutes/1440) < sysdate THEN
'N'
ELSE
'Y'
END
INTO l_is_session_valid
FROM users
WHERE username = l_current_user;
IF l_is_session_valid = 'N' THEN
return false;
ELSE
return true;
END IF;
END;
END app_users;


Step 4 – Create a user-friendly “Session timed out” page

Create an ApEx page and set its “Authentication” Security Setting to “Page is Public”. This page should contain a helpful message for the user indicating that their session has timed out. Make a note of the ID of this page (we’ll assume this is page 3 for this example).

Step 5 – Modify Authentication Scheme

Click “Shared Components > Security > Authentication Schemes” in Application Builder and select your authentication scheme.

Enter the following into the “Post-Authentication Process” field:

APP_USERS.set_last_activity_date;

Also, within this screen, set the Session Verify Function to be:

BEGIN
IF APP_USERS.is_session_valid THEN
APP_USERS.set_last_activity_date;
return true;
ELSE
OWA_UTIL.redirect_url(
owa_util.get_cgi_env('request_protocol')||
'://'||
owa_util.get_cgi_env('server_name')||
':'||
owa_util.get_cgi_env('server_port')||
owa_util.get_cgi_env('script_name')||
'/wwv_flow_custom_auth_std.logout?p_this_flow='||
:APP_ID||'&p_next_flow_page_sess='||:APP_ID||':3');
RETURN false;
END IF;
END;

In the above code, replace 3 with whatever the ID of your helpful “Your session has timed out” page is.

Step 6 – Finished, have a cup of tea

Now, whenever a user has been inactive for more than 20 minutes and they try to access another page in your application, they will be logged out and taken to a page indicating that there session has timed out.

Next time I’ll write about how we can make an ApEx application popup a message just before a user’s session is about to time out, giving them the option to remain logged in. You can now see the second part here.