Who's online

There are currently 0 users and 21 guests online.

Recent comments


Loading LOB from a file

I observed this idiosyncracy recently when loading some lob from external files using PL/SQL:

First we’ll create a file to be loaded, and ‘wc’ tells us it is 75 bytes in size 

SQL> !echo "This is line 1" > /tmp/lobfile
SQL> !echo "This is line 2" >> /tmp/lobfile
SQL> !echo "This is line 3" >> /tmp/lobfile
SQL> !echo "This is line 4" >> /tmp/lobfile
SQL> !echo "This is line 5" >> /tmp/lobfile
SQL> !wc /tmp/lobfile
       5      20      75 /tmp/lobfile

Then create a standard routine to load it into the database

Amped on Amper

This “problem” rates in the top 10 all time viewed questions on AskTom, and it demonstrates the importance of reading the Concepts guide with Oracle

SQL> create table CARTOONS ( name varchar2(30));

Table created.

SQL> insert into CARTOONS values ('Tom & Jerry');
Enter value for jerry:

And the question comes in: “How can I insert an ampersand into my table?”

How can I see my invisible columns

A cool new feature in 12c is the ability to make a column invisible.  The concept has existed since 8i to handle things like “set unused” and function based indexes, but now it is available to developers directly.

SQL> create table T ( c1 int, c2 int );

Table created.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)
 C2                                     NUMBER(38)

SQL> alter table T modify c2 invisible;

Table altered.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)

So at this point… how I can tell in SQL Plus that I even have an invisible column, without querying the data dictionary.

Equi-sized partitions on random data

Had an interesting AskTom question today about dynamically keeping partition sizes in check. The poster had large table, already partitioned, but since there was no logical column to do a range partition on, it was partitioned on a surrogate primary key value. Due to the volatility of the data, (with a BLOB column on the table) evenly sized primary key ranges led to dramatically different partition sizes, causing manageability concerns. Partitions could be split and/or merged, but obviously if those partitions contain data, that is both a very expensive operation, and could impact availability of the data depending on the type of maintenance operation performed.

So the challenge became – can we proactively have (roughly) equi-sized partitions, when we don’t know how and when the data is going to come in.

Something new learned every day

One of the reasons I leapt at the chance to be on the AskTom team when we resurrected the site, was that it’s like free training.  You get questions on topics you have not visited before, and you get new angles on things you thought you already knew.

Just today, someone posted a question about the new DEFAULT ON NULL syntax in 12c, with the following observation:

“Standard inserts advance the sequence only when needed, but a PL/SQL for-loop advances the sequence all the time”

And here was their test case… (Sidebar:  WOO HOO!!! Yippee !!!! A test case !! A test case !!! People get it !!! Smile )

On building SQL

I had a fun question on AskTom over the weekend, that of, how to display a monthly calendar for any provided date using just SQL.

You can see the question and the answer here

But I thought it might be worth explaining the process.  Of course, the way I approach the problem might be totally different to the way others would, and that’s fine.  But for the novice, you might get something useful out of this.

So here is January 2016. 


The first thing I’m thinking to print a calendar is


One of the cool things in 12c is that (finally after all these years) a sequence can be assigned as the default value for a column.

Of course, some may say “big deal” – after all, it is trivial to create a simple trigger to do the job.  And perhaps, we “crafty” folks at Oracle are just doing a hidden trigger in the background anyway Smile

Well… we’re not.  We’ve put some work into this to make it sing.  Let’s look at a demo.  First, here’ s the trigger-based approached.

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