Roels Blog

Collecting ideas for APEX 4.0 Plug-Ins...using Google Wave

noreply@blogger.com (Roel) — Wed, 11 Nov 2009 16:01:00 +0000

As you all know by now APEX 4.0 will have some new functionality regarding the creation of Custom Item Types and Custom Region Types. These types will be called "Plug-Ins". The idea is that there will be a public App Store like library where you can up- and download plug-ins.

A snippet from the Oracle Open World APEX 4.0 presentation:

Easy way to enhance the existing built-in functionality of Oracle APEX with new item types, region types, dynamic actions, etc.
Developers use in similar fashion to native widgets
Wizard support and declarative setting of attributes
Included in APEX application export
By providing this plug-in system in APEX, we want to engage the APEX community to create a rich ecosystem around Oracle APEX.

The APEX Development Team can’t possibly incorporate all the widgets that developers would like to utilize. By using plug-ins developers can readily incorporate additional item / region types to enhance the functionality, appearance and user friendliness of their applications. Once defined, plug-in based components are created and maintained very much like standard APEX components. We believe that the APEX community will build many plug-ins and make them available to others. Much like with the SQL Developer plug-ins it will be up to the contributors whether these are freeware, etc.

In the demo the APEX Dev Team is showing an Amazon style 'star rating' (as an Item Type Plug-In). (like the image above)

So maybe now is time to gather ideas for Plug-Ins (using this cool collaboration tool). So don't be shy and add your ideas about the Plug-Ins you think you could use (or even create yourself !) or are just very cool....

Custom Item Types

Amazon like star rating

Custom Region Types

Google Wave
Google Visualizations

So please add your own ideas to this wave, and let's make collaboration happen...

Oracle Application Express Forms Converter Book - Review

noreply@blogger.com (Roel) — Mon, 09 Nov 2009 15:38:00 +0000

