Who's online

There are currently 0 users and 34 guests online.

Recent comments


OUG Ireland

I’m chairing a Q&A session on the Cost Based Optimizer for OUG Ireland.

We will be copying the format we used for the UKOUG annual conference, with some questions submitted in advance and Martin Widlake on the door handing out 5×3 cards for the audience to supply questions as they arrive, then running around the room with a microphone reading out questions or getting the audience to reply to comments from the table.

If you’d like to submit a question in advance, email me, or post it in the comments section below.


Never rely on an assumed order

We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement.

Here’s another trivial example of this- I was doing a little demo script for an AskTom question.

Here’s the script running in


A while back I did a blog post showing that when you have set a column to UNUSED, it still “counts” as a validation step when doing exchange partition.  So if you had a partitioned table that previously had a column set to unused, then the candidate table to be exchanged in also had to have undergone the alteration.  Just having the visible columns aligned is not sufficient.

In the comments, Sergey presented an interesting variation of this, which we will look at below

Public / private cursors

As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic.

However, not many are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component.

Cursors can also be defined in the same way as the following example shows:

   CURSOR c_recent_hires RETURN emp%ROWTYPE;
END emp_stuff;

   CURSOR c_recent_hires RETURN emp%ROWTYPE IS
      SELECT * FROM emp 
      WHERE hiredate > sysdate-30;
END emp_stuff;

AskTom–some thoughts on the future

The structure of an AskTom question contains four elements:

  • The original question
  • Our answer
  • A review, which can be posted by anyone
  • And then we can opt to add a single Followup to any of those reviews



We’re thinking of changing AskTom to allow greater flexibility, namely:

Bitwise operations

The long existing BITAND function is now within the documentation, to let you do logical AND on two numbers, and is also available from PL/SQL



If you need other bit operations, a little boolean math should suffice Smile Just make sure you stay within the limits of BINARY_INTEGER

CREATE OR replace FUNCTION bitor( x IN binary_integer, y IN binary_integer ) RETURN binary_integer  AS

Auto-backups of PLSQL source

I saw this on an ideas forum today


and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to their source repository. 

So the idea has some merit… and maybe we can roll our own without too much fuss. I make no claims that this is a ‘complete’ solution, but it should get you going Smile

Forever is a long time

It’s interesting that we still see lots of code out “in the wild” that leans itself to taking forever. Now I’m not talking here about poorly performing SQL etc…because (at least theoretically) no matter how bad the SQL is, presumably it will finish one day, even if that day is years into the future. What I’m referring to here is the default mechanism for locking in Oracle.

If you try access a row that is locked by someone else, you will wait. Don’t get me wrong … that’s a very good thing. The alternative – of letting two people change the same data at the same time is equivalent to saying “I dont care about my data at all”.

And how long will we wait ? Well… we’ll wait forever. We will never stop waiting, until that lock has been released.

I don’t know about you … but for me… forever is long time. A really long time.

Friday Philosophy – If Only I Was As Good a Programmer As I Thought I Was Aged 22

I saw a tweet that made me smile a few days ago:

programmer quote

Like many of you, I started out my working life in IT as a computer programmer. Like some of you, I still am a computer programmer from time to time. I don’t think I’ve had a year of my working life when I did not do either some application development programming or some database infrastructure programming. I am constantly writing small (or not so small) SQL or PL/SQL programs to do what I do in Oracle.


Most people already know about the very cool GATHER_PLAN_STATISTICS hint.  If not, you can see an example here

But here’s a common cause of confusion when using it in SQL Plus: