Recursive Subquery

At my current assignment we are processing files coming from an online system to be inserted into our database (kind of a data warehouse). This is done using external tables and a scheduled job. The job just checks if there is a file available and will process this. The trouble is that the files might not make it to our database, for various reasons.

I want to be able to check if all the files (more...)

ACE Director

It has now been a week since I have been promoted to ACE Director. I want to thank everybody for their wishes. Directly after being promoted I was off to Dublin for the UKOUG Ireland conference. That is why I didn’t respond to every tweet individually. Please don’t think I didn’t like the tweets or wishes on Linked-In, but I just didn’t have the time and the opportunity to respond.
I especially want to thank (more...)

Polymorphic Table Functions – Part 2

In my previous post I wrote about a possible use-case for Polymorphic Table Functions. I also wrote that I had some ideas to implement extra functionality to make the function more generic.
After attending DOAG Konferentz und ausstellung 2018 and UKOUG Tech18 I finally came around to publishing my new version.
Lets start off with a table to hold my data, I chose to use the NATO phonetic alphabet for this:

create table t_alphabet
(thecount  (more...)

Polymorphic Table Functions

I have been working on a presentation on Polymorphic Table Functions. During this time I was looking for a real use case for Polymorphic Table Functions. I came up with an example which is not very useful in real life, but very useful to explain the technique.
At my current job I came across a piece of code that I had to copy and adjust to fit the needs for that specific case. The idea (more...)

Comparing queries…

How do you compare a rewritten query to its original version? Most of the time I just run a MINUS operation on the original and new query. Actually I execute two. Old query (A) MINUS New query (B) and vice versa (B) MINUS (A). Both should result in no rows. That way I thought I had proven that the resultsets for both queries are equal.
But there is a flaw in this assumption.

What if (more...)

Change UUIDs in VirtualBox

If you are anything like me you will have multiple virtualboxes running on your system. Sometimes you might want to run a copy of a virtualbox for different purposes. Like running an Oracle 11 Devdays instance as test environment but also running the same vbox for customer testing. If you copy the vbox and try to run it in the manager you’ll be presented with an error that a harddisk with the same UUID already (more...)

Connecting PL/SQL Developer

In SQL Developer you have a lot of options when connecting to the database. You can use the TNS entries defined but you can also give the hostname, port and SID or Servicename for instance. PL/SQL Developer doesn’t supply these options, but you can still use them…

In the logonscreen you can choose the database by choosing you TNS entry. But did you know you can put in the complete text of a tnsnames.ora (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 : Prebuilt Developer VMs

I learnt a lot from the entire Oracle Technology Network and I still do. One of the best features of OTN these days, IMHO, is the ability to download prebuilt Virtual Machines. Since I am a developer and not an administrator, I don’t like to be bothered with stuff like how much disk space do I need, how many cores should I use etc. I can just download a Virtual Box image, import it and (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...)