All posts by Duncan Worthy

ORA-07445 Error in a MERGE query

ORA-03113: end-of-file on communication channel. This was the error that greeted me after many hours work converting some PL/SQL scripts to run as a package of functions, views and stored procedures.

My first thought was actually that something or someone had terminated my connection to the database. Given that I was working on a DEV/TEST instance, I wouldn’t necessarily have expected to be notified about a momentary disruption to service. After reconnecting and repeating the same and other queries, it became clear the only thing causing this was one of the procedures in a package I had written.

I tried a few changes without any success and then decided to call it a day, with the intention of speaking to the one of the DBAs the following day to see if they could provide any further information.

As it turned out, I didn’t have get in touch with the DBAs… The next morning they were already looking for me!

It turns out that the error which had been unexpectedly terminating my connection had been causing an access violation followed by a core dump on the server. Of course this is the kind of thing that raises all kind of alerts even on a development/test instance.
Continue reading ORA-07445 Error in a MERGE query

Hierarchical Data in SQL Part 2: Recursive Queries with an Adjacency List

In the first post of this series, we saw a simple table representing a real-world hierarchy and the simplest of queries on that data, and finished with the question: “What else might we do with this data?”

Maybe we need to know the chain of responsibility leading from a particular position up to the top of the organisation.
Or, perhaps we would like to report on a subsection of the tree – for example, listing everyone working directly or indirectly below the Financial Director.
And what if we want to see what this organisational structure looks like?

For all of these, we can use recursive queries.
Continue reading Hierarchical Data in SQL Part 2: Recursive Queries with an Adjacency List

Hierarchical Data in SQL Part 1: The Adjacency List

One of the most obvious examples of hierarchical data frequently stored in a database is the organisational hierarchy of employees and their managers.

Typically this follows a one-to-many parent-child relationship whereby each employee reports to a single manager above them, and may have zero or more employees below them who they manage.
This tree is likely to have a relatively shallow structure, perhaps no more than 10 levels deep even in an organisation of 100,000 employees.

The simplest method of storing and maintaining a tree of this type is for each record to include a reference to its parent.
You may see this referred to as an “Adjacency List Model”.
It is worth noting that an adjacency list is a type of Relationship Graph, and that our example is a special case in which we want the graph to form a tree structure.

Representing a tree in this way allows us to easily write simple queries to access the nodes immediately adjacent to a given node (above or below them in the hierarchy), but requires us to use recursion if we wish to query data spanning further levels of the tree.
Continue reading Hierarchical Data in SQL Part 1: The Adjacency List

Working With Hierarchical Data in SQL

Over a series of articles, I will be writing about and demonstrating some of the ways in which hierarchical data can be stored, queried and presented using SQL. The primary focus will be Microsoft SQL Server, followed by Oracle, but there may also be some sections dedicated to other platforms.

I will update this page with links to the subsequent posts as and when they are added.

Continue reading:

ORA-600 Error in a Partitioned Join

In a previous post, I wrote about partitioned outer joins in Oracle.
The simple schema and queries in that post are similar to those I used when first learning about this feature. At the time, I came upon an unexpected error which caused some Oracle instances to throw an ORA-600 error from a very simple query.

The original test environment I had been using was Oracle Database 11g Enterprise Edition running on Solaris.
I had created a simple schema to experiment with, and written a couple of queries which returned results designed to illustrate the effect of a partitioned join as compared with other types of joins.
Once I had them doing what I wanted, I decided to discuss them with a colleague, who tried running the on his local instance of Oracle Express on Windows.
Instead of the expected results, he received the following error:
ORA-00600: internal error code, arguments: [kkoipt:invalid aptyp], [1], [0], [], []

Continue reading ORA-600 Error in a Partitioned Join

C# Constructor Gotchas Part 2: Singletons and the Sequence of Initialization

One of my favourite questions to ask interviewees is, “What is the order of initialization in a C# class?”

This is part of a series of posts featuring simple but informative code samples.
You can find a bit of background information and a list of the other posts in the series here.

If the question is too ambiguous, then to be more specific: place the following items in a list according to the order in which they are executed during initialization of a class in C#: static and instance class constructors, and static and instance field initializers.

The simple answer, ignoring inheritance, is:

Continue reading C# Constructor Gotchas Part 2: Singletons and the Sequence of Initialization

C# Constructor Gotchas Part 1: Calling virtual methods from a constructor

What could go wrong if we call an overridable method from within a constructor?

If you’ve used static code analysis tools then you may have come across a warning instructing you not to call virtual methods in constructors.
This particular warning is one that should not be ignored, and nor should it be suppressed without good reason. It is of particular relevance to those moving to C# from some other object oriented languages which do not behave in quite the same way (more on that later). Continue reading C# Constructor Gotchas Part 1: Calling virtual methods from a constructor

C# Constructor Gotchas

A series of short posts focusing on some interesting aspects of constructors in C#.


In my previous employment, I was involved in the selection and interviewing of candidates applying for software developer positions. Although we already had a small set of standard technical questions which were put to each candidate during the interview, I was asked to come up with an additional set which could be given as a short written test.
I began compiling a few basic questions covering some important C# keywords such as new, using, yield, and lock. Although I had enough simple questions to use, I began to wonder whether the questions might only be useful in identifying those who had recently read through their C# textbook to prepare for such a test. Continue reading C# Constructor Gotchas