Basic Apex scripting - show/hide & labels

Sometimes the scripting requirements for an Oracle Apex page are fairly simple, yet determining the required modifications can be frustrating, particularly for a beginner in either Apex or HTML behaviours.

Recently I had the requirement of showing or hiding both the label and input item, depending on the selection within a select list.

So with this example, let's say my requirement is to hide the "Other Description" label and input item if "My select list" equals "- Select me -"
Otherwise, display the items and as an added bonus - set the label to the value of the selected list item.

First I need to modify the label so I have the ability to reference it in my JavaScript. So I edit my P1_OTHER_DESCRIPTION field and extend the HTML Table Cell Attributes in the Label section (not the Element section).

Here I added id="label_other"

Now I can define a script to do my work. It's highly likely this script will be used solely on this page so there's no real harm in adding it directly to the page attributes - unless you have a dedicated JavaScript file you add your scripting code.

So this would be added to the page footer text:

<script language="JavaScript" type="text/javascript">
<!--
function toggleOtherDesc(){
  $f_Hide_On_Value_Item('P1_MY_SELECTLIST',['P1_OTHER_DESCRIPTION','LABEL_OTHER'],'hide_desc');

  //I could also change my actual label, for instance to the value of my select list input item
  document.getElementById('LABEL_OTHER').innerText = $v('P1_MY_SELECTLIST'); 
}
toggleOtherDesc();
//-->
</script>

Some points to note here

• $f_Hide_On_Value_Item is a pre-defined function you can call to hide a list of items based on the value of another. Its behaviour is documented online here. In this case I listed both the label I created an id for plus, the item name - surrounded by square brackets as to define an array.
• 'hide_desc' is the value I've set in this case when the input description is '- Select me'
• .innerText allows me to set the descriptive label, as long as I supply the id I defined for the label, not the item name you see in the Apex builder.
• $v will return the value in the field, as it would be posted. This is also documented in the JavaScript API reference page.
• The highlighted line 9 means the function will be called when you open the page, in this case after it's rendered. That way the fields will be shown/hidden depending on the set value in the select list.

Lastly, I can modify the P1_MY_SELECTLIST item to call the JavaScript each time the selection changes.

So I add onChange="javascript:toggleOtherDesc();" to the HTML Form Element Attributes in the Element section.

So the final product will change the label to the selected value in the select list, or hide the label and input field if '- Select me -' is chosen.

There you have it!

Holiday season cometh, want to decorate your laptop?

It's almost a long weekend. Awesome!

Firstly, I'd like to offer a brief sentiment: some people around the world use this time to celebrate Christmas. I find it a somewhat uncomfortable time because I am one of few in my country, certainly in my circle of friends and family that do not. For those curious, recently someone else found almost the perfect words that fit my noggin. I guess I find solace in the fact that at least a third of the world's population don't celebrate it either.

However... for those of you that do, I have a great (albeit late) gift idea for you! They are called Laptop wraps, and here is a funky design example:

For those with small to large businesses, you may even consider it a great tax write-off while promoting your business. During this year's conference, we certainly had many people interested in having one designed to suit their brand. As I was going through the airport scanners recently, it even caught the favourable and approving eye of some of the security staff. Here is my laptop -

So if you want one (or many), my sister, Vicki, is in the process of populating her website full of various ideas along the same concept. It doesn't matter where in the world you live, you can order one of these; and/or ask her to help design one to suit your business or taste. So give it a visit: ArtWraps.com.au

They're pretty easy to apply, I used a ruler, some scissors, credit card, and a screen-cloth (which you can see in the photo)

I think you'll agree the final product looks a lot better than advertising for the company who built your chosen laptop. And for those consultants out there, what better way to promote your brand. I'm getting comments everywhere asking about it (and yes, sis, I'm letting them know about you ;-)

Maybe if you're a complete Oracle/Java fanboi, you could borrow a design from here ;-)

As a little extra plug, she has another website promoting her graphic design business, she's been doing all sorts of design activities and sign-writing for years - www.grovewesley.com

Anyway, I hope you all enjoy whatever it is you choose to do this time of year, and make sure you have a relaxing time doing it. Being around 35C here in the southern hemisphere, I won't be far from the beach or a pool.

Happy holidays!

Modifying Apex Tab behaviour

Today I was having a minor battle with tabs in Oracle Apex (3.2).

Sometimes you don't necessarily want it to submit the page when you click on a tab, so I was having a think about options to override the functionality.

I found an interesting discussion here on OTN, but I came up with something else involving On Demand processes. I'm still deciding whether it's an overkill for my situation, but I thought I'd post it as it may help someone else one day.

The solution involved :

1. Current tab page template
2. Script on page zero
3. On-demand page process

My original "Current Tab" in my page template looked like this:

<td class="t9tabCurrent">
<a href="#TAB_LINK#" class="t9tabCurrent">#TAB_LABEL#</a>
#TAB_INLINE_EDIT#
</td>

I modified it to:

<td class="t9tabCurrent">
<a href=javascript:tabNav("#TAB_LINK#") class="t9tabCurrent">#TAB_LABEL#</a>
#TAB_INLINE_EDIT#
</td>

This calls a script I defined on page zero, passing through the original #TAB_LINK# code.

<script language="JavaScript">
<!--
function tabNav(p_orig)
{
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=tab_target',$v('pFlowStepId'));
    get.addParam('x01',p_orig);
    vReturn = get.get();
    window.location = vReturn;
}
-->
</script>

The script will catch any click on the tab set. It takes the #TAB_LINK# substitution value and sends it to the on-demand process for processing. Then it re-directs the page to the resulting vReturn value.

My on demand process just encapsulates the code, so it's a call to a package I have defined. The g_x01 variable is the value I added in the AJAX call :
htp.p(my_pkg.tab_target(apex_application.g_x01));
This function definition simply replicates existing behaviour, but without the page submit.

FUNCTION tab_target
  (p_source  VARCHAR2)
  RETURN VARCHAR2 IS
-- Replicate tab functionality, but not as a submit
  lc_target  VARCHAR2(200);
  lc_delim   VARCHAR2(1) := q'[']';
  ln_session NUMBER := nv('SESSION');
  ln_app_id  NUMBER := nv('APP_ID');
  lc_debug   VARCHAR2(5) := v('DEBUG');
BEGIN
  SELECT 'f?p='||ln_app_id||':'||tab_page||':'||ln_session_id||'::'||lc_debug||':' -- essentially existing behaviour
  INTO  lc_target
  FROM  apex_application_tabs
  WHERE application_id = ln_app_id
  AND   tab_name = SUBSTR(p_source -- obtains T_HOME from -- javascript:doSubmit('T_HOME')
                         ,INSTR(p_source,lc_delim)+1
                         ,INSTR(p_source,lc_delim,1,2)-1-INSTR(p_source,lc_delim));

  RETURN lc_target;
