Category Archives: Oracle

Views and Order By

Developers like views…they can simplify things and hide (protect us from) underlying complexity.

For example, would you rather write the query –



  emp e, 
  dept d 
  e.deptno = d.deptno;

It’s a rhetorical question so please don’t say “I prefer the second”…otherwise there’s no point me continuing this post. Anyway, that’s quite a simple example, imagine the view hides all sorts of complexity (analytics, subquerys etc).

The view gives us a single ‘point of truth’, we can tell developers “use that view, it contains all the information you need, you don’t need to understand all the logic yourself”, views are a very powerful tool for code re-usability (and simplification).

However, what if someone codes a view like this –

  emp e, 
  dept d 
  e.deptno = d.deptno
ORDER BY d.dname;

Notice the order by there? They probably/possibly created that view to use directly in a report, adding the ORDER BY into the view so they could just write –

SELECT * FROM empdept_vw

rather than

SELECT * FROM empdept_vw edv ORDER BY edv.dname

Well that’s fine, but what is someone else later on uses that view in this way –

SELECT * from empdept_vw edv ORDER BY edv.sal DESC

Well we’re now potentially making the database do more work because the query is sorting the data in a different way to the view sorts it (so therefore the sorting in the view is essentially redundant). The key point here is that the database cost based optimizer might be smart enough to figure out what you’re doing and eliminate the redundant sort, however there might be lots of cases where that doesn’t happen (consider the scenario where you have views built on top of views, with ORDER BY’s thrown in there at a deep level).

The cost-based optimizer gets smarter with every release of the Oracle datbase, however it will never (and I’m reasonably confident about this!) understand your data and requirements as much as you do (or rather as much as you should).

So, I would argue anytime you see a view with an ORDER BY in it you should really question why you’re doing that (rather than putting the ORDER BY in the query that uses the view). We can even make our job easier and look for any views that have an ORDER BY defined in them by querying DBA_VIEWS or USER_VIEWS. Unfortunately those views contain the text definition of the view in a LONG column rather than a CLOB, which means we can’t do a simple LIKE predicate –

JES@dbtest> select * from user_views where upper(text) like '%ORDER BY%'; 
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

We can get round this in a number of ways (such as creating a copy of the DBA_VIEWS or USER_VIEWS in a local table converting the LONG column to a CLOB etc), or we can use a different approach like this –

