New post on semantic query optimization bug

See: http://tkoppela.blogspot.com/2013/07/semantic-query-optimization-gone-bad.html

New blog with general Oracle database posts.

Started Oracle database tidbits blog here.

The materialized view approach for implementing a table constraint

Uncategorized
| Mar 8, 2013
In yesterdays post I announced that I'd spent a separate post on how we can use materialized views to enforce table constraints. So here goes.

The high-level cookbook for this approach is as follows:
  1. We create a materialized view that refreshes on commit,
  2. The materialized view is defined in such a manner that it will hold no rows when the table constraint is adhered to by the current transaction trying to commit,
  3. And it is defined such that it will hold (at least) one row when the table constraint is violated by the current transaction trying to commit,
  4. We devise (more...)

And what about table constraints?

Uncategorized
| Mar 7, 2013
In a previous post we've introduced a classification scheme for constraints:
  • attribute constraints
  • tuple constraints
  • table constraints
  • database constraints
  • dynamic constraints
And talked a bit about how we could implement the first two classes here. In today's post we will make a start talking about how we can implement table constraints using triggers. But before we do that we will offer some thoughts on how the ideal world with regards to this subject would look like.

Long ago, in a galaxy far away, an ANSI/ISO SQL standardization committee came up with the ground breaking concept of a SQL ASSERTION. SQL assertions would (more...)

Implementing attribute and tuple constraints

Uncategorized
| Mar 3, 2013
In our previous post we have introduced a classification scheme for data integrity constraints. In todays post we will present thoughts & guidelines around how to implement the first two classes:  attribute and tuple constraints.

The examples given in the previous post were:

Attribute constraints
  • Salary must be a positive integer between 750 and 14000.
  • Job must be one of the following list: MANAGER, CLERK, SALESMAN, etc.
Tuple constraints
  • A manager cannot have a salary less than 5000.
  • An employee working in department 10, cannot be a salesman.

We could implement these constraints using a trigger approach. Let's go (more...)

Data integrity constraint classification

Uncategorized
| Mar 3, 2013
Before we start investigating the complexities involved in implementing data integrity constraints using database triggers, we will first introduce a classification schema for data integrity constraints. Agreeing upon a classification scheme for constraints, helps us a lot in implementing them: for we can then approach the problem area on a class-by-class basis.

A classification scheme needs to have a few properties:
  1. The constraint classes should be mutually exclusive: any given constraint should clearly fall into one, and only one, of the classes;
  2. The scheme should cover all types of constraints: i.e. it cannot be that we can (more...)

The Helsinki Declaration (IT-version) 2012-09-30 12:49:00

Just attended this session here at Oracle Openworld: Flexible Design and Modeling: Planning for Constant Change Gwen Shapira - Senior Consultant, Pythian Robyn Sands - Principal Member of Technical Staff, Oracle Which was expressing the same message as the Helsinki Declaration does: Create API's in the database layer and do not let outside software layers access tables directly. It

The fourth use-case for triggers

Uncategorized
| Mar 26, 2012
In our previous post we talked about three of the four use-cases we introduced. Triggers can:
  1. Assign/modify (row) column values.
  2. Execute insert/update/delete statements.
  3. Execute non-transactional stuff.
We've also discussed why indeed using triggers for above use-cases, should be considered harmful. Their automagic side-effects are often not expected by other programmers / users of the database application.

In this post we continue with use-case four. Triggers can:
  1. Execute select statements.


This use-case, where we have the need to execute queries inside triggers, is a very common use-case, and it is one that always (re)surfaces, in almost every project. And there's a (more...)

Where TK agrees with TK (or: why are triggers harmful)

Uncategorized
| Feb 6, 2012
Sofar we've explored playing around with a few triggers to implement a business rule. In this post I'd like to step back a bit and take a birds-eye view at the common use-cases that I see brought up for introducing triggers into the app-development picture.


The first use-case is: we use triggers to assign or modify column values of mutating rows (that is, rows currently being inserted, updated, or deleted). Here's a few typical examples of this use case that, I'm sure, must look familiar to you too.


For this use-case we always use a row-level trigger (for each row), (more...)

Statement level constraint consistency

Uncategorized
| Jan 29, 2012
In the past week I've been investigating how Oracle fires triggers with the Merge and Multi-Table-Insert statements. Also took a look at 'statement-level constraint consistency' with these two types of statements. My findings are here: http://rulegen.blogspot.com/2012/01/statement-level-constraint-validation.html

Normal transmission on harmful triggers should resume shortly.

Workaround 2: don’t use the row-trigger