END tab_target;

You could extend this to do whatever you like, depending on which tab has been pressed. I know in the past I've considered wanting to pass parameters on tab press...

Hopefully all the code is displayed ok, I'm having trouble finding an easy way to paste html examples into blogspot. I'm happy for any suggestions on that matter...

So, is anyone still working or reading blogs right now, or am I one of few? ;-)

The bar has been raised

One day, I'd like to see something like this at an Oracle event.



Sure, it might be a little difficult if it's live, but I'm sure the technology will get there. Perhaps we'll see more of this type of thing once our broadband network gets up to scratch.

On a side note, look out for circles that do wild things - Japan in the 40s (medium red dot), China in the 50s (big red dot), Rwanda in the 90s (little blue dot), Middle East in the late 90s (green dots)... sense a trend?

Apparently Hans Rosling has some good TED talks, I might have to look those up.

Spotted at Pharyngula 

My thoughts on presenting

When it comes to presenting on Oracle topics, I am by no means a professional, nor would I consider myself an experienced presenter like Penny Cookson or Connor McDonald - in fact the delivery he mustered at this year's conference was outstanding.

I currently have seven presentations under my belt with 13 deliveries. I've experimented with a few different styles in an attempt to find what works best for me.

That being said, I'd like to offer a few words & discoveries to those newer at this to me, or even those considering submitting a presentation for your local event, without sounding like this.

#1 - nobody wants you to fail

Given the feedback and support I received as a new presenter, I immediately got the feeling that people wanted me to succeed.
As an attendee to many a presentation at conferences, branch meetings and other events - I'm sure I share the feeling amongst others that you will the person behind the lectern to succeed regardless of troubles faced.
As a volunteer at AUSOUG conferences, I've overheard conversations and witnessed feedback provided even when a demonstration blue screens at the critical moment - people are on your side.

We are all human, we want our fellow species to succeed in that position that many fear over death.

#2 - write a presentation

It sounds a little silly, but keep an eye out for an idea then start writing one. Do it from the top down - get yourself three main objectives, an agenda, a list of issues to cover, some slide headings... it flows. You don't have to commit yourself to a particular date - although that does help some people. However, once you have one in the bag, more ideas come from a variety of sources.

After years of looking procrastinating, now I've always got a handful of topic ideas that I'd love the time to investigate and write. If you're struggling to think of anything, look through past conference programs or have a look here - Tom Kyte started a discussion on it.

Stick with topics that interest you, because ultimately you will know these topics very well and that knowledge will enhance your career, either through product knowledge for your job or prospective clients witnessing your skills. Once again, I think Seth pretty much nails it.

Once you have it in the back of your mind, it's amazing where topic ideas come from.

#3 - be prepared

I'm not sure I can emphasize enough the importance of this one. I think Connor did the same when he was providing advice before my first session. I said above that people are ultimately there for the content - and if that isn't up to scratch, what do you have left? Your charm and charisma? We does that get car salesmen?

So what does it mean to be prepared? Ultimately, I think it means you must know your content. You must be able to rationalise a question and construct a response based on your understanding of the fundamentals, not just regurgitate a crammed fact. We all learnt from this mistake in high school. Sometimes with bleeding edge content you won't know information, or if the question borders the scope of your presentation. Then you just need the confidence to say "I don't know", then carry on further to say it would be good to investigate, or offer a way to potentially test the issue. That comes from understanding.

Preparation should also relate to any demonstrations you may have. Rule 1 - always have representations of your demo in slide form, you never know what's going to happen on the day. If you repeat the presentation one day, it will also be a nice reminder of how things were meant to happen. You can also use it to post your presentation on the net.

It's also a good idea to ensure your demonstrations are repeatable. Just like your polished production-level scripts, it's a good idea to have a test case that will clean itself up and be able to execute it more than once. People may like to see that example shown again. You don't want flaky demonstrations because it's difficult to think on the fly if something goes wrong.

Have a trial run at home. Present your slides to your wall/pet/partner/colleague - whatever works. Do it in front of a mirror to see your mannerisms and see if you move too much. Make sure you're speaking fluently, know what words you want to use. Make sure the slides flow and make sense together. Be consistent and make most of the clicking change the actual slides and limit internal events, you may forget they exist.

Connor gave me a rule of thumb and suggested you know the content of the next 2-3 slides. I noticed in Chris Muir's recent effort he had little grey text in the bottom corner to prompt what the next slide was about.

At the end of the day, the audience will know the difference between someone who has put the effort in and understands their content vs a fraud who is stumbling their way over plagiarised content.

#4 - simplify content

What do you want to see in a presentation? Nitty gritty detail that will one day serve as a reference item, or a bunch of ideas that you can use to add to your own repository of creativity?

I have been trying to limit the amount of code in my developer-based presentations. Nobody coming out of the seminar will remember code detail, but they'll remember the key ideas or words that they can look up during their own research that they will have to do regardless so they can apply it to their scenario.

My latest presentation was a bunch of simple concepts with perhaps the odd coding example that provides a starting point to a solution. It would be difficult to create and convey a bunch of case studies to attendees if you're trying to offer a decent amount of information without causing confusion.

Limit ideas/concepts per slide.

#5 - beware scope creep

After a while I noticed that 40 minutes to get your presentation out turns out to be a short amount of time. When I was in school I would think that even 20 minutes for a report felt like an eternity. I now need to ensure I stay focused on certain goals and not introduce too much semi-relevant information.

Phil Winstanley concisely covers some key tips in his 24 slides on good presenting. Slide 18 on structuring your presentation shows what I agree is key to avoiding scope creep.

- Key message

- Define the problem

- Benefits of solving the problem

- Proposed solution

- Reinforce key message

- Conclude and close

Information can be quite evenly shared over these points as well - it doesn't need to be all about the proposed solution. Everyone also needs to understand how to identify the problem and what the benefits would be if we tackled it.

Having a key message at the start helps put everything into perspective. Provide the big picture before attempting to describe the detail. Think about how you may wish this information presented to yourself. Sometimes it makes me feel better knowing that I've given the audience key objectives to keep pondering.

Think different. Your key message doesn't need to be displayed as an agenda slide.

#6 - get a hand-held presenter

This is the one I have, but companies like Logitech are always releasing snazzy new ones. Having a presenter will give you the flexibility to walk away from your laptop (or other device that does the job these days). I think the presenting style demonstrated by Dick Hardt was damaged by being chained to the laptop.
Beyond the obvious advantage of having a laser pointer, these devices allow you to step forward & back through the slides when you are ready. Mine also has a setting that allows me to set a timer, and it will vibrate five minutes before the end - a good cue for winding up.
Just limit the movement of the laser dot - just imagine what your pet would do with it zipping around the screen everywhere!

