Wednesday 18 January 2012

Introduction to Regular Expressions in Oracle

Regular expressions – RegEx or RegExp to their friends – were fashionably late in coming to the Oracle party. The seeds of what we know today as regular expressions were formulated in the period immediately following the Second World War in fields as diverse as formal language theory and neurophysiology; but it wasn’t until the PL/SQL Web toolkit arrived for 8i and 9i that they popped their heads over the Oracle parapet. By this time they were already rock stars in the worlds of Unix and Perl.

But I’m being rude here; I should introduce you. Regular expressions are a codified means to accomplish flexible pattern-matching in strings of text.

And, if you’re rereading that definition and thinking ‘huh?!’, that’s a feeling you might want to get used to, because, while regular expressions are powerful and definitely useful, they can seem very much like hieroglyphics to the uninitiated. But don’t worry, I’ll hold your hand.

String pattern matching has always been possible in Oracle sql and pl/sql, of course. With the LIKE condition and the simple metacharacters of “%” and “_”, character patterns could be described and integrated into queries. However, the moment you required a little complexity these basic wildcards cowered in fear. An example: my name is David, but friends tend to call me Dave when they have a beer or two in them. To match either spelling of my name, not using regular expressions, you would have to query the table for ‘Dav%’. But then your results might be flooded with Davidsons and Davises and Davinas. And I’m definitely not pretty enough to be called Davina!

Tuesday 10 January 2012

Introduction to Autonomous Transactions (Or If Obama And Merkel Had A Lovechild...)

I don't know if you've been paying attention, but you may have noticed that there's a bit of a recession going on at the moment. Stock prices are crashing around the globe, and I'm pretty sure that the Euro is now worth less than Monopoly money. It's very much like the Great Depression of the 1930s all over again - except that, this time, the bankers don't have the decency to throw themselves out of windows.

The solution, David Cameron, Angela Merkel and Barack Obama assure us, is for everyone to go out and spend more money. That'll kickstart our economies, they assure us. More transactions. A lot more.

So they'll probably thank me for this article that I wrote over at All Things Oracle about autonomous transactions. Have a read; you may thank me too.


Autonomous transactions are a bit like that dark part of the forest, beyond the rickety bridge, where the shadows are shaped like witches and bats swarm soundlessly from the trees. We all know it’s there, but the wise are in no hurry to visit.
Here, however, is a definition: an autonomous transaction is a completely independent transaction that is started by temporarily suspending the parent/calling transaction, which is then resumed after the independent transaction is completed. Or, if you prefer a real-world analogy: an autonomous transaction is a little like getting married, but having an affair on the side.
Morality isn’t the reason autonomous transactions are frowned upon; they possess the potential to get out of hand. Autonomous transactions are completely self-contained transactions that commit independently, and so to start one is to juggle two transactions...



Tuesday 3 January 2012

