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.
Friday, 19 December 2008
Tuesday, 16 December 2008
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.
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.
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.
Monday, 10 November 2008
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 -
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 -
Wednesday, 8 October 2008
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:
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:
I had:
Spotted it yet? I didn't either.
In typing my dads.conf I had typed 'object$' instead of 'objects$' in the line:
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.
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.
Thursday, 2 October 2008
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.
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.
Saturday, 20 September 2008
Pro Oracle Application Express: My first thoughts
"So why is it called that?" a colleague asked me, looking at the copy of Pro Oracle Application Express lying on my desk. "Do they mean pro as in 'professional' or as in the opposite of anti? Either way it's not a great title, is it?"
I shrugged. I'd been waiting for this book for a week and a year (ordered: September 11, 2007 - received: September 18, 2008) so I wasn't about to quibble about its title. What I was curious about was if, during its year-long gestation period, the authors had kept the book up to date with the rapidly-changing world of Apex - after all, version 3.1 had come out in that time. I searched the index for 3.1's most impressive feature, interactive reports. No luck; not one mention. Not a good sign; had they delayed the book for so long that it was born outdated?
Flicking through the book didn't fill me with confidence either. I spotted a reference to the htmldb_application package. Nothing wrong with that, but I did wonder if it was the authors' preference or maybe a sign of how long ago the book was written that they hadn't used apex_application instead.
Not exactly filled with confidence I settled down to work my way through the first few chapters. I noted that the authors hadn't resorted to that chatty, overly-informal tone that many textbook authors use these days. Good for them; when I'm studying Application Express I don't need to feel like I'm having a beer in the pub with my mates. (When I'm in the pub, if you want to know, I start out talking about football and end up, when I'm thoroughly drunk, swearing I saw a UFO hovering over my local KFC in 2003. Not one mention of Apex.)
I also liked the way the authors approached the subject of Apex, the sequence in which they dealt with its various topics. Too many Oracle textbooks read like freeze-dried reference books; this book, I noted, seemed to have found the right middle point between reference book and those annoying "for dummies" books.
Some topics in the book struck me as a little strange. For instance, there's a bit about VPD, a useful subject for an Oracle developer to be knowledgeable about but not exactly Apex. I didn't mind this though; I'd been planning to read up about application contexts for a while now, so I was grateful for their concise explanations on the subject. On the other hand when I came across 2 pages demonstrating to me that putting double quotes around table names made them case sensitive I must admit that I felt as if I was being talked down to.
Two paragraphs ago I said that I liked the sequence in which the authors lined up their various topics, and that's the truth. However, I wonder if I would have found this book so welcoming if I was a complete Apex newbie. I dunno; maybe not.
So maybe the pro in the title does stand for 'professional', because for someone like me who has dipped their toes in Apex (and therefore is partway between a newbie and an expert) this book is perfectly positioned. Reading back over what I've written so far I suspect that you might think that this is aiming to be a negative review: the complete opposite is the truth. I am very glad that I bought this book - and in some respects I am even pleased I had to wait a year for it. Because already I am using things I am learning from it to fine-tune and improve my application.
But I'm beginning to ramble; I should summarise. What you probably want to know is should you buy the book? Well, if you're a newbie the answer is yes. Be warned though that this book won't replace the resources of the web; you'll still need to work through the Oracle By Example docs - maybe before you dig into this book. If you're not exactly a newbie but still no expert, then I think this book is most definitely for you. (I've just been reading the chapter on the Apex dictionary, for example, and that alone is - almost - worth the cover price.) And if you're an expert? Well, what do I know? - I've only been working with Apex for 6 months. Why do you want to take advice from me? Huh?
Friday, 19 September 2008
APEX_UTIL.PREPARE_URL (or Better than sitting in a glass box)
You don't need me to tell you about Session State Protection (SSP). You probably know a lot more about it than I do, you snivelling show-off. But if, like me, you've only just enabled and configured SSP for your application and it's screwed up all your hard work then maybe you'll want to read on.
First things first, let me confirm what you already suspect: SSP didn't screw up your application - you did. The problem I have is that I'd built a number of links by constructing the URLs from the results of sql queries. (Hard-coding: tut tut)
(Yes yes, I know that the above query is silly but I'm just trying to make a point.)
The thing is, the moment you configure and enable session state protection links like this stop working. Suddenly you start getting session state protection violation errors. This is because links that you construct yourself don't contain the checksum portion of the URL.
To resolve this we need to turn to a rather useful function: APEX_UTIL.PREPARE_URL. What you do is feed this bad boy your self-constructed link and it'll spit out a link including the checksum. Sweet.
PS: Yesterday I received my copy of Pro Oracle Application Express. Only 10 months after I ordered it. Hopefully I'll be able to write a quick review in the next few days.
First things first, let me confirm what you already suspect: SSP didn't screw up your application - you did. The problem I have is that I'd built a number of links by constructing the URLs from the results of sql queries. (Hard-coding: tut tut)
Select empno, ename, 'f?p=:APP_ID:1::APP_SESSION::::P1_EMPNO:' ||empno employee_link
from emp;
(Yes yes, I know that the above query is silly but I'm just trying to make a point.)
The thing is, the moment you configure and enable session state protection links like this stop working. Suddenly you start getting session state protection violation errors. This is because links that you construct yourself don't contain the checksum portion of the URL.
To resolve this we need to turn to a rather useful function: APEX_UTIL.PREPARE_URL. What you do is feed this bad boy your self-constructed link and it'll spit out a link including the checksum. Sweet.
Select empno, ename, APEX_UTIL.PREPARE_URL ('f?p=:APP_ID:1::APP_SESSION::::P1_EMPNO:' ||empno) employee_linkAnd hey, all of a sudden your links start working again. Abracadabra! Eat your heart out, David Blaine: sitting in a glass box is NOT real magic.
from emp;
PS: Yesterday I received my copy of Pro Oracle Application Express. Only 10 months after I ordered it. Hopefully I'll be able to write a quick review in the next few days.
Wednesday, 17 September 2008
Version control (or How the world really ended)
I'm at the stage in the life of my app where I am beginning to think seriously about version control. Being a true software developer, I am as gung-ho and devil-may-care as the best of them and I have, thus far, regarded my nightly database backups as application backups too (and, because Oracle Apex lives completely within the database you can do this, but perhaps it is unwise). But now that we've delivered a beta version to the customer I have to take version control a lot more seriously.
The Wikipedia page for Oracle Application Express pre-warns me: version control is ground upon which Apex stands unsteadily. The reason for this is, paradoxically, Apex's main strength: Apex is a joy because it lives wholly in the database and every change you make merely updates the FLOWS_03000 tables - however this means that it is so much harder to obtain source files for your version control vault.
What I have decided to do is this: For every major release of my application I will export my whole application (along with my theme and images) and save the files to my source control programme. However, for the many minor changes and bug fixes that will come between these major releases I will simply export and save the relevant pages. This way whenever I need to upgrade a client with a bug fix all I'll have to do is send them the page files and some sql updating the FLOW_VERSION column of the WWV_FLOWS table (which is where Apex stores the version number of your application).
All of this, I admit, sounds more than a little fiddly. It means I'll need to keep an eye on every page I update for every bug fix, but I guess this is no different from keeping an eye on every form I update when working in Oracle Forms.
Having said that I'd very much like to hear what you do to version control your Apex application.
On a completely different note, am I the only one who has noticed that the week the Large Particle Hadron Collider was switched on is the exact same week the financial markets went into complete meltdown? Coincidence? Hmm, they did warn us that it'd bring about the end of the world.
I'm just saying.
The Wikipedia page for Oracle Application Express pre-warns me: version control is ground upon which Apex stands unsteadily. The reason for this is, paradoxically, Apex's main strength: Apex is a joy because it lives wholly in the database and every change you make merely updates the FLOWS_03000 tables - however this means that it is so much harder to obtain source files for your version control vault.
What I have decided to do is this: For every major release of my application I will export my whole application (along with my theme and images) and save the files to my source control programme. However, for the many minor changes and bug fixes that will come between these major releases I will simply export and save the relevant pages. This way whenever I need to upgrade a client with a bug fix all I'll have to do is send them the page files and some sql updating the FLOW_VERSION column of the WWV_FLOWS table (which is where Apex stores the version number of your application).
All of this, I admit, sounds more than a little fiddly. It means I'll need to keep an eye on every page I update for every bug fix, but I guess this is no different from keeping an eye on every form I update when working in Oracle Forms.
Having said that I'd very much like to hear what you do to version control your Apex application.
On a completely different note, am I the only one who has noticed that the week the Large Particle Hadron Collider was switched on is the exact same week the financial markets went into complete meltdown? Coincidence? Hmm, they did warn us that it'd bring about the end of the world.
I'm just saying.
Thursday, 11 September 2008
A few issues around deployment (or How the world ends)
Apparently, whenever people believe that the world is about to end they tend to react in one of two ways. They either get very prayerful and (re)discover God, or they let go of all their inhibitions and go wild with orgies and alcohol and such. I live in a block of flats, right above a pair of good-looking women, so with all this talk in the news of recent of the possibility of the Large Hadron Collider swallowing the earth in a black hole, I guess it's obvious which way I was hoping they'd go.
Large Hadron Collider or not, I have had a sense of ending of recent because this week I have finally deployed the Apex application that I have been working on (and bitching about in this blog) for the past few months.
I have learned a few things this past week. For instance, I spotted that you can enter a version number against your application (under Shared Components). Ah cool; I was installing my application for user acceptance testing so I entered a version number of 0.9. I applied my changes and ran my application and -
... nothing. Eventually I realised that I would have to edit my page templates to show the version. To do this I added #APP_VERSION# to the footer and voila! (I know I make this complaint all the time, but it's crazy that I had to search for close to an hour to find out about #APP_VERSION#. Go on, stick "#APP_VERSION# Apex" into Google - it's practically a googlewhack!)
Next thing I found out (although I concede that this might be due to some mistake that I am making) is that if you export an application as Run Only (as opposed to Run and Build) you can still import it as Run and Build - thus exposing all the code behind the app. So how am I supposed to protect my intellectual property from the prying eyes of my client - or am I missing something here? Also I found that if you import an application as run only you can't import images into it.
Other than all this - and to be fair all of this is rather insignificant - the deployment of my application went really smoothly. Not a thing to worry about.
Except that pesky Hadron Collider and the end of the world. Forgive me, I've gotta go now: I've got me a bottle of champagne; I'm gonna go knock on the flat downstairs.
I don't know for sure how the earth started, but let's see if I can end it with a big bang.
Large Hadron Collider or not, I have had a sense of ending of recent because this week I have finally deployed the Apex application that I have been working on (and bitching about in this blog) for the past few months.
I have learned a few things this past week. For instance, I spotted that you can enter a version number against your application (under Shared Components). Ah cool; I was installing my application for user acceptance testing so I entered a version number of 0.9. I applied my changes and ran my application and -
... nothing. Eventually I realised that I would have to edit my page templates to show the version. To do this I added #APP_VERSION# to the footer and voila! (I know I make this complaint all the time, but it's crazy that I had to search for close to an hour to find out about #APP_VERSION#. Go on, stick "#APP_VERSION# Apex" into Google - it's practically a googlewhack!)
Next thing I found out (although I concede that this might be due to some mistake that I am making) is that if you export an application as Run Only (as opposed to Run and Build) you can still import it as Run and Build - thus exposing all the code behind the app. So how am I supposed to protect my intellectual property from the prying eyes of my client - or am I missing something here? Also I found that if you import an application as run only you can't import images into it.
Other than all this - and to be fair all of this is rather insignificant - the deployment of my application went really smoothly. Not a thing to worry about.
Except that pesky Hadron Collider and the end of the world. Forgive me, I've gotta go now: I've got me a bottle of champagne; I'm gonna go knock on the flat downstairs.
I don't know for sure how the earth started, but let's see if I can end it with a big bang.
Wednesday, 27 August 2008
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?
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.
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?
- 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...
- 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...
- 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...
- 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...
- 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...
- 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.
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.
Sunday, 24 August 2008
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.
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.
Friday, 22 August 2008
APEX_APPLICATION.G_PRINT_SUCCESS_MESSAGE (or Donny From The Block)
There are known knownsAm 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.
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.
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!
Wednesday, 13 August 2008
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.)
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.)
Tuesday, 12 August 2008
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.
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.
Saturday, 9 August 2008
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.
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.
Friday, 8 August 2008
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.)
(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.)
Thursday, 7 August 2008
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.
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.
Wednesday, 6 August 2008
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.
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.
Wednesday, 30 July 2008
The Apex community (or standing naked in the window)
A few years ago I was assigned to a project in London and, as a result, found that my working day was bookended by one-hour train journeys to and from work. (It was an ... interesting project which involved designing a database in conjunction with a team of "flat-earther" web developers who didn't believe that relational databases were important and in whose world E F Codd was probably strangled at birth.) As the train neared London it snaked past a residential area. One day I looked up from the novel I was reading and caught a brief look of a woman standing in an upstairs window - completely naked.
The internet is something like that. The promise of anonymity that it affords us often brings out the weirdest facets of our personalities; the same facelessness that emboldened that nameless woman often makes people go on messageboards and act like fools.
However, fortunately, I have found that the Apex community - and it is a community - is completely different. Every day I am discovering new blogs that outline in painstaking detail solutions to problems I didn't even know I had yet. (I spent this morning writing code to log users out of my app if they've been inactive for half an hour: I found everything I need to achieve this here. I also adapted code I found here to write myself form that'll enable me quickly enter help text for the items in my app.) It's also quite nice that not long after I posted an entry questioning Oracle's commitment to Apex I got a response from David Peake, the product manager for Apex in Oracle reassuring me. Just in case you've had questions of your own on the same subject here's part of what he had to say:
I'll be emailing his response to the bosses and developers at work. It might be the final reassurance they need to confidently make business and career decisions.
Anyway, that was all I really wanted to say; I just wanted to note on record how helpful the Apex community is. My next post will probably be about this report builder that I want to write with Apex. I'll write that tomorrow - if I'm not too busy taking long train journeys and looking out of the window!
The internet is something like that. The promise of anonymity that it affords us often brings out the weirdest facets of our personalities; the same facelessness that emboldened that nameless woman often makes people go on messageboards and act like fools.
However, fortunately, I have found that the Apex community - and it is a community - is completely different. Every day I am discovering new blogs that outline in painstaking detail solutions to problems I didn't even know I had yet. (I spent this morning writing code to log users out of my app if they've been inactive for half an hour: I found everything I need to achieve this here. I also adapted code I found here to write myself form that'll enable me quickly enter help text for the items in my app.) It's also quite nice that not long after I posted an entry questioning Oracle's commitment to Apex I got a response from David Peake, the product manager for Apex in Oracle reassuring me. Just in case you've had questions of your own on the same subject here's part of what he had to say:
Oracle has shown its commitment to Application Express by making it part of the seed installation of Oracle Database 11g. Therefore, whenever you install Oracle Database 11g it automatically installs Application Express. Such decisions are not made lightly and not made for tools with limited life expectancy.
Internally Oracle Application Express has also become a critical development tool. Every Oracle Employee utilizes our ARIA People Search application (also downloadable from our Packaged Applications on OTN) built with APEX. There are countless internal applications hosted on our internal site with the majority of development teams using APEX applications in varying degrees.
Anyway, that was all I really wanted to say; I just wanted to note on record how helpful the Apex community is. My next post will probably be about this report builder that I want to write with Apex. I'll write that tomorrow - if I'm not too busy taking long train journeys and looking out of the window!
Monday, 28 July 2008
What does the future hold for Apex?
Last year was the worst year of my working life. Okay, maybe that's putting it a little too strongly - it's not as if I ended up sleeping in cornershop doorways being pissed on by drunks, fighting over half-eaten sandwiches with rats while supporting my crack cocaine habit by selling my body. But sometimes things seemed that bad: I spent, you see, last year working with Oracle ADF. (Arrgh, even after this much time the very name floods my mouth with bile.)
I'm willing to give ADF the benefit of the doubt and say that maybe (just maybe) it's actually a ... passable product, but it's definitely NOT the right product for an Oracle Forms developer to graduate to. The learning curve is far too steep and the technology is still far too tetchy.
Oracle Application Express is altogether a different story. I returned from a rather elementary 5 day course at Oracle and immediately felt ready to dive in head first. Yes, I'm still discovering new facets of the product even now, but at no point has this mountain felt unclimbable.
The one question I do have is one that no one outside Oracle would really be able to answer, and one which no one inside Oracle would necessarily answer truthfully: what does the future hold for Apex?
Just last week my boss at work informed us that the majority of our new development would now be written in Apex (which is great for me since I'm currently the only Apex 'expert' that we have). But at that meeting everyone turned to me and asked what my feelings about the future of Apex are - would Oracle give it the investment it will need to become an industry-leading development tool or will they abandon it one year down the line and move on to the next hyped thing? Does Apex even possess the oomph to power large-scale applications? Will we regret hitching our future to this bandwagon?
My answer was this: I don't know. However I'll be very interested in hearing what everyone else thinks.
I'm willing to give ADF the benefit of the doubt and say that maybe (just maybe) it's actually a ... passable product, but it's definitely NOT the right product for an Oracle Forms developer to graduate to. The learning curve is far too steep and the technology is still far too tetchy.
Oracle Application Express is altogether a different story. I returned from a rather elementary 5 day course at Oracle and immediately felt ready to dive in head first. Yes, I'm still discovering new facets of the product even now, but at no point has this mountain felt unclimbable.
The one question I do have is one that no one outside Oracle would really be able to answer, and one which no one inside Oracle would necessarily answer truthfully: what does the future hold for Apex?
Just last week my boss at work informed us that the majority of our new development would now be written in Apex (which is great for me since I'm currently the only Apex 'expert' that we have). But at that meeting everyone turned to me and asked what my feelings about the future of Apex are - would Oracle give it the investment it will need to become an industry-leading development tool or will they abandon it one year down the line and move on to the next hyped thing? Does Apex even possess the oomph to power large-scale applications? Will we regret hitching our future to this bandwagon?
My answer was this: I don't know. However I'll be very interested in hearing what everyone else thinks.
Thursday, 24 July 2008
Ajax and Improved help text (or the wasted life of Mungo Park)
Studying history in a Nigerian school was, sometimes, like having a conversation with a schizophrenic. They teach you African history from the European perspective - and then they teach you, well, the truth. I remember our teacher (hello, Mr Dumatonu) teaching us how Mungo Park discovered the river Niger in the 18th Century; and then I remember him returning to class the next day to tell us how, in truth, the Africans had discovered it millennia before that.
Sometimes studying Apex I feel a little like old Mungo Park discovering paths that many have walked down as if they're new. (It's kinda like Neil Armstrong landing on the moon and discovering a KFC.) Yesterday, for instance, a colleague commented that the popup windows that, er, pop up when you click on an Optional Label with Help link are terribly ugly. He was right, and so I spent today investigating ways to rewrite it. (I've been messing with about with my templates a lot of recent; perhaps I'd be better off making a copy of the whole theme so I don't do any irreparable damage?)
I've had to travel down many strange paths and some that are only half-familiar: the first thing I did was create a CSS div class which I put in an After Header region on my Page Zero. Whilst there I created an HTML region, inside which I created a div and applied the class I'd just created. My plan was to hide my div and use it to display help text when needed.
Next I wrote an application process which would take an item_id and return the appropriate HELP_TEXT from APEX_APPLICATION_PAGE_ITEMS (by the way, does anyone know if item_ids are unique or do I have to qualify my query by app_id and page_id too?).
And finally (and this is the bit I really enjoyed) I wrote some javascript (which I fired from the anchor tag of my label template) which contained some Ajax that executed my application process (to get my help-text). Next it was a simple task to place that text inside my div, position it on my page and set it to visible. (I created a timer to keep it visible for 5 seconds, which should be long enough for the slowest of readers.)
The Ajax I had to learn today. To call an application process is really easy. You create a new htmldb_get object (var lookAtMeMum = new htmlb_get(null,&APP_ID.,'APPLICATION_PROCESS=myApplicationProcess',0);). You can pass values into your application process - in my case I wanted to pass in the item_id: (lookAtMeMum.add('F_ITEM_ID',itemValue);). And finally, you get the value returned from your application process (you'll need to add a htp.prn(returnValue); in your process to send a value back).
Easy-peasy. When you know how. (It took me all of today to figure it out.)
Oh, one difference between Mungo Park and I. He died on the river Niger. I intend to die on the breast of a beautiful, leggy blonde.
Just watch.
Sometimes studying Apex I feel a little like old Mungo Park discovering paths that many have walked down as if they're new. (It's kinda like Neil Armstrong landing on the moon and discovering a KFC.) Yesterday, for instance, a colleague commented that the popup windows that, er, pop up when you click on an Optional Label with Help link are terribly ugly. He was right, and so I spent today investigating ways to rewrite it. (I've been messing with about with my templates a lot of recent; perhaps I'd be better off making a copy of the whole theme so I don't do any irreparable damage?)
I've had to travel down many strange paths and some that are only half-familiar: the first thing I did was create a CSS div class which I put in an After Header region on my Page Zero. Whilst there I created an HTML region, inside which I created a div and applied the class I'd just created. My plan was to hide my div and use it to display help text when needed.
Next I wrote an application process which would take an item_id and return the appropriate HELP_TEXT from APEX_APPLICATION_PAGE_ITEMS (by the way, does anyone know if item_ids are unique or do I have to qualify my query by app_id and page_id too?).
And finally (and this is the bit I really enjoyed) I wrote some javascript (which I fired from the anchor tag of my label template) which contained some Ajax that executed my application process (to get my help-text). Next it was a simple task to place that text inside my div, position it on my page and set it to visible. (I created a timer to keep it visible for 5 seconds, which should be long enough for the slowest of readers.)
The Ajax I had to learn today. To call an application process is really easy. You create a new htmldb_get object (var lookAtMeMum = new htmlb_get(null,&APP_ID.,'APPLICATION_PROCESS=myApplicationProcess',0);). You can pass values into your application process - in my case I wanted to pass in the item_id: (lookAtMeMum.add('F_ITEM_ID',itemValue);). And finally, you get the value returned from your application process (you'll need to add a htp.prn(returnValue); in your process to send a value back).
Easy-peasy. When you know how. (It took me all of today to figure it out.)
Oh, one difference between Mungo Park and I. He died on the river Niger. I intend to die on the breast of a beautiful, leggy blonde.
Just watch.
Tuesday, 22 July 2008
htp and htf (or seeking a sprig of parsley)
I'm still wrestling in my mind, seeking out ways to deliver more than the bare bones of the functional spec that has been handed to me. My philosophy as a software developer is this: give the client what they have asked for - but make sure you also give it a little flourish, like the sprig of parsley that completes that Michellin-starred meal.
That sprig of parsley isn't going to be one big thing - I have neither the inspiration nor, at the moment, the expertise in Apex to do that - but hopefully I'll think up loads of little things that'll make my application a pleasure to use - or at least not a pain.
Today I decided to give my users the ability to bookmark pages in the app an add shortcuts to them on the homepage. (Yes, I know browsers natively possess the ability to bookmark pages: don't be a smartarse!) To implement this functionality I'd have to wander down paths I'd avoided till now: regions based on dynamic PL/SQL, the htp package and the htf package (which, I've got to admit, I'd never even heard of until today).
Basically, what I'm gonna have to do is write some javaScript to grab the contents of the address bar, write a package to parse it (in order to find if any parameters have been passed to the page), and save this info to a database table.
I'll then build a region containing PL/SQL to create a list (hello htp.listitem and htf.anchor - it's nice making your acquaintance) based on the contents of my table.
Easy-peasy when you know how.
Obviously at the start of the day I didn't have a clue how to do any of this, or even know if it was possible. Yes, I know I could have hit the forums and copied someone else's work, but I wanted to figure it out for myself. That's the only way I'm going to learn the intricasies of the Apex thing.
That sprig of parsley isn't going to be one big thing - I have neither the inspiration nor, at the moment, the expertise in Apex to do that - but hopefully I'll think up loads of little things that'll make my application a pleasure to use - or at least not a pain.
Today I decided to give my users the ability to bookmark pages in the app an add shortcuts to them on the homepage. (Yes, I know browsers natively possess the ability to bookmark pages: don't be a smartarse!) To implement this functionality I'd have to wander down paths I'd avoided till now: regions based on dynamic PL/SQL, the htp package and the htf package (which, I've got to admit, I'd never even heard of until today).
Basically, what I'm gonna have to do is write some javaScript to grab the contents of the address bar, write a package to parse it (in order to find if any parameters have been passed to the page), and save this info to a database table.
I'll then build a region containing PL/SQL to create a list (hello htp.listitem and htf.anchor - it's nice making your acquaintance) based on the contents of my table.
Easy-peasy when you know how.
Obviously at the start of the day I didn't have a clue how to do any of this, or even know if it was possible. Yes, I know I could have hit the forums and copied someone else's work, but I wanted to figure it out for myself. That's the only way I'm going to learn the intricasies of the Apex thing.
Monday, 21 July 2008
To upgrade or not to upgrade? (Or the evolution of the slug, baby!)
Here's what happened with the slug:
Many millennia ago all the users had a meeting to discuss adoption of some new functionality that God was introducing to his hardware. There was Legs 1.3 and Brains 1.0 and Eyes 0.1. All the other users decided to adopt one module or another, or at least put them through some measure of suitability testing for a 30 day trial period.
But the Slug said, "Nah baby, I don't think I'll be adopting any of these new modules, baby." (If slugs could talk they'd say baby a lot: don't argue with me about this.) "I'm happy with the functionality I've got now, baby, and I'm not sure this new functionality will even work. This legs module sounds a bit like a gimmick. So no, I'll not be upgrading. Baby."
Upgrade after upgrade came and each time the slug, afraid of change and the work it entails, refused to upgrade - until other users had upgraded to a level where they could go down to the beach on a summer day in a teensy-weensy bikini and the slug was still stuck on Version 1.0, hiding in the undergrowth and scared of salt.
There's a point to this story (there isn't always a point to my stories); I'm wondering if I should upgrade my Apex to version 3.1. And, I've got to admit this, I'm rather hesitant. Oracle and I have had a long and tumultuous marriage, and upgrades haven't always gone well in our history. I even got very peed off when I was installing Apex. I accept that half the time the fault lies completely with me (I do not read instructions), but that doesn't change the facts: upgrades don't always go well.
So what I have to do now is weigh the pluses against the cons. On the one hand there's all the cool new stuff that comes with version 3.1 like interactive reports (actually is there anything new in 3.1 other than interactive reports? That's all everyone seems to talk about), and on the other hand there's the potential complete destruction of my database, the loss of my job, the breakdown of my relationship, penury, destitution, leprosy and a lonely death in the poorhouse.
I must upgrade immediately!
I've had a look at interactive reports and I think they're brilliant, taking power over the data out of the hands of the developer and giving it back to the owner of that data. But - and I'm not joking here - how much of that power does the user really want? My application is aimed at simple, basic computer users and I'm worried that the flexibility (and accompanying complexity) of interactive reports will scare them more than it would excite them. Too many times we software developers assume that the things that excite us will automatically excite our users - and we end up writing software for ourselves rather than them.
And so, for the time being, I think I'll be a slug (baby) and not upgrade to 3.1, even though polls seem to show that other developers love it. I'll come late to the party and make a dramatic entrance. Hopefully.
Many millennia ago all the users had a meeting to discuss adoption of some new functionality that God was introducing to his hardware. There was Legs 1.3 and Brains 1.0 and Eyes 0.1. All the other users decided to adopt one module or another, or at least put them through some measure of suitability testing for a 30 day trial period.
But the Slug said, "Nah baby, I don't think I'll be adopting any of these new modules, baby." (If slugs could talk they'd say baby a lot: don't argue with me about this.) "I'm happy with the functionality I've got now, baby, and I'm not sure this new functionality will even work. This legs module sounds a bit like a gimmick. So no, I'll not be upgrading. Baby."
Upgrade after upgrade came and each time the slug, afraid of change and the work it entails, refused to upgrade - until other users had upgraded to a level where they could go down to the beach on a summer day in a teensy-weensy bikini and the slug was still stuck on Version 1.0, hiding in the undergrowth and scared of salt.
There's a point to this story (there isn't always a point to my stories); I'm wondering if I should upgrade my Apex to version 3.1. And, I've got to admit this, I'm rather hesitant. Oracle and I have had a long and tumultuous marriage, and upgrades haven't always gone well in our history. I even got very peed off when I was installing Apex. I accept that half the time the fault lies completely with me (I do not read instructions), but that doesn't change the facts: upgrades don't always go well.
So what I have to do now is weigh the pluses against the cons. On the one hand there's all the cool new stuff that comes with version 3.1 like interactive reports (actually is there anything new in 3.1 other than interactive reports? That's all everyone seems to talk about), and on the other hand there's the potential complete destruction of my database, the loss of my job, the breakdown of my relationship, penury, destitution, leprosy and a lonely death in the poorhouse.
I must upgrade immediately!
I've had a look at interactive reports and I think they're brilliant, taking power over the data out of the hands of the developer and giving it back to the owner of that data. But - and I'm not joking here - how much of that power does the user really want? My application is aimed at simple, basic computer users and I'm worried that the flexibility (and accompanying complexity) of interactive reports will scare them more than it would excite them. Too many times we software developers assume that the things that excite us will automatically excite our users - and we end up writing software for ourselves rather than them.
And so, for the time being, I think I'll be a slug (baby) and not upgrade to 3.1, even though polls seem to show that other developers love it. I'll come late to the party and make a dramatic entrance. Hopefully.
Sunday, 20 July 2008
Throw some D's
Ask anyone who works with Oracle Application Express and they'll probably tell you that the most difficult/unreliable/uncontrollable/unruly ghetto in its whole universe is the tabular form. In truth, I don't really have enough experience to agree with them with any certainty, but hey, if that's what the experts say then who am I to disagree?
This past week I've had to create a few data entry tables (based on an irregularly-structured database table [someone else's fault - of course]) and I found that the regular wizard-driven tabular forms just couldn't cut it. And so I packed my rucksack and headed down the road signposted APEX_ITEM.
Actually I'm exaggerating when I say "signposted", cos like everything else in the Oracledom, the whole thing is very poorly documented, and I only stumbled across it by chance. But now that I'm here I'm having a really great time. It's so easy to use - all you do is write your SQL statement, throw in APEX_ITEM.TEXT(indexNo, defaultValue) (where indexNo is a number counting up from 1) , stick it in an SQL Report and - voila! It's brilliant.
And for data manipulation all you have to do is create a PL/SQL process that'll loop through the rows (you'll need APEX_APPLICATION.G_FindexNo(rowNumber) to read the values) and do what you want with them. To quote someone I like and respect a great deal (me, me!): it's brilliant! It's way better - if a little more fiddly - than the wizard-driven tabular forms.
There's only one little thing that I need to figure out how to do now. I'd love to be able to include some javaScript (did I tell you how much I'm enjoying the javaScript book I bought? There's this other non-tech book that I'm reading - and enjoying - at the moment, but often I find myself choosing the javascript one over it) to validate each text item as it is entered. You can't do that with wizard-driven tabular forms (I think), but I reckon it should be possible with these manual tables. It's Sunday today, I'll look into it tomorrow: can't wait.
In other news today: my laptop is old and crappy - the wifi has gone deaf and the keyboard is arthritic. The ctrl is missing and, today, the D key has stiffened. So if you find any words in today's entry that aren't in the lexicon just do like the rap song says and Throw some D's on it.
This past week I've had to create a few data entry tables (based on an irregularly-structured database table [someone else's fault - of course]) and I found that the regular wizard-driven tabular forms just couldn't cut it. And so I packed my rucksack and headed down the road signposted APEX_ITEM.
Actually I'm exaggerating when I say "signposted", cos like everything else in the Oracledom, the whole thing is very poorly documented, and I only stumbled across it by chance. But now that I'm here I'm having a really great time. It's so easy to use - all you do is write your SQL statement, throw in APEX_ITEM.TEXT(indexNo, defaultValue) (where indexNo is a number counting up from 1) , stick it in an SQL Report and - voila! It's brilliant.
And for data manipulation all you have to do is create a PL/SQL process that'll loop through the rows (you'll need APEX_APPLICATION.G_FindexNo(rowNumber) to read the values) and do what you want with them. To quote someone I like and respect a great deal (me, me!): it's brilliant! It's way better - if a little more fiddly - than the wizard-driven tabular forms.
There's only one little thing that I need to figure out how to do now. I'd love to be able to include some javaScript (did I tell you how much I'm enjoying the javaScript book I bought? There's this other non-tech book that I'm reading - and enjoying - at the moment, but often I find myself choosing the javascript one over it) to validate each text item as it is entered. You can't do that with wizard-driven tabular forms (I think), but I reckon it should be possible with these manual tables. It's Sunday today, I'll look into it tomorrow: can't wait.
In other news today: my laptop is old and crappy - the wifi has gone deaf and the keyboard is arthritic. The ctrl is missing and, today, the D key has stiffened. So if you find any words in today's entry that aren't in the lexicon just do like the rap song says and Throw some D's on it.
Monday, 14 July 2008
Oracle Forms: Alfa Romeo
Everyone goes on about how much better than Oracle Forms this Oracle Apex thing is - and they're right; I hate going back to bug-fix my old Forms apps (not that I have much choice; the application that makes my employers most of their money is written in Forms). But one thing people don't mention is how developing in Apex makes you feel.
I was watching Top Gear the other day and the presenters were practically having a communal orgasm as they discussed Alfa Romeos: they agreed that they were terribly unreliable cars that were likely to spend as much time with your mechanic as with you, but they said that even that was more than made up for by the way Alfa Romeos made you feel when you got behind their wheel.
So what does developing in Apex feel like compared to developing in Forms? Well, let me persist with the car analogy: developing in Apex is like driving a Ferrari - it's fast and smooth and enjoyable - for the first 100 miles. After that you begin to wish the seats were a little more comfortable and you begin to miss those little 'useless' extras that you took for granted in your old family sedan.
Okay, enough with the car analogies. Truth is the only thing I've driven in the past 10 years was a Segway, and if I went anywhere near a Ferrari I'd probably get arrested. The point I'm trying to make is that somehow I've found that developing in Forms was more fun; Apex is great but I don't know that I'll ever learn to love it the way I loved Forms. Yes, it's easy to build applications and create pages and graffiti them with pie charts and scattergrams - but have they made it too easy?
My application is now a 65-page sprawl that keeps spreading like eczema and I'm reasonably proud of it, but the truth is that these days I have the most fun when I'm writing database packages, procedures and functions, and not when I'm building (yet another) Apex page. It was never this way back in my Forms days.
I was watching Top Gear the other day and the presenters were practically having a communal orgasm as they discussed Alfa Romeos: they agreed that they were terribly unreliable cars that were likely to spend as much time with your mechanic as with you, but they said that even that was more than made up for by the way Alfa Romeos made you feel when you got behind their wheel.
So what does developing in Apex feel like compared to developing in Forms? Well, let me persist with the car analogy: developing in Apex is like driving a Ferrari - it's fast and smooth and enjoyable - for the first 100 miles. After that you begin to wish the seats were a little more comfortable and you begin to miss those little 'useless' extras that you took for granted in your old family sedan.
Okay, enough with the car analogies. Truth is the only thing I've driven in the past 10 years was a Segway, and if I went anywhere near a Ferrari I'd probably get arrested. The point I'm trying to make is that somehow I've found that developing in Forms was more fun; Apex is great but I don't know that I'll ever learn to love it the way I loved Forms. Yes, it's easy to build applications and create pages and graffiti them with pie charts and scattergrams - but have they made it too easy?
My application is now a 65-page sprawl that keeps spreading like eczema and I'm reasonably proud of it, but the truth is that these days I have the most fun when I'm writing database packages, procedures and functions, and not when I'm building (yet another) Apex page. It was never this way back in my Forms days.
Sunday, 13 July 2008
Javascript and Jean Grae
This weekend I'm mostly studying Javascript (and listening to Jean Grae's new album, Jeanius - but that's none of your business). It hasn't taken me long to realise that I won't survive long in the world of Oracle Apex (or any web technology) without it.
Coming from an Oracle Forms background it's a miracle I even know what javascript is - it's a bloody miracle that I even know what HLTM is (that was a joke, by the way - laugh with me, not at me)!
Fortunately for me I've got HeadFirst's excellent book to help me along. It makes studying a breeze, and with every chapter I see loads of ways I can - to use marketing wank speech - bring my app to life with what I'm learning. I almost can't wait to get back to the office.
Having said all that it's not that my app is completely bereft of JavaScript. One of the first things I did was install Patrick Wolf's Apexlib Framework, which is kinda excellent. No, not kinda excellent - just plain excellent. Only problem with it (apart from the fact that, if I remember correctly, it was a bit of a bitch to install) is that it works so effortlessly that I don't remember what is ApexLib and what is just simple, native functionality. So maybe I'm giving it too much - or too little - credit.
It's blokes like this Patrick Wolf and many, many other experts (I've got a bookmark list as long as your ... well, as long as you claim your ... is) that make working with Apex a joy. Whenever I encounter a problem - and that's not happening as frequently as it used to - there's always a blog somewhere on the webbienet with the answer. Sometimes it makes me forget that 8 months after I ordered the one official Oracle Apex book it still hasn't arrived. I recently got another email from Amazon saying its release has been pushed back - again!
Anyway, back to Javascript. And Jean Grae.
Coming from an Oracle Forms background it's a miracle I even know what javascript is - it's a bloody miracle that I even know what HLTM is (that was a joke, by the way - laugh with me, not at me)!
Fortunately for me I've got HeadFirst's excellent book to help me along. It makes studying a breeze, and with every chapter I see loads of ways I can - to use marketing wank speech - bring my app to life with what I'm learning. I almost can't wait to get back to the office.
Having said all that it's not that my app is completely bereft of JavaScript. One of the first things I did was install Patrick Wolf's Apexlib Framework, which is kinda excellent. No, not kinda excellent - just plain excellent. Only problem with it (apart from the fact that, if I remember correctly, it was a bit of a bitch to install) is that it works so effortlessly that I don't remember what is ApexLib and what is just simple, native functionality. So maybe I'm giving it too much - or too little - credit.
It's blokes like this Patrick Wolf and many, many other experts (I've got a bookmark list as long as your ... well, as long as you claim your ... is) that make working with Apex a joy. Whenever I encounter a problem - and that's not happening as frequently as it used to - there's always a blog somewhere on the webbienet with the answer. Sometimes it makes me forget that 8 months after I ordered the one official Oracle Apex book it still hasn't arrived. I recently got another email from Amazon saying its release has been pushed back - again!
Anyway, back to Javascript. And Jean Grae.
Great work, Batman
My project manager has been standing over me a lot these past few days, casually eyeing my monitor. Yes, he's my friend and he comes over to talk football (he's a Man U fan - but since when have you expected good sense from a project manager), but why do I get the feeling that he's surreptitiously keeping an eye on my progress?
It probably doesn't help that the application I'm writing is being shared by two different projects and so I have project managers fighting over my time like drunk guys fighting over a girl in a pub (you can tell I live in the rough part of town, can't you?).
My latest challenge (hey, get the image of the slutty chick in the pub out of your mind and keep up, willya?!) is writing in functionality to produce mail merge letters from the app. Sounds easy, doesn't it? You'd have thought that every one would want that, that all I'd have to do would be to hit the forums and copy someone else's work, right? Wrong. Well, kinda wrong. Scott Spendolini has written a utility that does mail merges that works excellently - until you try to merge a document that's larger than 32k in size! Great, what is this - mail merges for bloody elves?
So I've had to spend days amending the code to get it work with larger documents (the problem was that SUBSTR and REPLACE and htp.p work with VARCHAR2s and so can't go beyond 32767 characters). And so I've been fiddling with CLOBs and DBMS_LOB, breaking my documents down into bitesize chunks. And I've always bloody hated CLOBs so the last thing I want is a bloody Man U fan standing over me as I work! Grrr!
Anyways, I finally got it working (see how I take all the credit, instead of admitting that it was really Mr Spendolini that did most of the work?) and so now my users will be able to merge mail to their little hearts desires. Great work, Batman!
It probably doesn't help that the application I'm writing is being shared by two different projects and so I have project managers fighting over my time like drunk guys fighting over a girl in a pub (you can tell I live in the rough part of town, can't you?).
My latest challenge (hey, get the image of the slutty chick in the pub out of your mind and keep up, willya?!) is writing in functionality to produce mail merge letters from the app. Sounds easy, doesn't it? You'd have thought that every one would want that, that all I'd have to do would be to hit the forums and copy someone else's work, right? Wrong. Well, kinda wrong. Scott Spendolini has written a utility that does mail merges that works excellently - until you try to merge a document that's larger than 32k in size! Great, what is this - mail merges for bloody elves?
So I've had to spend days amending the code to get it work with larger documents (the problem was that SUBSTR and REPLACE and htp.p work with VARCHAR2s and so can't go beyond 32767 characters). And so I've been fiddling with CLOBs and DBMS_LOB, breaking my documents down into bitesize chunks. And I've always bloody hated CLOBs so the last thing I want is a bloody Man U fan standing over me as I work! Grrr!
Anyways, I finally got it working (see how I take all the credit, instead of admitting that it was really Mr Spendolini that did most of the work?) and so now my users will be able to merge mail to their little hearts desires. Great work, Batman!
Saturday, 12 July 2008
No dumb blonde
So my direct boss is having a go at me again about the way my application looks. It's not that the app is all skew-whiff and ramshackle, with text items all over the place like alphabet magnets on a fridge door, but I admit that it won't be winning any beauty pageants any time soon. (I'm using the Light Blue theme, which I think is the coolest of the themes that come packaged with Apex 3.)
The problem is that I'm working to such a tight deadline and I've got miles of specs left unploughed. I haven't got the time to put make-up on the application. That'll have to wait till later.
The thing that makes my boss such a good boss is the same thing that makes him a rubbish boss at times: he loves tech. As long as it's new and shiny and being written about in tech magazines he loves it. And since he's too busy to try stuff out himself he's always on at me to try them. And ever since he stumbled across Ext JS (which is this really cool javascript library) he's been badgering me to use it to pretty up my app.
"When a client looks at an application for the first time," he said to me, "70% of his first impression comes from the look of it, only 30% of it is based on functionality. So make it prettier!"
The thing that sucks about this whole thing is that I agree with him. And the reason I agree with him is that I made up that bloody fact. Back in the days when we used to develop butt-ugly apps in Oracle Forms I used to have a go at him all the time that we needed to make our products prettier, and to convince him I made up this beauty-versus-functionality percentage thing. And now he's using it on me!
So, if anyone out there has some suggestions on how I can make an Apex app pretty, in ways that go beyond simply applying a pre-packaged theme but won't take too much time - and don't require much more than the basic skills of an Oracle Forms developer - then I'md love to hear them.
Oh, and on a total tangent: are all women crazy or is it just the ones I go out with? Or perhaps there's something about going out with me that drives them to craziness.
The problem is that I'm working to such a tight deadline and I've got miles of specs left unploughed. I haven't got the time to put make-up on the application. That'll have to wait till later.
The thing that makes my boss such a good boss is the same thing that makes him a rubbish boss at times: he loves tech. As long as it's new and shiny and being written about in tech magazines he loves it. And since he's too busy to try stuff out himself he's always on at me to try them. And ever since he stumbled across Ext JS (which is this really cool javascript library) he's been badgering me to use it to pretty up my app.
"When a client looks at an application for the first time," he said to me, "70% of his first impression comes from the look of it, only 30% of it is based on functionality. So make it prettier!"
The thing that sucks about this whole thing is that I agree with him. And the reason I agree with him is that I made up that bloody fact. Back in the days when we used to develop butt-ugly apps in Oracle Forms I used to have a go at him all the time that we needed to make our products prettier, and to convince him I made up this beauty-versus-functionality percentage thing. And now he's using it on me!
So, if anyone out there has some suggestions on how I can make an Apex app pretty, in ways that go beyond simply applying a pre-packaged theme but won't take too much time - and don't require much more than the basic skills of an Oracle Forms developer - then I'md love to hear them.
Oh, and on a total tangent: are all women crazy or is it just the ones I go out with? Or perhaps there's something about going out with me that drives them to craziness.
Subscribe to:
Posts (Atom)