view counter


Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Drop Types Recursively

As covered in my new Oracle Database 12c PL/SQL Programming book (publisher’s satisfied), you can evolve object types. That means you can change a base object type and the change cascades through dependents. Somebody asked how to remove an object type chain without appending the FORCE clause.

It’s quite easy if you understand writing a recursive function in PL/SQL, as done here:

Snake handlers and their faith - why not go all the way?

Just reading about another snake-handling pastor who died from a snake bite.

My feeling about these folks is: go for it. If you want to risk death for your beliefs, why not? It's not like there aren't enough people alive in the world to fill in any gaps you leave behind.

A Glimpse into Feuerstein Refactoring....End Result Better?

As some of you may recall, we held our first non-PLSQL and non-quarterly championship since the PL/SQL Challenge started: the 12 February Annual SQL Championship.

Now, of course, if I had designed my database without any flaws, fully taking into account all possible directions in which the website could go, anticipating all possible user requests, etc., then we would not have encountered any bugs in the process of applying our code base to this new championship.

Ha. Ha. Ha.

"Are you a runner?"

That's a question I get a lot.

I guess that's because I am not overweight and kind of tall....?

I can remember a few times when I really enjoyed running: both times they were more an adventure, in which running was the mode of transport. But generally, I have been bored by running so, no, I am not a runner.

In fact, over the past year, I have completely changed my views on exercise, and running is even less a part of what I do to stay healthy than ever before.

SQL Quiz: What’s different about these two tables?

Two tables that look the same, but for some reason I can insert my pounds (£) into the first one, but not the second:

Signal from a procedure

As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:

Results of First-ever SQL Championship for 2013

You will find below the rankings for the first ever 2013 SQL championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Vincent Malgrat of French Republic

2nd Place: Christoph Hillinger of Austria

Brush up your PL/SQL skills with Steven’s new book

insert into my_reading_list (
book, author
) values (
'Oracle PL/SQL Programming, 6th Edition',
'Steven Feuerstein'
That’s right, Steven Feuerstein – PL/SQL expert and all round great guy – has recently published the 6th edition of his PL/SQL programming book.

Union via Join

Here is really bizarre technique: one can perform union via join! Consider:

(([p] 0 [q] a b) ([p] 1 [q] c)) v [q];

which outputs


(We assumed domain p to include {0,1} and q to include {a,b,c}, at least). You may object that the expression involves [domain dependent] union operation , but it is applied for two relations with disjoint attributes, so it is nothing like classic union.

Oracle 12c: scalar subqueries

We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):

view counter