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 IN ORACLE

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...

Continue reading...

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. 

Of Comments And Panties...

Code comments are like underwear: people can't see them, but they really should be there, and it's best if you keep them clean. And brief.

Oh, and it's never as funny as you think it is, if they contain references to Homer Simpson.

Here's an article I wrote for All Things Oracle about commenting in programming. (Un)fortunately, it contains no further references to underwear. I apologise.

How to make comments the most important ‘code’ you write

80% of all communication, it is often said, is nonverbal. The actual percentage is disputed – with experts quoting figures that range from 50% all the way up to 93% – but what is not in dispute is the fact that what is left unspoken is often as important as what is said.

An almost direct parallel can be drawn with comments in code. Annotations aimed at the programmer but ignored by the compiler can be as important as actual functional code and should be treated with a comparable level of seriousness.

Should be treated with seriousness; however, unfortunately, they almost never are. Most programmers will confess that their commenting is, at best, patchy and often non-existent. The excuses run from the threadbare to the clichéd; from “I do not have enough time to comment” to “Good code is self-explanatory”.

Fewer programmers will admit to this, but a lack of knowledge of the many uses – and the power  – of commenting is widespread.  That is what this article intends to address.
Continue reading at All Things Oracle

All Things Oracle (or LeBron James's Explain Plan)

I don't know if you follow basketball, but if you do - and, perhaps, even if you don't - you will know that when, in 2010, the best basketball player in the world, LeBron James, decided to leave the Cleveland Cavaliers he organised a one-hour television show to announce which team he had chosen to join. The Decision, as the 'extravaganza' was called was broadcast on ESPN and got 10 million viewers.

I was not one of them. But I'm going to assume that if 10 million people - more than the populations of Finland and Norway put together - watched it, it must have featured gorgeous cheerleaders, dressed in a blur of motion and very little else, dancing around, joyfully pumping the air with their pompoms as LeBron explained his plan to join a new team.

No? No cheerleaders? Not even one? Well, in that case it's really lucky you're here, cos I've got a dozen cheerleaders whooping and dancing all around my tiny flat as I prepare to make this announcement.

Hey, take your eyes off them! Focus on me.

I have decided to join the All Things Oracle team. [Insert fanfare here.]

This does not mean that I will be abandoning this blog. Quite the contrary, I am hoping that contributing to All Things Oracle will exercise my blog muscles and get me into the habit of blogging here more regularly. My plan is that I will continue to blog about my trials and triumphs with Apex here, while writing about the other facets of Oracle that I am interested in - Oracle Forms and the database in general - over at ATO. (I have already contributed pieces on regular expressions and autonomous transactions.)

ATO is brought to you by the guys at Red Gate software, and their ambition is to turn it into a hub for Oracle developers and DBAs by publishing original content and by pointing readers in the direction of useful information elsewhere. To achieve this they have assembled a formidable team of experts: I've studied that list and I cannot see one weak link ... oh, there I am! The fields covered include database tuning, Apex, system architecture, Forms, database administration, warehousing and much more.

So make sure you add the ATO rss feed to your reader and that you click on their links whenever you google stuff on bing or bing stuff on google.

Now please go back to watching the cheerleaders. And enjoy the champagne - it, er, comes from France.

Apex & Version Control (Or Dance like Google's watching)

Sit back, young one, and listen. You may learn something.

Just the other weekend I went to a party, one of those dance parties with loud, fast music, free-flowing alcohol and the type of beautiful women who wear short skirts, irrespective of the temperature outside. Y'know, just the average weekend for your typical Oracle Application Express blogger.

The thing to do, when you find yourself in this situation, is sit back, have a sip of alcohol to soothe your nerves, and survey the scene. Step back into the shadows of evolution: it is 2000BC again and you are a hunter on the plains of the Serengeti overlooking your prey. Choose your quarry and then step onto the dancefloor, making gentle rhythmic moves. Circle her (or him) gradually and then move in, bringing your arms and your head into your dance. Whisper to them, "May I have this dance?"

Guaranteed success, young one. Or your money back.

Real life is nothing like Hollywood. When you're dancing with(/against) a female in real life you don't have a troupe of other dancers dancing behind you in formation, matching your every move perfectly, like the dancers in Thriller, Step Up 3D or any of these movies. Real life does not naturally come with back up.

Which is why, of course, we need to back up our Apex applications ourselves. The question you will have to answer is this: should you back up your full application in one file or back it up page by page?

If the application you are building is a website that you publish to the internet then in many ways your challenge is an easy one. You will need a development environment, perhaps a test environment and a production environment. There's no reason for one environment to pollute any of the others.

Export your full application after each major code change and back it up wholly. Backing up apex applications page by page is complicated; there's no need to take unnecessary risks.

However if, like us, you are creating a huge system which spans multiple apex applications and hundreds of pages which you intend to sell to multiple customers who have divergent upgrade schedules and obliquely differing requirements, then the picture is slightly more complicated. Your application may never be in a state where you can export it fully. You will need to export each page and each shared component as each bit of development is completed.

Application Express's versioning capabilities are simplistic at best; you can version the application, but not the page. To keep a record of the various versions of your pages you will need to use the page comments.

If you choose to export your pages individually you will need to bear in mind that exporting the page does not export any shared components that you may use on that page. You'll need to do that separately. And, unfortunately, there's no built-in way to export application items. As usual, Apex forces you to dance a merry dance to achieve what you need.

Talking about dancing, you'll want to forget that "dance like no one's watching" nonsense. You don't code like no one's going to use your software, do you? This is 2011, my friend; and Youtube has a very long memory.

Oh, and if you've already built your application and need help exporting your individual pages the following procedure should help. You'll need to run it in SQL*Plus and have set up a directory.

/*
** This procedure will write out an apex page.
*/
create or replace procedure export_apex_pages( p_dir  in varchar2, app_id number )
is
l_thePage       htp.htbuf_arr;
l_output        utl_file.file_type;
l_lines         number default 999999999;
vWorkspace  number;
vLength    number := 0;
vFileName  varchar2(100);
begin

-- Find out what the workspace is.
for i in (select workspace_id
  from apex_applications
  where application_id = app_id) loop
 
  vWorkspace := i.workspace_id;
end loop;

-- Loop through all the pages for the application.
for j in (select  page_id
   from apex_application_pages
   where application_id = app_id ) loop

  -- Determine the file name.
  vFileName  :=  'f'||app_id||'_page_'||j.page_id||'.sql';
  vLength    := 0;

  OWA.num_cgi_vars    := 0;
  apex_util.export_application_page(p_application_id=>app_id,p_workspace_id=>vWorkspace, p_page_id=>j.page_id);

  l_output         := utl_file.fopen( p_dir, vFileName, 'w' );
  l_lines         := 99999999;
  owa.get_page( l_thePage, l_lines );

  for i in 1 .. l_lines loop

   utl_file.put( l_output, l_thePage(i) );
 
   /*
   ** We need to calculate the length cos we can only write up to 32k.
   ** If it starts getting close to that we need to close the file, and then reopen it.
   */
   vLength := vLength + length(l_thePage(i));
   if vLength > 30000 then -- it's getting close to our limit.  Look for a point where we can close it.
    if l_thePage(i) like '%'||chr(10) then -- the line ends with a page return. Perfect place to close and reopen the file.
     vLength := 0;
     utl_file.fclose(l_output);
     l_output := utl_file.fopen(p_dir,vFileName,'A');
    end if;
   end if;
  end loop;

  utl_file.fclose( l_output ); 
end loop;
end export_apex_pages;
/

Learning Oracle Application Express (or teaching Angelina Jolie the MC Hammer dance)

I'm a Londoner so, unsurprisingly, I think London is the greatest city in the world (feel free to disagree if you enjoy being wrong). However I do accept that London isn't always that friendly to foreigners. Other major European cities have street signs and directions in the local language and English, but London seems to say to you, "Speak English - or get lost. Literally."

The Apex blogosphere can be a little like that; we often blog about advanced minutiae, perhaps to the exclusion of absolute beginners. This week 2 of my colleagues started learning Apex and so I've decided to put together a little Apex 101 study guide for their benefit and for that of other Apex padawans. (And if you say that this is only tangentially different from a post I wrote 2 years ago my answer is that all blog posts are kinda related; it's like that 6 degrees of separation theory that says, as far as I understand it, that I and Angelina Jolie are practically best friends.)

Where to start: There is only one place to start, and that's at the Oracle Apex website. Sign up for a workspace - it's free and easy. Have a look round, press buttons to see what they do: it's like developing with a condom on - safe, without commitment, and, hey, you can even do it drunk. However, the very best way to really dip your toes into the water is to work through Oracle's 2 Day Developers Guide [PDF link here]. When you're done with that you may wish to return here to flirt with the other tutorials.

Internet resources: Did you know that you're only ever 6 clicks away from an Apex blogger? Fact. We're like LOLCATS, adverts for blue pills for men, and YouTube videos of Rick Astley. Fortunately, the Apex Blog Aggregator has found the best blogs about Apex and, erm, aggregated them. Alternatively, you can go here or even here.

Another internet resource that you'll probably want to bookmark is OTN (which either stands for a. Oracle Technology Network or b. Orangutans Tangoing in the Netherlands. You decide.) The forum's search functionality is more Altavista than Google, but chances are that any question you've got has probably been asked and answered before.

Tutorials: The same way that with each regeneration since the first series in 1963, Dr Who has come back as someone slightly younger and better looking, each regeneration of Apex has resulted in a slicker, more intuitive IDE. However, I attended the Oracle University's 5 day Apex course and found it very useful. You may also want to have a look at SkillBuilders. I've attended some of their webinars and they've been excellent. Hey, why don't you put on a pair of geeky glasses, download some of their past webinars and pretend you're me? Whatever pedals your bike, pal.

Books: If you are the type who prefers to learn from books there is a growing catalogue of Apex books out there. Here's a list. And here are two reviews by a blogger who I respect immensely - and who Angelina Jolie is practically best friends with.

And that's it, my friend, all you need to get started. I'm not going to claim that this blog post has changed your life or that you came to me as a boy/girl and are leaving as a man/woman (unless it's taken you 20 years to read these 600 words), but hopefully you now got all the resources you need to hack your way through the Apex jungle. There's a lot more I can show you (have you seen my MC Hammer dance? No?) but my doorbell is ringing. It's probably Angelina.

Oracle Application Express 3.2: The Essentials and More: A review (or Things I've learned from TV)

I have a nephew who is 10. Just the other day he was asking me about ancient Rome and I was halfway through a long description of the lives of gladiators before I realised that all the 'facts' I was giving him had come straight from the TV show, Spartacus: Blood and Sand and not from a history book at all.

I realised, with shame, that almost everything I think I know comes from television. Ask me about psychology and I've got Lie to Me; ask me about forensic science and I've got CSI; ask me about the inner thoughts of women and I've got Sex and the City; ask me about Oracle Apex...

A couple weeks ago the good people at Packt Publishers kindly sent me a copy of the newest Apex book on the market, Oracle Application Express 3.2: The Essentials and More by Arie Geller and Matthew Lyon to review.

Here's what I think:

Don't write an expiry date into your name (or what I learned from Space 1999):

There's an elephant in the room; let's ignore it no longer. It is the most unfortunate coincidence of timing that this book on Apex 3.2 has come out the same week we've all been going crazy about Apex 4. Definitely embarrassing. But does this mean that the book, like Benjamin Button, is born already old and out-of-date? In some ways the answer, unfortunately, is yes; but until the market is flooded with Apex 4 books you should not let that put you off this book. The core of Apex remains unchanged, and this book covers that admirably.

Sequence does not matter (or what I learned from Quantum Leap)

I must admit that I had a serious issue with the sequence in which the authors chose to order their topics. I completely understand their thinking, but when I buy an Apex book I do not want to spend the first 40 pages reading about DOM objects, javascript, CSS and html before the first real mention of Apex, and another 40 pages before I get my first real look at the IDE!

However, I understand that technical books are not necessarily meant to be read sequentially. If you buy this book feel free to skip straight to page 79; you can return to the earlier pages later. And you should, because a lot of it is actually informative (I, personally, needed to read the section on shortcuts on page 63).

People talk about what they know (or what I learned from House)

"If you go to an oncologist with a headache," Dr House says in one episode, "he'll diagnose cancer; but take that same headache to an optician and he'll recommend new glasses."

This book is full of references to right-to-left languages and Apex's globalisation abilities. I'm guessing this is something one or both of the authors are interested in. If this is functionality that you require then you really must buy this book.

The story is in the details (or what I learned from The Wire)

Reading this book I made a list of subjects I felt the authors covered very well, and of those capabilities of Apex that I - not a newbie in Apex but far from an expert - was discovering for the first time. And I'm pleased to report that this list was much longer than I expected it to be. From whole chapters like the very useful section on Best Practices (Chapter 24: a must-read for any newbie) and the chapter on Deployment (Chapter 20), to little things using $v in Apex Ajax instead of $x(itemName).value.

There's more. Their work on the APEX_ITEM api was exhaustive (I did not know about APEX_ITEM.MD5_CHECKSUM). And, as an Oracle Forms developer, I was very interested in Chapter 23, which is about migrating applications from Forms. I got the impression that this was not necessarily something that interested the authors as much as it interested me, but it is good to find a book that covers the subject.

Nothing's perfect (or what I learned from Lost)

The chapter on migration wasn't the only time I felt that perhaps the authors were bored with a particular aspect of their subject. The whole section on the Apex IDE was often merely descriptive rather than explanatory.

In addition, I understand that they needed to include a section on the Apex SQL Workshop for completeness, but if you're an Oracle developer and you don't use TOAD, PL/SQL Developer, SQL Developer or even SQL*Plus then you really shouldn't be over here playing with the big kids. Apologise to the rest of the class and then leave the room immediately.

Conclusion

As I said earlier we cannot ignore the fact that Apex 4 casts its wide shadow over this book. Only you can decide how important a factor this is to you. Outside of that fact, this is quite a good book: if you are a newbie it will not get you started on its own - for that you will need the Internet's resources (start here: apex.oracle.com) - however, the authors' approach to Apex is often quite theoritical (and not just brutishly practical as many technical books can be) and this will furnish you with the background information you will need if you wish to do more than merely dabble with this technology.

If you are an experienced Apex developer the answer to the question "should I buy this book?" depends largely on your attitude to technical books. Personally, I like them. Google has pretty much killed off technical reference books, but I think there is still room for books like this that give you more information than a cursory web search can.

I'd love to tell you more, but I've got to go watch Jerry Springer. My nephew might have some questions about the life of the average, normal American.