ODC Appreciation Day: EMP #ThanksODC

Here is my very short entry for the Oracle Developer Community Appreciation Day 2017.

Very often I use the good ol' EMP table to demonstrate even the latest features of SQL and PL/SQL.
Everybody seems to know the EMP table, and some even know some of the content off the top of their head (yes, I'm guilty of that too). Whenever I need to write a hierarchical query and am not really sure what which (more...)

ODC Appreciation Day: Collections in SQL

Here’s my contribution to the ODC Appreciation Day.


Last week I had the privilege to participate in the EOUC Database ACES Share Their Favorite Database Things session at Oracle OpenWorld, so I think that the best topic to write about, as part of the ODC Appreciation Day, is the one I talked about in this session.
My 5-minute presentation was about Collections in SQL.

Collections are very useful in PL/SQL development. This is (more...)

ODC Appreciation Day: The PL/SQL Language

Like last year, Tim Hall of oracle-base.com fame suggested we should all do an "ODC Appreciation Day" in honor of the Oracle Developer Community (ODC), by blogging about our favorite Oracle product or feature.

My personal favorite, after the database itself, is the PL/SQL language that runs inside the database.

Here's what's great about it:
  • Simple (and therefore easy to understand and quick to learn)
  • Runs everywhere the Oracle database runs (any operating (more...)

ODC Appreciation Day: Cursor Variables

You know everything is Tim Halls fault, right? Including this blog post? Oh yes it is, he came up with the idea of OTN ODC Appreciation Day - a day where Oracle Bloggers all over the world show their appreciation of the Oracle Developer Community by blogging about some favorite Oracle feature.

So what should I pick this year of the multitude of possible favorites? Something brand new? No, I think I'll write about (more...)

RETURNING INTO – Enhancement Suggestion

The RETURNING INTO clause is one of my favorite features.
It returns data from the rows that have been affected by the DML statement, and as I wrote in this previous post:
For INSERT it returns the after-insert values of the new row’s columns.
For UPDATE it returns the after-update values of the affected rows’ columns.
For DELETE it returns the before-delete values of the affected rows’ columns.

For INSERT there are no before-insert (more...)

PL/SQL functions: Iterate and keys for associative arrays

Unfortunately associative arrays still require more “coding”:
we still can’t use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don’t have convinient iterators and even function to get all keys…
That’s why I want to show my templates for such things like iterator and keys function. You can adopt these functions and create them on schema level.

   type numbers  is table of number;
   type anumbers  (more...)

Implementing Arc Relationships with Virtual Columns? Or Not?

I wrote a post some time ago about implementing arc relationships using virtual columns.
Recently, Toon Koppelaars wrote a detailed and reasoned comment to that post. Since I admire Toon, getting his point of view on something that I wrote is a privilege for me, regardless if he agrees with me or disagrees (and just to be clear, it’s the latter this time). I think that having a public (and civilized) discussion – this time (more...)

How to group connected elements (or pairs)

I see quite often when developers ask questions about connected components:

Table “MESSAGES” contains fields “SENDER” and “RECIPIENT”, which store clients id.
How to quickly get all groups of clients who are connected even through other clients if the table has X million rows?
So for this table, there should be 4 groups:
  • (1, 2, 4, 8, 16)
  • (3, 6, 12)
  • (5, 10, 20)
  • (7, 14)
  • (9, 18)
3 (more...)

Avoiding reinventing the wheel – use MULTISET EXCEPT to get set relative complement

Today in an application I stumbled upon a PL/SQL function created to return those elements of one nested table that did not exist in another nested table. Not a huge function, but still a bit of work and some thinking that had been done some while ago to compare the elements and create the desired output nested table.

The trouble is, that this functionality already natively exists in PL/SQL (and SQL) - the developer had (more...)

“#PL/SQL: Therefore, whoever binds forever: automate your tests” – I’m a speaker at #DOAG2017


Today my presentation is confirmed: “Therefore, whoever binds forever: automate your tests”. Maybe the German pun will be the better understood: “PL/SQL: Drum test-automatisiere, wer sich sich ewig bindet!”

I will speak over my experiences with following

  • several PL/SQL testing tools like at least
  • I will give you a demo of this tools and compare their capabilities for
    • architecture
    • branching
    • continuous integration
    • code coverage

If you (more...)

OGh Tech Experience 2017 – recap

On June 15th and 16th 2017 the very first OGh Tech Experience was held. This 2-day conference was a new combination of the DBA Days and Fusion Middleware Tech Experience that were held in previous years. To summarize: OGh hit bullseye. It was two days packed with excellent in-depth technical sessions, good customer experiences and great networking opportunities.

The venue was well chosen. De Rijtuigenloods in Amersfoort is a former maintenance building of the Dutch (more...)

PL/SQL in SQL in View in SQL in PL/SQL

I presented “Write Less (Code) With More (Oracle 12c New Features)” yesterday at OGh Tech Experience 2017.
One of the features I talked about was PL/SQL in the WITH Clause. One of the restrictions of this feature is that you cannot embed a static SQL query, that contains PL/SQL in the WITH clause, in PL/SQL (see the section PL/SQL in SQL in PL/SQL in this post).
I was asked, regarding this restriction, if it’s (more...)

Avoiding Coincidental Cohesion

Given that Coincidental Cohesion is bad for our code base so obviously we want to avoid writing utilities packages. Fortunately it is mostly quite easy to do so. It requires vigilance on our part. Utilities packages are rarely planned. More often we are writing a piece of business functionality when we find ourselves in need of some low level functionality. It doesn't fit in the application package we're working on, perhaps we suspect that it (more...)

Utilities – the Coincidental Cohesion anti-pattern

One way to understand the importance of cohesion is to examine an example of a non-cohesive package, one exhibiting a random level of cohesion. The poster child for Coincidental Cohesion is the utility or helper package. Most applications will have one or more of these, and Oracle's PL/SQL library is no exception. DBMS_UTILITY has 37 distinct procedures and functions (i.e. not counting overloaded signatures) in 11gR2 and 38 in 12cR1 (and R2). Does DBMS_UTILITY (more...)


The RETURNING INTO clause is one of my favorite PL/SQL features. It allows to write less code, improves readability and reduces context switches between PL/SQL and SQL.
In this post I’d like to highlight some less-known characteristics of the RETURNING INTO clause and emphasize differences that exist when it is used in different DML statements.

Supported Statements

The RETURNING INTO clause is supported by the UPDATE, DELETE, and single-table single-row (“values-based”) INSERT statements.
It is (more...)

SQL validation during PL/SQL compilation

A recent posting on SQL.RU asked why Oracle doesn’t raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn’t find a link to the answer (though I read about it many years ago, but I don’t remember where…), I’ve decided to post short answer:

During PL/SQL compilation Oracle checks static SQL using only:

  1. Syntactic analysis – Oracle verifies that keywords, object names, operators, delimiters, and so on are (more...)

COLLECT DISTINCT in PL/SQL Works in Oracle 12.2

About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there:

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.

And I showed an example that was executed in and in (more...)

Good old BIN_TO_NUM to check the overall status

A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.


Using VS Code for PL/SQL development

I've been using Sublime Text as my main editor for PL/SQL development for many years, but I'm now in the process of switching to Visual Studio Code (VS Code).

Some good reasons to use VS Code:
  • Multi-platform (Windows, OS X, Linux)
  • Free, open source
  • Lightweight, fast
  • Large ecosystem of extensions
  • Built-in Git support
  • Can be adapted to PL/SQL coding via a plsql language extension (syntax highlighting, go to/peek definition, go to symbol) and PL/SQL compilation (more...)

#DEVCAMP17 wrap up


Yesterday I attend the annual barcamp DEVCAMP of the DOAG development community. There are mostly developers with SQL, PL/SQL, Forms & Reports, ADF, JET background and some Java, Javascript and APEX developers too. And not to forget: 1 dba and some managers. Here are my summary.

I arrived on evening before just in time for the life cooking event and later at the bar to meet lot of the people I already know and some new too.

The morning begins with (more...)