Tuesday, 24 March 2009

The 3rd way to export applications (or Oracle are(n't) the Devil)

Not too long into your career as an Oracle developer - perhaps after your database has spewed a stream of error messages in your face kinda like the green vomit in The Exorcist - you begin to suspect that Oracle Corp. is the Devil. The Devil: not merely bad, not merely irritating - but Lucifer, Beelzebub, Satan, Esu, the Antichrist himself. So much so that whenever I meet an Oracle employee I study them to see if they've got horns or a tail, and I sniff them to see if they smell of hellfire and brimstone.

This week at work we upgraded our Apex environment to version 3.2 from version 3.1.2. The upgrade seemed to go smoothly, but for some reason it turned our applications buggy. Pressed for time, we decided to downgrade back to 3.1.2 and we followed the instructions (here and here), but that didn't go so well either. The package sys.wwv_dbms_sql wouldn't compile; it complained that the wwv_dbms_sql.parse_as_user procedure had errors.

With absolutely nothing working now we decided to completely uninstall and reinstall Apex. But first we had to export our applications.

And this is what I wanted to talk to you about. You obviously know that you can export an application from within the Application Express development environment. No need to talk about that. You probably even know that you can export applications using SQL Developer, but perhaps, like me, you are unaware that there is a third way to export applications.

In the apex/utilities folder (that you got when you first downloaded Application Express), there are 2 java programs - APEXexport and ApexExportSplitter - and a readme file. The readme.txt is short but tells you all you need to know about using the java programs, including setting up your classpaths.

APEXexport takes a number of the usual boring parameters - db, user, password - but more interestingly you can feed it an application id (if you want to export a single application), a workspace id (if you want all the applications in a particular workspace) or the word instance if you wish to export all applications irrespective of workspace.

And, um, that's it. It works - no bugs, no quirks, no green vomit (unless, of course, you failed to follow the advice of the readme and set up your classpath first). It's very useful if you find yourself in a situation like me - i.e you are unable to get into your Apex IDE but need to salvage your applications before reinstalling Apex - or if you wish to write a batch file to backup your apps.

Very useful, very ... unevil. Hmm, maybe Oracle aren't the Devil after all...


Joel R. Kallman said...

Hi David,

Interesting post.

When you say "The upgrade seemed to go smoothly, but for some reason it turned our applications buggy.", can you please elucidate the issues you encountered?

Also, when you say "The package sys.wwv_dbms_sql wouldn't compile; it complained that the wwv_dbms_sql.parse_as_user procedure had errors.", what were the specific errors? Did you follow the instructions from the release notes in point #1 where it stated "Change your working directory to the apex directory on the filesystem that includes the *3.1* version source."?

I'd like to understand this better so we can help other customers who may run into these same issues.


Louis-Guillaume said...

Thanks for sharing.

David Njoku said...

Hi Joel,

My colleagues told me that their apps stopped working. I'll find out details and let you know on Monday.

I'm confident I followed the instructions correctly for downgrading, including changing my working directory. However, one can ever be 100% sure, so maybe I didn't.

The error I got when I tried to compile the wwv_dbms_sql package said that I was passing in the incorrect number/type of parameters to the parse_as_user procedure (note that I wasn't running the procedure, simply compiling the package).

I hope this helps. I'll get back to you with the specifics of the problems my colleagues had.


David Njoku said...

Hi Joel,

Here's an email I got from my colleague talking about the problems they had after the upgrade to 3.2:

[Since] you're interested in the issue we were having with 3.2 the exact sql statement that was failing is:

SELECT context
INTO v_context
FROM pro_dynamic_form
WHERE id = V('P11_FORM_ID');

As you can see there's nothing strange about it at all only it's called from a database package. In the browser P11_FORM_ID is populated with a value and the Session information had a status of Inserted. However, V('P11_FORM_ID') didn't have a value. I've tried to reproduce this on the Oracle hosted environment and couldn't so it's probably a security setting that they've brought in that we need to set up. Or something.

The other issue I was having was that the extJS code didn't render unless you hit ctrl+f5 on a page to do a complete reload. This was every time you loaded any page, not just when you loaded a new page. Which is very strange. This also seems to have been resolved now that we're back at 3.1.2.



Unknown said...

Hi there, I also attempted to downgrade from 3.2 to 3.1 and I get an error on the 'switch_schemas' function - the wwv_meta_cleanup function does not compile. Prior to the command it did compile so something in the switch_schemas caused it to fail. Several other packages in the FLOWS_030100 schema also stopped compiling after running the command. Any tips?