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...)

ETL using Oracle DBMS_HS_PASSTHROUGH and SQL Server

While I prefer a “loosely coupled architecture” for replication between Oracle and SQL Server, sometimes a direct (database) link cannot be avoided. By using DBMS_HS_PASSTHROUGH for data extraction the 2 other ETL processes (transformation and load) can be configured and administered with more flexibility, providing an almost acceptable level of “loosely coupled processing“.
Consider this as a really simple ETL config:

    Extract: Select SQL Server data with native sql, using (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...)

Explicit Semantic Analysis setup using SQL and PL/SQL

In my previous blog post I introduced the new Explicit Semantic Analysis (ESA) algorithm and gave an example of how you can build an ESA model and use it. Check out this link for that blog post.

In this blog post I will show you how you can manually create an ESA model. The reason that I'm showing you this way is that the workflow (in ODMr and it's scheduler) may not be for everyone. (more...)

Oracle Diagnostic Queries

It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.

For this example, you create the following table_struct object type and a table_list (more...)

How can we use Oracle to deduplicate our data

Dear Patrick,

We have gone through a merger at our company where we are trying to merge the databases. The problem now is that we have duplicate records in our tables. We can of course go through all the records by hand and check if they exist twice. Another option is to build an application to do this. But using the Oracle Database there must be a better way to do this. Any ideas?

Ramesh (more...)

OTN Appreciation Day: PL/SQL

We are posting these blog posts today as part of the OTN Appreciation Day, a celebration for the Oracle Technology Network as suggested by Tim Hall, inspired by Debra Lilley.

The mission was not too hard: write about your favorite bit of Oracle Technology.

As a developer and a core-tech DBA and APEX enthusiast… the choice was easy! PL/SQL (apart from how it is pronounced or even written ;-)!

Why? Easy!

Not just because (more...)

How can we add custom code to existing triggers?

Dear Patrick,

We have bought an application that runs on an Oracle database. There are triggers defined on the tables, but we want to add our own code to these triggers, but we don’t have access to the source code. What is the approach we should follow to accomplish this?

Collin Bratforth

Dear Collin,

There are two types of DML triggers. Statement level triggers and row level triggers. Then for these two types there are (more...)

What is overloading and how and when do I use it

Dear Patrick,

Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?

Ramesh Cumar

Dear Ramesh,

Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in (more...)

When would you use a normal table function?

Dear Patrick,

Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?

Erik van Roon

Dear Erik,

Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)

RegExp: Constraint to prevent spaces at the beginning or end.

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with (more...)

An introduction to Oracle PL/SQL for beginners

PL/SQL offers the entire suite of structured programming mechanisms, such as condition checking, loops, and subroutines, as shown in the following figure. (read more)