view counter


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


Using Oracle’s Resource Manager requires you to understand the IO dynamics. The first step requires you to run the CALIBRATE_IO procedure from the DBMS_RESOURCE_MANAGER package.

Free Oracle Tuning Book

Quick Start Guide to Oracle Query TuningWho can resist a free book Rich Nimeiec book on SQL Tuning? O.K., those who know everything can resist. If you’re like me, this is an opportunity to learn from Rich.

Use an object in a query?

Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the FROM clause), but then they get errors like this:

Rounding Amounts, the missing cent: with the MODEL clause

Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

Rounding Amounts, the missing cent

Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences.
Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes).
Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99)
To solve this cent-problem, we decide that the difference should be added (or subtracted) on the last row.

Row pattern matching nested within hierarchy

I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12. Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after those rows to avoid unwanted "double" matches. But can there be uses where we want overlapping or even nested matches? Well, I found at least one case where I think it makes perfect sense...

Planet Hard Drive? Muddled nonsense from Scientific American

In the August 2015 issue of Scientific American, I came across an article titled "Planet Hard Drive", a "thought experiment" arguing that we can think of Earth as a kind of "hard drive" and "although Earth has an enormous capacity to store information, order is still rare....but the growth of order on Earth also stems from the production of cultural information."

A Lot To Listen To

A Lot To Listen To

Sometimes, if you're lucky,
there is nothing to hear
but the sound of the wind
blowing through trees.
Now you could say:
"That's not much to listen to."
Or you could listen...

Object Type with Optional Attribute: Extra Constructor Function

When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
If you are unfamiliair with this technique, there are some links at the bottom of this article.


Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.

I gave my students this ORDER BY clause as an example:

view counter