Having freedom to walk away from the laptop may help settle your nerves.

#7 - identify 2-4 people around the room to alternate your attention

I find that if my eyes focus on just a handful of people as I speak and wander about, I limit the amount of distractions I face and stop myself from getting dizzy. I also find these people a gauge on how the talk is going - whether I need to up the pace or concentrate on certain slides.

Don't make it a hard & fast rule that it will always be people sitting in the four corners - you won't always have people sitting in these locations and you kinda want to avoid the habitual sleeper. I also tend to avoid those of a particular authority as they can add undesired & unwarranted pressure.

I also don't agree with trying to imagine people naked - I'm sure that's just an old wive's tale. I prefer to think I'm just talking amongst friends.

This will ultimately limit your movements and allow people to focus more on what you have to show & say.

#8 - body language

Your body speaks a thousand words. It is often said that if aliens came to observe us, they would think the majority of our communication is non-auditory. I think this site explains my thoughts well. I let my enthusiasm for my topic (as nerdy as some people say it makes me) steer the ship when it comes to my movements, and that also seems to assist audience enjoyment and satisfaction - if I'm coming across happy, it's hard to ignore that contagion. Trouble is, with that I always forget to pause - I'm still learning the best time to throw pauses in. I've seen good presenters do it at the perfect moments, I'm still working on that skill.

Speak with the confidence that you believe what you're saying.

#9 - they are there for the content

At the end of the day, if your slides aren't projecting well; if you have an accent foreign to the majority; or your demo just isn't playing ball - people want quality verbal/visual content. If at least one of these succeeds, you've probably made your mark.

The rest will probably come with practice and experience.

**

No doubt over the months & years these thoughts will change and evolve into something else, but it's where I stand right now.

At the end of the day one may ask, why present? For starters, as an example, it helped provide me the opportunity to work for Penny in a job that suits me perfectly. You never know the opportunities it might present. You also gain thorough knowledge of your chosen topic - a skill that won't go astray in your day-to-day job. So there's two-three good examples straight off the bat.

I hope I've provided at least something to think about, so you can go away an adapt to your own style and scenario. No doubt some of the suggestions here may not fit the type of information you need to get across. For me they've worked for semi-technical Oracle developer information.

I'll leave this with another site I might start sinking my teeth into:
http://speakingaboutpresenting.com/

ScottWe

Ubuntu, here I come...

For many many years, I've promised myself I would learn a flavour of Linux. After suffering some more Windows pain yesterday, a few contacts suggested Ubuntu, which has been on my short-list for a while.

I have my old laptop which has been a bit of a trusty work-horse (except for the frequent blue-screens), so it's now dual-booting into Ubuntu 10.10 - in fact I'm writing this post via Ubuntu!

First impressions - wow. While I can see the learning curve will be a little steep at times, it seems that releases such as Ubuntu have reached a level of maturity that will hopefully see a larger uptake from professionals around the world - particularly (I suspect) from University students whose brain will be like a sponge.

I may liken it a little to all the benefits of the simplicity of an iPhone, with the flexibility of an Android - let's hope that stays true.

I'm in the process of attempting to install Oracle 11gR2 with the following instructions (thanks Pythian). I've come across my first of many hurdles...

In the meantime, I'm downloading the Debian package for OracleXE, see if that's a little easier for my baby teeth to bite into.

I've registered for the Ubuntu forums, and will always welcome further advice.

ScottWe

SAGE 2010 AUSOUG Conference presentations available

To all those that requested or are interested in our presentations from the AUSOUG conference, all five are now available on the SAGE website - click on the New! 2010 Presentations link.

As for all other presentations, the AUSOUG committee are in the process of chasing down those we did not obtain and with any luck, should be available on the AUSOUG website before the next conference.

You may even find some on the websites of the respective presenters. Big names such as Steven Feuerstein and Tom Kyte always have their's available on-line. However for presentations such as Guy Harrison's keynote & Connor McDonald's 350+ slide masterpiece - you're never going to get the same value reading it as being there - their delivery was impeccable.

Truth be told, the WA state committee has gathered fresh and keen members this year, and we are pushing hard to make dramatic improvements to the design and functionality of the site. During the AGM, it was mentioned that around March 2011 should see the fruition of this change.

On top of website changes, we have received good feedback from the conference this year and we have plenty of changes and improvements in mind for next year to make it more exciting. I look forward to how many of these improvements we can fit in, and 2012 should be even better!

One of these changes is to ensure we get better attendance for the AGM, since we would like to better serve and provide for our members. This means we will try to schedule it so it's not the same time the drinks & nibblies come out ;-)

If you have something to contribute, or a super idea to suggest - you don't even have to until then, contact the committee today.

This should be my last user group related post this year, back to normal Oracle blogging now. If I don't see you at the end of year function (8th Dec @ Rosemount Bowl), I may see you at our first event next year which will be a breakfast in February, if I'm not mistaken.

ScottWe

Sage AUSOUG Conference photo

Our entire team in our snazzy new tops on day one of the conference.

Shame our camera man had an unsteady hand on a smart phone...

From left to right: Chris Muir, Kylie Payne, Scott Wesley (me), Kate Marshall, Penny Cookson (our illustrious leader), Branka Njegich, Eddie Harris, Ray Tindall.

Advert: SAGE Computing Services Training - Apex 4.0 Workshops

SAGE Computing Services are featuring two training workshops early next month, not long after the AUSOUG conference.

December 6th - Oracle Application Express 4.0 New Features Workshop
This course is designed to familiarize current Application Express developers with the exciting new features in Apex 4.0. Topics will include the changes in the interface, new client events, tabular form enhancements, web sheets and team development.

December 8th - 10th - Application Express 4.0 Workshop
If you're looking to get involved with Apex, this course provide you with the skills and knowledge required to develop a complete application with the latest release of Oracle’s Application Express 4.0 product. You will develop web interfaces including forms, reports and charts, add validation and customise the formatting to create a small application.

Visit our training web page for more information about our extensive course catalogue. Follow through to the contact us details page. We look forward to hearing from you soon.

Scott Wesley & the team at SAGE Computing Services.

I'm sure Penny, Eddie, Kate, Chris, Branka, Kylie & Ray would like you all to enjoy summer (or winter/wet/dry), and have a great new year!

Final days before the 2010 AUSOUG conference

This blog has been a little neglected as I've been a busy little bee recently, mainly fretting about getting my presentation completed for next week's Australian Oracle User Group Conference - AUSOUG 2020. I have plenty of ideas lined up for post topics, so I should be more active come the next few weeks.

As for the conference, SAGE Computing Services (my employer) will have an exhibition booth, so why don't you come by and say hello to our team. We will have a few things to give away plus some prizes to win - but make sure you bring your thinking caps because we're going to make you work for them!

