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

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?

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

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)

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)

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)

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)

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)

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)

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")

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.