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: Edition-Based Redefinition

Here’s my contribution to the OTN Appreciation Day.

Edition-Based Redefinition (EBR) is a really great feature, or more accurately a set of features, that was introduced in Oracle 11.2 and allows for online application upgrades using hot rollover. If you know me then you know that I talk (ok, preach) about it a lot, and from a long and successful experience.

It is impossible to learn EBR in 5 minutes, but perhaps you’ll understand (more...)

OTN Appreciation Day – Instrument Your Damned Code. Please!

Today is OTN Appreciation Day.

This day is the idea of Tim Hall, Mr OracleBase, and you can See his post here. The idea is that as a sign of appreciation to OTN we do a technical (or not so technical) post on a feature of Oracle we like. I’m going to visit an area I have mentioned before…


In my opinion, one of the key differences between good code and poor code is (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...)

New PL/SQL pragma (12cR2) to deprecate program units – sign of a modern programming language

Bryn Llewellyn (Distinguished Product Manager, Database Division, Oracle) presented at OOW2016 on new features in PL/SQL in Oracle Database 12cR2. One of the features that stood out was a new pragma deprecate that can be added to program units such as functions and procedures inside packages. This pragma is used to mark a program unit at deprecated, which typically means: the program unit is still valid, but has been superseded by a better option and (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)

Umwandlung von LONG in CLOB mit SYS_DBURIGEN

Meine Standardantwort auf die Frage, wie man die Inhalte von LONG-Spalten auslesen kann, war seit vielen Jahren: ich hab's vergessen, aber Adrian Billington hat alles notiert, was man über diesen unerfreulichen Datentyp wissen muss. Diese Antwort kann ich jetzt modifizieren: der handlichste Weg, um LONGs in etwas weniger Häßliches zu verwandeln, ist die Verwendung der builtin-Funktion SYS_DBURIGEN, der von Marc Bleron (aka odi_63) in seinem Blog beschrieben wird. Intern erzeugt Oracle in diesem Zusammenhang ein (more...)

Real World SQL and PL/SQL: Advice from the Experts

Because my hero is Cary Millsap, I'm going to do what he did and publish my foreword Preface. All joking aside, I consider myself incredibly fortunate to have been included in this project. I learned...a lot, by simply trying to find the author's mistakes (and there were not many). There was a lot more work than I expected, as well. (Technical) Editing is lot easier than writing, to be sure.

Brendan Tierney and (more...)

My first book is now physically in my hands.

Proud "parent" of a bouncing baby book

Proud “parent” of a bouncing baby book

Today a box arrived from Oracle Press. In it were a few copies of “Real-World SQL and PL/SQL” which I co-authored with Arup Nanda, Brendan Tierney, Heli Helskyaho and Alex Nuitjen. I know I only blogged about the book a couple of weeks back, how I became involved and the impact it had on my life for several months, but as I can now physically handle and read (more...)

Send SMS, MMS and Voice messages from Oracle PL/SQL


If you need to send almost any message to almost any phone from your Oracle database, and you want to use straight PL/SQL, you may want to consider using my Clicksend API.

  • SMS (Short Message Service)
  • MMS (Multimedia Message Service)
  • Text to Voice

I have released the first beta version of my Oracle PL/SQL API for Clicksend. Read the installation instructions, API reference and download the release from here:

Sending an SMS is as (more...)

Using the PayPal REST API from PL/SQL

Do you need to accept payments for goods and services via your (APEX) application and would you prefer to handle the payments in the database via PL/SQL? Then this blog post is for you... :-)

Almost a decade ago (in 2007), Oracle released a whitepaper on Integrating Application Express with PayPal Payments Pro which used PayPal's Name Value Pair (NVP) API.

In the years since then, PayPal has made available a new API which is (more...)

The Book.

I’ve just added a picture to the right side of this site. It is for a book about SQL and PL/SQL. If you look at the image of the front cover, at the bottom is a list of authors and, near the end, is my name. It’s all finished and at the printers, but it is not out yet – It should be published in the next few weeks.

The British part of me wants (more...)

Spaltenvergleichen mit NULL-Werten

Randolf Geist hat vor kurzem einen interessanten Artikel zu einem Thema veröffentlicht, mit dem man sich beim Schreiben komplexerer SQL-Queries regelmäßig herumschlagen muss: dem Vergleichen von Spalten, in denen NULL-Werte auftauchen können. Für die Prüfung der Gleichheit von Werten bedarf die korrekte Behandlung von NULL-Werten bereits eines recht sperrigen Ausdrucks:
column1 = column2 or (column1 is null and column2 is null)
Und noch unhandlicher wird der Ausdruck, wenn man die Ungleichheit von Werten prüfen möchte:

Virtual Private Database…

Some time ago I was asked to assist in fixing or at least finding the cause of a performance problem. The application ran fine until the Virtual Private Database (VPD) policy was applied. Oracle claims there should be near zero impact on your application when you implement VPD, then how is this possible?

First of all, the policy applied was a rather complex one. A complex query should be executed to determine if the current (more...)

Checking out the Oracle Reserved Words using V$RESERVED_WORDS

When working with SQL or PL/SQL we all know there are some words we cannot use in our code or to label various parts of it. These languages have a number of reserved words that form the language.
Somethings it can be a challenge to know what is or isn't a reserved word. Yes we can check the Oracle documentation for the SQL reserved words and the PL/SQL reserved words. There are other references and (more...)

A Neural Network Scoring Engine in PL/SQL

Topic: In this post, you will find an example of how to build and deploy a basic artificial neural network scoring engine using PL/SQL for recognizing handwritten digits. This post is intended for learning purposes, in particular for Oracle practitioners who want a hands-on introduction to neural networks.


Machine learning and neural networks in particular, are currently hot topics in data processing. Many tools and platform are now easily available to work and experiment  (more...)