Apex Tutorial: Adding a Read Only Option

Last week Recently I published a tutorial walking through setting up a form and report on the same Apex page. This one done using out of the box functionality, without the use of dynamic actions. Hence this is aimed at those learning the Oracle Application Express development tool.

Edit Feb 2012 :
Bug 13587192 Quick picks are displayed for read-only page items and in Printer Friendly mode
Has been included in the 4.1.1 patch; Demo; OTN Forum entry.

We can take this a step further and extend this functionality to open the record in read-only mode first.

1) Modify report link to pass P6_MODE = R, instead of E
Now we have three "modes" View (which doesn't display the HTML region), Read only & Edit.

2) Modify all form items to apply read only condition to P6_MODE = R
Now when you click the link for an employee record, the form initially opens in read-only mode.

3) Modify HMTL region condition to be :P6_MODE IN ('E','R')
We need this region to display in edit and read-only mode.

4) Extend Delete/Save buttons conditions to include :P6_MODE = 'E'
ie => :P6_EMPNO IS NOT NULL AND :P6_MODE = 'E'
We don't necessarily want these buttons being displayed in read only mode. 

5) Create Edit button in the HTML region
You could add an authorisation condition on this button to make editing records available only to privileged users.

* condition :P6_MODE = 'R'

* redirect to page 6 

* passing parameter P6_MODE = E

The problem now if if you have, for example, quick picks on any of your items - these will still be displayed as the read only option only adds the class="display_only" tag to the item definition:

6) So to clean this up, we need to make a copy of item P6_JOB

Use nearby sequence number to create the copy in the same position on the page.

My job field sequence was 30, so I chose 35

7) Remove the quick picks option from copied item P6_JOB_DSP

These are the options we don't want displayed in read-only mode. The user can't save the change, but it looks untidy.

8) Add display condition on P6_JOB_DSP as :P6_MODE = 'R', retaining the existing read only condition.

Alternatively we could set the item type to display only, however we still only want it appearing in read-only mode.

9) Add display condition to P6_JOB as :P6_MODE = 'E'

The original item only needs to appear in edit mode.

We now have a functioning read-only mode option, with an Edit button available to re-open the page in edit mode.

The item list in the page builder will now look more like this:

Run the demo to see it in action. There are other techniques demonstrated on this page.

I hope that further helps your understanding of some of the concepts available to the Application Express environment.

Scott.

Apex Tutorial - Form & Report sharing the same page

During some training recently I walked through the "Form on a Table with Report" wizard to create two pages - one report listing records from a table, which then linked to a form page allowing edits on that table.

I was asked how you could amalgamate the two pages into one. Here is a tutorial that completes this exercise from scratch.
Note: if you have existing pages already defined, I would recommend you copy the report region onto your Form page, and modify the relevant attributes - then remove your report page.

1) First, create a form on page on a table - I use the sample EMP table

* When prompted during the create form wizard, set branch for apply/cancel to the same page you're creating. Note in my case it's page 6.

Now we have a basic form page, where most of the hard work has been done by the wizard, such as defining page processes, branches and item definitions.

You form will look something like this to start with:

2) Now create a report region listing employees from EMP

 This will show a simple report underneath your form:

3) Create item P6_MODE
This will be the catalyst for showing/hiding certain components on the page

4) Create a button called NEW on the Employee List report region
This will allow you to create a new employee record

Action details:

* redirect to 6
* set parameter P6_MODE = E
* clear cache => 6
* condition => COALESCE(:P6_MODE,'V') = 'V'

5) Modify the Cancel button action to set P6_MODE = V
This ensures your page returns to status quo when the cancel button is pressed

6) Modify the branch defined by the create form wizard and pass parameter P6_MODE = V
Whenever you submit your page, it will now return it to status quo

7) Add condition on HTML region that contains your form items to check :P6_MODE = 'E'
Now your form region will only appear when in "Edit mode", such as when the New button is pressed or the user edits an existing record (next step)

8) Modify EMPNO column in the report to become a link
This will allow your user to edit an existing employee record. 
While in the report attributes page, I set the column headings to custom and cleared the heading for EMPNO - just to tidy up the display a touch.

* redirect to 6
* set parameters P6_EMPNO = #EMPNO# and P6_MODE = E

You should now have report page that displays the form region when the New button is pressed, or when you click to edit an existing record. The form region will disappear again once you Apply/Delete/Cancel.

Above is "status quo"; below while editing an existing record

I hope that helps, and I also hope it made sense. I made certain presumptions about you finding the relevant settings - otherwise there would be a real flood of screenshots!

Here is a screen grab of the page builder after all the modifications have been made (the row fetch page render process is off-screen, and shared components section not displayed)

Run the demo to see it in action. There are other techniques demonstrated on this page.

Check out this tutorial to extend this functionality by adding a read-only step.

Scott

Short Oracle Forms Survey (Whitehorses)

Still using Oracle Forms?

Why not participate in this quick Forms survey at Whitehorses.

It has 9 short questions and only takes a couple of minutes.

Get it done ;-)

Scott

Perth 2011 conference program available

Yep, this year's AUSOUG conference program is now available.

