Dancing and Wrestling with Oracle APEX

Alter session set triggers disabled (or The personal anti-ninja assassin insurance policy)

2012-02-07T15:00:00.000Z

If your database was a city, your tables would be buildings, your constraints would be the streets and motorways that connect them, your records would be the citizens that populate them, your views the nightclubs and restaurants where like-minded records go to hang out.

And triggers? Well, they'll definitely be ninjas. Think about it: easily overlooked, moving with stealth, 'firing' on command, shrouded in mystery.

Triggers, of course, are immeasurably useful. You don't need me to tell you that. However, there are times when you wish you could flip a switch and turn them off: imagine you are inserting or updating a truckload of data and cannot afford the overhead, or you are tracking a simple statement and do not want it to take any unexpected detours.

Of course, you can knock up a quick anonymous pl/sql block to loop through all your triggers (or at least all the relevant ones) and dynamically disable them.

/*
** This block will disable all database triggers. Do NOT do this.
*/
begin
  for i in (select trigger_name
            from user_triggers) loop

            execute immediate 'alter trigger '||i.trigger_name||' disable';
  end loop;
end;

"Ladies and gentlemen, this is your pilot speaking. We will soon be flying across the Himalayas mountains. I will fly this section with my eyes closed. Because I can. Please, sit back and enjoy the complimentary peanuts and your in-flight movie, Alive."

Just because you can do a thing, does not mean that you should. You wrote those triggers for a reason; so unless you can guarantee that no other users will be using your database, switching them all off for any length of time might be a tad unwise. What you need in those circumstances is the ability to disable triggers for only your session. It'll be like your personal anti-ninja assassin insurance policy.

Unfortunately, ALTER SESSION SET TRIGGERS DISABLED; is not a valid Oracle command. So until it is, we are going to need to create a substitute for it.

The easiest way to do this would be to create a session variable, a boolean that you can switch on or off, and then edit your triggers to only fire dependent on the state of this variable.

The first bit is easy. A variable in the specification section of a database package makes a perfect session-specific boolean.

create or replace package trg_pkg as
   skip_trigger  boolean := FALSE;
end trg_pkg;

Now all we need do is add a line as the first bit of executable code in our triggers checking the state of our trg_pkg.skip_trigger variable. If it is TRUE, we exit the trigger without executing any code; if not, we fire the trigger as normal. Locate the triggers you may want to switch off and add the following to them:

 
...
BEGIN
  if trg_pkg.skip_trigger then return; end if; -- do not execute the trigger.
...

And that's it. Whenever you do not want triggers to fire in your session, all you need do is flip your trg_pkg.skip_trigger switch. The triggers won't fire for whatever commands you carry out, but will work as normal for everyone else. (For security reasons, you will probably want to control which users are granted execute permissions on your trg_pkg package.)

One last thing, if you want to add the clause to all the triggers in your schema - as I have needed to do recently - you might find the following code handy.

/*
** This script will rebuild all triggers adding a clause which
** prevents the trigger from firing if trg_pkg.skip_trigger is TRUE.
** This code presumes that no trigger is longer than 32767 characters long. 

** 04/02/2012  David Njoku * Created this script.
*/

declare 
 vSql  varchar2(32767);
 vBackup  varchar2(32767);
 vString  user_source.text%type;
 
 vCLAUSE  CONSTANT varchar2(500)  := ' if trg_pkg.skip_trigger then return; end if; -- Do not execute this trigger if trg_pkg.skip_trigger is set to true. ';
 
 
begin 
 -- Find all the triggers that relate to this schema's tables.
 for i in (select at.trigger_name, at.owner
    from all_triggers at, all_objects ao
    where at.table_owner = USER
    and at.status = 'ENABLED'
    and at.trigger_name = ao.object_name
    and at.owner = ao.owner
    and ao.object_type = 'TRIGGER'
    and ao.status = 'VALID'
    and not exists (select 1
      from recyclebin
      where object_name = at.trigger_name)) loop 
    
    -- Now that we've got the trigger name and owner, we need to grab a hold of the existing code, in case we need to revert.
    vBackup := ' CREATE OR REPLACE ';
    for j in (select replace(text,chr(10),' ') text 
       from all_source
       where owner = i.owner
       and name = i.trigger_name
       and type = 'TRIGGER'
       order by line) loop 
       
       vBackup := vBackup||chr(10)||j.text;
    end loop;
    
    /*
    ** We now need to start building our new version of the trigger.  The plan is to add our if clause immediately after the BEGIN 
    ** Some triggers will contain more than 1 BEGIN. Since we cannot tell which is the correct one, we'll need to add our clause after
    ** all of them.
    */

    vSql := ' CREATE OR REPLACE ';
    for j in (select replace(text,chr(10),' ') text 
       from all_source
       where owner = i.owner
       and name = i.trigger_name
       and type = 'TRIGGER'
       order by line) loop 
       
       -- do we need to add our clause here?
       if replace(upper(j.text),' ','') like '%BEGIN' then 
        vSql := vSql||chr(10)||j.text||chr(10)||vCLAUSE;
       else 
        vSql := vSql||chr(10)||j.text;
       end if;
    end loop; 
    
    -- Now we've got our trigger we should try building it.  
    begin 
     execute immediate vSql;
    exception 
     when others then 
     dbms_output.put_line(i.trigger_name||' '||sqlerrm);
    end; 
    
    -- If the trigger was not built successfully for whatever reason we should revert to the orginal.
    for j in (select 1
      from all_triggers
      where trigger_name = i.trigger_name
      and owner = i.owner
      and status = 'DISABLED') loop 
      
      begin   
       execute immediate vBackup;
      exception 
       when others then 
       dbms_output.put_line('BACKUP '||i.trigger_name||' '||sqlerrm);
      end;
    end loop; 
 end loop;
end;
/

Introduction to Regular Expressions in Oracle

2012-01-18T22:21:00.000Z

Regular expressions – RegEx or RegExp to theirfriends – were fashionably late in coming to the Oracle party. The seeds ofwhat we know today as regular expressions were formulated in the periodimmediately following the Second World War in fields as diverse as formallanguage theory and neurophysiology; but it wasn’t until the PL/SQL Web toolkitarrived for 8i and 9i that they popped their heads over the Oracle parapet. Bythis 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 flexiblepattern-matching in strings of text.

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

String pattern matching has always been possible inOracle sql and pl/sql, of course. With the LIKE condition and the simplemetacharacters of “%” and “_”, character patterns could be described andintegrated into queries. However, the moment you required a little complexitythese basic wildcards cowered in fear. An example: my name is David, butfriends tend to call me Dave when they have a beer or two in them. To matcheither spelling of my name, not using regular expressions, you would have toquery the table for ‘Dav%’. But then your results might be flooded withDavidsons and Davises and Davinas. And I’m definitely not pretty enough to becalled Davina!

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

2012-01-10T12:00:00.000Z

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

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

2012-01-03T12:00:00.000Z

Thepolygamist pauses, his hand hovered above the doorknob. He looks back down thehallway, blinking in the harsh light of the naked bulb he has just flipped onto do war with the shapeless darkness of the night. A few paces away - nofurther away than a firm decision - stands anotherdoor: silent, inviting. His gaze returns to the door in front of him; also assilent as the entrance to Aladdin's cave. Which door should he open? Which ofhis 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 readingLola Shoneyin's The Secret Lives Of BabaSegi's Wives, but that's about it.

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

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

Youwill probably want to base your decision on factors more concrete andquantifiable than imaginary asteroids, so here are some things to consider.

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

Platform: A huge part of your choice must dependon the demands of the project itself. For instance, what platform are youhoping to run it on? If you are planning to take advantage of the current boomin mobile computing, then Apex should be your choice. I must admit that I havenot yet tried to run a Forms application on a phone or a tablet, but I do notsuspect 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 laptopsusing a myriad of browsers, with or without javascript enabled, then possiblyForms with its browser-agnostic java applet might be worth a look-in.

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

Audience: Who is your application aimed at? If itis 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 theold girl, but I doubt that it will ever be truly suitable for the world-wideweb.

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

Extendability: The answer to the question ofwhich is the more extendable technology may not be as obvious as it may firstseem. Being a web technology, Apex can more easily sup at the banquet of ajaxand javascript, can more easily flirt with jQuery and whatever the next bigthing will be. But with each iteration of Forms, its door is opened wider, withJava pluggable components and javascript and CSS support. However, I believethat Apex will always outpace Forms in this respect; the story of the tortoiseand 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 foreverengaged in a battle with Oracle Application Server. One of them - OAS or the ITguys - isn't very good at their job. Since no one at Oracle has ever bought mea drink at the pub, I'll assume it's their fault. Apex is easier to deploy thanForms; indeed there is a growing number of companies that will host your Apexapplication for you.

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

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

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

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

Of Comments And Panties...

2011-12-22T19:52:00.000Z

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)

2011-09-14T20:49:00.000+01:00

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)

2011-02-27T16:51:00.010Z

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)

2010-08-22T11:53:00.008+01:00

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)

2010-07-19T09:26:00.014+01:00

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.

Should you upgrade to Apex 4.0? (or Apex - with extra Katherine Heigl!)

2010-06-24T17:38:00.015+01:00

There comes a time in the life of every man (and be warned that I spend all my time outside of work watching cheesy Hollywood romcoms and this might have warped my sense of reality) when his wife comes to him and says these words:

"Do you think I should get breast implants?"

In that instant the man is faced with the most crucial of dilemmas: it's not that he doesn't love his wife (who, in a Hollywood romcom, will inevitably be played with cutesy kookiness by Katherine Heigl), but surely an upgrade is always a good thing? After the implants she won't just be his darling Katherine Heigl, she'll be be darling Katherine Heigl - with extra Katherine Heigl!

Developers all across Apexdom are waking up to the same dilemma this morning, because of the following bit of news:

That's right, Oracle Application Express 4.0 has abandoned the shy coyness of beta mode and is finally fluttering its flirty eyes at us as a full release. Come and get me, boys (and girls), it seems to be saying. We've all always loved Apex - but Application Express 4.0 is Apex, in a manner of speaking, with extra Katherine Heigl!

Over the past 2 years my company has strengthened our commitment to Apex. It is now the first solution we look to for every project we get and, despite the fact that Oracle still seem intent on marketing it as an lightweight means of pimping your Excel spreadsheets, it has been able to cope with everything we have thrown at it.

Apex 4.0 wears its bling with ease: its UI has been tarted up impressively; its Dynamic Actions is a new, simple declarative interface for the creation of javascript/jQuery; its Plug-ins is unquestionably the first step in bringing an iTunes-style 'app store' that will enable developers share clever bits of code to Apex; and its Websheets gives developers the ability to easily add Google Docs-style collaboration to their applications. Oh, and interactive reports, the crown jewel of Apex's previous incarnation has been spruced up too. All in all, Application Express 4.0 is definitely a huge step forward from version 3.2.

So should we upgrade immediately? Should you?

In our Hollywood romcom Katherine Heigl will probably not get those breast implants. Instead she'll learn, through a hilarious sequence of events triggered by an encounter with a wise, old Chinaman, to love who she is inside. But, gorgeous as she is, you should be ashamed of yourself - why are you letting Ms Heigl take your technical decisions for you?

Here are the questions we are asking ourselves to help us decide whether to upgrade:

Can we upgrade? 4.0 requires, at the very least, a 10.2.0.3 database. Our development environment is a 9.2 database (we choose to develop using a database version that matches that of our most backward customer). Before we upgrade we'll need to sort this out.
What are the risks of upgrading? It is rarely wise to be the first guy to scream Leeroy Jenkins! and go rushing in to upgrade. It's usually wiser to let others make mistakes and then gingerly step over their bodies.
What are the risks of not upgrading? Minimal, in the short run. In the long run, of course, you don't want to fall too far behind.
Do you need to upgrade? Of course not. Apex 3.2 was a fantastic product and I assume that you are happy with the applications you have built with it. But 'need' is one thing, 'want' is another. The wheel was a great invention - but hey, wasn't it a sweet thing that Mr Dunlop improved it by wrapping it in rubber? Technology creates its own need: I bet that in a year you won't be able to imagine Apex development without plug-ins.
How easy is it to upgrade? Having not upgraded myself I can only pass on hearsay. I have heard that it's as easy as pie. However, do remember that you will need to upgrade the environments of all your clients too if yours is an application that is deployed in multiple locations.

