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