Some time ago I got my hands on Seven Databases in Seven Weeks. I managed to evaluate PostgreSQL database examples of that book and they made me fully aware of the technological gap in Oracle exceptions.

In PostgreSQL you may see error message like this one:

ERROR: duplicate key value violates unique constraint "constraint_name"
DETAIL: Key (column,column...)=(value,value...) already exists.

From the very first days of my adventures with Oracle databases, which was about 1998 and Oracle 8, the thing that made me spent most of my time was finding out what is causing the exceptions I get.

It is now year 2014 and actually nothing has changed. Whenever I encounter some of the most common exceptions, it usually leads to hours of investigations, depending on the data volume and the data transformation complexity that I’m working on.

ORA-00001: unique constraint (string.string) violated
ORA-02290: check constraint <constraint_name> violated

What kid of information do I get with those error messages?

I have the information about the character of the exception and I can get the information from stack trace, to see at which place in source code they occurred.

What information do I miss?

The data that caused the exception.

Imagine that the exception occurred at the very end of a complex  data transformation process involving multiple inserts into a single table, populating about 1.000.000 rows.

The exception itself says nothing about the data that caused the exception. But oracle, to check it there is a duplicate value, needs to check if the particular value that is to be populated for a unique column exist in the table already, so internally, the database is aware of the value that caused the exception, it is simply not reported by the exception.

If I would get:

ORA-00001: unique constraint (string.string) violated on value "123456"

Then I could at least narrow the search and reduce the time needed to fix the issue.

Some exceptions however are self descriptive. The one of the most common is:

ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE"."COLUMN")

When I see it, I know that a NULL value was to be inserted/updated into a particular column while performing an SQL statement. I have all the information i can get to look up the invalid data that caused the error and find a bug in code that did not prevent the data from being passed into the statement.

What can I do to make it better?

For now I see no good solution that would be efficient, easy and transparent.

I guess it would be best if Oracle would invest a bit more in making the basic of the SQL and PL/SQL fit to the programming languages of the 21st century.

Maybe some community initiative could do the work here.

Leave a Reply