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

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

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)

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)

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)

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)

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.