So here's what I would advise: wait a little - not too long - and then update your development environment to 4.0. Play around with it (or, if you prefer, play around with it at http://apex.oracle.com) and discover how it can make your applications better.

So I guess what I'm saying is this: Yes, Katherine Heigl, do get your implants - but perhaps, one breast at a time?

The coming of Apex 4 (or 12 kilometres of features)

2010-02-28T23:33:00.003Z

Take me to Bloggers Square and have all the other Apex Bloggers hurl rotting vegetables at me, because I have barely had a moment to glance at Apex EA4. Work gets in the way, unfortunately. However, what I have managed to see of it so far is impressive. Actually, it's a lot more than merely impressive, it's ... exciting. And how often do we get to say that about technology that doesn't have a name that begins with a lowercase i?

Anyway, I thought I should do my own little thing towards building up anticipation of this new version of Apex. And so, without further ado, as the vicar said to the actress: Here's my little thing...

Calling stored procedures from apex pages (or The Da Vinci PL/SQL Code)

2010-01-19T19:54:00.008Z

Hollywood director Ron Howard had a problem. He'd been given the bestselling book in the world to turn into a movie. But while The Da Vinci Code was undoubtedly a page-turner, it did not readily lend itself to celluloid. After all, the story was about a professor of religion - not about a wisecracking, shoot-first-ask-questions-later action hero who likes to blow stuff up and make love to beautiful women. How do you make that exciting?

What Ron Howard did was this: he played loud, suspenseful mood music even in scenes where Tom Hanks is merely racing through cathedrals or reading books in the library. How else could he make a film about Roman catholic history seem exciting?

Last week, a colleague of mine was heading to a client's site for an important demo of one of our Apex applications. Sitting in the back of a taxi, 10 minutes away from the meeting, he tested the application by pressing a button and then ...

[insert loud suspenseful music here]

- an error!

HTTP 403

Forbidden

The requested operation is not allowed.

He was now 7 minutes away from a demo which could make or lose our company half a million pounds... 6 minutes away ... 5 minutes...

[more suspenseful music ... drums that sound like heartbeats ... ]

Tom Hanks quickly consults Google; it spits back a million unhelpful results ("want to buy cheap meds from Canada?") ... 4 minutes ... 3 minutes...

And then he read about the wwv_flow_epg_include_mod_local function.

Here's the deal with this function. It is in your Flows_xxxxx schema and if you wish to execute a stored procedure directly from your URL (http://.../apex/schemaName.procedureName) you need to edit this function, explicitly naming the stored procedures that you wish to run. Specific details of how to do this are available here.

... 2 minutes to deadline ... 1 minute ... 30 seconds...

Tom Hanks quickly edits the function. He comments out the apposite sections and adds his procedure name to the list. He executes the function. [... 15 seconds ... 10 seconds ...]

It works.

... 0 seconds ...

The End.

Epilogue: My colleague is happy to report that the demo went well and we are in with a good chance of winning the contract. His name is not really Tom Hanks. (It is Steven Seagal.)

At fault for this problem, of course, is Oracle. Apex is undoubtedly their most developer-friendly tool, but it is mind-boggling that there isn't a declarative way of updating the wwv_flow_epg_include_mod_local function. Also since it resides in the Flows schema it is the easiest thing in the world to update it in the production environment but forget to make the same changes when deploying at a client site (after all, the function is not exported with your application).

Oracle Forms v Apex (or Please Lean Forwards Jennifer Lopez)

2009-12-31T14:20:00.009Z

It is the 31st of December, 2009. The sun is setting on the year, the lifeforce is draining from the decade. Across the globe people are assessing the past and preparing for the future. And newspapers are overflowing with important newsstories like The 10 Best Celebrity Outfits of the Decade.

At this time of year it's obvious what the author of an Oracle Apex blog will write about. Surely it'll be an indepth article on how Apex can take over the world in the next decade, replete with annotated footnotes. Or maybe I'll write exhaustively about Apex 4.0, outlining the bright future it heralds for all Apex developers or bemoaning it as an opportunity lost. Right? Right? Wrong.

