Understanding...

I was reading around the web and read an article that was talking about the EXCEPTIONS INTO clause – a feature whereby when you enable a constraint, Oracle will record the rows that do not pass the constraint check into an exceptions table. It works with unique, primary key, check and so on. The article seemed to be indicating “exceptions into was broken” – but it seemed more likely to me, that this was a case of not understanding how it actually works.

So, I contacted the author and we had a back and forth email exchange. Basically, they came back with “the documentation isn’t good enough, this could really hose someone up”. Well, I’d like to do a poll. I’m curious. What I’d like you to do is read the documentation, the SQL Reference guide had this to say:

exceptions_clause

Specify a table into which Oracle places the rowids of all rows violating the constraint. If you omit schema, then Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, then Oracle assumes that the table is named EXCEPTIONS. The exceptions table must be on your local database.
----------------------------------------------

And the Admin Guide had more. Now, after reading that (don’t run the example! Just answer the poll based on the reading or your past knowledge of using this command), can you fill in the blanks of this example? We insert two rows into T with the same value – when we enable a primary key with exceptions into, how many rows will be “exceptional”?

ops$tkyte@ORA10GR1> create table t ( x int );
Table created.
ops$tkyte@ORA10GR1> create table exceptions(row_id rowid,
2 owner varchar2(30), table_name varchar2(30),
3 constraint varchar2(30));
Table created.
ops$tkyte@ORA10GR1> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA10GR1> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA10GR1> alter table t add constraint t_pk primary key(x)
2 EXCEPTIONS INTO exceptions;
alter table t add constraint t_pk primary key(x)
ERROR at line 1:
ORA-02437: cannot validate (OPS$TKYTE.T_PK) - primary key violated
ops$tkyte@ORA10GR1> select count(*) from exceptions;

COUNT(*)
----------
?????

Warning: The following poll will display to you advertising which is totally unrelated to this site:

After reading the above example, how many rows will be in the Exceptions table?
	0
	1
	2
	More Than 2
Free polls from Pollhost.com

I give a presentation on “Things you Know”, sort of a fun keynote type address. I used part of it the other day in the things you know blog – I’m going to use the opening slide here as it is very relevant to this. Actually, the opening slide comes from asktom – the writer (Gabe) was mentioning that unless you took the time to understand the implementation of a snippet of code another reader had posted – bad things would happen, it was a bug waiting to happen. The slide I have looks like this at first:

Quote of the Day….

Not that anyone should care by now (we are in 10g times after all) ... but that "Hierarchy" package presented last year as a 8i method for doing what sys_connect_by_path does is a bug waiting to happen. One needs to understand how it works in order to use it safely.

I thought – how perfect, that sums up a lot of things. I use this slide in a build – the last line morphs into:

One needs to understand how Oracle works in order to use it safely.

But I thought – hey, that makes Oracle sound “special”, so the slide morphs into:

One needs to understand how ANYTHING works in order to use it safely.

There we go. Unless you understand a command, have read the documentation, have tested it, have tried it – you probably don’t get it. Understanding comes from

• Reading
• Testing
• Implementing

To assume something is broken because it didn’t work the way you thought it should is wrong. And, given this person had at least as many years experience with Oracle as I did, goes to show – if you want to provide advice, back it up, show what you say is true. We now have another piece of material on this thing called the internet with a myth on it. Because someone had never used a command before, didn’t read the documentation on it, and assumed how it should work – given the problem he had to solve. Recipe for disaster.