Thursday 26 May 2016

APEX Survey Results: What development resources?

The next question in my 2015 survey was a high level look at what resources developers use to get through your day.

On a slightly side note, there was an interesting discussion on the science of preferred vs effective learning styles in this podcast. It reminded me of my scuba diving course where we learnt the content using 5 different methods, which was a great way to ensure everyone understood how to survive in a pressure environment (pun intended).

Q3: What resources do you use to aid development

Personally I find the OTN forums great for most things APEX or database related. StackOverflow is great for JavaScript/jQuery/CSS questions, and most have already been asked for you. The forum format there is superb. Don't forget the revitalised AskTom.

It's great that some people have the opportunity to learn from colleagues. I've had some great mentors in the past that really helped my career. Blogs can be a moderate second, with APEX content aggregated at

Documentation is a great source of truth, and I recommend four places. Sense a trend?

Some of the "Other" responses included Twitter, which is a great tool for picking up information coming out of conferences you can't attend, tech information in general, and amusing parody accounts. Slack is also worth a go.

One interesting response was "Package Specs, Views, Application Code". I often find the package specifications of Oracle Supplied Packages full of useful information not necessarily found in the documentation. I recommend you start with APEX_APPLICATION and APEX_APPLICATION_GLOBAL.
As for views, I've learned a thing or two an how APEX ticks by looking at the SQL for dictionary views, which also explains why many don't return any records when you aren't within an APEX application.
I'm curious what was meant by application code, perhaps the same as another response that said "Packaged Applications". These applications improve every release and are a great demonstrator of APEX capability. Check them out for application design ideas, and if you want to know how something was done, install (and unlock) the application and look under the hood. This is where I learned how to display images in interactive reports.

Webinars was another suggestion, which I guess is a subset of Videos. I recommend paying for membership at ODTUG where they have a brilliant library of webinars, of which I have many yet to catch up on.
These days videos are quite diverse, from quick and simple youtube demos like Connor McDonald's KISS series on analytics; to published screencasts like I did once.

Someone even suggested "Connor McDonald", though I think there was some bias in that one.

My final recommendation would be regardless of preferred source, diversify your content. Don't just read Oracle APEX stuff, but subscribe/buy/view content for related technologies, and why not the competition?

Wednesday 25 May 2016

More on CSS selector performance in Oracle APEX

Last month I wrote a post about CSS performance, including some performance test results.

I recently encountered this brilliant post on Medium that describes some best practices for CSS.

While APEX does a lot of this for you, I think it's worth a read by all developers. Even applying basic naming conventions can make code easier to read and understand.

There was also a section on performance that described an issue that my previous example didn't explore, and that's 'tree walking'. Each web page could be likened to a giant tree, and your CPU will like you if you can minimise the amount of traversing necessary to verify the component being sought.

So for APEX developers, consider the selector reading right to left, and try keep it to two steps.

For instance, to identify a button within a classic report, you would define a static region ID for the report, and use the following selector
#p1_region a.t-Button

This would look for anchors with the standard Universal Theme button class, but only if it exists within the classic report region. Concise & effective.

You could use this selector to apply further CSS attributes to the buttons, perhaps via the Inline CSS page attribute.
#p1_region a.t-Button { font-weight : bold; }

Or you could use it as a jQuery selector for an on-click Dynamic Action, responding to the button press. If you add the following as link attributes to the column with the link button. It adds an attribute to the anchor with information from a column aliased as season in your SQL.
Note, to render a column link as a button, this attribute would also contain this class listing, to look like the nearby image.
class="t-Button t-Button--warning t-Button--simple t-Button--large t-Button--stretch"

You can refer to this information within a JavaScript action to set a hidden page item with the data from the season column.
$s('P1_SEASON', $(this.triggeringElement).data('season'));

A subsequent PL/SQL action could then submit this value to session state and execute something related on the database, without submitting the page on click of the report row button.

While that has side tracked from selector performance, it shows how a simple selector can be used within APEX to trigger events. A pattern I use regularly, done with minimum effort.

If you like this post, you may like my book /plug

Small tip: Align button in UT

Ever had this problem where your button is offset to page items?

Item / Button misalignment

While the Universal Theme is awesome, it hasn't quite got everything right. I look forward to trying the updated version of the theme in our existing applications.

APEX forum legend fac586 (a.k.a. Paul MacMillan) provided a simple solution here.

Add t-Form-inputContainer to the Column CSS Classes property for the button.

Little tips like this can be acquired just by occasionally checking out the recent responses in the forums, perhaps opening anything of interest that might pertain to your work.

I think this tip should be suffixed with this funny

Modified from the xkcd original

Tuesday 10 May 2016

Difference between SQL Injection and Cross Site Scripting

I came across a tweet from a non-Oracle person I follow that should amuse many web developers:
One of the replies referred to "little bobby tables", eluding to a classic xkcd comic about SQL injection.

Of course I had to make the correction that this was in fact Cross Site Scripting (XSS), not SQL injection. This post summarises syntactical considerations within APEX, but in the spirit of #entrylevel content I'd like to summarise the conceptual difference. It's a little like flogging a dead horse, but security is everyone's concern so it won't hurt to mention it again!

Both concepts are about information being entered by a user within an application to do something nefarious, but they differ in where this malicious activity ends up happening.

SQL Injection is about information being entered in the browser, such as within a search field, which would impact the query being executed on the database. This could modify the statement to produces results you wouldn't normally have access to see, or even drop a table.

Don't try this on the road (Gizmodo)

Using bind variables instead of building dynamic queries usually mitigates SQL injection, but it doesn't stop bored programmers from finding susceptible examples in the wild.