I'll be joining the rest of the user group committee in Burswood on Sunday night just to make sure everything is good to go - including perhaps that I did the right thing setting up for a wide-screen projector. We should have a good turn out, although humbling in comparison to some of the overseas gigs, I hear.

Some of us are scheduled to present:

• Penny Cookson - Meet the CBO in Version 11g
  • 11:30am 22nd Nov 2010
• Scott Wesley - 'n' methods to improve Apex performance
  • 3:15pm 22nd Nov 2010
• Ray Tindall - Active Directory Integration - AD, WLS & ADF in Harmony
  • 9:45pm 23rd Nov 2010
• Chris Muir - A change is as good as a REST: JDeveloper 11g's REST web services
  • 11:00am 23rd Nov 2010
• Penny Cookson - A Path to the Future for Dinosaur Nerds: JDeveloper ADF - A Mind Map for Forms Developers
  • 3:45pm 23rd Nov 2010

The conference program can be subject to change, so check on the day to ensure you don't miss out!

So unless the schedule's changed on me, I'll probably be at the following sessions:

• Steven Feuerstein - Golden Rules for Developers
• Penny Cookson - Meet the CBO in Version 11g
• Howard Ong - Make your legacy application live forever
• Scott Wesley (won't want to miss this one!) - Apex Performance
• Jeff Kemp - Oracle Apex Themes and Templates
• (but sorry Jeff, I may find myself at Mogens Nørgaard - Oracle Licencing)
• Damien Bootsma - Database performance made easy
• Tom Kyte - The Best Way
• Stuart Long Keynote - Cloud Services Automation (CSA)
• Guy Harrison Keynote - Technology trends impacting Oracle professionals
• Connor McDonald - Managing optimiser statistics
• Frank Bommarito - Outlines, Profiles, SQL Plan Baselines

Yes, my selection is very much weighted towards Oracle performance this year. Nothing wrong with having a theme...

Please do come by our booth if you are also after training information, we train around Australia all year round and cover a wide range of Oracle topics. We can also customise the content to suit your organisation - talk to us to find out more. If you can make it to the conference, contact us on-line.

For you conference goers, if I don't see you anywhere amongst all of that, no doubt we'll all be with a beer at the welcome reception from 5pm (oh, I'll be at the AUSOUG AGM first - save me some beer)

Speakers, committee members, exhibitors et al. - listen out for details regarding dinner on Tuesday night, it's always a great night. I may be there... but it's my & Tracey's first wedding anniversary that day ;-)

ScottWe

Quoting inside literal strings

Well some of you may have noticed my slight boo-boo yesterday. I had made some notes on a blog post I planned to finish later, and accidentally published instead of just saving to continue later - so here's my second attempt ;-)

What I found funny, was the post was about exactly what Steven Feuerstein implemented for yesterdays (4/11/2010) PL/SQL Challenge - embedding quotes in literals.

I was reminded of it recently while searching for something - I can't remember what for, but I came across this forum entry: http://database.itags.org/oracle/81577/

I used what I thought an interesting method for adding single quotes to strings - interesting I suppose because I've never used this method before:
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=' || chr(39)||'DD.MM.YYYY'||chr(39);

The single row function CHR() will return the character equivalent to the numeric passed, in my case the ASCII character for single quotes. I the past I've mainly used CHR to provide my line feeds and carriage returns. (chr(10 & chr(13) respectively)

The author used this method because inserting a single quote in a normal string means you need to escape it, and this sometimes looks ugly or difficult to interpret:
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT='''DD.MM.YYYY''';

Oracle 10g introduced a method often called q-quote notation, further information can be found in the SQL Reference documentation here.

This format basically allows you to type the string as you would normally expect to see it, so the following example would display everything between the brackets [ ]
q'[ this isn't my string, is it? ]'

Which would mean our previous example would look like:
EXECUTE IMMEDIATE q'[ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY']';

Of course, as the documentation describes, you don't need to use those particular brackets. For a while I thought the brackets had to be curly { }, which was frustrating because it became annoying to type. Until recently, I wasn't aware you could go beyond any form of brackets and use any character - as long as it matched up. Lucky, considering yesterday's PL/SQL Challenge did just that!

I found it uncanny, since the list of examples I had lined up for this post was very similar to Steven's list - these will all produce the same result - Hello Scott's computer:

select 'Hello Scott''s computer' hello from dual; 

select 'Hello Scott'||chr(39)||'s computer' hello from dual; 

select q'[Hello Scott's computer]' hello from dual;
select q'{Hello Scott's computer}' hello from dual;
select q'<Hello Scott's computer>' hello from dual;
select q'(Hello Scott's computer)' hello from dual;
select q'"Hello Scott's computer"' hello from dual;
select q'sHello Scott's computers' hello from dual;

So I would suggest choose a combination of brackets that makes sense to you - probably not the last example since it's not that intuitive.

Someone asked me what the point of this particular notation was. I've found it very handy in the past for a number of reasons, but it probably still depends on what your brain is used to seeing (I'm still quite happy using DECODE over CASE, for instance)