[By the way, in case there are any journalists reading, the most important celebrity outfit of the decade was the Versace dress Jennifer Lopez wore to the Grammy's in 2000 because it declared that the new decade would be one of daring, outrageous fashion. Trust me, I know these things (because I just read it in Cosmopolitan magazine).]

Instead, I'd like to talk about the technology that has bookended the decade for me. In 2000 I got my first job developing with Oracle Forms 4.5, and these past few months, instead of developing in Apex, I have been converting my company's (massive) Oracle Forms application from client/server to the web.

I know I've been very disparaging of Oracle Forms in this blog - but you know what? It's been an utter joy. I and my team were able to transform the application from drab to fab (I'm quoting Cosmo again. Sorry).

We've taken it from this:

To this:
Along the way we cursed Oracle (again) for their shambolic documentation (want to read up about set_custom_property? Well, tough!). Eventually, we discovered that the internet (especially The Forms Look and Feel Project, the PJC Community and FRITE) was our best resource. Oftentimes we had to scale back our ambitions (giving our canvases a nice textured look made the form flicker unacceptably when loading). But eventually, we ended up with a product that we are proud of.

I won't go into further detail because this is, after all, an Oracle Apex blog - and Tiger Woods has taught me that it's not wise to publicly cheat on your first love. However, if you are interested in modernising your Oracle Forms do feel free to drop me an email.

So in conclusion, if I was given a choice between moving client/server forms to Apex or Oracle web forms, what would I choose? It depends. Moving them to Oracle web forms is definitely easier and you will end up with a product that you'll be proud of. But. But it'll still be an applet (uurgh!) and I still believe that Apex is more future-proof. Yes, Oracle Forms has opened the door to the world of Java, but Oracle Apex opens the door to the whole world.

Somehow I think that come 2019, I'm more likely to be writing about Apex. Forms will be forgotten.

EDIT: This story appeared in the British Guardian newspaper a couple of days after I first wrote this post. Hmm, maybe fashion journalists do read this blog after all!

Readying database triggers for Apex (or Will Smith's stamp collection)

2009-09-25T19:35:00.006+01:00

If you've written database triggers that record the username of the person who's updated a record [:new.modified_user := USER;] you probably discovered soon after you switched to Apex that the column was full of APEX_PUBLIC_USERs, the middleman through whom all Apex transactions with the database must pass. The solution, you probably realised, was easy: to get the name of the user of your apex application you need to make a call to APEX_APPLICATION [:new.modified_user := nvl(apex_application.g_user,USER);].

But it's not always that simple and uncomplicated; if it was I could end this blog entry right here and get back to watching my latest shameful TV addiction, Dating in the Dark.

Here's how Dating in the Dark works: 3 guys and 3 girls live in separate sections of a house and only get to meet in complete darkness so that the impressions they develop of each other are based totally on personality and not looks. Soon they pair off and have a number of dates (still in absolute darkness). Then, once they're sure they're totally in love, the lights are switched on - and they get their first looks at each other. And they're asked if they wish to continue the relationship.

Of course, because this is reality TV we're served up the weirdest combinations. And so the nerd with a mole the size of Switzerland and the pink Homer Simpson-print trousers is paired with the blond bimbo with shop-bought boobs so new they've still got the pricetag on.

But darkness is a great equaliser, and when those lights go off boring old John Smith the librarian with the stamp collection and poster of Marie Curie on his bedroom wall can become cool, can transform into Will Smith.

Which (coming back to my complication) is kinda what I wanted my database to do too. Because my database pre-dated my Apex application it already contained hundreds of triggers that would need editing. But with gold like Dating in the Dark on the telly, who has the time? What I needed was a script that would metaphorically turn the lights down on my boring database and allow it transform into a superstar, a script that'll run through my database, find my triggers, and 'apexify' them.

Well, here's that script:


/*
** This script will 'Apexify' mod triggers if Apex is installed.
** Most tables in the database have a column called MODIFIED_USER
** and accompanying triggers that set it to the current user
** after a row has been updated or insterted.
*/
set serveroutput on;

DECLARE
 vSql   VARCHAR2(32767);
 vInstalled BOOLEAN := FALSE;
 vText   VARCHAR2(4000);

BEGIN

 -- First thing we've got to do is check if Apex is installed.
 for i in (select 1
  from all_users
  where username like 'FLOWS%') loop

  -- If we get here it means it's installed.
  vInstalled := TRUE;
 end loop;

 if not vInstalled then -- No need to continue.
  RETURN;
 end if;

 -- Find all affected triggers.
 for i in (select name
  from user_source
  where upper(text) like '%:NEW.MODIFIED_USER%:=% USER;%'
  and type = 'TRIGGER') loop
  
  -- Now get the code for the trigger.
  vSql := 'CREATE OR REPLACE ';
  for j in (select text
   from user_source
   where type = 'TRIGGER'
   and name = i.name
   order by line) loop

   if upper(j.text) like '%:NEW.MODIFIED_USER%:=% USER;%' then
    vText := replace(upper(j.text),' USER;',' NVL(APEX_APPLICATION.G_USER,USER);');
   else
    vText := j.text;
   end if;

   vSql := vSql||vText;
  end loop;

  -- Now that we've built the script for this trigger, run it.
  begin
   EXECUTE IMMEDIATE vSql;
  exception
  when others then
   dbms_output.put_line(i.name||' '||sqlerrm);
  end;
 end loop;
END;
/

Harvesting Apex_Util in Oracle Forms (Or zombies are just misunderstood)

2009-07-18T12:19:00.008+01:00

Consider this. The internet owes its early roots to military research. So every time you send an email, update Twitter or google for photos of Borat in his mankini (don't deny it; you know you do) you're effectively profiting from the deaths of helpless children, women and men. You're kinda like a zombie tap-dancing along a beach of blood, bones and brain matter. How do you sleep at night?!

In truth, most times something big is born the fallout is as useful as the main event. Which kinda got me thinking. Apex is the biggest, most innovative, freely available PL/SQL project ever built (that I know of) so I wondered if there were any useful functions or procedures I could borrow from it to use in, say, my Oracle Forms applications.

Here are some I found in the APEX_UTIL package: (If you're new to Application Express one of the most productive things you can do is spend an afternoon studying its packages and views.)

PROCEDURE: Pause(p_seconds in number): I haven't got a clue where this is used in Apex itself but it's so endearingly useful. For all those times where you want to slow your Oracle Forms application down, now you've got a simple solution: apex_util.pause(p_seconds); (where p_seconds is the length of time in seconds that you wish to pause for, up to a maximum of 120).
FUNCTION: Get_since(p_date date) return varchar2: If like me you've always felt that the ability to express a date as a function of time passed is the one flavour missing from the TO_CHAR chocolate box then this bad boy is just what you need. Cleverly it expresses time not as cold, mechanical fractions but as rounded up terms that make human sense (e.g, 1 year ago or 3 weeks ago).
PROCEDURE: export_application(p_workspace_id in number, p_application_id in number): This procedure (and the sidekicks it brings along like exuberant, kid brothers, export_application_page(p_workspace in number, p_application_id in number, p_page_id in number) and export_application_component(p_workspace_id in number, p_application_id in number, p_component_id in number, p_component_type in varchar2)) exports an application or a page to an HTP buffer. Nothing that revolutionary there, but as the number of applications I have mounts and begins to spread across workspaces, I am beginning to investigate ways of managing them easily. One of the ideas I'm toying with is build an (Oracle Forms?) application that gives me a better, more easily manipulated, view of my applications than the Apex environment does.
FUNCTION: filesize_mask(p_number in number) return varchar2: This takes in a file size in bytes and expresses it as a size in KB, MB or GB. Doesn't change the world - but then, neither does Ben & Jerry's Chocolate Fudge Brownie ice cream, and everybody loves Ben & Jerry's Chocolate Fudge Brownie ice cream, right? Yum.
FUNCTION: strong_password_validation: Time for a confession: I have not yet given this function a go. But if it does what it seems to be saying on the tin then it sounds very useful indeed. If you have tried it, please let me know.

I'm from Africa so it's only right that I throw in one of those exotic proverbs that Africans in Hollywood movies seem so fond of: when the lion feeds she leave enough meat for the vultures. Which either means that if you sit around doing nothing for long enough somebody's gonna give you their leftover hamburgers. Or it means that even if you've not yet made the move from Oracle Forms to Oracle Application Express that doesn't mean that you can't come dance at the Apex party.

No code here (or breaking knives and bending spears)

2009-06-11T15:05:00.002+01:00

Unless you were the kind of geek who was bullied mercilessly at school (and, since you’re reading a technology blog, there’s every likelihood you were), chances are that you don’t spend too many evenings in the pub drinking non-alcoholic beer. Because what would the point of that be?

Well, I have bad news for you, my friend, because this entry is just like non-alcoholic beer, because it contains no Apex code at all. It’s made up entirely of the musings of a man who has been an Apex developer for a year. There’s a small chance that it might be of interest to developers who are taking their first tentative steps down this road. And the rest of you? Well, to ensure that these next few minutes are not a complete waste of time, why don’t you do a buttocks-clenching exercise while reading? I hear it does wonders for your physique. Clench … relax … clench … relax … clench … clench … clench … relaaaax.

I have been working fairly steadily on Apex projects since my last entry (well, whenever I wasn’t away on holiday: click here or here if you enjoy the occasional stab of jealousy). None of them has been ground-breaking or earth-moving – just your usual confection of PL/SQL, javascript, ajax and googling. The questions I face these days are mostly mundane (example: how do you set the default number of rows returned by an interactive report? Answer(ish) here.)

All of which has led me to this realisation: Oracle Application Express is, in itself, quite boring. Just like Oracle Forms and every other development platform and language ever invented (with the exception of Oracle ADF which is the evil spawn of Satan). I know this should not have come as a surprise to me, but during those heady first days of constant challenges and adventures of discovery it is easy to lose sight of this fact of life: even beauty fades to ordinariness with time. No wonder there’s talk of Angelina Jolie and Brad Pitt breaking up, and Halle Berry’s ex cheated on her repeatedly.

The excitement’s got to come from the project itself.

The good news, on the other hand, is that Oracle Application Express does seem to be gaining in popularity. In the past fortnight alone I’ve received 3 phone calls from recruitment agents desperately looking for Apex developers to fill well-paid positions. Which is encouraging, considering the world economy’s supposed to have imploded and whatnot.

Hey, how’s the butt-clenching going? Feel the burn, my friend, feel the burn! Keep this up and you’ll soon have a bum firm enough to break a knife or bend a spear.

And isn’t that what life’s all about?

Sharing code between multiple applications

2009-03-29T20:03:00.000+01:00

9 months ago when I started this blog I was like a biblical prophet, the only apex developer in my company, the lone voice in the wilderness. However now I am part of an ever-growing team of apex developers, as my colleagues are being converted (rarely kickin'n'screamin) from Forms. I know it's always a mistake to extrapolate personal experience and apply it globally, but I do take it as a sign of Application Express's increasing popularity.

However, with popularity comes extra burdens, and different expectations. We found, for example, that Apex is excellent for rapidly building one-off applications of a small-to-medium size - but are its shoulders sturdy enough to bear the responsibility of being the main development tool of a software company?

First, a little background: my company sells a large database to UK local authorities, and has developed a large (Oracle Forms) application to go with it. We often, however, develop smaller, ancilliary applications to go with it. With Forms we find it easy to share forms between these applications - shipping copies of the same .fmx with various applications, while maintaining a single source code.

How can we do the same with Apex? Yes, we can export pages from one application and import them into another, but Apex doesn't maintain a link between them, and changes in the master copy aren't propagated to its copies. So what should we do?

We decided that we had 2 choices:

1. Build one huuuuge application containing all the pages we would ever need and control the availability of these pages using conditions and build options. So when Customer 1 and Customer B come to us with very different specs we sell them the exact same application but switch on pages 1, 21 and 53 for Customer A and pages 21, 34 and 876 for Customer 2. We'll also need to customise the branching within the app, so page 21 leads to page 53 for Customer i, but to page 876 for Customer ii.

Not an elegant or very practical solution.

So instead we decided to go for (2). We will still build one huge application, but this time we'll use it only as a sort of repository for many dozens of basic pages. Each of these pages will carry out a single, simple task - display the details of a single record, edit a record, or maybe display a report. In addition, these pages will be plain vanilla, lacking any visual flourishes - no fancy javascript or anything.

And then whenever we get an order from a customer for an application all we'll need to do is build a sort of shell app, which will control things like the look'n'feel and the branching, but whenever it needs to do anything it will use an iframe to call the relevant page from our page repository and house it within a region. Of course this means that we'll need to install both applications at each customer site, with them running only the 'shell' app.

Is this system perfect? Of course not, but it is the most manageable way (that we can think of - cos our brains have been addled by drug-use and overexposure to naughty websites) to ensure that we maintain a single source code that we can propagate out to all of our applications, and are able to build new applications rapidly and reliably.

(Oh, and I was joking about that drug-use and pornography thing, by the way. Well, at least for myself - I can't vouch for my colleagues! In fact, now that you mention it...)

However, I'll be very interested in hearing if you've faced a similar issue and what you did to overcome it. How do you share code between your apps? Are there holes in this system that we've come up with that perhaps we haven't thought about? What do you think?

The 3rd way to export applications (or Oracle are(n't) the Devil)

2009-03-24T20:33:00.003Z

Not too long into your career as an Oracle developer - perhaps after your database has spewed a stream of error messages in your face kinda like the green vomit in The Exorcist - you begin to suspect that Oracle Corp. is the Devil. The Devil: not merely bad, not merely irritating - but Lucifer, Beelzebub, Satan, Esu, the Antichrist himself. So much so that whenever I meet an Oracle employee I study them to see if they've got horns or a tail, and I sniff them to see if they smell of hellfire and brimstone.

This week at work we upgraded our Apex environment to version 3.2 from version 3.1.2. The upgrade seemed to go smoothly, but for some reason it turned our applications buggy. Pressed for time, we decided to downgrade back to 3.1.2 and we followed the instructions (here and here), but that didn't go so well either. The package sys.wwv_dbms_sql wouldn't compile; it complained that the wwv_dbms_sql.parse_as_user procedure had errors.

With absolutely nothing working now we decided to completely uninstall and reinstall Apex. But first we had to export our applications.

And this is what I wanted to talk to you about. You obviously know that you can export an application from within the Application Express development environment. No need to talk about that. You probably even know that you can export applications using SQL Developer, but perhaps, like me, you are unaware that there is a third way to export applications.

In the apex/utilities folder (that you got when you first downloaded Application Express), there are 2 java programs - APEXexport and ApexExportSplitter - and a readme file. The readme.txt is short but tells you all you need to know about using the java programs, including setting up your classpaths.

APEXexport takes a number of the usual boring parameters - db, user, password - but more interestingly you can feed it an application id (if you want to export a single application), a workspace id (if you want all the applications in a particular workspace) or the word instance if you wish to export all applications irrespective of workspace.

And, um, that's it. It works - no bugs, no quirks, no green vomit (unless, of course, you failed to follow the advice of the readme and set up your classpath first). It's very useful if you find yourself in a situation like me - i.e you are unable to get into your Apex IDE but need to salvage your applications before reinstalling Apex - or if you wish to write a batch file to backup your apps.

Very useful, very ... unevil. Hmm, maybe Oracle aren't the Devil after all...

Error creating database users in Apex (or my life as bald Britney)

2009-03-01T19:31:00.007Z

Hello, my name is David, and I have suffered a relapse.

I haven't blogged in many, many months: I'm sorry. The reason for the silence is simple; after all this time enjoying the fresh new oxygen of Apex, I've got to confess that I've fallen off the wagon a lot lately and gone back to developing in Oracle Forms. What can I say? For close to 10 years Forms has been my addiction; it's hard to go cold turkey. I keep sneaking back down those dark, open-sewered alleys to return to my dirty addiction. So I guess I'm a bit like Britney Spears. Except I've got no hair and I can't sing.

Um ... er... oh...

I have done some Apex development of recent and I ran into a problem. I was developing a small app that is designed to be used by a small number of users. I decided to make them database users, rather than make them Application Express users. To that end I created a user page and backed it up with a pl/sql page process containing the following code:


Begin
Execute immediate 'create user '||:P10_USERNAME||' identified by '||:P10_PASSWORD;
End;

So far, so simple. However, when I clicked my button I had an error spat in my face: ORA-01031: Insufficient privileges. Hmm, I'm like Tarzan - I've grown up in this jungle of error messages, the roar of an ORA number no longer fills me with fear. Obviously all I need to do is grant the CREATE USER privilege to my user.

But who? All Apex commands are executed as the APEX_PUBLIC_USER user (or whichever user you have specified in your dads.conf file). Thus my first thought was to grant the privilege to this user. Easily done.

I went back to my page, pressed my button - and again was greeted with the ugliness of an error message.

Further investigation (and some very gracious assistance from OTN users) led me to the "parsing schema". The parsing schema is the schema that backs up your application - of course you know what it is, but you can doublecheck by clicking on Shared Components >>> Edit Application Definition.

Ay, but there's the rub: logged into SQL Plus as this user I was able to create users. So what was going on?

To cut a long story short (and that's not something I often do) I found that if the parsing schema has the create user privilege as part of a role that it has been granted, the parsing schema will be able to create database users in SQL Plus - but not through an Apex app. To create database users through Apex you have to grant create user directly to the parsing schema.

And that, dear friend, is what I learned last week. Gotta go now; they're trying to make me go to Oracle Forms rehab. No no no...

LIVE! LIVE!! LIVE!!! (or girls! girls! girls!)

2008-12-19T23:12:00.004Z

When I was young and innocent, some friends and I, armed with money in our pockets, headed off to Soho in central London. If you've never been to London you may not know of Soho's seedy reputation: let's just say that as we walked down its littered sidestreets, past the dark Gothic pubs, past the shops that peddled adult movies and bondage gear, we tittered at the hand-written signs that said "Blonde busty model within". Finally we came across a club with a lurid, red fluorescent sign: GIRLS! GIRLS! GIRLS! LIVE! LIVE!! LIVE!!! and we knew we had to go in.

Half an hour later, we were thrown out - penniless - realising that the place was a barely-legal tourist trap where menacing Russian men threaten you into buying expensive drinks as they endlessly promised that the dancing girls were just about to show up.

So I guess it's no surprise that I woke up this morning with a leaden sense of foreboding: today was the day my first Apex application was going LIVE! LIVE!! LIVE!!!

And it went perfectly smoothly. We installed the Oracle Apex runtime environment without a hitch (although I did find out that if you run the install script - apxrtins.sql - passing in the images virtual directory as /I/ but have a lowercase /i/ in your dads.conf not all the images will show in your app).

And that's about it - nothing to report. Come Monday the users get their grubby mitts on it and come January they begin to use it in earnest. Meanwhile, I move on to my next Apex project. Can't wait.

Tables with multiple primary keys (or Halle Berry hates World Peace)

2008-12-16T21:17:00.008Z

In a perfect world Jews and Arabs would be best of buddies, wars would rage only behind the pixelated curtains of computer games, all hunger would be metaphorical rather than literal, the global recession would have been foreseen and avoided, and all database tables would be perfectly normalised. Oh, and Halle Berry would be in my bedroom now, dressed in nothing but a flirtatious look, waiting impatiently for me to finish this blog entry.

But no, we don't live in any sort of utopia and wars, hunger and debt scar the face of the globe, and most real-life databases are littered with poorly normalised tables.

(And Halle Berry still hasn't replied any of my emails!)

The database I work on is, perhaps, a little untidier than most, and one problem that I faced when I first started building an Apex application against it is the large number of tables with primary keys that are an unholy combination of multiple columns. Apex gives you some leeway (perhaps not enough) and will cope with two-column primary keys, but when you get to those miserable tables with four- and five-column primary key columns? You're on your own, my friend.

This is the point at which most experts would snootily tell you to simply sort out your database and change your tables. But we know that in real life that's not always an option. Which is why the generally recommended Plan B is to create a view based on your table and, with it, an instead-of trigger to manage the data. Your view should have a single column that represents the primay key (how about all your real primary key columns concatenated?) and your trigger will use this id to update the real table when it needs to.

However if, like me, you've got dozens of these messy tables all over the place it'd be great if you could run a script that'll find them and create the views and triggers for you. Well, today's your lucky day, cos I wrote such a script. It creates views called table_name_V with a column called view_id and triggers called table_name_V_TRG to manage them.


/*
** This procedure creates a view for all tables in the database that
** have more than 2 columns in their primary key. The views it creates
** are named [table_name]_V (unless the table name is longer than 24
** characters long in which case it only takes the first 24 characters).
**
** This procedure also creates INSTEAD OF triggers for the views that it
** creates. These triggers handle updates, insertion and deletion from these views.
**
** Finally, the procedure ensures that all the grants that the view needs
** are in place.
**
** 1    15/12/2008    David Njoku     * Created this script.
*/
DECLARE
vView VARCHAR2(32767);
vTrg VARCHAR2(32767);
vKey VARCHAR2(32767);
vKey1 VARCHAR2(32767);
vUpd VARCHAR2(32767);
vDel VARCHAR2(32767);
vIns VARCHAR2(32767);
vVals VARCHAR2(32767);
vPriv VARCHAR2(32767);
vWhr VARCHAR2(32767);


BEGIN

-- Find the tables.
FOR I IN (SELECT UC.TABLE_NAME, COUNT(UC.TABLE_NAME)
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
HAVING COUNT(UC.TABLE_NAME) > 2
GROUP BY UC.TABLE_NAME) LOOP

-- Start building our scripts.
vView  := 'CREATE OR REPLACE VIEW 'SUBSTR(I.TABLE_NAME,1,24)'_V AS 'CHR(10)' SELECT ';
vTrg := 'CREATE OR REPLACE TRIGGER 'SUBSTR(I.TABLE_NAME,1,24)'_V_TRG 'CHR(10)' INSTEAD OF UPDATE OR DELETE OR INSERT ON 'I.TABLE_NAME'_V '
 CHR(10)' /* '
 CHR(10)' ** This trigger has been automatically generated '
 CHR(10)' */ '
 CHR(10)'BEGIN '
 CHR(10)' IF UPDATING THEN '
 CHR(10)'   UPDATE 'I.TABLE_NAME' 'CHR(10);

-- Now let's find out what the columns are. First empty our variables.
vKey  := NULL;
vKey1 := NULL;
vUpd := NULL;
vDel := NULL;
vVals := NULL;
vWhr := NULL;


FOR J IN (SELECT COLUMN_NAME
 FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
 WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
 AND UC.CONSTRAINT_TYPE = 'P'
 AND UC.TABLE_NAME = I.TABLE_NAME) LOOP


 -- vKey will be the columns concatenated with 
 -- a period between them. You'll see why later.
 IF vKey IS NULL THEN
  vKey := J.COLUMN_NAME;
 ELSE
  vKey := vKey'''.'''J.COLUMN_NAME;
 END IF;

 -- vDel is for the delete bit.
 IF vDel IS NULL THEN
  vDel := CHR(10)'   DELETE 'I.TABLE_NAMECHR(10)'   WHERE 'J.COLUMN_NAME' = :OLD.'J.COLUMN_NAMECHR(10);
 ELSE
  vDel := vDel'   AND 'J.COLUMN_NAME' = :OLD.'J.COLUMN_NAMECHR(10);
 END IF;

 -- vWhr is there where clause for updating.
 IF vWhr IS NULL THEN
  vWhr := CHR(10)'WHERE 'J.COLUMN_NAME' = :OLD.'J.COLUMN_NAMECHR(10);
 ELSE
  vWhr := vWhr' AND 'J.COLUMN_NAME' = :OLD.'J.COLUMN_NAMECHR(10);
 END IF;
END LOOP;

-- Now lets get all the columns in the table.
FOR J IN (SELECT COLUMN_NAME
 FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = I.TABLE_NAME) LOOP

 -- vKey1 will the columns separated by commas.
 IF vKey1 IS NULL THEN
  vKey1 := J.COLUMN_NAME;
 ELSE
  vKey1 := vKey1', 'CHR(10)J.COLUMN_NAME;
 END IF;

 -- vVals are going to be used in the insert section.
 IF vVals IS NULL THEN
  vVals := '  :NEW.'J.COLUMN_NAME;
 ELSE
  vVals := vVals','CHR(10)'  :NEW.'J.COLUMN_NAME;
 END IF;

 -- vUpd is for the update bit.
 IF vUpd IS NULL THEN
  vUpd := '   SET 'J.COLUMN_NAME' = :NEW.'J.COLUMN_NAME' ';
 ELSE
  vUpd := vUpd', 'CHR(10)'   'J.COLUMN_NAME' = :NEW.'J.COLUMN_NAME' ';
 END IF;

END LOOP;


-- Now that we've got all the pieces all we have to do is stick em together.
vView  := vView' 'vKey' VIEW_ID, 'CHR(10)vKey1CHR(10)'FROM 'I.TABLE_NAME;
vTrg := vTrgvUpdvWhr'; 'CHR(10)' ELSIF DELETING THEN'vDel'   ; 'CHR(10)
 ' ELSE /* inserting */ 'CHR(10)'   INSERT INTO 'I.TABLE_NAMECHR(10)
 '   ('vKey1') 'CHR(10)'   VALUES ('vVals'); 'CHR(10)' END IF;'CHR(10)'END;';

-- Finally, execute the statements.
EXECUTE IMMEDIATE vView;
EXECUTE IMMEDIATE vTrg;

-- Before we leave we need to grant permissions on our view.
FOR J IN (SELECT GRANTEE, PRIVILEGE
 FROM DBA_TAB_PRIVS
 WHERE TABLE_NAME = I.TABLE_NAME
 AND PRIVILEGE IN ('SELECT','UPDATE','DELETE')) LOOP

 vPriv := 'GRANT 'J.PRIVILEGE' ON 'SUBSTR(I.TABLE_NAME,1,24)'_V TO 'J.GRANTEE;
 EXECUTE IMMEDIATE vPriv;
END LOOP;
END LOOP;
END;
/

Of course none of the above is exactly rocket science, but if you're at this particular stage of your Apex development I figured it might save you an hour or so. Now, go waste it on Digg. I'm off to email Halle Berry.

Again.

EDIT: It's been pointed out to me that all the concatenation pipes are missing from the script above. Which is kinda strange since I definitely put them in. If I can't sort it out you might have to put them back in yourself. I apologise.

Oracle Forms to Apex Conversion (or Obama might not actually be Jesus)

2008-11-10T17:51:00.005Z

Hey, I just thought up a definition for politics: soap operas for boring people. (Or maybe I should change that from boring people to grown ups; I'm not sure.) Think about it: a small cast of main characters, huge dramas, power play, and the occasional sex scandal - how is that any different from Desperate Housewives or Eastenders?

Not being American I watched the recent elections with semi-detached interest. Every few days I'd log onto YouTube and watch John McCain's latest attack ad calling Obama a terrorist, a Muslim, a communist, an Arab, an ... elitist?

But here's the weird thing; ever since Obama won the election he and his men have practically been releasing attack ads against Obama themselves. "You know how we talked about hope and change? Well, is there any chance you could, erm, hope for a little less and expect change a little slower?"

The excitement and anticipation in the Apex community (or at least that subset of it that intersects with the Oracle Forms community) that followed the announcement of version 3.2, and the subsequent need to manage our expectations kinda reminds me of Obama trying to dampen his supporters' frenzy.

The revelation that version 3.2 will include a Forms to Apex converter is, undoubtedly, big news. If, like me, you work for a company that has been churning out .fmxes for well over a decade and has now decided that the best path to the future is Oracle Apex, a Forms2Apex engine could save you thousands of man-hours, hundreds of thousands of pounds, and an incalculable amount of earache from your customers. How can you not get excited about that?

I have the privilege of being one of a small number of outsiders invited by Oracle's David Peake (he's the PM for Application Express) to participate in their Early Adopter programme for version 3.2. He kicked it off with a web conference where, while he was understandably proud of the hard work he and his team have put into this release, I sensed he was doing an Obama and managing our expectations.

So here's what you need to know: Apex version 3.2 does not come with a witchcraft plugin pre-installed. You will still need to do some work. You will need to set up a project and dedicate time to it. Some things just cannot be done automatically.

So are your expectations sufficiently dampened yet? Well, here's the good news. Apex version 3.2 will do a lot more than you now currently expect. We were treated to a demo of its capabilities (and later this week I'll get my grubby mitts on it myself) and I'm very impressed by what it can do and the way that it does it.

I hope to be blogging a bit more in the next few days, giving you details of how it works and what exactly it can do. If you've got any specific questions that you'd like me to investigate for you, feel free to leave them as comments to this entry. Do be aware that I am not an employee of Oracle (as a developer that uses Oracle's technology I have an intense love-hate - or rather like-hate - relationship with the company) and so the answers I give you will not necessarily represent the views of Oracle. There's even a possibility that my answers may be wrong. But hey -

Typos in my dads.conf (or Car chases, sex scenes and a dashing blogger)

2008-10-08T22:47:00.003+01:00

Apparently Jerry Bruckheimer refuses to make any movie that cannot be summarised in six words (although lots of people would probably argue that most Jerry Bruckheimer movies can easily be summarised in a single four-letter word that starts with a 'c', ends with a 'p' and manages to squeeze an 'r' and an 'a' between them). In this respect, I guess, this blog is very much like a Bruckheimer movie - car chases, sex scenes, gunfight and a swashbucklingly handsome blogger - in that it can be summarised in 6 words: Apex is great because it's in the database. (Okay, make that 8 words.)

This is brilliant because it means that when I need to send my client an upgrade (version 0.9.0.1 baby!) all I have to do is send them a file which they can run in SQL Plus. Except my last upgrade didn't work... Instead I got an error message saying that I needed to set something called a security_group_id. Huh?

I tried to import the application via Apex and my browser exploded (not literally, of course; I'm not really Jerry Bruckheimer).

The error I got was:

Mon, 06 Oct 2008 13:59:00 GMT

ORA-00942: table or view does not exist

DAD name: apex
PROCEDURE : wwv_flow.accept
URL : http://xxxxxx.net:80/pls/apex/wwv_flow.accept
PARAMETERS :
===========

ENVIRONMENT:
============
PLSQL_GATEWAY=WebDb
GATEWAY_IVERSION=3

It went on in a similar vein for a hundred more lines but what the hell did it all mean? ORA -00942 I understood (I've been an Oracle developer for so long that I see ORA numbers in my nightmares) and I could see that the procedure it was trying to run was wwv_flow.accept (which definitely existed). A second glance led me suspect that the problem might be in my dads.conf file (the Database Access Descriptor config file). I had a look at it; it looked fine to me. I went over it line by line and still couldn't see anything wrong with it.

Turns out I was looking to the wrong Jerry Bruckheimer vehicle; instead of Con Air I should have been looking to the subtlety of CSI. Here's what I was doing wrong (and if you're getting this same error when you try to upload a file in Apex you've probably made this same mistake); I had a typo in my dads.conf.

Instead of:

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword apex

I had:

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_object$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword apex

Spotted it yet? I didn't either.

In typing my dads.conf I had typed 'object$' instead of 'objects$' in the line:

PlsqlDocumentTablename wwv_flow_file_objects$

Once I corrected this typo and restarted my web service it all worked. Easy-peasy - when you know how. So I've got to say thank you to this site which pointed me in the right direction.

Google Motion Charts in Apex (or Me, February, March & Mummy)

2008-10-02T19:32:00.009+01:00

I've got an old Iraqi 100 dinars note in my wallet (don't ask me why - long story) and it's got this huge picture of Saddam Hussein on it. Just how vain do you have to be as a president to put your own face on your country's money? Or what about President Saparmurat Niyazov of Turkmenistan who was so egocentric that he renamed the month of January after himself and April after his mother?

Crazy, huh? Except we've all got a little narcissistic dictator in us, which is why I sometimes obsessively keep an eye on this blog's hit-counter. Which is why I know that most people who chance upon this blog do so from googling "AnyCharts" or "FusionCharts" (see my post about integrating FusionCharts with Apex here). Flash charts are obviously a big thing: users find them informative and we developers find them fun to play with.

Which is why I nearly wet myself with excitement when I heard that Gapminder's really cool flash chart (go on, click the link and press play. We'll wait for you) had recently been rebranded as 'motion charts', released as a Google gadget and can be accessed using the Google visualization API (no, I hadn't heard of it either). How cool would it be to have one of them in your apex app? (The Google visualization gallery actually has quite a few interesting charts; they're probably worth a second look too.)

The Google motion chart page, fortunately, contains some sample code (although you might want to read the Google Visualization overview to fully understand how the whole thing holds together). To use it in your apex application all you have to do is create a Dynamic PL/SQL region and output the javascript code using htp.prn, populating the Google DataTable in a pl/sql loop.

And that's that. Easy-peasy when you know how.

The only small issues I've had with motion charts so far is that while they seem happy to display an annual timeline they do appear a little buggy when you try to portray smaller date increases, and no matter what the user guide says I am unable programmatically control the size or colour of the bubbles.

Pro Oracle Application Express: My first thoughts

2008-09-20T20:26:00.008+01:00

"So why is it called that?" a colleague asked me, looking at the copy of Pro Oracle Application Express lying on my desk. "Do they mean pro as in 'professional' or as in the opposite of anti? Either way it's not a great title, is it?"

I shrugged. I'd been waiting for this book for a week and a year (ordered: September 11, 2007 - received: September 18, 2008) so I wasn't about to quibble about its title. What I was curious about was if, during its year-long gestation period, the authors had kept the book up to date with the rapidly-changing world of Apex - after all, version 3.1 had come out in that time. I searched the index for 3.1's most impressive feature, interactive reports. No luck; not one mention. Not a good sign; had they delayed the book for so long that it was born outdated?

Flicking through the book didn't fill me with confidence either. I spotted a reference to the htmldb_application package. Nothing wrong with that, but I did wonder if it was the authors' preference or maybe a sign of how long ago the book was written that they hadn't used apex_application instead.

Not exactly filled with confidence I settled down to work my way through the first few chapters. I noted that the authors hadn't resorted to that chatty, overly-informal tone that many textbook authors use these days. Good for them; when I'm studying Application Express I don't need to feel like I'm having a beer in the pub with my mates. (When I'm in the pub, if you want to know, I start out talking about football and end up, when I'm thoroughly drunk, swearing I saw a UFO hovering over my local KFC in 2003. Not one mention of Apex.)

I also liked the way the authors approached the subject of Apex, the sequence in which they dealt with its various topics. Too many Oracle textbooks read like freeze-dried reference books; this book, I noted, seemed to have found the right middle point between reference book and those annoying "for dummies" books.

Some topics in the book struck me as a little strange. For instance, there's a bit about VPD, a useful subject for an Oracle developer to be knowledgeable about but not exactly Apex. I didn't mind this though; I'd been planning to read up about application contexts for a while now, so I was grateful for their concise explanations on the subject. On the other hand when I came across 2 pages demonstrating to me that putting double quotes around table names made them case sensitive I must admit that I felt as if I was being talked down to.

Two paragraphs ago I said that I liked the sequence in which the authors lined up their various topics, and that's the truth. However, I wonder if I would have found this book so welcoming if I was a complete Apex newbie. I dunno; maybe not.

So maybe the pro in the title does stand for 'professional', because for someone like me who has dipped their toes in Apex (and therefore is partway between a newbie and an expert) this book is perfectly positioned. Reading back over what I've written so far I suspect that you might think that this is aiming to be a negative review: the complete opposite is the truth. I am very glad that I bought this book - and in some respects I am even pleased I had to wait a year for it. Because already I am using things I am learning from it to fine-tune and improve my application.

But I'm beginning to ramble; I should summarise. What you probably want to know is should you buy the book? Well, if you're a newbie the answer is yes. Be warned though that this book won't replace the resources of the web; you'll still need to work through the Oracle By Example docs - maybe before you dig into this book. If you're not exactly a newbie but still no expert, then I think this book is most definitely for you. (I've just been reading the chapter on the Apex dictionary, for example, and that alone is - almost - worth the cover price.) And if you're an expert? Well, what do I know? - I've only been working with Apex for 6 months. Why do you want to take advice from me? Huh?

APEX_UTIL.PREPARE_URL (or Better than sitting in a glass box)

2008-09-19T20:38:00.005+01:00

You don't need me to tell you about Session State Protection (SSP). You probably know a lot more about it than I do, you snivelling show-off. But if, like me, you've only just enabled and configured SSP for your application and it's screwed up all your hard work then maybe you'll want to read on.

First things first, let me confirm what you already suspect: SSP didn't screw up your application - you did. The problem I have is that I'd built a number of links by constructing the URLs from the results of sql queries. (Hard-coding: tut tut)

Select empno, ename, 'f?p=:APP_ID:1::APP_SESSION::::P1_EMPNO:' ||empno employee_link
from emp;

(Yes yes, I know that the above query is silly but I'm just trying to make a point.)

The thing is, the moment you configure and enable session state protection links like this stop working. Suddenly you start getting session state protection violation errors. This is because links that you construct yourself don't contain the checksum portion of the URL.

To resolve this we need to turn to a rather useful function: APEX_UTIL.PREPARE_URL. What you do is feed this bad boy your self-constructed link and it'll spit out a link including the checksum. Sweet.

Select empno, ename, APEX_UTIL.PREPARE_URL ('f?p=:APP_ID:1::APP_SESSION::::P1_EMPNO:' ||empno) employee_link
from emp;

And hey, all of a sudden your links start working again. Abracadabra! Eat your heart out, David Blaine: sitting in a glass box is NOT real magic.

PS: Yesterday I received my copy of Pro Oracle Application Express. Only 10 months after I ordered it. Hopefully I'll be able to write a quick review in the next few days.

Version control (or How the world really ended)

2008-09-17T08:33:00.005+01:00

I'm at the stage in the life of my app where I am beginning to think seriously about version control. Being a true software developer, I am as gung-ho and devil-may-care as the best of them and I have, thus far, regarded my nightly database backups as application backups too (and, because Oracle Apex lives completely within the database you can do this, but perhaps it is unwise). But now that we've delivered a beta version to the customer I have to take version control a lot more seriously.

The Wikipedia page for Oracle Application Express pre-warns me: version control is ground upon which Apex stands unsteadily. The reason for this is, paradoxically, Apex's main strength: Apex is a joy because it lives wholly in the database and every change you make merely updates the FLOWS_03000 tables - however this means that it is so much harder to obtain source files for your version control vault.

What I have decided to do is this: For every major release of my application I will export my whole application (along with my theme and images) and save the files to my source control programme. However, for the many minor changes and bug fixes that will come between these major releases I will simply export and save the relevant pages. This way whenever I need to upgrade a client with a bug fix all I'll have to do is send them the page files and some sql updating the FLOW_VERSION column of the WWV_FLOWS table (which is where Apex stores the version number of your application).

All of this, I admit, sounds more than a little fiddly. It means I'll need to keep an eye on every page I update for every bug fix, but I guess this is no different from keeping an eye on every form I update when working in Oracle Forms.

Having said that I'd very much like to hear what you do to version control your Apex application.

On a completely different note, am I the only one who has noticed that the week the Large Particle Hadron Collider was switched on is the exact same week the financial markets went into complete meltdown? Coincidence? Hmm, they did warn us that it'd bring about the end of the world.

I'm just saying.

A few issues around deployment (or How the world ends)

2008-09-11T20:47:00.003+01:00

Apparently, whenever people believe that the world is about to end they tend to react in one of two ways. They either get very prayerful and (re)discover God, or they let go of all their inhibitions and go wild with orgies and alcohol and such. I live in a block of flats, right above a pair of good-looking women, so with all this talk in the news of recent of the possibility of the Large Hadron Collider swallowing the earth in a black hole, I guess it's obvious which way I was hoping they'd go.

Large Hadron Collider or not, I have had a sense of ending of recent because this week I have finally deployed the Apex application that I have been working on (and bitching about in this blog) for the past few months.

I have learned a few things this past week. For instance, I spotted that you can enter a version number against your application (under Shared Components). Ah cool; I was installing my application for user acceptance testing so I entered a version number of 0.9. I applied my changes and ran my application and -

... nothing. Eventually I realised that I would have to edit my page templates to show the version. To do this I added #APP_VERSION# to the footer and voila! (I know I make this complaint all the time, but it's crazy that I had to search for close to an hour to find out about #APP_VERSION#. Go on, stick "#APP_VERSION# Apex" into Google - it's practically a googlewhack!)

Next thing I found out (although I concede that this might be due to some mistake that I am making) is that if you export an application as Run Only (as opposed to Run and Build) you can still import it as Run and Build - thus exposing all the code behind the app. So how am I supposed to protect my intellectual property from the prying eyes of my client - or am I missing something here? Also I found that if you import an application as run only you can't import images into it.

Other than all this - and to be fair all of this is rather insignificant - the deployment of my application went really smoothly. Not a thing to worry about.

Except that pesky Hadron Collider and the end of the world. Forgive me, I've gotta go now: I've got me a bottle of champagne; I'm gonna go knock on the flat downstairs.

I don't know for sure how the earth started, but let's see if I can end it with a big bang.

Making the move from Forms to Apex: What a developer needs to know

2008-08-27T18:58:00.004+01:00

It's been close to 5 months since I first dipped my toes in the waters of Oracle Application Express and whilst I am not foolish enough to yet claim the status of expert, I believe I've travelled far enough down the road to be able to advise those coming behind me.

This entry is aimed mostly at Oracle Forms developers who are thinking of taking up Apex. I know you: I was you.

The most comforting thing about Oracle Forms is its insularity - it's a small, closed world which one can easily conquer. All you need do is master PL/SQL and a few Forms trigger/validation tricks and you're basically there. But you and I both know that despite Oracle's half-hearted protestations the road Forms is travelling down is a dead-end. It's time to get out.

So what do you need to know or do if you want to jump ship to Application Express?

You already have the tools: The good news is that your PL/SQL skills are not redundant. Validations, page processes, LOVs and lots more in Apex are - or can be - coded in PL/SQL. Which means that you're practically ready to get started. However...
You do not have all the tools: I don't know about you, but as a Forms developer after I'd mastered PL/SQL I never really did that much studying. If you're thinking of transferring to Apex be prepared to go back to hitting the books. PL/SQL is useful, but it's not all you'll need. Because Apex is a web technology and is reasonably open you'll need to be familiar with other web technologies. You will definitely need to learn HTML and JavaScript; you'll probably want to acquaint yourself with Ajax and CSS. You may want to look into DHTML; I have needed to dabble with a bit of Flash. The list is endless so be prepared to study. However...
Don't expect to find any books: Cos there aren't any at the moment. I've had this book on pre-order for close to a year and it's been pushed back more times than Bruce Willis's hairline. However...
The internet is full of resources: You've somehow found your way to this blog. Good. You're obviously intelligent and discerning; probably drop-dead gorgeous too. No need to be modest about it. However you'll find the Apex forum even more useful. Also take a look at Oracle's documentation (the 2 Day developer's guide is a very good start, and if you work your way through the User's guide you won't need to spend thousands of pounds/euros/dollars/naira on an Apex course). You'll also want to keep an eye on the growing army of Apex blogs: the Apex Blog Aggregator is the best way to do so. With all of this you'll be ready to get started with Application Express. However...
You will want to install the ApexLib framework: the ApexLib framework is an Application Express add-on developed by a guy named Patrick Wolf. It's a pl/sql-javascript brew that adds an extra layer of user-friendliness and developer-friendliness to Apex. If you're a newbie you might not, as yet, appreciate it's full impact but - trust me - it's brilliant. (If you're a little worried that Apex should need an add-on, don't be. Instead appreciate the fact that you're moving to an IDE that allows the development of add-ons. We didn't get that in the closed shop of Forms! I don't currently know that many other Apex add-ons - tho you can buy some extra themes from ApexSkins - but I'm sure others will appear with time.) Finally...
You will want to bear in mind the differences between Forms and Apex: Perhaps the most frustrating thing when you start developing in Apex is actually a little thing. You have reduced control over the actual look of your application. You know how you can draw and size and align and position text items in Forms' WYSIWYG editor, right? Well, because Apex is a web technology you have no such luxury. It's a small thing but it may irritate you. But the fact that you even notice that tells you how short the learning curve is. I guarantee you that you'll be up and running in no time.

So should you make the jump from Forms to Apex? In my opinion you should. In my experience Apex has been welcoming, robust and reliable. In Apex you can develop forms as rapidly as you would in Oracle Forms: debugging them is not necessarily a breeze, but then debugging is more of a bitch in Forms.

Of course I cannot guarantee you that Oracle Application Express will enjoy the longevity of Oracle Forms - as a matter of fact, I doubt that in today's fast-changing world any tool will last that long. However, for the Oracle Forms developer, still hobbled by Forms' closed shop, it is - again this is just my opinion - the best introduction to the wider world of web technologies. So if/when Apex does disappear you and I will be armed and ready to attack the next big thing.

Building a report-builder (or my career as an inbred redneck)

2008-08-24T12:53:00.005+01:00

New day, new requirement. Welcome to my life.

What my boss would like is a sort of halfway house between a set of pre-written, inflexible standard reports and a completely flexible (and thus complicated) report-building tool, such as Crystal Reports or Hummingbird BI. An additional requirement is that the solution I come up with should cost my company nothing but my time - and so going out and buying some third-party product is out of the question.

Time to get my MacGyver hat on.

My intention is to kinda reproduce (and improve upon) a form I previously created in Oracle Forms, in which I applied a thin layer of interpretation between the end user and the database, and then presented the user with a list of report columns, a list of report filters, and a number of report templates. The user can then select any combination of columns to appear in their report, and any combination of parameters to filter it by. And my job is to take this permutation (whatever it might be) and build the sql code needed to produce a report from it.

The way I achieved this in the earlier incarnation of my Oracle Forms flexible report builder was to back each report column and filter with a huge block of sql code and, depending on the columns and filters selected by the user, I outer-joined these blocks of code together to produce the report's sql code.

This system worked (and works) reasonably well, but has the disadvantage of producing humongously long sql scripts to create even the simplest of reports. Goodbye code efficiency and often, goodbye runtime speed.

And so I'm wondering if I can do things a little differently this time; perhaps by dynamically determining the relationship between tables on the basis of their primary and foreign key constraints. Sounds horribly complicated though (imagine having to write code that will automatically determine the connection between any set of 2 or 3 or 20 tables in a database of 1000 tables!) and I'm not as clever as I used to be - I've got too many pints of Foster's lager and too many hours watching American Idol with my girlfriend to thank for that!

And so I'm wondering if anyone else has had to create their own reasonably-flexible report builder who can give me any pointers? (All the developers in my office have been working there for far too many years and so our knowledge is now a little inbred - kinda like the Royal family or some redneck hicks from Kentucky - and it's sometimes difficult to generate new ideas in-house.) How do you generate the sql that you need? How do you ensure that your tables are correctly joined?

I know that none of the foregoing is exactly Apex-related (although I will be implementing my solution in Apex and probably be blogging about it here) but I do think the pl/sql involved might be quite interesting, so please do pitch in with your two cents' worth if you've got any ideas or suggestions.

APEX_APPLICATION.G_PRINT_SUCCESS_MESSAGE (or Donny From The Block)

2008-08-22T19:52:00.006+01:00

There are known knowns
These are things that we know that we know
There are known unknowns
That is, there are things we now know we don't know
But there are also unknown unknowns
These are things we do not know we do not know.

Am I the only one that can see that there's a sort of rough meter to Donald Rumsfeld's famous words? Why hasn't anyone sampled it and laid it over a slammin' dance or hiphop beat yet? There's a chart number one in there somewhere - and I'm giving it to you for free.

Every day I bump into the unknown unknowns of Application Express, discovering - often by happy serendipity - cool things that it is capable of. It's almost like Apex is a character in Heroes, and I'm not yet aware of its superpowers. (Don't believe me? Set the boolean apex_application.g_read_users_bloody_minds to TRUE and watch your app fly!)

Last week I needed to dynamically output a success message to the screen. Yes, I know that whilst creating my processes, validations etc, I can specify the messages that I would like to send to the user - but how can I dynamically control that #SUCCESS_MESSAGE# region?

After a lot of searching I chanced upon this variable: apex_application.g_print_success_message. This is the variable that holds the success message that is output to the screen and it can be set programmatically.

APEX_APPLICAITON (which is the synonym for the FLOWS_03000.WWV_FLOW package) is a veritable chocolate box of useful stuff - chances are that you've used some of it in the past, but perhaps, like me, you are unaware of exactly how much it contains. When I get a spare afternoon (like that'll ever happen!) I intend to sit down and study it.

If only - and I know I sound like a broken record here - Oracle would deign to give us more than the for dummies documentation that is currently available! (I'm currently considering whether to go on this Advanced Apex course but I'm hesitant because I don't know how advanced it'll actually be: has anyone out there been on it?)

Talking about records I'm already working on my second single with Donald Rumsfeld:

Don't be fooled by the rockets that I've got
I'm still, I'm still Donny from the block.
Used to have a little, now I've got Iraq
I'm still, I'm still Donny from the block.

See you at number 1!

Apex and FusionCharts (or There be dragons at the end of the earth)

2008-08-13T22:00:00.004+01:00

According to an article I read on the BBC website there's a whole coterie of people out there who still believe that the earth is flat. Yes, they're a bunch of crazies but I'm kinda grateful they exist cos they've given me the opportunity to use the word coterie in a sentence: admit it, you never have, have you?

But when you ask these flat-earthers what is beyond the edge of the earth they've got one simple answer: More earth.

Oracle Application Express, I've found, is kinda like that. Yes, you're occasionally going to hit the outer edges of its capabilities, but if you push against these edges you find more earth. Today I had a requirement to integrate a genogram into my app. It was immediately obvious that I'd need a Flash chart. And then it became immediately obvious that none of the pre-packaged Flash charts (pies, lines, bars, dials etc) would fullfil the need.

All of which led me to FusionCharts, which is a brilliant set of flash charts and widgets. All I had to do was figure out how to integrate it into my app. First I had to write a function to extract the data I needed from my database and output it as correctly-formatted XML. That bit was easy so I won't bore you with it.

Next I uploaded the Flash (SWF) file for my chart into my workspace. (Tell me something: when you upload an image to your application using Apex's image uploader you refer to it by pointing at # APP_IMAGES#, so how do you think you'd refer to a file you've uploaded using Apex's file uploader? #APP_FILES#? Wrong! Illogically, all files uploaded into your application should be pointed at using the #APP_IMAGES# substitution string.)

Finally, I created a dynamic PL/SQL content region outputting the necessary wrapper tags for my Flash movie (which I copied from the FusionCharts examples), pointing it to my uploaded swf file and feeding it the XML from my database function (which I call in "before regions" page process).

And there you have it - more earth. Suddenly you are no longer constrained by the limited list of flash charts that come packaged with Apex. Easy-peasy when you know how.

(Apparently, Scott Spendolini recently presented a paper on integrating FusionCharts into Apex but I couldn't find it on the internet. Go ye forth and Google for it for thou might have more luck than I.)

Forms to Apex Migration (or Facebooking with Angelina Jolie)

2008-08-12T23:08:00.006+01:00

I don't know if you read David Peake's recent blog entry about Apex 3.2 and how they're planning to produce a Forms-to-Apex migration tool. Yes? Good. Cos I'm really excited about it; I can't think of any news that could be more exciting than that. (Well okay, I might be a little more excited if I found I'd just won the lottery, or if I got an email from Angelina Jolie saying she'd spotted me on Facebook and wanted to come round to my flat to watch the Indiana Jones Trilogy on DVD and drink cheap wine.)

But think about it - no, not Angelina Jolie, the migration tool! It has the potential of being massive, of completely changing the direction in which a company like mine is moving. We've invested over a decade of our existence in building a sprawling (and successful) Oracle Forms application. We navigated the tricky waters of migrating from the frankly ugly Forms 4.5 to Forms 6i, but now we're struggling to convince our customers to take on the extra expense of upgrading from client/server Forms 6i to web Forms.

But even as we plough this row we're acutely aware that Oracle Forms is kinda like Glenn Close at the end of Fatal Attraction - yeah, she might spring to her feet with a sudden spurt of manic energy, but the end is definitely in sight. Would clients be willing to accept software written in dated Forms in 2 years' time? Or five? But what choice do we have? It'll cost us a million pounds and perhaps 2 years to rewrite our Forms app.

So this migration tool could be a total lifesaver. And it is completely in our interest to get in on the ground floor, and so we'll be taking David Peake up on his open invitation to collaborate with his team to convert some of our forms. I must admit that I'm a bit of a sceptic who cannot see how a Forms to Apex conversion could ever work smoothly, but I'm willing to be ... erm ... converted.

Apex Performance Testing (or great for washing your underwear)

2008-08-09T11:57:00.003+01:00

I was talking about Oracle Application Express with a friend who works for a large American bank, and he told me that they'd looked into using it for a new project they were working on and eventually decided against it. Interested, I asked him why, and he said that they'd carried out some performance tests and Apex had come out poorly in them. He said their load testing had revealed that Apex began to creak and stutter when you put it under a lot of strain.

I do not have the resources to carry out tests of my own, and so I must admit that what he said worried me a lot. The application I'm currently writing is intended for a small band of users, but if it is successful that number might ramp up pretty quickly. Should I be concerned? I hit Google, but couldn't find anyone else who seemed to have reached similar conclusions to my friend. Do you, dear reader, know of any tests that have been carried out showing how well Apex performs under pressure?

My friend's bank had a look at Oracle ADF, but eventually decided to go with Spring. Have you noticed how, these days, software companies seem to steal names for their products from brands of bottled water? I'm sure I've drank Spring Bottled Water before; and there must be an Apex Bottled Water somewhere too. And can't you just imagine the adverts for Vista? Vista Bottled Water: Good for drinking, even better for washing your underwear in.

Custom Authentication (or Torturing Larry Ellison)

2008-08-08T19:50:00.005+01:00

Yet another day and Oracle Application Express has revealed yet another facet of its extensive capabilities to me. It's almost as if it's performing a slow, sexy striptease for me: with each passing day it pops another button, loosens another strap, until one day - hopefully - it'll be dancing naked before me to a Pussycat Dolls song.

(Welcome to the dark and disturbing crannies of my mind.)

The database that my Apex application is going to be sitting on is shared by the other (Oracle Forms) application that my company supplies to these clients. Both applications use Database Authentication (in fact, the plan is to manage the Apex users from the Oracle Forms application). However, being a database user is not enough to grant a person access to the Apex application; there are other things (rows in tables etc.) I have to check.

And so I was forced today to study the authentication schemes closer. I noticed that using the Database Authentication Scheme Apex uses the code -DBACCOUNT- to validate a user's password. Ah, so all I needed to do was replace those words with a call to a function of my own. Apex specifies that my function will have to take two parameters (username, password) and return a boolean.

I thought this would be easy. I wrote a function; inside it I started off by testing if the parameterised password was correct for the user. To do this I used the native Apex function APEX_UTIL.IS_LOGIN_PASSWORD_VALID(username,password). If the password was incorrect I returned FALSE and did not continue with further tests; however, if it was correct I then proceeded to carry out the additional tests required by my clients.

However - and this is the weird thing that I cannot explain - I found that apex_util.is_login_password_valid() ALWAYS returned false - even when I passed in correct credentials!

I spent the next 10 minutes swearing like a sailor and torturing Larry Ellison slowly in my mind, but then I started investigating a Plan B.

Apex allows you to create your own custom authentication: should I try that? I looked into it for a while, but decided against it because I felt it was possibly an overkill for what I was trying to achieve.

Eventually what I decided to do was this. I rewrote my function, turning it into a procedure which carried out my clients' additional tests and which, if a user failed, called wwv_flow_custom_auth_std.logout to kick them out and redirect them back to the Login page. I then returned to my Database Authentication and stuck a call to my procedure in the Post-Authentication section.

And it worked fantastically. Users who do not meet my clients' rules can no longer log into the application, even if they are set up as database users.

By this time it was 5.30 in the evening, and so I shut my computer down, let Larry Ellison go free and headed home a happy man. (I have since then discovered this Custom Authentication how-to written by Duncan Mein however, since my system works well and is much easier to implement, I'll leave it be.)

Build options and deployment (or Call me Gottfried)

2008-08-07T21:24:00.004+01:00

I feel a little like Gottfried Leibniz, y'know the German scientist who in 1675 jumped out of the bath shouting, "Eureka! I have just invented a branch of mathematics that I intend to call calculus," only to be told: "Erm dude, didn't you get the email? Isaac Newton invented calculus last week!"

I'd better explain. I spent all of yesterday working extremely hard (I had to say that just in case my boss gets to read this) on inventing a way to manage all of my app's components and authorisation schemes from one form to enable me easily manipulate the various configurations that my various customers require. I succeeded (I wrote a single page from which I can easily change and apply different authorisation schemes to pages, regions, buttons, tabs etc.) and I promptly wrote about it here...

... only to be told that I was reinventing the wheel, that Application Express's Build Options was designed to help me configure applications for deployment to different clients.

Damn you, Apex!

(Actually, my work wasn't completely wasted because build options will help me include or exclude components, but I also need the ability to manage authorisation schemes because my clients have asked for differing user roles to be able to access different components.)

Build options, in case you know as little about them as I did yesterday, are named shared components that you can apply to page objects. If you set it to "exclude" any component that it is applied to will not be included in the application when you deploy it, preventing clients from getting their grubby little hands on things they haven't paid for.

Thanks to Patrick Wolf for pointing me in the right direction.

Authorisation schemes (or "a deerstalker and a pipe")

2008-08-06T18:38:00.002+01:00

Yesterday I met with my clients to give them a first look at the application I've been developing for them in Apex. They're old clients of ours who have been buying applications developed in Oracle Forms from us for over a decade, and so yesterday was not just an unveiling of the new application but an opportunity to hint at the future development direction of our company. No pressure then.

Fortunately they liked what they saw, and thought that the app looked better than any of our Oracle Forms offerings from the past. Phew!

Today, however, a small issue arose. We've managed to sell this app to two different clients (and a few others are very interested); however, our customers are very much like the blind men who went to see the elephant - they all interpret the government legislature that referees their existence slightly differently and so they all want the application to look slightly different and do things slightly differently (they say "to-ma-to", we say "to-may-to"). What to do?

Authorisation schemes to the rescue!

The idea behind authorisation schemes is easy: you attach some code to an object and if it resolves to true the object is displayed, otherwise it isn't. But I don't want the hassle of setting authorisation schemes object-by-sodding-object, what I need is the ability to, in one place, attach different authorisation schemes to various objects (items, buttons, regions, pages, list entries) and, in that way, easily customise my app for every client. Time to play Sherlock Holmes and delve into the innards of Application Express.

(It is my duty to warn you here that you must be very careful if you decide to tamper with the tables on which Application Express is built as you may cause irreparable damage. Ah, but what the heck, we're software developers - twice as brave as James Bond and nine point five times as sexy! Let's dive in with our eyes closed!)

I quickly discovered that Application Express stores all its authorisation schemes in a table called FLOWS_030000.WWV_SECURITY_SCHEMES. From this table I will get the Id and Name of the authorisation scheme that I wish to apply to my object.

Further investigations reveal that I can get a list of my page items from the view APEX_APPLICATION_PAGE_ITEMS. This view, however, is built on the table FLOWS_030000.WWV_FLOW_STEP_ITEMS. This is the table I will be updating to apply authorisation schemes to my items.

Now I know this it is a simple task to build an SQL report listing my many page items along with a select list (APEX_ITEM.SELECT_LIST_FROM_QUERY) that will allow me to choose the authorisation scheme I want attached to each one. Of course I'll also need a PL/SQL process to actually update the table when I press my Submit button.

Easy-peasy. When you know how.

Page items are not the only objects you may wish to apply authorisation schemes to. To apply them to regions the view you need is APEX_APPLICATION_PAGE_REGIONS and the table you need to update is FLOWS_030000.WWV_FLOW_PAGE_PLUGS. (The column in this table that controls the authorisation scheme is, counter-intuitively, called PLUG_REQUIRED_ROLE.)

Buttons are listed in the APEX_APPLICATION_PAGE_BUTTONS view; to update the authorisation scheme you'd want to update FLOWS_030000.WWV_FLOW_STEP_BUTTONS.

Other views that will be of interest to you are: APEX_APPLICATION_TABS, APEX_APPLICATION_LISTS, APEX_APPLICATION_LIST_ENTRIES. And other underlying tables that you will want to update include: WWV_FLOW_TABS, WWV_FLOW_LIST_ITEMS.

Now that I have built a page where I can easily manipulate the authorisation of my various objects it is the easiest thing in the world to create an "Export this configuration to a file" button, behind which I will create an SQL file containing scripts to set up my underlying tables exactly the way I want them. This way I can configure my application for one customer, export my configuration file, and then completely reconfigure it for the next client knowing I can simply revert to my old set-up by running a file.

Go on, say it. Genius.

The Apex community (or standing naked in the window)

2008-07-30T22:31:00.004+01:00

A few years ago I was assigned to a project in London and, as a result, found that my working day was bookended by one-hour train journeys to and from work. (It was an ... interesting project which involved designing a database in conjunction with a team of "flat-earther" web developers who didn't believe that relational databases were important and in whose world E F Codd was probably strangled at birth.) As the train neared London it snaked past a residential area. One day I looked up from the novel I was reading and caught a brief look of a woman standing in an upstairs window - completely naked.

The internet is something like that. The promise of anonymity that it affords us often brings out the weirdest facets of our personalities; the same facelessness that emboldened that nameless woman often makes people go on messageboards and act like fools.

However, fortunately, I have found that the Apex community - and it is a community - is completely different. Every day I am discovering new blogs that outline in painstaking detail solutions to problems I didn't even know I had yet. (I spent this morning writing code to log users out of my app if they've been inactive for half an hour: I found everything I need to achieve this here. I also adapted code I found here to write myself form that'll enable me quickly enter help text for the items in my app.) It's also quite nice that not long after I posted an entry questioning Oracle's commitment to Apex I got a response from David Peake, the product manager for Apex in Oracle reassuring me. Just in case you've had questions of your own on the same subject here's part of what he had to say:

Oracle has shown its commitment to Application Express by making it part of the seed installation of Oracle Database 11g. Therefore, whenever you install Oracle Database 11g it automatically installs Application Express. Such decisions are not made lightly and not made for tools with limited life expectancy.

Internally Oracle Application Express has also become a critical development tool. Every Oracle Employee utilizes our ARIA People Search application (also downloadable from our Packaged Applications on OTN) built with APEX. There are countless internal applications hosted on our internal site with the majority of development teams using APEX applications in varying degrees.

I'll be emailing his response to the bosses and developers at work. It might be the final reassurance they need to confidently make business and career decisions.

Anyway, that was all I really wanted to say; I just wanted to note on record how helpful the Apex community is. My next post will probably be about this report builder that I want to write with Apex. I'll write that tomorrow - if I'm not too busy taking long train journeys and looking out of the window!

What does the future hold for Apex?

2008-07-28T22:36:00.003+01:00

Last year was the worst year of my working life. Okay, maybe that's putting it a little too strongly - it's not as if I ended up sleeping in cornershop doorways being pissed on by drunks, fighting over half-eaten sandwiches with rats while supporting my crack cocaine habit by selling my body. But sometimes things seemed that bad: I spent, you see, last year working with Oracle ADF. (Arrgh, even after this much time the very name floods my mouth with bile.)

I'm willing to give ADF the benefit of the doubt and say that maybe (just maybe) it's actually a ... passable product, but it's definitely NOT the right product for an Oracle Forms developer to graduate to. The learning curve is far too steep and the technology is still far too tetchy.

Oracle Application Express is altogether a different story. I returned from a rather elementary 5 day course at Oracle and immediately felt ready to dive in head first. Yes, I'm still discovering new facets of the product even now, but at no point has this mountain felt unclimbable.

The one question I do have is one that no one outside Oracle would really be able to answer, and one which no one inside Oracle would necessarily answer truthfully: what does the future hold for Apex?

Just last week my boss at work informed us that the majority of our new development would now be written in Apex (which is great for me since I'm currently the only Apex 'expert' that we have). But at that meeting everyone turned to me and asked what my feelings about the future of Apex are - would Oracle give it the investment it will need to become an industry-leading development tool or will they abandon it one year down the line and move on to the next hyped thing? Does Apex even possess the oomph to power large-scale applications? Will we regret hitching our future to this bandwagon?

My answer was this: I don't know. However I'll be very interested in hearing what everyone else thinks.

Ajax and Improved help text (or the wasted life of Mungo Park)

2008-07-24T19:47:00.004+01:00

Studying history in a Nigerian school was, sometimes, like having a conversation with a schizophrenic. They teach you African history from the European perspective - and then they teach you, well, the truth. I remember our teacher (hello, Mr Dumatonu) teaching us how Mungo Park discovered the river Niger in the 18th Century; and then I remember him returning to class the next day to tell us how, in truth, the Africans had discovered it millennia before that.

Sometimes studying Apex I feel a little like old Mungo Park discovering paths that many have walked down as if they're new. (It's kinda like Neil Armstrong landing on the moon and discovering a KFC.) Yesterday, for instance, a colleague commented that the popup windows that, er, pop up when you click on an Optional Label with Help link are terribly ugly. He was right, and so I spent today investigating ways to rewrite it. (I've been messing with about with my templates a lot of recent; perhaps I'd be better off making a copy of the whole theme so I don't do any irreparable damage?)

I've had to travel down many strange paths and some that are only half-familiar: the first thing I did was create a CSS div class which I put in an After Header region on my Page Zero. Whilst there I created an HTML region, inside which I created a div and applied the class I'd just created. My plan was to hide my div and use it to display help text when needed.

Next I wrote an application process which would take an item_id and return the appropriate HELP_TEXT from APEX_APPLICATION_PAGE_ITEMS (by the way, does anyone know if item_ids are unique or do I have to qualify my query by app_id and page_id too?).

And finally (and this is the bit I really enjoyed) I wrote some javascript (which I fired from the anchor tag of my label template) which contained some Ajax that executed my application process (to get my help-text). Next it was a simple task to place that text inside my div, position it on my page and set it to visible. (I created a timer to keep it visible for 5 seconds, which should be long enough for the slowest of readers.)

The Ajax I had to learn today. To call an application process is really easy. You create a new htmldb_get object (var lookAtMeMum = new htmlb_get(null,&APP_ID.,'APPLICATION_PROCESS=myApplicationProcess',0);). You can pass values into your application process - in my case I wanted to pass in the item_id: (lookAtMeMum.add('F_ITEM_ID',itemValue);). And finally, you get the value returned from your application process (you'll need to add a htp.prn(returnValue); in your process to send a value back).

Easy-peasy. When you know how. (It took me all of today to figure it out.)

Oh, one difference between Mungo Park and I. He died on the river Niger. I intend to die on the breast of a beautiful, leggy blonde.

Just watch.

htp and htf (or seeking a sprig of parsley)

2008-07-22T19:38:00.003+01:00

I'm still wrestling in my mind, seeking out ways to deliver more than the bare bones of the functional spec that has been handed to me. My philosophy as a software developer is this: give the client what they have asked for - but make sure you also give it a little flourish, like the sprig of parsley that completes that Michellin-starred meal.

That sprig of parsley isn't going to be one big thing - I have neither the inspiration nor, at the moment, the expertise in Apex to do that - but hopefully I'll think up loads of little things that'll make my application a pleasure to use - or at least not a pain.

Today I decided to give my users the ability to bookmark pages in the app an add shortcuts to them on the homepage. (Yes, I know browsers natively possess the ability to bookmark pages: don't be a smartarse!) To implement this functionality I'd have to wander down paths I'd avoided till now: regions based on dynamic PL/SQL, the htp package and the htf package (which, I've got to admit, I'd never even heard of until today).

Basically, what I'm gonna have to do is write some javaScript to grab the contents of the address bar, write a package to parse it (in order to find if any parameters have been passed to the page), and save this info to a database table.

I'll then build a region containing PL/SQL to create a list (hello htp.listitem and htf.anchor - it's nice making your acquaintance) based on the contents of my table.

Easy-peasy when you know how.

Obviously at the start of the day I didn't have a clue how to do any of this, or even know if it was possible. Yes, I know I could have hit the forums and copied someone else's work, but I wanted to figure it out for myself. That's the only way I'm going to learn the intricasies of the Apex thing.

To upgrade or not to upgrade? (Or the evolution of the slug, baby!)

2008-07-21T18:22:00.005+01:00

Here's what happened with the slug:

Many millennia ago all the users had a meeting to discuss adoption of some new functionality that God was introducing to his hardware. There was Legs 1.3 and Brains 1.0 and Eyes 0.1. All the other users decided to adopt one module or another, or at least put them through some measure of suitability testing for a 30 day trial period.

But the Slug said, "Nah baby, I don't think I'll be adopting any of these new modules, baby." (If slugs could talk they'd say baby a lot: don't argue with me about this.) "I'm happy with the functionality I've got now, baby, and I'm not sure this new functionality will even work. This legs module sounds a bit like a gimmick. So no, I'll not be upgrading. Baby."

Upgrade after upgrade came and each time the slug, afraid of change and the work it entails, refused to upgrade - until other users had upgraded to a level where they could go down to the beach on a summer day in a teensy-weensy bikini and the slug was still stuck on Version 1.0, hiding in the undergrowth and scared of salt.

There's a point to this story (there isn't always a point to my stories); I'm wondering if I should upgrade my Apex to version 3.1. And, I've got to admit this, I'm rather hesitant. Oracle and I have had a long and tumultuous marriage, and upgrades haven't always gone well in our history. I even got very peed off when I was installing Apex. I accept that half the time the fault lies completely with me (I do not read instructions), but that doesn't change the facts: upgrades don't always go well.

So what I have to do now is weigh the pluses against the cons. On the one hand there's all the cool new stuff that comes with version 3.1 like interactive reports (actually is there anything new in 3.1 other than interactive reports? That's all everyone seems to talk about), and on the other hand there's the potential complete destruction of my database, the loss of my job, the breakdown of my relationship, penury, destitution, leprosy and a lonely death in the poorhouse.

I must upgrade immediately!

I've had a look at interactive reports and I think they're brilliant, taking power over the data out of the hands of the developer and giving it back to the owner of that data. But - and I'm not joking here - how much of that power does the user really want? My application is aimed at simple, basic computer users and I'm worried that the flexibility (and accompanying complexity) of interactive reports will scare them more than it would excite them. Too many times we software developers assume that the things that excite us will automatically excite our users - and we end up writing software for ourselves rather than them.

And so, for the time being, I think I'll be a slug (baby) and not upgrade to 3.1, even though polls seem to show that other developers love it. I'll come late to the party and make a dramatic entrance. Hopefully.

Throw some D's

2008-07-20T17:19:00.003+01:00

Ask anyone who works with Oracle Application Express and they'll probably tell you that the most difficult/unreliable/uncontrollable/unruly ghetto in its whole universe is the tabular form. In truth, I don't really have enough experience to agree with them with any certainty, but hey, if that's what the experts say then who am I to disagree?

This past week I've had to create a few data entry tables (based on an irregularly-structured database table [someone else's fault - of course]) and I found that the regular wizard-driven tabular forms just couldn't cut it. And so I packed my rucksack and headed down the road signposted APEX_ITEM.

Actually I'm exaggerating when I say "signposted", cos like everything else in the Oracledom, the whole thing is very poorly documented, and I only stumbled across it by chance. But now that I'm here I'm having a really great time. It's so easy to use - all you do is write your SQL statement, throw in APEX_ITEM.TEXT(indexNo, defaultValue) (where indexNo is a number counting up from 1) , stick it in an SQL Report and - voila! It's brilliant.

And for data manipulation all you have to do is create a PL/SQL process that'll loop through the rows (you'll need APEX_APPLICATION.G_FindexNo(rowNumber) to read the values) and do what you want with them. To quote someone I like and respect a great deal (me, me!): it's brilliant! It's way better - if a little more fiddly - than the wizard-driven tabular forms.

There's only one little thing that I need to figure out how to do now. I'd love to be able to include some javaScript (did I tell you how much I'm enjoying the javaScript book I bought? There's this other non-tech book that I'm reading - and enjoying - at the moment, but often I find myself choosing the javascript one over it) to validate each text item as it is entered. You can't do that with wizard-driven tabular forms (I think), but I reckon it should be possible with these manual tables. It's Sunday today, I'll look into it tomorrow: can't wait.

In other news today: my laptop is old and crappy - the wifi has gone deaf and the keyboard is arthritic. The ctrl is missing and, today, the D key has stiffened. So if you find any words in today's entry that aren't in the lexicon just do like the rap song says and Throw some D's on it.

Oracle Forms: Alfa Romeo

2008-07-14T20:34:00.003+01:00

Everyone goes on about how much better than Oracle Forms this Oracle Apex thing is - and they're right; I hate going back to bug-fix my old Forms apps (not that I have much choice; the application that makes my employers most of their money is written in Forms). But one thing people don't mention is how developing in Apex makes you feel.

I was watching Top Gear the other day and the presenters were practically having a communal orgasm as they discussed Alfa Romeos: they agreed that they were terribly unreliable cars that were likely to spend as much time with your mechanic as with you, but they said that even that was more than made up for by the way Alfa Romeos made you feel when you got behind their wheel.

So what does developing in Apex feel like compared to developing in Forms? Well, let me persist with the car analogy: developing in Apex is like driving a Ferrari - it's fast and smooth and enjoyable - for the first 100 miles. After that you begin to wish the seats were a little more comfortable and you begin to miss those little 'useless' extras that you took for granted in your old family sedan.

Okay, enough with the car analogies. Truth is the only thing I've driven in the past 10 years was a Segway, and if I went anywhere near a Ferrari I'd probably get arrested. The point I'm trying to make is that somehow I've found that developing in Forms was more fun; Apex is great but I don't know that I'll ever learn to love it the way I loved Forms. Yes, it's easy to build applications and create pages and graffiti them with pie charts and scattergrams - but have they made it too easy?

My application is now a 65-page sprawl that keeps spreading like eczema and I'm reasonably proud of it, but the truth is that these days I have the most fun when I'm writing database packages, procedures and functions, and not when I'm building (yet another) Apex page. It was never this way back in my Forms days.

Javascript and Jean Grae

2008-07-13T18:36:00.000+01:00

This weekend I'm mostly studying Javascript (and listening to Jean Grae's new album, Jeanius - but that's none of your business). It hasn't taken me long to realise that I won't survive long in the world of Oracle Apex (or any web technology) without it.

Coming from an Oracle Forms background it's a miracle I even know what javascript is - it's a bloody miracle that I even know what HLTM is (that was a joke, by the way - laugh with me, not at me)!

Fortunately for me I've got HeadFirst's excellent book to help me along. It makes studying a breeze, and with every chapter I see loads of ways I can - to use marketing wank speech - bring my app to life with what I'm learning. I almost can't wait to get back to the office.

Having said all that it's not that my app is completely bereft of JavaScript. One of the first things I did was install Patrick Wolf's Apexlib Framework, which is kinda excellent. No, not kinda excellent - just plain excellent. Only problem with it (apart from the fact that, if I remember correctly, it was a bit of a bitch to install) is that it works so effortlessly that I don't remember what is ApexLib and what is just simple, native functionality. So maybe I'm giving it too much - or too little - credit.

It's blokes like this Patrick Wolf and many, many other experts (I've got a bookmark list as long as your ... well, as long as you claim your ... is) that make working with Apex a joy. Whenever I encounter a problem - and that's not happening as frequently as it used to - there's always a blog somewhere on the webbienet with the answer. Sometimes it makes me forget that 8 months after I ordered the one official Oracle Apex book it still hasn't arrived. I recently got another email from Amazon saying its release has been pushed back - again!

Anyway, back to Javascript. And Jean Grae.

Great work, Batman

2008-07-13T18:16:00.000+01:00

My project manager has been standing over me a lot these past few days, casually eyeing my monitor. Yes, he's my friend and he comes over to talk football (he's a Man U fan - but since when have you expected good sense from a project manager), but why do I get the feeling that he's surreptitiously keeping an eye on my progress?

It probably doesn't help that the application I'm writing is being shared by two different projects and so I have project managers fighting over my time like drunk guys fighting over a girl in a pub (you can tell I live in the rough part of town, can't you?).

My latest challenge (hey, get the image of the slutty chick in the pub out of your mind and keep up, willya?!) is writing in functionality to produce mail merge letters from the app. Sounds easy, doesn't it? You'd have thought that every one would want that, that all I'd have to do would be to hit the forums and copy someone else's work, right? Wrong. Well, kinda wrong. Scott Spendolini has written a utility that does mail merges that works excellently - until you try to merge a document that's larger than 32k in size! Great, what is this - mail merges for bloody elves?

So I've had to spend days amending the code to get it work with larger documents (the problem was that SUBSTR and REPLACE and htp.p work with VARCHAR2s and so can't go beyond 32767 characters). And so I've been fiddling with CLOBs and DBMS_LOB, breaking my documents down into bitesize chunks. And I've always bloody hated CLOBs so the last thing I want is a bloody Man U fan standing over me as I work! Grrr!

Anyways, I finally got it working (see how I take all the credit, instead of admitting that it was really Mr Spendolini that did most of the work?) and so now my users will be able to merge mail to their little hearts desires. Great work, Batman!

No dumb blonde

2008-07-12T17:39:00.000+01:00

So my direct boss is having a go at me again about the way my application looks. It's not that the app is all skew-whiff and ramshackle, with text items all over the place like alphabet magnets on a fridge door, but I admit that it won't be winning any beauty pageants any time soon. (I'm using the Light Blue theme, which I think is the coolest of the themes that come packaged with Apex 3.)

The problem is that I'm working to such a tight deadline and I've got miles of specs left unploughed. I haven't got the time to put make-up on the application. That'll have to wait till later.

The thing that makes my boss such a good boss is the same thing that makes him a rubbish boss at times: he loves tech. As long as it's new and shiny and being written about in tech magazines he loves it. And since he's too busy to try stuff out himself he's always on at me to try them. And ever since he stumbled across Ext JS (which is this really cool javascript library) he's been badgering me to use it to pretty up my app.

"When a client looks at an application for the first time," he said to me, "70% of his first impression comes from the look of it, only 30% of it is based on functionality. So make it prettier!"

The thing that sucks about this whole thing is that I agree with him. And the reason I agree with him is that I made up that bloody fact. Back in the days when we used to develop butt-ugly apps in Oracle Forms I used to have a go at him all the time that we needed to make our products prettier, and to convince him I made up this beauty-versus-functionality percentage thing. And now he's using it on me!

So, if anyone out there has some suggestions on how I can make an Apex app pretty, in ways that go beyond simply applying a pre-packaged theme but won't take too much time - and don't require much more than the basic skills of an Oracle Forms developer - then I'md love to hear them.

Oh, and on a total tangent: are all women crazy or is it just the ones I go out with? Or perhaps there's something about going out with me that drives them to craziness.