Tag: sql

Take care with regular expressions

In an Office Hours session a couple of months back, I covered an important change that comes to regular expressions once you upgrade to 12c Release 2. You can see the video covering the issue here: but for the TL;DR brigade reading this post:…

Gooey GUIDs

Do a quick Google search and you’ll find plenty of blog posts about why GUIDs are superior to integers for a unique identifier, and of course, an equal number of posts about why integers are superior to GUIDs. In the Oracle world, most people…

From file names to directory hierarchy

I had a fun request come in from a colleague the other day.  They had a simple list of fully qualified file names and they needed to present that data in the familiar hierarchical tree layout.  To demonstrate, I took a little trip down…

Concurrency … the path to success and the path the failure

Let’s face it. Concurrency is a good thing when it comes to database applications. After all, if there is only a single user of your application, then chances are, it is not a successful application .  Of course there are exceptions to this rule,…

Choose your names wisely

It all seems simple enough.  You pick a name for an object…and that is the only object that have than name right ?  For example: SQL> create table MY_UNIQUE_NAME ( x int ); Table created. SQL> SQL> create synonym MY_UNIQUE_NAME for MY_UNIQUE_NAME; create synonym…

NVL vs COALESCE

Jonathan Lewis just published a blog post about NVL and COALESCE and the optimizer costings for each. There is also perhaps a significant difference between NVL and COALESCE in that the former seems to have an in-built optimization for handling bind variables and nulls. …

Buzzword Bingo

Looking for that catchy title for your next presentation ? I took the first word from the title of 1000 Oracle OpenWorld presentations, and looked for patterns .  I omitted some obvious terms that are either products or definite/indefinite articles: Oracle Peoplesoft  The How …

AskTOM TV–episode 11

Just a quick note to give the supporting collateral to the latest episode of AskTOM TV. The question I tackled is this one: https://asktom.oracle.com/pls/apex/asktom.search?tag=want-to-retrive-numbers-in-words which was a fun one to answer because it showcases several useful SQL techniques: Using CONNECT to synthesize rows, Using…

Partition-wise join

So just what is a “partition-wise” join ?  We will use a metaphor to hopefully explain the benefit. Let’s say two people, Logan and Shannon, decide to move in together.  If each of them already have an existing residence, they will both have a…

Oracle Code … Not for database people ?

Jump over to the Oracle Code home page and you will see the “mission statement” of the Oracle Code conference series: “Learn from technical experts in sessions for developing software in Java, Node.js, and other languages and frameworks.” You might hence be thinking that…

AskTOM TV episode 8

On AskTOM episode 8, I’ve taken a look at locating the SQL Plan Directives used for a particular query.  Here is the script output from the video if you want to use this for your own exploration SQL> SQL> create table t as 2…

SUM is better than DISTINCT

There is a good chance that (based on this blog post title) that you’re expecting a post on SQL, and that’s understandable. But I’ll come clean nice and early – that was just to lure you in The post is about SUM and DISTINCT,…