Should Your Next Project Be Written In Forms or Apex? (Or The Polygamist's Dilemma)


The polygamist pauses, his hand hovered above the doorknob. He looks back down the hallway, blinking in the harsh light of the naked bulb he has just flipped on to do war with the shapeless darkness of the night. A few paces away - no further away than a firm decision - stands another door: silent, inviting. His gaze returns to the door in front of him; also as silent as the entrance to Aladdin's cave. Which door should he open? Which of his beloved wives should he visit?

Okay, I admit it, I know nothing about polygamy. I once caught an episode of Big Love on telly, and I enjoyed reading Lola Shoneyin's The Secret Lives Of Baba Segi's Wives, but that's about it.

I do, however, know a bit about Oracle Forms and Oracle Application Express. Which is kinda fortunate since I'm guessing you're not here for marital advice. A question I do get asked from time to time - most recently on LinkedIn - is this: What technology should I choose for my new project - Oracle Forms or Oracle Application Express?

At the risk of sounding like a guru sat atop a Himalayan rock, there is only one real answer to this question: look within your heart,  the answer floats like an asteroid in the galaxy of your inner space. Or something. Personally, if I was making the decision a year ago I'd have said Apex cos it was new and exciting; today, I'd probably say Forms, cos it's old and I better understand its foibles: the polygamist's dilemma - the new, nubile wife, or the old wife who he fully understands?

You will probably want to base your decision on factors more concrete and quantifiable than imaginary asteroids, so here are some things to consider.

Skills: What skills do you have? What skills are you willing to invest in? What skills are you capable of acquiring? I would imagine that you have a greater wealth of Forms experience. This need not be the deciding factor, but it should definitely count as a tick in that box. I was able to retrain my team of Forms developers in Apex, but it took some time and some expense. And, crucially, it was a challenge they were happy to face: I still have nightmares about The Big Oracle ADF Disaster of 2008. My therapist says I'll get over it one day, but I doubt it.

Platform: A huge part of your choice must depend on the demands of the project itself. For instance, what platform are you hoping to run it on? If you are planning to take advantage of the current boom in mobile computing, then Apex should be your choice. I must admit that I have not yet tried to run a Forms application on a phone or a tablet, but I do not suspect that that is an experiment that would end happily.

If, on the other hand, you expect your application to be run only from PCs and laptops using a myriad of browsers, with or without javascript enabled, then possibly Forms with its browser-agnostic java applet might be worth a look-in.

Cost: If you are a millionaire Saudi Arabian prince who has only got to this page cos you googled "I am looking for the oracle on polygamy", you can skip this bit. The rest of you, pay attention. With its middleware costs,  Forms is undeniably the more expensive option. I am not allowed anywhere near the money at my company, but I am assured that the difference in cost can be considerable.

Audience: Who is your application aimed at? If it is to be published on the open Internet, then you only really have one choice - Apex. I am not denying the work Oracle's Forms team has done to modernise the old girl, but I doubt that it will ever be truly suitable for the world-wide web.

However, if you are building an enterprise application, then there is definitely something to be said for the solidity and gravitas that Forms still exudes. And if your application will be used mostly for data-entry, then Forms provides speed that Apex, even with its clever javascript and ajax hooks, cannot match.

Extendability: The answer to the question of which is the more extendable technology may not be as obvious as it may first seem. Being a web technology, Apex can more easily sup at the banquet of ajax and javascript, can more easily flirt with jQuery and whatever the next big thing will be. But with each iteration of Forms, its door is opened wider, with Java pluggable components and javascript and CSS support. However, I believe that Apex will always outpace Forms in this respect; the story of the tortoise and the hare was only a fairy tale.

Speed: I cannot get my hands on the exact figures now, but I have read that for every tenth of a second that Amazon manages to shave off their website's response time, sales increase by many millions of dollars. Google experienced a similar increase in the stickiness of their site when they introduced Google Instant. If response times are as crucial to your project, then perhaps you should be leaning towards lightweight Apex. But do not dismiss Forms out of hand; from 11g, it is possible to slash load times by pre-starting runtime engines. The tortoise may have bought itself some roller skates. 

Deployability: Our IT team are forever engaged in a battle with Oracle Application Server. One of them - OAS or the IT guys - isn't very good at their job. Since no one at Oracle has ever bought me a drink at the pub, I'll assume it's their fault. Apex is easier to deploy than Forms; indeed there is a growing number of companies that will host your Apex application for you.

The Big Picture:  You have to consider the wider context. In my case, the option of building Forms applications is a receding one, since my company has taken the decision to move towards Apex. We now have a cohort of new, young developers to who Forms is Betamax.

Conclusion: The polygamist makes his decision and quietly opens a door. He enters the room and closes the door behind him. Are you awake? he whispers into the darkness. There is a crack in the curtains, and a shaft of milky moonlight is lying across the bed like an abandoned sword. Yes, a voice whispers from beneath the covers. He walks towards the sound, his pace quickens with excitement.

There is no universally right or wrong answer; it depends on your project and the factors I have named above (and the many I have not thought of - which is why there is a comments section below).

PS: I'd like to thank Steve Cairns for his help with this post. He is one of the country's leading experts on polygamy. Or Oracle technologies. I forget which