Uncategorized
| Jan 4, 2012
So where were we?

We wanted to implement the following business rule: "Cannot have a manager without a clerk in the same department." At the end of this post, we tried implementing that using a straight-forward row-trigger, which introduced us to the mutating table error. And we've explained thereafter (here and here) that this error is there for a very good reason.



We've also clarified that the two common 'workarounds' 1a and 1b really should not be used.

So using a row-trigger to check for a clerk whenever a manager is inserted will not work. (more...)

Look mom: a mutating table error without a trigger!

Uncategorized
| Dec 30, 2011
Did you ever wonder why it says 'trigger/function' in the error message of ORA-04091?


ORA-04091: table ... is mutating, trigger/function may not see it


We know (and understand) by now that a row trigger cannot read a mutating table, but what's the /function all about in above message text? Well there is a completely different scenario that has nothing to do with triggers, where you can run into this error. I thought to spend a short post on that first, so that you really fully understand that ORA-04091 is your friend. And again in this scenario it prevents you from (more...)

The mutating table error prevents non-deterministic behavior of your code

Uncategorized
| Dec 23, 2011
A short recap of workarounds 1a and 1b from our previous post.
  • In workaround 1a we ensured that the query that was selecting from the mutating table, was being executed from within an autonomous transaction.
  • In workaround 1b we ensured that this query was being executed via a loopback database link.
We also discussed the major difference between the two workarounds:
  • 1a: the query sees the mutating table as it existed prior to the start of the main transaction.
  • 1b: the query sees the mutating table as it is being mutated by the triggering DML statement. Essentially this workaround behaves (more...)

"Workarounds" for ORA-04091

Uncategorized
| Dec 13, 2011
In the previous post, we demonstrated running into the mutating table error. The example involved the well-known EMP table and a business rule that we are trying to implement: all departments with a Manager should also employ a Clerk. We started our implementation for that rule by writing a straight-forward 'after-row-insert' trigger on the EMP table.


  • The row trigger detects whenever a Manager is inserted (remember, we can only detect this with a row trigger, not with a statement trigger);
  • It then calls a stored procedure to which it supplies the department number (deptno-column value) of the inserted Manager;
  • (more...)

Some preliminaries

Uncategorized
| Nov 25, 2011
There are a few remarks to be made on DML event triggers. Just to be sure we are all at the same level on the playing field. The first one is that since these triggers fire as part of the execution of a DML statement, the trigger code is not allowed to execute any DDL (which causes an implicit commit) or (explicit) transactional control statements, such as commit, rollback, savepoint or rollback to savepoint. This restriction is true for both statement level as well as row level DML event triggers.


If you try to do so, Oracle will throw an (more...)

So what triggers are we talking about?

Uncategorized
| Nov 22, 2011
Here's an overview of the talk.


This post will deal with the first bullet: the scope, what triggers are we talking about? And what triggers are we not talking about. Then there will probably be a few posts on 'properties' of the triggers, most notably I will spend some time on explaining the infamous mutating table error. Next we move on to a high level classification of use-cases of triggers. And talk a bit about why some of these might be considered harmful. Finally we will explain, in detail, the one use case where triggers are the perfect means to (more...)

"Triggers considered harmful", considered harmful

I know I should finish posting here on TheHelsinkiDeclaration... But in the meantime I decided to start a new blog dedicated on database triggers. It will be like TheHelsinkiDeclaration a documentation of a presentation I have been giving recently entitled "Triggers considered harmful", considered harmful.

Starting this blog

Uncategorized
| Nov 22, 2011
So I finally was able to find some time to start this blog. I mentioned before that I might do this in the asktom discussion here. This blog will be about database triggers. Oracle database triggers that is. I'm a strong advocate of database triggers. Now hold your horses... I have been using database triggers ever since Oracle7 in the early nineties brought them to us. But I've been using them for a very specific reason, and have been presenting about this ever since. I will blog about this in a very similar way as I blogged about TheHelsinkiDeclaration, (more...)

The Helsinki Declaration (IT-version) 2011-08-05 05:40:00

Nice one:http://ora-00001.blogspot.com/2011/07/mythbusters-stored-procedures-edition.html

Using pipelined table function as the UI API object

In my previous post I've introduced you to an example WoD application page, and showed how the render-flow could be built in an Helsinki manner using a ref-cursor. The UI technology stack would call a stored procedure which in turn would return a ref-cursor. Inside the UI code stack all that needs to be done is fetch the rows from the ref-cursor (until %NOTFOUND), then close the cursor, and