Cross Site Scripting is also about information being entered in the browser, but instead of the malicious code impacting the database, it affects a user's machine when the information is redisplayed. Someone could enter JavaScript in a text area that when redisplayed as data, the code could actually execute.

It's never hard to find an example of XSS in the news, and it requires a little more proactive coding to minimise flaws.

Oracle APEX does a good job of 'escaping' information displayed in reports by default, so any code gets rendered as it looks, instead of being treated as if it's code. You may have already used the "Escape Special Characters" toggle to allow the use of the apex_item API within a report, but this should normally be used in conjunction with the apex_escape package. Another alternative is to use the HTML Expression attribute to construct your final code for output.

In the end this pizza store wins, since the person's "name" was rendered as entered instead of displaying an alert.

But they lose points for spelling "piza"

Friday 6 May 2016

APEX Survey Results: Which versions have you experienced?

Following up from the results on when you started learning APEX, here is question 2 from my 2015 survey.

Note that questions such as this offered the respondent to choose multiple selections, hence a count much higher than 192.

Q2: Which versions have you experienced?

I think it's fair to say that most of the 2.x respondents might also be part of the third that said they started prior to 2007.

I started in 3.x, and still have a client to this day on that version, despite the fact it's no longer supported.

No doubt if we asked the question today, the responses for 5.0 would be far higher. And we could almost ask about 5.1 and the goodies it will bring.

On reflection, I don't miss 4.x. Page Designer in 5.0 has been fantastically more productive. I also think the Universal Theme is a great idea that I haven't sunk my teeth right into yet. The upgrade to 5.1 should really put it's new life cycle to the test. I think this post will come in handy.

Thursday 5 May 2016

Puzzle: What's my birthday?

I was lucky enough to attend a 'Let's Talk Oracle' session from Canberran Richard Foote today diving into AWR.

After an early dad joke and before we got into his AWR collection, we gave this brain teaser.

This hurt many heads, including mine, and I also picked the wrong answer. Richard reports our group did well though, compared to other cities. Nice work to the Twitter peeps that also gave this a go.

My very first pass got me to July 18, but let the brain simmer a little further and you realise the rabbit hole is longer than expected.

Then I arrived at July 16 because I wasn't scribbling anywhere and my brain obviously couldn't plan enough moves ahead. The answer slapped me in the face soon after Richard crossed out May. I eliminated 18 and 19 and thought there was a nice looking date sitting by itself.

I knew a friend would eat this up and he vehemently concluded the answer was Aug 16, so I just had to break it down so I could send him my working.

Spoiler alert: the true answer appears at the end of this post.


Bowie and Ziggy see this list

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

Bowie told Month
Ziggy told Day

Fair presumption: Each other knows they only know a portion, because Bowie states Ziggy doesn't know both pieces of information.

If May, there is potential Ziggy knows unique number, so Bowie couldn't be certain Ziggy doesn't know.
If June, days are non-unique, so Bowies knows Ziggy couldn't be certain either way.
If July, same possibility as May
If August, same uncertainty as June.

So from Bowie's perspective, we are left with these possibilities, those months with unique numbers

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

Ziggy states at first he didn't know, so unique numbers can be eliminated.

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

All other days have duplicate, so Ziggy still uncertain of month.
Now Ziggy knows Bowie was uncertain with just month, Ziggy shares that knowledge and eliminates same months.

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

It's down to three uniques, because Ziggy states he now knows, and 14 is not unique.

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

Now Bowie says he also knows. He still can't tell between the two August days

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

June 13 must be the answer.

Richard's explanation

When the question went through the media rounds


Wednesday 4 May 2016

APEX Page Numbering

Today while perusing posts in the #orclapex hashtag, I came across this from Christina Moore.
She hasn't been blogging long but I love her style and format. I found this one on classic report templates a great resource, though I wish the Oracle team would produce content like this to accompany the documentation, or as an extension to

Anyway, back to the tweet. The post she links is about planning page numbers. It had me intrigued because I do like sourcing menus with SQL direct from the APEX dictionary. My long standing top post includes a SQL pattern I use regularly to populate APEX menus.

With respect to Christina, I would like to nitpick with some points, or at least offer my perspective.

Planning page numbers

I consider myself to be a page number pedant, and why not? I'm sure we all named our Oracle Forms .fmb files in such a way to have a useful order in the folder, even though a search facility was available.

Why not do the same for APEX page numbers? I like to group my generic popups together in the lower end. Admin pages surrounding 101. Then segments of the application are grouped in 20s, 30s, 40s etc. A report/form combination is handy together since the next/previous page buttons are useful when jumping between the two, as Christina also describes.

I've even found use across applications where pages with similar functions share the same number, making it even easier to jump to the page I need. Christina mentions this when copying pages. Consistency is always a winner.

Page Grouping

Unless I've been really serious about page segmentation, I couldn't see myself applying a condition with a bounded range of page numbers such as
:APP_PAGE_ID between 100 and 200
I find this akin to hardcoding, something Steven Feuerstein could rabbit on about for days. I typically use Page Groups for times I need to identify a set of pages, in part because you're essentially documenting the pages by categorising them. Inherent, inline documentation that can be found by the next developer.

Using page groups also lends itself to building menus out of SQL, potentially in a hierarchical manner.


I also find the use of sys.dual superfluous in cases like this, to a point where it's like rubbing hair the wrong way.  All that's required is a simple PL/SQL expression (as shown above), and declarative if possible.

The Alternative

Instead of defining a process to number your pages, you could just let APEX find the next number for you. For playpen applications or those under a hundred pages, that's probably fine. Even then there will come a day you regret not forming some assemblance in your pages.

(Bert Myers)
Organisation is only human. Such abstraction has enabled our species to plan crops, track livestock, leap ahead in our understanding of the universe. Heck, nature has been doing it for over 500 million years.

... too profound?