Sunday, 28 January 2007

Back to work!!!!!!!!!

Well, our week skiing in Meribel came and went too quickly. We all had am awesome time in the "Best British Ski Resort" France has to offer.

The Skiing was good (not excellent) but enough to thoroughly exhaust every one of us and our Chalet was very comfortable.

Living costs in the Trois Valleys was not cheap however. Beer ranged between 8 and 10 Euros a pint which made for some very expensive nights out. Fortunately, food was included in our Chalet costs. I was devastated parting with 40 Euros for a lasagne, chips and a coke in Courcheval but hey.

For anyone interested, some pictures can be found here

I shall be putting them all on my website in the next few weeks.

Wednesday, 10 January 2007

Optimistic Locking Using ORA_ROWSCN

For anyone who has manually written an Update Statement in a Web Application, the issue of Optimistic Locking always took a while to address.

The normal convention is to use a series of hidden elements for every form element that is submitted and doing an "old vs new" comparison in the Update statement to see if any valus in the database have changed.

I found the main drawback was the need to create large amounts of hidden elements that are then passed into the DML procedure for evaluation.

I came across ORA_ROWSCN in 10GR2 that reduces the amount of hidden form elements you need to submit.
Its basically a new pseudo column in that database that provides the SCN (System Clock Number for the last time the row was updated) associated with individual rows when they were read.

The basic steps to utilise this approach are as follows:

1) create table users (id number,
name VARCHAR2 (100),
address VARCHAR2 (100),
tel NUMBER)
ROWDEPENDENCIES
/

ROWDEPENDENCIES need to set as SCN by default is implemented at block level. By setting ROWDEPENDENCIES, SCN should now track at row level.

2) query that a form on page 100 is based on would look like this (note that a hidden element P100_USER_RCN should be created at page level):

SELECT name, address, tel, ORA_ROWSCN
INTO :P100_USER_NAME, :P100_ADDRESS, :P100_TEL, :P100_USER_RCN
FROM users
WHERE id = 1;

3) Your update statement would then look like this:

UPDATE users
SET id = :P100_USER_ID,
name = :P100_USER_NAME,
WHERE id = 1
AND ORA_ROWSCN = :P100_USER_RCN;

-- Check to see if update took place
IF SQL%ROWCOUNT <= 0
THEN
RAISE_APPLICATION_ERROR (-20002, 'Optimistic Locking Violation');
END IF;

Of course my first advice would be let Application Express handle your DML updates if you can, but for the times when you have to manually create your own update, this may be of some help to you.

Here are some benchmarked examples of this in operation on AskTom

I also posted this in the Application Express Forum a while back. There were some good comments / additions made by others so here is the link to the post.

Tuesday, 9 January 2007

New Line Character Using UTL_FILE

A while ago I came across an issue exporting the contents of a BLOB to a flat file on my file system. My processing was to upload a .csv file into the wwv_flows_files table, and write a file back to my Oracle directory using the UTL_FILE api. Upon inspecting the file produced, a newline character was inserted after every line.

To cut a long story short, a colleague of mine came up with a workaround that involved parsing the newley produced file, removing any spurious lines and creating a new file minus the blank lines. As you can imagine, for large files, this added a fair amount of time to the Upload process.

There is light at the end of the tunnel however.

This article was sent to me by another colleague. At the end there is a solution to the problem. Cheers Kris

"Oracle 10g includes extra open modes (rb, wr, ab) to signify byte mode operation. The "wb" open mode can be used along with the PUT_RAW procedure to prevent extra newline characters being added on a Windows platform"

I shall refrain from any Windows bashing as it would be too easy as the problem does not occur on Linux. If anyone else wants to jump in and have a dig at Microsoft, feel free.

Here is a link to the a thread on the Application Express Discussion Forum as well.

Friday, 5 January 2007

Reference JavaScript from the File System in Application Express

The strangest thing happened the other day whilst performing an application import within Oracle XE. The import was successful but running the application however was less so.

Some of my pages contained a lot of JavaScript in the HTML Header section of the Page Attributes. On some of these pages, the import appeared to add a carriage return to one of the lines and thus breaking my script. The knock on effect was that my application ground to a rather unimpressive halt.

Without having the time to investigate why, I decided that it would be better to store all my JavaScript in a .js file on the server and write a reference to it on the pages where the scripts were needed.

Once you have created your .js file (I store in a sub directory if the /i/ path) on the server, you can reference these files by adding the following to the HTML Header of your page:



Storing your long JavaScript externally to your application also makes debugging a bit easier as well.

Goodbye Notepad, Hello Firebug

For those web developers out there using FireFox,you have to install the Firebug extension. It lets you explore the DoM in far more detail than ever before.

Simply download and install the extension, restart FireFox and hit F12. You can then see all your HTML source, JavaScript, CSS etc. It also has a cool debugging tool for JavaScript that lets you step through line at a time.

I can't tell you how much time this extension has saved me this year. Get it, install it and give it a whirl. You wont be disappointed I promise.

Microsoft have released their Developer Toolbar for Internet Explorer which goes some way to providing the same functionality for the Microsoft die hards out there but its a million miles away from our friends over at Mozilla.

Thursday, 4 January 2007

The New Year Blues

Well, finally made it into work on the 3rd January and it's always tough after the holidays. However its not long to wait until the 13th January when I will be spending a week in Meribel. Hopefully we will all return in one piece this year as well. My girlfriend had quite a nasty accident last year in Austria which resulted in a large laceration to her leg and required several rather large stitches.

I digress however and just wanted to offer some advice for those who are struggling with the "back to work" bug doing the rounds. A colleague of mine told me yesterday that his girlfriend always has to have a holiday to look forward to. I thought this was a brilliant way to beat off those New Year Blues and so why not start planning your Easter getaway. Surly the prospect of another holiday is the best way to cure the disappointment of one just ending.........

Just a thought.