As I promised earlier I should write a review of the Oracle Application Express Forms Converter book by Douwe Pieter van den Bos. I finally found the time to work it through...it doesn't take that much time, because it is "just" 150 pages thick (or thin).
The book contains 8 chapters, in which the reader is guided through an Forms2APEX conversion project from start to finish. It starts of with "Understanding your Project" (1), wherein understanding the reasons for conversion as well as the functionality of the Forms application is stressed as important, because these will influence the way you'll solve things in the APEX application. One or more examples should have been useful here.
The next chapter, "Preparing your Forms Conversion" (2), covers the creation of XML files from the source files. BTW, not only Forms, also "Reports" are - briefly - touched (and can be converted as well). Also different ways of creating a target database are mentioned (but IMHO you usually can just re-use your current Forms development environment).
Chapter (3), "Create your Forms Conversion Project", covers the next step in the project: uploading the XML files and understanding the Project Overview Page.
The next chapter is about "Planning your Project" (4). It shows how you can dive into the different components (blocks, triggers and what's not), and how to use the Annotations, Tags, Assignments and Applicablity to estimate the work you've done and still have to do. That last one is rather difficult, because only the number of objects-to-do are counted - and, as we all know - that doesn't tell you everything about the time needed to accomplish things. But it might help you in planning and monitoring the project.
Chapter (5), "Getting your Logic Right!", is probably the key-chapter of the book. The difference between Original, Enhanced and Custom Query is explained in detail. Alas one line "Implementing the business logic is done post-generation, execpt for some Post-Query triggers" says it all... I now the conversion utlity is not a silver bullet, but some examples on how to convert Forms logic to the APEX equivalent would have been a very useful add-on!
Chapter (6) covers "Generating your Application", and doesn't tell anything new if you're already an APEX Developer (actually there is nothing more to tell about it than what covered here).
"Reviewing and Customizing your Application" is the penultimate chapter (7). It handles tweaking the generated LOV's, Titles and how to create a Validation based on what was an WHEN-VALIDATE-RECORD trigger in the Form.
The last chapter (8), on "Delivering your Application", discusses (very briefly) how to create an Application Export and import that in a Test-, Acceptance or Production environment. No specific Forms2APEX stuff.

All in all it is a good book when you need an overview of the steps you have to take for converting an Oracle Forms application to APEX - more on a Project Management level than a Developer level. If you really need to get your hands dirty and do the actual conversion yourself, this book is just the beginning of a great adventure...

A Google Wave in an APEX Page

noreply@blogger.com (Roel) — Thu, 05 Nov 2009 12:48:00 +0000

That is soooo simple, click here http://apex.oracle.com/pls/otn/f?p=41715:APEXWAVE.

My first blogpost using Google Wave

noreply@blogger.com (Roel) — Thu, 05 Nov 2009 10:57:00 +0000

It is somewhat experimental, but here it is...and you probably can't see it if you don't have a Wave account.

There should be a "Bloggy" robot to facilitate postings from within Wave with just one click, but that doesn't seem to work. Instead I used Embeddy (embeddy@appspot.com) to give me some javascript with the Wave ID. I copied that and pasted it in the HTML of the Blogger Template. The DIV is just added to thi post.
You can only have either one blogpost with a Wave or need a line of javascript for each Wave you need to publish. So a working version of Bloggy might be handy...

Handling information overload: Showing data on demand in an IR

noreply@blogger.com (Roel) — Tue, 03 Nov 2009 12:27:00 +0000

Using APEX Interactive Reports you can let the user decide which columns to show or hide in a report. But even then, reports with over 20 columns are still being used. So I tried to come up with a solution where you can hide less important (detail) column information and still reveal that information with minimal effort - like a mouse-over event. In this tiny example the list of employees of a department pops up when the user moves his mouse over the little question mark icon.
By adding some HTML and Javascript in the select statement (I know, that is not a "best practice"...but if you've got a better idea, please drop a comment) and some CSS you can use this technique - even for more complex "detail" reports!
The select statement used in this example is:

select dept.deptno
,      dname
,      loc
,      'onmouseover="javascript:$(''.overlay'').hide();$x_Toggle('''||dept.DEPTNO||''')">'||
''||enames|| '
'  enames
from dept
,  ( select deptno, listagg( ename, ', ') within group (order by sal) enames 
     from emp
     group by deptno
   ) emps
where emps.deptno (+) = dept.deptno

The CSS used in that statement is defined by:

One remark: You should disable sorting, filtering etc. on the "enames" column, because that makes no sense...

Edit: Thanks to Alex (see comments) the solution can be changed to something more elegant:
Just select the ENAMES column, and set these properties
- Link Text = <img src="#IMAGE_PREFIX#apps_info.gif" alt=""></a><div class="overlay" id="#DEPTNO#" style="display:none">#ENAMES#</div>
- Link Attributes = onmouseover="javascript:$('.overlay').hide();$x_Toggle('#DEPTNO#');"
- Target (URL) = javascript:void(0);

So the SQL is "clean" now and you even can enable the sorting etc again!

Integration of Forms and APEX : Calling APEX from Forms

noreply@blogger.com (Roel) — Thu, 29 Oct 2009 11:50:00 +0000

In previous blog posts (like this, this and this one) I explained how you can integrate (or embed) Oracle Forms within an APEX Page. But yesterday I stumbled upon this question on the Oracle APEX Forum. There someone was looking for a solution how to access an APEX Page from an Oracle Form - so exactly the other way round. And, after some trial-and-error I came up with this solution:

1. Copy the basejpi.htm (or whatever html file you're using in the formsweb.cfg) on your application server to (something like) forms2apex-jpi.htm and reference that in your formsweb.cfg.

2. Edit the forms2apex-jpi.htm and add an IFRAME tag just before the closing BODY tag:

<IFRAME src="http://localhost:7778/pls/apex/f?p=104"
       style="width:800px;height:450px;visibility:hidden"  
       name="APEX" 
       id="APEX" 
       scrolling="auto" 
       marginwidth="1" 
       marginheight="1"
       frameborder="1" 
       vspace="1" 
       hspace="1" />

where the src references your APEX app.

3. In the same file, add a javascript function between the HEAD-tags, to call the APEX page (in this example it is an IR and I add a parameter to the call):

and save the file.

4. In your Form add some code to a button or - in this example - to the WHEN-NEW-RECORD-INSTANCE trigger:


ShowApexPage( 12, :CUSTOMERS.CUSTOMER_ID );

and define that ShowApexProcedure as a Program Unit (in your Form or Library):

 PROCEDURE ShowApexPage
          ( p_page_no number
          , p_id      number 
          ) IS
BEGIN
 web.show_document('javascript:ShowApexPage( '||p_page_no||','||p_id||');', '_self');  
END;

And now you're ready to roll....

Of course there are some limitations: It works best when you're using SSO or a Public APEX Page, otherwise APEX will present a login screen first...

New cool Quest Tools

noreply@blogger.com (Roel) — Wed, 28 Oct 2009 11:42:00 +0000

Yesterday evening guys from Quest presented their latest cool new tools at one of our company locations. They started with Foglight, a tool for Application Management. Using that tool you can see on one page the status of the applications that are running, where users experience some problems (based on thresholds), the number of users affected and where in the application stack the problem occurs. With a few clicks you can drill down to the actual origin of the problem. Foglight supports a number of application types (like Java, .Net, Siebel, SAP), different types of access (o.a. Apache, Citrix, Rich Clients), multiple infrastructures (Windows, Solaris, HP/UX, VMWare) and different databases (SQL Server and - of course - Oracle).
Foglight uses so called "Quest Collectors" on the web-, application- and databaseservers to sample data. That data is collected in the Foglight Management Server. Apart from that you can use an Experience Monitor and Viewer as a network sniffer.
Quest claims it is an end-to-end performance management tool, but actually the scope is somewhat limited to "just" the servers: storage is not included and for network management there is only a diagnosis and no suggested solution.
Foglight is complementary to Spotlight that has been around much longer. Spotlight is also a very nice graphical tool, but is - much more - limited to only one database instance.
Of course everything has it's price. This cool tool costs around 500 euro per CPU socket. A 'normal' implementation will cost you 20 - 200 kEuro (+ 18% support fee a year).
Also some new features of good old TOAD (v10) where addressed, like the enhanced ER Diagrammer, a new sort of Data Grid (with grouping, colors, hide/display columns etc.), an enhanced Data Generator - that can generate sample data with taking care of referential integrity and Unicode support. All the details are on www.toadworld.com.
The last product they showed was Foglight Performance Analyzer, a tool that has overlapping functionality with AWR, ASH and ADDM (but then without the Oracle Diagnostic and Tuning Packs license). The analyzer uses a StealthCollect memory tool to detect changes to the Oracle memory stack (without having an Oracle connection!) and offloads these changes to a data warehouse. So you can analyze and query the use and changes of your Oracle DB, without actually using the DB itself. Also tracking errors or dumps when the database itself is down is a nice functionality.
All in all a nice informative evening, with lots of tools that I definitely would like to try in a real environment (now I have to find a customer who uses or needs this sort of stuff).

Want to be a presenter at the Oracle Developers conference?

noreply@blogger.com (Roel) — Sat, 24 Oct 2009 10:50:00 +0000

Developers and architects attend Kaleidoscope to find out how to use the new technologies to make their jobs easier. Your expertise and experience are key to our participants getting their questions answered. Click here to submit your abstract to speak at the developer event of the year.

ODTUG Kaleidoscope 2010 at the Marriott Wardman Park Hotel in Washington, D.C. is the conference for Oracle developers and architects. Kaleidoscope has it all - more than 150 technical sessions, day-long symposia, hands-on training, chats with participants and speakers, and even a community service project.

So far the "offical" advertisement. My personal opinion is that Kaleidscope is the place to be for Oracle Developers. Just because it is at a 'somewhat' smaller scale than - for instance - Oracle Open World (last year there where around 700 attendees), you can speak to whoever is there (and 'everyone' is!). Also the content of the presentations is very good. But don't let that discourage you from entering an abstract! If you have a good subject and a good abstract (and a catchy title could help..) you definitely will make a chance!

Hope to see you there (if my abstract gets accepted....).

Oracle Application Express Forms Converter Book - Review coming up!

noreply@blogger.com (Roel) — Thu, 22 Oct 2009 14:52:00 +0000

Back from OOW I found the Oracle Application Express Forms Converter book in my pile of mail. I received a free copy - just adding the word 'free' in order to prevent a claim from the FTC... ;-) for writing a review on this blog. I haven't had a change to read the book, although it is only 172 pages, significantly less than the other Oracle related books on my shelf. If you are interested in a review now, please read Marc Sewtz's blogpost - he has already read it and wrote a review. I will try to write the review somewhere next week (with the usual disclaimers applied). If you're curious, you can read a preview chapter of the book here.

New article : Integration of Oracle Forms and APEX

noreply@blogger.com (Roel) — Wed, 21 Oct 2009 15:00:00 +0000

When I opened my mail after a week in San Francisco I discovered that my article was published in the latest release of WeLoveIT (a free magazine for Oracle & Java users and developers). If you can read Dutch (or want to try), you can read it either here or here.

OOW 2009 : Thursday and wrap up

noreply@blogger.com (Roel) — Sun, 18 Oct 2009 14:08:00 +0000

The day started easy: The first session had similar content as the last one of yesterday - which I didn't like too much - and the second one got cancelled. So a late start, what was fairly convenient because of yesterday's party...
The first one was by Arup Nanda about 11g New Features for DBA's. Since 11g is already 'old school', I didn't learned that much: physical / logical / snapshot standby databases (quite handy for testing in a real production environment, performing rolling upgrades and reporting); partition by reference (where you can easily keep master and detail data in the same partition) etc. I was hoping for more R2 stuff, but maybe it is just to early as that version was released just a couple of weeks ago.
The second one was all about Data Security. There are so many features and options regarding that subject. To name a few: VPD, FGA, Encryption, Data Masking, Advanced Security, Audit Vault, Database Vault, Label Security, Secure Backup.... I think it is time for a new SQL command: "alter database set security = high".
The last session of this years OpenWorld was done by Rich Niemiec. He is a great entertainer (throwing candy, bars, chips etc. in the audience, lots of funny stories) and managed to put over 200 slides in 1.5 hour - what was only half an hour over time. This session was also called "11g New Features" - but this time not only for DBA's. And again, not many things that I haven't heard before.
That night Andreas surprised his housemates with a real gourmet dinner (with bison steak). And that was a really good closure of what should become a tradition: the Holland ACE House! Thank you Andreas, Jacco and Marko for your great company this week. And of course a special big THANK YOU for Anjo: He made this all possible!

Typed on my iPod in a shaking plane...

OOW 2009 : Wednesday

noreply@blogger.com (Roel) — Thu, 15 Oct 2009 16:54:00 +0000

I started off with a good breakfast with my roomies Marco and Jacco. Because there wasn't any interesting session in the first timeslot (or I couldn't find it), I attended the Exhibition Hall - which is mainly the same as previous years. Everyone wants to 'scan your badge' so they can spam you afterwards.
Next there were two sessions I really liked, and - it may or may not be a coincidence - both were 30 minutes 'power sessions'. The first one was by Mark Drake, the Product Manager for XML DB, about Managing XML Content with APEX. Very good job, although having live demo's obviously are a risk...and that's why I like them!
The next one was about Row Level Security / VPD and APEX. The presenter had a good demo and explained the stuff nicely.
I followed the keynote from the OTN Lounge (it is less boring with a beer). It wasn't a surprise anymore that The Governator would attend. Arnold emphasized that technology will deliver the solution for CO2 reduction - he didn't say that technology was also the cause of all kinds of pollution...
Larry's story - the only presentation from Oracle without the safe harbor slide - didn't contain anything astonishing new: Oracle has Linux and VM (that's a three year old story); has an Exadata machine thats an (undefined) number of times faster than the one form IBM (Sunday's news..); My Oracle Support will get better; Fusion Apps are on their way (with demo).
The last real session of the day was Building a Mashup with APEX. The presenter painted out an architecture where you create webservices on your database objects, and consume those in APEX on XE. The reasons for doing so was that
- you don't need a license for XE (that's true, but you do need a license for the underlying database, so that's a non-argument)
- he thought that APEX itself would have a negative impact on the performance of his database (which is obviously also a non-argument, as APEX adds only a very little bit of overhead - and creating extra layers as he suggested will create more overhead)
- the DBA's didn't like the idea for having an 'application in the database': the database is for data, not for applications... (An misconception that some old school DBA's hang on to).

And at night : The Appreciation Event with Aerosmith. And they rocked - especially when you know that Steven Tyler (the leadsinger) is 61!

OOW 2009 : Tuesday

noreply@blogger.com (Roel) — Thu, 15 Oct 2009 16:32:00 +0000

This day started easy by skipping the keynotes. So the first session I attended was at 11:30, called The Life of a Query in which the presenter revealed the details on the internal processing of the Oracle database.
Next was Giving your Application Express a Web 2.0 Facelift. Not that many new things, but Raj is always very entertaining to watch. The most interesting thing I saw was a new prototype of the APEX Developer page on Patrick Wolfs laptop: Instead of the current Page Definition view (with Page, Regions, Items, Processes and so on), the same information was presented in an DHTMLX tree view! Patrick showed how you can easily re-order Items, copy buttons etc by just dragging them around in the tree. And on every object you can do a right mouse click that would present the actions you can do with that object. It looked very slick! We shortly discussed whether or not you should expand that tree with one level (Page) or even two (Application), to make it even easier to copy and move Pages or Regions around. Imagine something like the tree view of your application in Oracle Designer - but then better looking. I guess this prototype won't make it to APEX 4.0, but it shows that the product is still evolving - even after APEX 4.0!
After that Understanding Real Application Clusters for Developers was on the program - I guess using Dummies instead of Developers was to offensive. However it was a reasonable good overview about RAC, but didn't associate about anything that a developer might have to do with it. And the presenter ran seriously out of time....
The last one of the day: Oracle Performance by Design. A presentation full of half truths and generalizations. So no further comments....
In thee evening there were three meet-ups to attend: The Blogger MeetUp, Benelux Cocktail Party and the APEX MeetUp. I had to pick and did the first and the last. Alex G., who organized the Blogger thing this year, also introduced a nice competition. He/she with the most names on their new T-shirt (with the text "I met more bloggers than you"), would win a netbook! So there was a lot of signing going on! Good job, thank you Alex!
And of course, at the APEX meetup al the usual suspects where there. And - as every time - it was really nice to meat each other again!

OOW 2009 : Monday

noreply@blogger.com (Roel) — Tue, 13 Oct 2009 17:15:00 +0000

Still suffering from the jet lag I woke up at 6 AM today. Due to all kinds of other 'obligations' I only attended one session today about "How to get 10x improvement of your PL/SQL Application performance time". And that one was all about TimesTen (a.k.a. IMDB - In Memory Database Cache). The session started with a small - alas pre-recorded - demo that indeed showed a 10x performance benefit. But before you all start to move all your applications to TimesTen, be aware that there are 'some' limitations:
- Not all standard PL/SQL packages are available in TimesTen
- You can't call PL/SQL from SQL
- You can't use triggers in TimesTen
- You can't join between tables in TimesTen and the 'real' database
So due to this limitations, you can't run all PL/SQL applications (like APEX!) in a TimesTen database. However it would be extremely cool if you could use TimesTen for APEX (imagine APEX running 10x faster!). So maybe somewhere in the future, but not with the current release.
Next a had a (long) lunch with 16 Logica attendees from all over the world, very well organized by my US colleagues.
After that, I headed to the demogrounds, to take a close look at APEX 4.0. Mark and Anthony where so kind to show me a (private) deep dive into the new features, like enhanced tabular forms (with field validations), Team Development (a sort of integrated packaged application for registering enhancements and bugs, assignments etc), Dynamic Actions and Plug-Ins. This all looks very promising and opens up a whole new set of functionality, that would require a lot of handcraft in the current version. APEX 4.0 is (still) planned for production somewhere in 2010 (I hope it will be in the first quarter!).
Next I went to a hotel where a bus picked me up for a ride to the HP Pavilion in San Jose to see my first ice hockey game: San Jose Sharks v.s Phoenix Coyotes. Very nice to see that sport live in an American ambiance! Thanks to my French colleagues for organizing this!
At the moment of writing this - Tuesday morning - it is very rainy and windy in San Francisco. Rather different from my previous visits at OOW, where the sun always shined... Time to get my umbrella!

OOW 2009 : SUN-rise on a SUN-day

noreply@blogger.com (Roel) — Mon, 12 Oct 2009 14:07:00 +0000

This week I am in San Francisco for my third Oracle Oracle Open World. Because it was my second consecutive visit I received an "Oracle Alumni" tag (for my badge) and a nice jacket during registration.
This Sunday the first session (at 800 AM! - OOW is really hard work...) was by...me! There were around 50 to 60 attendees, and IMHO that's not too bad for a session at that time. The session seemed to be interesting enough for 111 people to include in their schedule, but I guess for a few of them it was too early after all...
The session went ok, not great, but ok. I would rather do a session after being a couple of days in the US, because then you're more used to the language. This one was within 20 hours of arrival.
Right after my session - and a well deserved cup of Starbucks coffee with Edwin - I went back to the "Holland ACE House", where I am staying with Marco, Andreas and Jacco, to drop off my laptop. BTW the house, rented by Anjo (thanks mate!), is very nice. You can see the pictures here. Right after that I went back to the Moscone for some sessions within the ODTUG track. They were all about migration to APEX. One company moved from Access to APEX, another from PowerBuilder / Cobol / to APEX. Another proof that APEX is suitable for smaller (Access like) apps as well as Enterprise level applications.
After that on to the Hilton for a presentation by "Mr PL/SQL" : Bryn Llewellyn, about OnLine Application Upgrade. Bryn thoroughly explained the goal and the use of the new 11gR2 feature "Edition Based Redefinition".
Meanwhile I missed the APEX 4.0 session (I have seen the major part of it at last June's ODTUG), but it was very well received. If you're interested you should take a look at Lucas' blog post.
Then off to the impressive Hall D for the opening keynote of this year's OOW by Scott McNealy, the chair man of Sun. He did a good job presenting two top tens - in an almost Oracle-red sweater. Also on stage came James Goslin, the inventor of Java. Dresses in an old jeans and a baggy T-shirt he perfectly fitted the description of a Java nerd. Scott introduced "his hero" (what sounded a little bit sarcastic): Larry Ellison. Larry, again, stated that Oracle will spend more money on the development of SPARC, Solaris and MySQL than Sun would do. Apple already proved that the combination of delivering hardware and software can be successful, so why can't Oracle do something similar?
The main part of Larry's talk was aimed at IBM. Larry stated that IBM is more expensive, uses more electricity (that's why they call it the "POWER" processor ;-) ) and not fault tolerant. Suns runs Oracle twice as fast than IBM. Larry also announced a new chapter in the Oracle - IBM competition: If your database doesn't run two times faster on Oracle/Sun than you'll win 10,000,000!

In the evening the ACE dinner was organized on a nice location near the Bay Bridge.

Forms / APEX Integration video

noreply@blogger.com (Roel) — Fri, 02 Oct 2009 11:43:00 +0000

I just uploaded a video with a small demo of the integration of Oracle Forms and APEX. The quality is not quite what I wanted, but it gives - some kind - of idea. If you want to see the real (high res) demo, you can visit my presentation at Oracle Open World (at Sunday morning 8:00...) - or at the upcoming UKOUG.

APEX Meetup @ OOW

noreply@blogger.com (Roel) — Tue, 22 Sep 2009 14:02:00 +0000

Also this year : APEX Meetup @ OOW

Getting rid of the annoying popup help

noreply@blogger.com (Roel) — Tue, 22 Sep 2009 12:39:00 +0000

Recently I stumbled upon two excellent blog posts about how to change the default pop up APEX Help functionality to something more user friendly. Martin explained how to transform the Help into a tooltip, while Piotr transformed the Help pop up into a DIV.
I will add my two cents to this discussion by adding another option...
The pop up is trigger by a call to the Javascript function "popupFieldHelp'. So I decided to locally overwrite that function with this one:


function popupFieldHelp(pItemId, pSessionId ){
  // Construct the URL
  vURL = "wwv_flow_item_help.show_help?p_item_id=" + pItemId + "&p_session=" + pSessionId;
  // Call getRequest to get the HTML for the popupWindow
  var popupWindow = getRequest(vURL);
  // Get the Subject (item name) from the Window
  var HelpSubject = $(popupWindow).find('.fieldtitlebold').html();
  // Get the HelpText from the Window
  var HelpText = $(popupWindow).find('.instructiontext').html();
  // Show the results as an alert
  showHelp( HelpSubject, HelpText );
}

This function calls two other functions:
a. showHelp - to show the Helptext in a jQuery Dialog box
b. getRequest - to retrieve the results of an URL by executing an AJAX call (I was hoping to find something like that available in the standard APEX Javascript lib, but couldn't find it - if you know a better way, please let me know!).
The result is a nice looking help (you can tweak the settings and/or presentation the way you like):

IMHO the main advantage for this approach is that you don't need to change anything in your templates (apart from loading the necessary Javascript files), so it is very easy to implement!
As usual there is an example on apex.oracle.com...

The code for "showHelp" is below:


function showHelp(pTitle, pText){
  vText = ''+''+pText+'
';
  $(document.body).append(vText);
  $("#info").dialog({
               bgiframe: true,
               modal: true,
               minHeight : 200,
               width : 200,
               close : function(){$("#info").remove();},
               closeOnEscape : false,
               buttons: {
                   Ok: function() {
                     $(this).dialog('close');
                     $("#info").remove();
              }}
   });
}

The code for "getRequest" is here:


function getRequest( vURL ){
  http_request = false;
  if (window.XMLHttpRequest) { // Mozilla, Safari,…
    http_request = new XMLHttpRequest();
    if (http_request.overrideMimeType) {
      // set type accordingly to anticipated content type
      http_request.overrideMimeType("text/html");
    }
  } 
  else if (window.ActiveXObject) { // IE
    try {
      http_request = new ActiveXObject("Msxml2.XMLHTTP");
    } catch (e) {
        try {
          http_request = new ActiveXObject("Microsoft.XMLHTTP");
        } catch (e) {}
      }
  }
  if (!http_request) {
    alert("Cannot create XMLHTTP instance");
    return false;
  }
  http_request.open("GET", vURL, false);
  http_request.send(null);
  return http_request.responseText;
}

Running APEX on 11gR2 using Sun's (Oracle's?) VirtualBox

noreply@blogger.com (Roel) — Mon, 21 Sep 2009 12:39:00 +0000

Today I decided that I needed an environment to play with the latest and greatest Oracle RDBMS : 11gR2. This version is only available for Unix platforms, so I needed a virtual environment to get that working on my laptop - running on XP.
You can pick any kind of virtualization software, but, due to the recent take-over of Sun by Oracle, I decided to go for what's currently known as Sun's VirtualBox. I guess this product will re-branded to Oracle's VirtualBox somewhere soon.
So what are the steps:

1. Download VirtualBox from http://www.virtualbox.org/ and run the installer.

2. Start VirtualBox, click 'New' and enter a Name, Operating and Version for the Virtual machine. This is gonna be a 100% Oracle thing, so I installed Oracle Enterprise Linux (OEL).

3. Oracle 11gR2 needs 1Gb of RAM, so set the memory size to 1024 - you can change this afterwards.

4. Create a new boot harddisk with dynamically expanding storage.

5. On the next page you have to set the 'size of the virtual hard disk'. This is not the initial size, but the maximum to which the harddisk can be expanded. So don't take it too small, because adding or expanding a hard disk to a current installation is not so easy...(as I have experienced).

6. Next start the VM and install OEL from a DVD or ISO download. I installed it with all defaults, with support for Software Development and Webserver. Honestly I have no idea what is added by checking these features, but I checked it just because that's what I want to use it for.

7. Now take a break - or do something else - while OEL is being installed... it takes a while.

8. After installation you have to configure OEL. You can accept all defaults, I just changed the settings for the Firewall to accept HTTP request on port 8080 - the one we're going to use for running APEX.

9. Next add a Shared Folder in VirtualBox - a folder that can be used by both the host and the guest OS. To use it within the Linux environment: login as root, mount the VBoxGuestAdditons.iso (by pressing the Host-D key, whereby Host is the right Ctrl key by default), copy VBoxLinuxAddtions-x86.run to the Desktop and run it. Restart the VM when that's finished.

10. Mount the added shared drive: sudo mount -t vboxsf Shared /media/shared

11. In the Host OS: Download the two 11gR2 disks, copy them to c:\shared, unzip them - of course you can do that in the Guest system also, but not while you're installing it ;-). Install 11gR2 on the Guest OS following the steps in the documentation. Again some time for a good cup of coffee....

12. 11gR2 is shipped with APEX, so it just takes two steps to get it running:
Open up the EPG using port 8080 : Start SQLPlus, login as SYS, exec dbms_xdb.sethttpport(8080) and set a password for the APEX Admin user by executing the apxchpwd script (located in /home/oracle/app/oracle/product/11.2.0/dbhome_1/apex for a default installation). Now you can open up the browser, navigate to http://localhost:8080/apex/apex_admin and off you go.

13. Now where almost there...because now we want to use the database in the VM from the browser in the Host OS. Therefore we need to set up Port Forwarding in VirtualBox. Shutdown the Guest VM and VirtualBox. Run these three commands:

vboxmanage setextradata OEL "VBoxInternal/Devices/pcnet/0/LUN#0/Config/Apex/HostPort" 8080
vboxmanage setextradata OEL "VBoxInternal/Devices/pcnet/0/LUN#0/Config/Apex/GuestPort" 8080
vboxmanage setextradata OEL "VBoxInternal/Devices/pcnet/0/LUN#0/Config/Apex/Protocol" TCP

Startup VirtualBox and the VM. If the database doesn't start, login as the oracle user, set the environment (. /usr/local/bin/oraenv), start the listener (lsnrctl start) and start the database (sqlplus sys as sysdba and enter 'startup').
Now we can use the APEX on Oracle 11gR2 in a Oracle Enterprise Linux VirtualBox from an XP Host OS. And it runs very fast....!!

One final remark: If you've got an Oracle instance running on your Host OS, http://localhost:8080/apex will open up the login page from your Host. So you either have to shut that down, or forward another HostPort (like 8081).

Easy Show/Hide items in a Form

noreply@blogger.com (Roel) — Thu, 17 Sep 2009 10:08:00 +0000

For showing and hiding information in a Form you can use a default "Show and Hide Region". But what to do if you want to show/hide some details within a region? The answer is: with a Label Template, some cool jQuery UI stuff and "Stop and Start HTML Table" Items.

1. Create a new Label Template (From Scratch) for Template Class 'No Label'. Call it 'Show/Hide Next Image' or something else you fancy.
2. Edit the Label Template you've just created. Remove everything that's there by default.
3. Set the 'Before Label' to :
<img src="wwv_flow_file_mgr.get_file?p_security_group_id=<your workspace id>&p_fname=
4. Set the 'After Label' to :
" onload="$(this).next().hide();" onclick="$(this).next().toggle('blind');">
5. Now create a "Stop and Start HTML Table" Item just before the items you want to show/hide. Edit that Item and set the label property to the name of the image you want to show (like Info.png) and set the Template to the template you've just created.
6. Run your page... you should see something like this example.

Everything between the "Stop and Start HTML Table" and another "Stop and Start HTML Table" or until the end of your Form will be shown/hidden.

Now you can use this throughout your application to save space or hide information that should only be shown on request. For instance, it is perfect for hiding some detail audit info.
Of course you can tweak the Template and use some other kind of jQuery UI effect, or change it to show/hide on mouse over.

Transform ORA-xxxx messages into something more user friendly in APEX

noreply@blogger.com (Roel) — Tue, 15 Sep 2009 14:43:00 +0000

When you delete a record that is referenced using a foreign key constraint you get a rather ugly error like this:
Of course you can prevent such a thing from happening by creating a Page process that checks if your delete is "Ok". But that's double coding...and dangerous when you have to maintain your database structure: You'll probably forget to update the processes as well. Wouldn't it be nice if you can 'catch' that error an present it to the end user in a more friendly way (and with a somewhat more understandable text)? I guess your users will be more enthusiastic about your application (and you) when you show them something like this:
So how do we achieve that?

1. Edit the "Error Page Template Control" region of your Application Default Page Template, because that's the one that is used for presenting errors. Set it to :


<script type="text/javascript">raiseErrorHandler();</script>

2. Create the raiseErrorHandler function (I'll use some jQuery UI stuff to show the nice dialog box):


function raiseErrorHandler(){
  vError = $(".ErrorPageMessage");
  vError.hide();
   var get = new htmldb_Get( null, $v('pFlowId')
                ,'APPLICATION_PROCESS=GetErrorMessage'
                ,$v('pFlowStepId'));  
   get.addParam( 'x01', vError.html());
   gReturn = get.get();
   get = null;
   var errArray = gReturn.split("#",2);
   showError( vError, errArray[0], errArray[1]);
}

function showError(pThis, pTitle, pText){
 vText = ''+'
'+pText+'
'
  $(pThis).append(vText);
  $("#alert").dialog({
               bgiframe: true,
               modal: true,
               minHeight : 200,
               width : 600,
               closeOnEscape : false,
               close : function(){window.history.go(-1)},
               buttons: {
                   Ok: function() {
                     $(this).dialog('close');
                     $("#alert").remove();
                     window.history.go(-1); 
              }}
   });
}

3. That Javascript function calls an Application Process GetErrorMessage:

DECLARE
  p_error_title varchar2(32767);
  p_error_text varchar2(32767);
  p_ora_error varchar2(32767);
BEGIN
  p_ora_error := wwv_flow.g_x01;
  -- Get "translated" error message
  get_error_message( p_ora_error, p_error_title, p_error_text );
  htp.p( p_error_title||'#'|| p_error_text );
END;

4. The procedure get_error_message is defined in the database. This procedure queries a table that contains the contraint name (the 'ORA Error') and a more user friendly description.


create or replace PROCEDURE GET_ERROR_MESSAGE
( p_ora_error   IN  VARCHAR2
, p_error_title OUT VARCHAR2
, p_error_text  OUT VARCHAR2
) AS
  CURSOR c_mps IS
  SELECT code, description
  FROM afk_message_properties
  WHERE  INSTR( p_ora_error, constraint_name ) > 0;
BEGIN
  OPEN c_mps;
  FETCH c_mps INTO p_error_title, p_error_text;
  CLOSE c_mps;
EXCEPTION 
  WHEN NO_DATA_FOUND  
  THEN 
    p_error_title := 'No translation found for this error';
    p_error_text := p_ora_error;
END GET_ERROR_MESSAGE;

The Application Process spits out a concatenated text, that will be segregated again in the Javascript function. The result is presented to the user in a way that would make him happy....
Even if there is no error definition found in the table, the error will be shown in the dialog box - only the translation to a normal language is missing.

Kinda cool I think!
You can see a public demo when you click here.

Blackbird : Javascript logging 2.0

noreply@blogger.com (Roel) — Tue, 15 Sep 2009 08:36:00 +0000

When debugging your APEX Javascript stuff you probably use Firefox with Firebug. By writing messages to the console, you can follow the flow of your Javascript. Incidentally you use - annoying - alerts in your code to find out where you messed up your code. But recently I stumbled upon BlackBird! With blackbird you can write different types of messages, like info, warning and error messages, to a little console window, that looks a bit like a Twitter client. You can show/hide the window using F2. In this little window you can toggle the type of messages you're interested in. And it works even on IE!
Blackbird consists just of 1 CSS, 1 JS and two image files, so it is very easy to install.
Just a little tip, rename the namespace from 'log' to something like 'bb', to avoid conflicts with the Firebug log.
You can find the code, demo and docs on http://www.gscottolson.com/blackbirdjs/.

OOW 2009 Blogger Meetup

noreply@blogger.com (Roel) — Thu, 10 Sep 2009 06:56:00 +0000

All you bloggers out there visiting Oracle Open World this year, let's get together at the traditional annual Blogger Meetup. This time the organizer is Pythian's Alex Gorbachev. Thank you Alex!!

Wondering what an Oracle ACE Award looks like?

noreply@blogger.com (Roel) — Fri, 04 Sep 2009 09:41:00 +0000

I (finally) received the actual award (thanks to Lillian and Justin)!

Problems with running Oracle Forms on Firefox?

noreply@blogger.com (Roel) — Mon, 31 Aug 2009 11:41:00 +0000

As I blogged about two weeks ago, I still get a lot of hits regarding problems when running Oracle Forms with Firefox. From this Client Platform Statement of Direction you can see that Firefox 2.0 (current is 3.5!) is the latest version that is certified in combination with JInitiator and Sun's JVM version 1.6.004 (current is 1.6.015). Not certified doesn't imply that it doesn't work, but from my experience running Oracle Forms with Firefox 3.0/JVM 1.6.005 works without any problems, but Firefox 3.5/JVM 1.6.005 does not!
So be aware of 'automatic updates'...