It was quite a challenge putting it together, and I thank those involved in helping out. It's not yet final, but gives a good indication of how strong it is this year. I count 3 Oracle Aces & 9 Ace Directors - plus a few quality speakers not (yet) recognised by that particular program.

The early bird special finishes at the end of September, so why not register early to see speakers such as:

• Local
• Connor McDonald
• Penny Cookson
• Chris Muir
• Mark Lancaster
• Yury Velikanov
• Gavin Soorma
• International
• Graham Wood
• Francisco Munoz Alvarez
• Debra Lilley
• Craig Shallahamer
• Tim Hall
• Ronald Bradford

Plus me, of course

All covering topics ranging through Fusion Middleware, Apex, ADF, EBS, DBA interests and even MySQL & Java.

Burswood is a fantastic venue, hope to see you there.

Scott

Oracle in pop culture

Twice this week I noticed Oracle mentioned in a web comic.

The first was Dilbert joking about the patent infringements amongst all the major companies at the moment - of which you could probably find a zillion info-graphics looking like this these days. I think Scott Adams had a flaw in the middle frame however - there is no way Google, Apple, Microsoft and Oracle would join forces. Oracle & Microsoft alone are justification for that - evidence in that the Windows version of SQL*Plus is no longer available from 11g, and SQL Developer is as portable as my Android phone.

What I did enjoy however was this from BonkersWorld.

Having just come from an Oracle conference, the comparison of more power for less cost seems spot on! Mind you, the other vendors seem well represented also. Looking through the archives, I found another suggesting Oracle will just sue you if necessary - I think perhaps they might buy you out!

Nothing quite so simple as this though, comparing computer updates for Apple, Windows & Mac.

My question for you is - have you seen Oracle mentioned in a web-comic or something similar and you think it's worth a giggle? Please comment below.

Scott.

APEX performance presentation

At the Insync conference in Sydney I experimented with a different presentation technique.

I've forgotten already where I stumbled across it - might have even been somewhere on twitter - but it's called Prezi. I did have a fear that I would instill motion sickness onto my audience, but after some initial feedback with a prototype I decided to go for it.

My topic was Oracle APEX Performance, and I originally presented it as a powerpoint slide deck at last year's AUSOUG conference in Perth (Nov 3-4 2011, btw).

So I updated and converted it to the Prezi format, which can be found here:
http://prezi.com/gfmw8q_gstcl/n-methods-to-improve-apex-performance/

A link to the PDF version of my powerpoint display can be found here. Yes, in writing that it does seem a little strange, I'm not sure why I didn't publish the PPTx version - not that there would be much difference.

I certainly learned a few things from the experience - things that worked and things that didn't. I have plenty of visual ideas for the topic I'm doing in November, to try and further embrace the prezi style instead of thinking "slides".

I don't think the technique will work for everyone. Connor certainly has a unique style, similar to Larry Lessig and further pioneered in IT by Dick Hardt. A great write up can be found here. If you've never seen Connor present, you must make it happen. He makes powerpoint his own and will rip through a zillion slides in less than one hour - and you'll come out very much entertained!

I don't think Connor could adapt his slick style to the prezi format, and I think many technical presentations would be difficult to translate. I found when converting my original presentation I had trouble "forgetting" the powerpoint way of thinking. I think when I do my next one, since it will be fresh I'll be able to plan the layout a little better and provide something more fresh & creative. I found myself jotting ideas down as I listened to people speak at Insync. It will be on APEX 4.1 security.

If you come along to our Perth conference you can experience it first hand! ;-)

Now, to start writing it...

Scott

Off topic: Friday fact

Being the fact it's Friday, I'm home sick, it's related to my interests and that this picture moved me - I thought I'd share it.

I'm a regular reader of Phil Plait's Bad Astronomy blog, and recently he posted this image from NASA's Juno spacecraft heading to Jupiter.

It's a picture of Earth and our moon from almost 10 million kilometers away. Now just for a moment, contemplate the sheer scale of what's being mentioned there - a tenth of the distance from Earth to the Sun, and this is all we see.

It also helps highlight just how far away the moon really is, and it's not even at an angle which reflects the true distance spanning the two.

As in Phil's blog, this reminded me of part of what I think is my favourite literary piece ever, from Carl Sagan's Pale Blue Dot.

"The earth is a very small stage in a vast cosmic arena. Think of the rivers of blood spilled by all those generals and emperors, so that in glory and triumph they can become the momentary masters of a fraction of a dot. Our planet is a lonely speck in the great enveloping cosmic dark. There is perhaps no better demonstration of the folly of human conceits than this distant image of our tiny world. To me, it underscores our responsibility to deal more kindly with one another, and to preserve and cherish the pale blue dot. The only home we've ever known"


He is of course referring to a different image, taken from 6 billion kilometers away - but it works just the same.


Have a great weekend :-)


Scott.

Free Oracle 11.2 database just released

Kris Rice has just announced the release of Oracle Express Edition 11.2.

This is a free product you can download, install & learn. A thorough description of the exact licencing can be found here.

It's very easy to install, just like installing any other software on your computer. I created a document with some step-by-steps to help get people learning started - including logging in with SQL Developer to establish some users, instead of using SYS.

Go, install, play, learn.

Scott