JES@dbtest> select 
  dbms_xmlgen.getxml('select text from user_views where view_name = ''' || view_name || '''') like '%ORDER BY%' 

(note unfortunately I can’t remember who to credit with the DBMS_XMLGEN trick above, I saw it a long time ago and it’s been lost in the mists of time).

and yes…before you ask…in the schema I ran that query in I did find a view that had an unnecessary ORDER BY in 😉

I’d be interested to hear your thoughts on if you find ORDER BY’s in views a problem/issue/area-of-concern, or if it’s just me?

Certification Exam for Oracle Application Express

Joel Kallman just posted that Oracle are in the process of creating a certification exam for Oracle Application Express, this is a very interesting development (and something that we in the community have discussed for a few years now).

Whilst I’m still on the fence about the whole ‘certification thing’, I do see this as another commitment by Oracle to make Application Express a serious contender amongst some of the more publicised (shall we say) development tool offerings they have.

You can get directly to the pilot page here (but I advise reading Joel’s blog first).

Pro Oracle Application Express

Well it’s been a long time in the making, but the Pro Oracle Application Express book that I wrote (with a chapter on Themes and Templates contributed by Scott Spendolini) is finally shipping!

I started working on the book way back and it seems barely a week has gone by since when I haven’t been asked the question (quite rightly!) “when is the book coming out?”.

This is the first technical book (or any book for that matter) that I have written, so the whole thing was very much a learning experience for me, although obviously I like to think I know about the content side of it, the logistics of writing the chapters, the review process, the amount of time it consumes was all very new.

When I agreed to write it, I contacted a few people who have already published Oracle technical books just to get some opinions on what I’d just agreed to. The comments all had a very similar theme of “Prepare to put your social life on hold because writing a book takes a lot longer than you imagine”.

I really did enjoy writing the book, but I totally agree with how much time it can take, there is nothing quite looking at a blank screen for an hour, or spending three hours reworking a single page and ending up with what you had original, or the joy of laying out at example only to find that you want to completely change it and have to start again from scratch.

Now, those who know me know I’m not a very ego-driven person, however, I have to say that it did raise goose-bumps actually seeing the book selling on Amazon.

Pro Oracle Application Express

Even weirder was seeing where the book was positioned in Amazon’s sales figures.

Pro Oracle Application Express

Fleetingly, the book was in the top 1000 books being sold on Amazon and was the number #1 seller in the Databases section (and the Oracle section). Of course the cynic in me would say that’s partly due to all the back-orders being satisfied from it being late, but it’s still nice to see that people are actually buying it.

I certainly don’t expect to please everyone with the book, since I was limited in terms of pages what I could write, I already went way over the original 400 page estimate (to over 700 pages) and could have easily carried on for another 500 pages or so. Also the way I’ve structured the book is to cover a different area of development in each chapter, things that are typical to encounter when designing production applications in Application Express, so there are certainly topics that I don’t talk about since I needed to talk about other areas in much more detail.

I certainly expect a lot of emails along the lines of “Why doesn’t it contain XYZ?”. Well the answer to that is that it is not intended to be a complete guide of everything you can do in Apex, more an in-depth guide on certain key areas.

So that’s that one done…is it time to start thinking about another one? Hmm maybe after a short “rest” 😉

OTN Forums – Change the look and feel yourself

The recent OTN Forums update has been heavily blogged/Twittered/posted about already, so I’m not even going to touch that one.

However, one thing that a few people have focused on (pardon the pun) is that the update has changed the way that viewed threads are displayed in a very light grey colour, making them a bit hard to read, as shown in this screenshot.


One option is obviously to petition the folks at OTN to try and get the look and feel changed (personally I don’t find the grey colour too bad, but I can see how it could be a problem to some people). However, obviously the OTN folks are never going to be able to please everyone at the same time.

Fortunately there is a way to change the look and feel yourself, not only of the OTN site, but of any website. Now I’m not going to get into ‘Browser Wars’ here, but if you use Firefox it is pretty simple, since you can use tools like Stylish and Greasemonkey to custom sites. If you use IE then there are other plugins around you can use.

I use Stylish (actually I use Stylish *and* Greasemonkey but that’s another story) and once you have installed the plugin you can create new styles for any website you visit. For example if I create a new style for the OTN forums like this:

@-moz-document url-prefix( {
a:visited {
color:#00007F !important;
text-decoration:underline !important;

Then the next time I visit the OTN Forum (actually immediately if you have stylish already enabled) then the visited links should be in a darker blue colour, like this:


Now I’m not saying this colour scheme is any nicer (in fact I think it’s a bit harder to read, but it does show how you can easily style any website yourself if you don’t like the design.

If you modify the layout, feel free to let me know and perhaps we can start a collection of OTN Forum skins?

Application Express 3.1.2 Upgrade – Session Zero and Redirects

I was glancing through the list of fixed bugs in the Apex 3.1.2 release and noticed that one of my long time ‘annoyances’ has been fixed.

Many people have had problems getting their Apex applications indexed by Google and other search engines. One of the reasons for this was that Google (and other search engines) would often index the page using a different session id each time, since Google (and others…ok, let’s just say Google from here on in?) does not ‘understand’ the format of Apex style URL’s and would therefore consider each URL unique.

In short, you could end up with the same page in your application indexed multiple times, with a different session id in each and therefore Google would not treat it as the same page.

The Apex team introduced the idea of Session 0, specifically for the purpose of being able to provide a link without having to specify a valid session id (or rather 0 is considered a valid session id, however when the user requires a ‘real’ session id then one is generated at that point).

So instead of links being indexed like this –

The link can be –

Where the 0 represents the session id, so all the links in Google would reference the same URL.

So, that’s all great isn’t it? Well…almost…unfortunately there was a problem with using Session 0, and it was to do with redirects.

First, let’s look at what happens when a user (or really a browser) requested a page using session 0 in Apex 3.1.1 and earlier (note that in the following code I’ve removed certain identifying things like IP addresses etc).

[jes@MBP ~]$ GET -d -e "http://dbvm/pls/apex/f?p=101:1:0"
Connection: close
Date: Fri, 29 Aug 2008 05:00:26 GMT
Location: f?p=101:1:0
Content-Length: 0
Content-Type: text/html; charset=UTF-8
Client-Date: Fri, 29 Aug 2008 05:00:26 GMT
Client-Response-Num: 1
Client-Warning: Redirect loop detected (max_redirect = 7)
Set-Cookie: WWV_PUBLIC_SESSION_101=1074339690918688

Here I am using the GET command from the libwww-perl toolkit to simulate a browser request for the page. The parameters I use tell the command to only show me the HTTP Response details (the -e parameter) and that I’m not interested in seeing the actual response (the -d parameter).

The key thing here is the ‘Redirect loop detected’ message, this is the GET command telling you that it has found a redirect back to the same URI. The message is a little misleading since it sort of implies an infinite loop (which you’d think would make your browser hang). However if we simulate the same URL request using plain old telnet, you’ll see the real response:

[jes@MBP ~]$ telnet dbvm 80
Connected to
Escape character is '^]'
GET /pls/apex/f?p=101:1:0 HTTP/1.1
<p>HTTP/1.1 302 Found
Date: Fri, 29 Aug 2008 05:00:56 GMT
Location: f?p=101:1:0
Set-Cookie: WWV_PUBLIC_SESSION_101=507304029881630
Content-Type: text/html; charset=UTF-8
Content-Length: 0
Connection: close

The line with “Location: f?p=101:1:0” is the killer line here, as it tells the browser to redirect back to the same page (using a relative link rather than an absolute one), however also notice that a cookie is being set.

So the sequence of events is:

  1. Browser requests a page using session 0.
  2. Server responds with a redirect location and a cookie
  3. Browser requests the redirect location

So, essentially whenever a browser requested a URL using session 0 your webserver would actually be hit at least twice. For a small website this might not be a problem, however for a large site with lots of users who bookmarked a link to the home page with a Session 0 link, or people who used a link from another site etc, this could potentially add a big overhead to the number of requests your webserver had to handle (whilst the redirect response is not a large amount of content to server, it is still nonetheless a web request that needs to be handled).

This was also a potential problem for search engines, since many search engines do not always handle redirects nicely, since they may assume that when they try to access (and index) a page that if you’re sending them somewhere else with a redirect that something is ‘not quite right’. It’s certainly a factor in getting Google to nicely index Apex applications.

So, let’s take a look at how it works after patching to 3.1.2, running the same request for the same session 0 URL we get:

[jes@MBP ~]$ GET -d -e "http://dbvm/pls/apex/f?p=101:1:0"
Connection: close
Date: Fri, 22 Aug 2008 05:02:01 GMT
Content-Length: 13352
Content-Type: text/html; charset=UTF-8
Content-Type: text/html; charset=utf-8
Client-Date: Fri, 29 Aug 2008 05:02:01 GMT
Client-Response-Num: 1
Set-Cookie: WWV_PUBLIC_SESSION_101=8141285575191180

Notice how this time there is no redirect at all, the content is returned directly (note the Content-Length response header) and the cookie is automatically set.

If you’ve never had first hand experience of the problems the previous Session 0 behaviour could cause, then this might not look that interesting, however the fact it is now patched has huge consequences for most Apex applications out there, in two key areas:

  • Your webserver will now not need to handle all those additional redirect requests, meaning the the webserver is freed up to support even more ‘real’ end user requests.
  • Search engines can now more easily index Apex applications, without you having to do a single thing (well besides installing the patch).

In short, by applying this patch you have taken another step forward in making your Apex infrastructure much more scalable and I also expect to start seeing many more Apex applications ranked higher in Google (and other search engines….had to say it, sorry).

I’ll hopefully post some more on the other implications in some of the patches if I get a chance…

Apex 3.1.2 Released

Hot off the press, Application Express version 3.1.2 has just been released.
The link to the direct download is here, if you’re upgrading via the patchset on Metalink then the patch number is 7313609.

You can find more details about the patch here.

There looks like quite a few fixed bugs (it’s always hard to say that without it sounding like a bad thing!) in this release, definitely worth the upgrade!

Oracle OpenWorld 2008 – My Schedule

Even though it only seems like a few months since we were in the Cow Palace listening to Billy Joel, Oracle OpenWorld 2008 is just a few weeks away.

There are lots of Apex sessions going on at OOW this year, it seems to increase every year (in the 3 years I’ve been going to OOW). Rather than repeat the list here, David Peake has created a nice little Apex app (what else?!) to show the details of all the Apex sessions, including the number of seats and registered people, you can also find Davids original post about the sessions here.

My schedule for presenting this year is:

  • Sunday 21st Sept (10:00 – 11:30) – Oracle Application Express SIG: Ask the Experts
  • Sunday 21st Sept (15:45 – 16:45) – Oracle Application Express Hints, Tips, and Best Practices
  • Sunday 21st Sept (16:50 – 17:30) – Oracle APEX: Power to the People (Customer Success Story with Rich Mutell of AMGEN)
  • Thursday 25th Sept (09:00 – 10:00) – Dispelling Myths About Oracle Application Express

I seem to have a habit of getting ‘not so great’ time slots at OOW for some reason and this year seems to be no different. There’s a chance on the Sunday that the ‘Power to the People’ session will overlap with the ‘Hints, Tips and Best Practices’ one. Fortunately for the ‘Power to the People’ one it is Rich who talks through it all, I just sit there and press the buttons and move the mouse, so I’m sure Rich can handle that himself.

Both Joel Kallman and myself are presenting during the Customer Success Stories sessions, which tough luck as the Customer Success Stories are always popular sessions, however hopefully I’ll see a few friendly (perhaps lost?) people in my session.

The Best Practices presentation I’m doing is the same one I did at the Collaborate and ODTUG events this year, so if you’ve already seen it then that’s one more session you can chalk off your list!

The new presentation that I’m doing this year for OOW is the “Dispelling Myths About Oracle Application Express” presentation on thursday. Once again this has to perhaps be one of the worst times possible to presentation, think about it…9am the morning after the big party…there’s going to be lots of sleepy eyelids in that session, tough crowd! I’m looking forward to doing this session and have a few ideas on how to make it a little bit different and fun, but then again the things that work in your mind sometimes don’t work out in-front of an audience so we will have to see how brave I’m feeling!