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

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

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

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

Here's what I think:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Conclusion

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

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

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

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

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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)

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)

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)

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)

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)

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.