For example, when it comes to replacing quotes with double quotes, an exercise I did for something long ago, I can either write it as this complicated list of quotes:
with mine as (select q'[Hello Scott's computer]' hello from dual)
select replace(hello, '''', '"') from mine;

Or make it a little more elegant. This is still probably a weak example, but I've seen a horrible list of quotes in my time that took a while for me to count and realise what was doing what.
with mine as (select q'[Hello Scott's computer]' hello from dual)
select replace(hello, q'[']', '"') from mine;

And then there is the case of large dynamic SQL. It truly is horrible to have to apply two single quotes every time a quote is required, particularly when you're conditionally concatenating certain clauses; and want to just copy and paste the query directly in your GUI to test without replacing all the quotes again.
Here is a basic example of how a small bit of dynamic SQL could become much more readable with the q-quote notation:

declare
  my_string varchar2(100);
begin
  execute immediate q'(select dummy from dual where dummy = 'X')'
  into my_string;
end;
/

I think this PL/SQL challenge has turned out to be a great boon for Oracle developers worldwide. You may be quite a proficient PL/SQL programmer, but not everyone has had the opportunity for exposure to all facets of the database, and people are reporting how wonderful it is to pick up these tips and tricks - in the arsenal ready for future use.

Attention Students! (AUSOUG plug)

Are you a student interested in where the Oracle industry is heading? Are you keen on meeting key figures in the Perth & Australian community? Do you want to be a few steps ahead of your fellow graduates?

These are the questions I remember thinking about as I was studying. There were a number of options out there, and sometimes it's hard to sift through the options to find those of value.

I think the Australian Oracle User Group (AUSOUG) is one of the valuable options, and this year the committee has organised an even better value deal.

The 2010 AUSOUG Perth Conference is just around the corner, and we've brokered a deal to benefit the grass roots of our industry - students!

For $150 tax deductible dollars, you receive membership to the user group AND registration for the two day conference. 

If you happen to be a member already, it's only $100. Membership is normally $65, and student conference registration is normally $320.

I'll let the user group brochure spruik it's benefits, and you can find a link to the conference brochure at the SAGE website - it's worth it, even just listening to the great presentations to be found in the program.

If you're doing a degree like Computer Science or Software Engineering, students of Edith Cowan University, Murdoch University, University of WA, Central Institute of Technology (TAFE), (and any others beyond the major players) contact membership@ausoug.org.au for enquires, or contact Ailsa, our WA President.

ScottWe.

SAGE Computing Services welcomes Kylie Payne

While I was in wonderful Kong Kong, the mugshot arrived and I now have the content to announce Kylie Payne has arrived on our SAGE team:

http://sagecomputing.com.au/about_sage_computing_services.html#kylie

I've already had the opportunity to work with Kylie on a few things and I enjoy her great attitude. We have a similar background - working on a few data integration projects; and now we look forward to the growing world of Oracle Application Express.

To meet Kylie come by our booth at November's AUSOUG conference.

See you there!

ScottWe

Compute the area of...

Riddle me this

Somewhere in the world there must be an exam or test that asks the question to compute the area of a given shape using PL/SQL. I know this because occasionally I look at the Google Analytics for my blog and see some pretty crazy google searches that arrive at my page because of my chosen blog name.

In just a few hours I take off for Hong Kong to take the kids to Disneyland and visit the city for a few days. I'm happy, so (while I don't condone the use of the Internet to solve all your problems) I thought I'd make a few other people happy, and make the visit to my site worthwhile :-)

I've used formulas according to Maths is Fun, and also demonstrated a few other SQL rounding functions you can find well documented here. That's right kids, documentation is your friend.

declare
  lc_pi constant number := 3.141592;
  
  -- triangle
  ln_t_base    number default 2;
  ln_t_height  number default 4;
  
  -- square
  ln_s_length  number default 5;
  
  -- circle
  ln_c_radius  number default 200;
  
  -- ellipse
  ln_e_width   number default 3;
  ln_e_height  number default 2;
  
  -- trapezoid / trapezium
  ln_z_a       number default 2;
  ln_z_b       number default 5;
  ln_z_height  number default 3;
  
  -- sector
  ln_r_radius  number default 4;
  ln_r_degrees number default 45;
  
  ln_area  number;

begin
  -- triangle
  ln_area := 0.5 * ln_t_base * ln_t_height;
  dbms_output.put_line('Triangle: '||ln_area);

  -- square
  ln_area := POWER(ln_s_length, 2);
  dbms_output.put_line('Square: '||ln_area);

  -- circle
  ln_area := lc_pi * POWER(ln_c_radius, 2);

  dbms_output.put_line('Circle: '||ROUND(ln_area, -2));

  -- ellipse
  ln_area := lc_pi * ln_e_width * ln_e_height;
  dbms_output.put_line('Ellipse: '||FLOOR(ln_area));

  -- trapezium
  ln_area := 0.5 * (ln_z_a + ln_z_b) * ln_z_height;
  dbms_output.put_line('Trapezoid: '||CEIL(ln_area));
  
  -- sector
  ln_area := 1/2 * ln_r_radius**2 * ln_r_degrees / 180 / lc_pi;

  dbms_output.put_line('Sector: '||ROUND(ln_area, 5));
  

end simple_calcs;
/  

Triangle: 4
Square: 25
Circle: 125700
Ellipse: 18
Trapezoid: 11
Sector: 6.28319

PL/SQL procedure successfully completed.

See you on the other side of Hong Kong!

Perth AUSOUG Conference Information 2010 (advert)

To all Oracle, E-Business Suite; J2EE; DBA; Enterprise Architects; Siebel Users; MIS Management; and Academia professionals interested in this years Australian Oracle User Group conference in Perth (and why wouldn't you be?), I've just published some information on our SAGE website.

Here is a page linking to an amazing brochure that details everything you need to know about the conference.

And here I detail times where you can catch our SAGE experts present; and also link to our past presentations.

Download the daily session program for more information.

There really is a great line-up this year, and as usual I look forward to catching up with local & international guests.

ScottWe.

Full outer join example

Recently I came across a scenario where I thought "say, a full outer join might hit the spot here"

It was a simple problem - we need to check that an old mapping table mapped all the old values to new values.

I figured doing a full outer join would assist making sure all values were accounted for.

Below I've cut down the actual output, but it highlights to me that the Yilgarn & Avon-Mortlock districts are not mapped to any new records, and there are number of new records that don't have a presence in the old tables.

SQL> l
  1  select distinct
  2    o.mapped_val
  3   ,o.old_district
  4   ,d.dst_name
  5   ,d.rgn_name
  6  from old_mapping_table o
  7  full outer join new_region_list d
  8  on d.dst_id = o.mapped_val
 
MAPPED_VAL        OLD_DISTRICT         DST_NAME             RGN_NAME
----------------- -------------------- -------------------- --------------------
               92 AVON-MORTLOCK
               93 YILGARN
               16 ALBANY               ALBANY               SOUTH COAST
               17 ESPERANCE            ESPERANCE            SOUTH COAST
               69 GERALDTON            GERALDTON            MIDWEST
               94 GREAT SOUTHERN       GREAT SOUTHERN       WHEATBELT
               14 KALGOORLIE           KALGOORLIE           GOLDFIELDS
               71 PERTH HILLS          PERTH HILLS          SWAN
               73 SHARK BAY            SHARK BAY            MIDWEST
                                       BUNBURY              SOUTH WEST
                                       GASCOYNE             MIDWEST
                                       GOLDFIELDS           GOLDFIELDS

Nothing more, just thought I'd share.

Off Topic - twttr

I've done it now, do you need to buy a vowel?

I'm not yet sure what my use of this will be like, but you can be assured there will be many personal posts as I think about issues that may be found at sites such as the JREF. So perhaps use that as a guide if you may be interested in following (I have set my profile to private at this stage)

No doubt if I my Oracle related network grows, my use may change. You never know, we may all even learn a few things along the way.

swesley_perth

West Aussies - have a great, sunny, grand final long weekend!

Another freebie from Oracle

On the back of my recent discussions on SQL Developer, at the start of the week Mike Hichwa announced on his blog that SQL Developer Data Modeler is now free!

This announcement was also confirmed by Sue Harper on OTN.

How there is no excuse not to model your applications :-)

More information on this product is available here.

note - us Aussies and probably a whole bunch of other countries spell it "Modeller" - good thing Google Search doesn't care.

JQuery Error Page Replacement

Yesterday I commented on using JQuery to replace the standard JavaScript dialog boxes.

Another simple solution to pretty-up your application is to replace the standard error display.

I'm not so much referring to making the messages user friendly, Martin Giffy D'Souza covered that very well recently, and you may have also seen Roel's example in the past.

So to really summarise their posts, define an On-Demand Application process called GetErrorMessage.

BEGIN
  -- this is probably the simplest example available - limited by imagination.
  htp.p('error title'||'#'||wwv_flow.g_x01);
END;

The following needs to be in the Error Page Template section of your page template.

<script type="text/javascript">
$(document).ready(function() {
  raiseErrorHandler();
}
);
</script>

And most of this you may choose to leave alone, except for the highlighted lines. I place it in my application's JavaScript file:

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 = '<div id="alert" title="'+pTitle+'">'
          +'<img src="/i/alert_error.gif">'+pText+'</div>';
  $(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);
              }}
   });
}

Then bang, you have a mildly more beautified application.

I scraped this example off the application I'm currently working on. We have not yet filtered the errors to display something more user-friendly - but you can read more Martin's post about that.

Basically instead of navigating to the default error page, you get this pop-up instead.

ScottWe

SQL Developer gripes - Part II

Last week I commented on some issues I have with SQL Developer, from which a number of people commented and e-mailed me about.

Stew in particular commented on his usage of PL/SQL Developer (aka PSD) and asked the question:

  why would you move away from using PL/SQL Developer?

So I thought it would be polite to respond with a fresh post.

I think the best way for me to respond to that question is to highlight the most pertinent part of that statement - "move away"

To add some more context:

• I learned Oracle using SQL*Plus. I like using it, I still do for some tasks.
• I started using PSD when we (myself and the fellow that sent the original e-mail that spurred this discussion) pushed the company we were working for to supply a GUI tool. We evaluated a few, and PSD fitted the bill. It served me well, and the company managed the licences.
• In mid 2005 I started with another company doing mainly Oracle Forms programming - so I was essentially forced to stop using PSD as I no longer had a licence, and I didn't really need a GUI at the new job.
• When SQL Developer was first released, I did download and use it occasionally, but since I learned Oracle using SQL*Plus and a fancy text editor, I managed to retain productivity. I have a suit of scripts that displays the same information I get out of the GUI tool and have them ready to fire in a nice output in SQL*Plus.
• Since joining Sage Computing Services, I've relied on SQL Developer even more-so for some positives I'll explain later.

So in essence I was forced to move away from PL/SQL developer mainly because of job changes. Had I still been working there, I may still be happily using it - unless of course they've done the cheap and gone to SQL Developer. (nope, I asked the question - they still use PSD)

If I had use of a PSD licence, I don't see any real reason why you'd purposely shift to SQL Developer, unless you are a real Oracle love-bird and want to use only their products.

However, for the position I currently find myself in, SQL Developer is great  (over others) because:

1. It's free - Licensing for PL/SQL Developer starts at US$180+60. I had trouble finding an exact figure for Toad, but "six times the price" seems like a common phrase.
2. Easy to "install" - if I find myself on a client's PC, I can drop the SQL Developer folder in with no admin rights, open it up and use it. There are other similar advantages your typical consultant might cite.
3. Forget TNSNAMES.ora - that little file has bugged me for a long time. Not having to worry about it has made my time easier and I can connect to any database I have the host, port & SID for - without having to worry about multiple copies of tnsnames.
4. It's evolving - Oracle haven't dropped a grenade and ran - they are continually updating, refining & extending SQL Developer. Good things come to those who wait. In fact, I heard a little whisper that improvements to the JRE will help improve the saskatchewan footprint over time. 
5. Did I mention it's free?

As for combining it's use with SQL*Plus

1. it's thin - half a second and you have a fresh connection to the DB
2. preparation is key - I have a login script that suits me nicely (for Windows versions prior to 11g) and a bunch of scripts that I can fire on demand to get the same info the GUI provides:
   SQL> @constraints my_table
   I even have scripts called "source" and "pksource" which quickly give me a nice view of compiled objects.
3. it works - without fail, every time.  

Obviously this is all my opinion subject to my personal working scenario. 

If anyone's interested, there are detailed reviews to other software tools for Developers and DBAs here at OraFAQ. There is a more succinct and colloquial comparison written at OTN here. And here is an old but interesting & relevant article (2007).

Oh, and Kris - if you're still listening - handling serveroutput was ok a few versions ago, but very frustrating in 2.x

It would be simply super if output to calls to dbms_output.put_line() became more predictable and was controlled by an icon/shortcut key/menu item. :-)

Using JQuery dialog boxes

I was having a discussion with a client recently about how many users ignore any pop-up messages that may appear. I've seen it many times, particularly in Forms applications, where the user just dismisses any pop-up before reading it while I stand behind them thinking "I'm pretty sure that message was important!"

I think the same can be said for JavaScript messages, which is why I'm really liking the JQuery dialog box. Instead of just being a message, you can use it to dim the entire browser canvas and the dialog box appears quite imposing. Check out the demo here.

I've been using JQuery for specific components, such as the Datepicker in Apex 3.x, so since my library is already included in my application, it's not much to replace the JavaScript messages. When you construct your library, ensure you include the Dialog widget.

The implementation of a JQuery dialog box also lends itself to customising the message content via PL/SQL quite easily.

Let's take the example of replacing the standard Delete question. First, define your message as a hidden DIV"

<div id="DELETE_MSG" title="Delete record"  style="display:none">
<p>
Are you sure you wish to delete this record?
(You may undelete in future)
</p>
</div>

This could quite easily be a PL/SQL canvas, or any other method to dynamically generate HTML of your choosing.

Then you define a normal javascript function that your button would call:

function delete_msg() {

   $('#DELETE_MSG').dialog('open');

}

At the same place you can define the actual dialog call. Review the JQuery documentation for all the implementation options

$(function(){
  // Dialog
  $('#DELETE_MSG').dialog({
    autoOpen: false,
    resizable:false,
    modal:true,
    width: 600,
    buttons: {
      "Delete": function() {
        $(this).dialog("close");
        doSubmit('DELETE');
      },
      "Cancel": function() {
        $(this).dialog("close");
      }
    }
  });
});

The highlighted line doSubmit() is where you essentially change the value of :REQUEST, which you would interrogate after page submission to decide what processes/branches to execute. Alternatively, you could replace it with whatever other JavaScript you wish to action.

The possibilities are endless :-)

 ScottWe

Trying to continue a programming meme

Do you ever find yourself thinking about something and 5 seconds later, you've arrived at a diametrically opposed thought and wonder how you got there?

I did that recently while surfing the net - I think it started when I found myself on a a site suggested by Google Reader's Explore function. It listed 5 most overused expressions on the Internet.

Sometimes I encounter internet memes that I've never heard of - maybe I'm turning into a fuddy-duddy, too esoteric in my usual surfing or just simply ignorant, I don't know; but I didn't realise "die in a fire" was such a fad. I heard it for the first time recently while watching 2 Months 2 Million, when some guy got burned in an on-line poker game and jested back with "hope you die in a grease fire". Charming.

So I googled it, as you do, to find out how much of a hit it is. Oddly, a handful of listings down I came across a programming blog.

Blogger Eric Florenzano was/is trying to start a programming meme (another word I'm almost surprised people haven't heard of).

His basic premise was to ask a simple programming question, and see how many languages the blogosphere tried to solve it with - so here's my contribution:

The Rules:

1. Implement a program that takes in a user's name and their age, and prints hello to them once for every year that they have been alive.
2. Post these rules, the source code for your solution, and the following list (with you included) on your blog.
3. Bonus points if you implement it in a language not yet seen on the following list!

The List (as I found it):

1. [Python] http://www.eflorenzano.com/blog/post/trying-start-programming-meme
2. [Bash] http://aartemenko.com/texts/bash-meme/
3. [C] http://dakrauth.com/media/site/text/hello.c
4. [Java] http://adoleo.com/blog/2008/nov/25/programming-meme/
5. [Python 3] http://mikewatkins.ca/2008/11/25/hello-meme/
6. [Ruby] http://stroky.l.googlepages.com/gem
7. [Ruby] http://im.camronflanders.com/archive/meme/
8. [Lisp] http://justinlilly.com/blog/2008/nov/25/back-on-the-horse/
9. [Lua] http://aartemenko.com/texts/lua-hello-meme/
10. [Functional Python] http://aartemenko.com/texts/python-functional-hello-meme/
11. [Erlang] http://surfacedepth.blogspot.com/2008/11/erics-programming-meme-in-erlang.html
12. [Haskell] http://jasonwalsh.us/meme.html
13. [PHP] http://fitzgeraldsteele.wordpress.com/2008/11/25/memeing-in-php-2/
14. [Javascript] http://www.taylanpince.com/blog/posts/responding-to-a-programming-meme/
15. [Single-File Django] http://www.pocketuniverse.ca/archive/2008/november/27/florenzano-factor/
16. [Oracle SQL] http://triangle-circle-square.blogspot.com/2010/09/trying-to-continue-programming-meme.html

And my solution (with some added flair)

accept my_name CHAR   prompt 'name   [] '
accept age     NUMBER prompt 'age    [] '

select 'Happy '||rownum||substr(to_char(to_date('201001'||(mod(rownum,10)+20),'yyyymmdd'),'ddth'),3)
       ||' birthday, &my_name' salutation
from dual connect by level <= &age
/

SQL> @grease
name   [] Scott Wesley
age    [] 31

SALUTATION
---------------------------------
Happy 1st birthday, Scott Wesley
Happy 2nd birthday, Scott Wesley
Happy 3rd birthday, Scott Wesley
Happy 4th birthday, Scott Wesley
Happy 5th birthday, Scott Wesley
Happy 6th birthday, Scott Wesley
Happy 7th birthday, Scott Wesley
Happy 8th birthday, Scott Wesley
Happy 9th birthday, Scott Wesley
Happy 10th birthday, Scott Wesley
Happy 11st birthday, Scott Wesley
Happy 12nd birthday, Scott Wesley
Happy 13rd birthday, Scott Wesley
Happy 14th birthday, Scott Wesley
Happy 15th birthday, Scott Wesley
Happy 16th birthday, Scott Wesley
Happy 17th birthday, Scott Wesley
Happy 18th birthday, Scott Wesley
Happy 19th birthday, Scott Wesley
Happy 20th birthday, Scott Wesley
Happy 21st birthday, Scott Wesley
Happy 22nd birthday, Scott Wesley
Happy 23rd birthday, Scott Wesley
Happy 24th birthday, Scott Wesley
Happy 25th birthday, Scott Wesley
Happy 26th birthday, Scott Wesley
Happy 27th birthday, Scott Wesley
Happy 28th birthday, Scott Wesley
Happy 29th birthday, Scott Wesley
Happy 30th birthday, Scott Wesley
Happy 31st birthday, Scott Wesley

31 rows selected.

To get the format in context (st | nd | th), I had to do some manipulation with rownum. I've used the first solution I came to that worked, I'd be interested to know if anyone has any normalised solutions.

ScottWe

SQL Developer gripes

When Project Raptor finally came to fruition as a download in mid 2007 as SQL Developer 1.2, I was excited at the prospect of having a free GUI that I could take with me to various clients, without worrying about licensing.

I was using PL/SQL Developer at the time which was great for the PL/SQL development I was doing. I still use SQL*Plus fairly regularly as a lightweight tool, put as OraFAQ suggests, SQL Developer was intended as a complimentary tool.

I've heard a few comments recently saying how flakey and unreliable SQL Developer is. After further questioning, it appears they are referring to the 1.2 version which I will admit, was obviously an entry-level product. v1.5 was the better stable release before 2.x came out.

I'm currently using the most recent patched release (2.1.1.64), and I'm fairly happy although I still have a few gripes.

What made me think of this was an e-mail from a colleague now working in the UK, here are my extended responses:

I started a new contract this week and have been trying to use SQL Developer again, but it doesn't always do things I expect. They are using some cross database tool called DBArtisan (Sybase background) and I'm not impressed with it. 

It's always good to get feedback on tools from other databases - interesting.

So far there are 2 things I haven't worked out. The first is multi-session/threads, is this possible?

Most of the time I work with one connection for each schema/database that I happen to be working on. This means that any DML is reflected in all views of that information - on the worksheet, the table data view etc.

If you define a new named connection for the same schema, then you have started a separate session that will not see your uncommitted DML.

The second is that there code base is not exactly formatted very nice so I wanted to try auto-format it. I end up with line breaks where i don't expect, not enough white space and it doesn't seem to allow you to set the case on anything other than keywords, rather frustrating.

I've been thinking about this, and I think it just boils down to personal style. An evolving formatting tool couldn't possibly cater for everybody, and I know our styles are similar. I remember the formatter for PL/SQL Developer & Toad being very effective - but they're more mature products. That being said, I don't really use auto-formatters. In fact, I still do most of my PL/SQL development using TextPad. I use SQL Developer mainly for ad hoc SQL queries and looking at table definitions.

Any advice you can give me on how I might achieve this in SQL Developer. I will admit that what it does with statements when you are grouping by adding to the group by clause is pretty cool, it surprised me today when it appeared in something I was writing

This is what I told him about that one: As for completion insight - I have all those options turned off, first preferences I change on a new pc, that and line numbers in the gutter & NLS date parameter to include century.

There are a couple of other comments I let fly in my reply, things I felt compelled to address.

Compiling PL/SQL

Error reporting is a real pain in the behind. If you're just tweaking things, yeah, it works - but iterative development, I still compile my PL/SQL in SQL*Plus - my edits direct from Textpad. The error reporting is much clearer.
Unless I'm using it wrong, I don't find compiling code in SQL Developer helpful at all.

It's JRE based

It just makes things slower, introduces lag which grinds my gears. Now I may be pinning the tail on the wrong donkey, but all the other non JRE products I use don't have these issues. That, and it consistently leaves a 500mb footprint in my laptop's memory. However I know this will never change - tweaks in its guts may improve efficiency, but it will always be JRE based. I will say a clear advantage here is you don't need to "install" it, copy the files onto your OS and you're ready to go. Super!

Best I conclude my rant for now. Did I miss anything?

ScottWe

ps - commendations to Sue Harper, Kris Rice and the rest of the SQL Developer team - the product has come a long way in the past half-decade. People always have something to complain about :-)

APEX IR CSS tweaks

Recently I had a request to top align all the data within an APEX 3.2 interactive report.

There were some data in the report that made other information appear out of place. Consider the sample data below on demo_customers table. The address field is subtly pushing the other fields down. Combine this with an Edit record image and it starts to look silly.

Normally you can hop into the templates of your application and throw in the occasional tweak to make these adjustments, but I found nothing for an IR.

After a quick search, I found the solution here by Matt Nolan - who ironically enough commented on my blog recently.

My task wasn't as in depth, so I simply used the vertical align style and inserted it in my application's custom CSS file. Alternatively, I could have inserted it into my page footer, for instance.

<style type="text/css"> 
  table.apexir_WORKSHEET_DATA td {
    vertical-align:text-top; 
} </style> 

This adjusted my report to appear just how my customer wanted, and because I put it in my application's CSS file, it applied to all interactive reports.

ScottWe.

Apex Post Calculation Computations

A warning - perhaps my musings are different as I wrote this sitting in Melbourne airport, my mind exhausted after a few days of conducting training with not much sleep in the hotel bed.

Once upon a time I was a regular Oracle Forms programmer (and sometimes still current). These days I spend most of my time with Application Express. This makes me happy as I did enjoy mod_plsql - an ancestor (of sorts) of Apex.

Occasionally I notice some parallels between the two, even more occasionally I get around to writing an entry for the world to see - a strange urge for some but it seems that people read even more mundane topics.

There are many attributes available within the Apex environment. By attributes I mean little boxes in the various wizards ready for my to type something in. Sometimes it seems overwhelming. Then I remind myself how flooded with settings the Forms environment must seem. Of course I snap myself back to normal when I think about what I've seen of JDeveloper.

Have you ever wondered what some of these settings do?

Recently I was creating a copy of a data entry form within Apex so I could present a cut-down / read-only version of the page. There were some fields that instead of being Select Lists, I needed to display their descriptive value - not the return value that is stored in the column.

There are a number of solutions to this problem, as with most problems. One solution I came to involved utilising the "Post Calculation Computation" attribute of the item. This means that after I source the item from the database column, I can transform it's value into something else. The obvious solution here would be to pass the value to a function that determines the descriptive form of the value - from some sort of reference code table.
my_pkg.get_label(:P1_VALUE)

I mentioned forms programming before, right? Immediately I thought of post-query triggers and the pros and cons behind various coding techniques in these triggers. First and foremost was the very same practice of taking a value and converting it to a description. This was an expensive task as not only did it require an extra hit on the database, you needed another round trip from Forms runtime to the application server. The better solution was to incorporate the request within the query - perhaps via a key-preserved view.

The same rings true within Application express. Sure, we don't have another round trip between servers since all the action is happening on the database; however it still requires another select statement to be executed. For a dinky (a Aussie/British colloquialism meaning small and insignificant) little Apex page, what's an extra hit on the ever powerful Oracle database? Perhaps try see what happens when scaling your request to thousands of users.

So perhaps some of our old habits can carry on to this modern day programming tool? I'm certainly not saying this post calculation attribute is not useful. I have another field populated via a list manager with a popup lov. This means the values are separated by a colon. In my application, this field holds a list of e-mail addresses. When I want to present this list to the user in a pretty format, I can use this attribute to convert it to something suitable for a HTML page:
REPLACE(:P1_EMAIL_LIST, ':', '<br>')

Of course if you wish to do this, you may need to ensure your item type does not convert special characters.

It seems my plane is about to call for boarding, so I'll save you all from further ramblings... for now. Enjoy your weekend.

Oracle's Broken Links

It seems that like me, people like Tim Hall are finding some of the changes regarding the amalgamation of sites within Oracle a little frustrating.

I've noticed many of the comments within OTN contain links to pages that just aren't there any more.

This OTN entry here mentions a vital link to
http://www.oracle.com/technology/products/database/application_express/howtos/how_to_create_custom_popups.html
which unfortunately redirects to the Oracle Apex overview page
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html

In an effort to find the contents of this page, I used my friend Google. I took a key part of the URL and used a special search facility that Google has:
inurl:how_to_create_custom_popups

This gave me three results

1. The original document, which still redirects to the overview page. However, if you click the cached link next the described URL, you can open Google's cached version of the page - which should usually suffice.
2. Some Xmarks widget, which didn't help me - but I do recommend Xmarks, by the way.
3. A Japanese hosted version of this page. Google offered to translate it for me, which worked very well. This also solved my problem.

So until Oracle gets up to speed with all the older links, perhaps give the inurl: search option a go.

ScottWE

ps - congrats to Tim for his recent induction into the OakTable Network.

Insync 2010 - I wasn't there.

Hello everyone

I'm starting to see a bit of feedback from the Insync Conference 2010 in Melbourne Australia.

It seems Down Under we're still working on creating the conference vibe, but I'm impressed to find a dedicated (yet thin) blog for the Melbourne leg. Here I found interviews with Connor McDonald & Tom Kyte.

http://insync-conference.com.au/blog/?p=211

I'll note that video of Connor confirms that I'm not the only one that seems to associate alcohol consumption with Oracle. I noted in the latest AUSOUG Foresight Magazine, Marc Lancaster gave a warm review of my appearance at a Brisbane branch meeting - apparently I commented how wonderful the beer & pizza concept is, or something along those lines!

Congratulations to Jeff Kemp, who took out Steven's live PL/SQL Challenge.
http://feuerthoughts.blogspot.com/2010/08/plsql-challenge-live-event-in-melbourne.html
I was looking forward to the live challenge this Friday when Steven comes to Perth, but alas - I have a previous date with our wonderful coastal isle - Rottnest.

Next time, Kempy... next time ;-)

I do look forward to catching up with friends this year at the Perth's leg of the conference series in November, perhaps I'll see you there.