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.

No code here (or breaking knives and bending spears)

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

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)

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)

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