Re: Primary Key Exceptions ??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/09
Message-ID: <3505046d.4863242@192.86.155.100>#1/1

A copy of this was sent to Joe R Jurgena <jjurgena_at_shell.clark.net> (if that email address didn't require changing) On 7 Mar 1998 18:18:44 GMT, you wrote:

exceptions for constraints are captured only when the constraint is enabled. It is used as a 'debugging' tool of sorts -- I want to put a primary key on an existing table but it won't let me, why not? The exceptions into clause lets you identify the rows that violate a constraint in an existing table.

Thats why it doesn't work on the create, exceptions into is only valid for existing objects and existing data.

It doesn't caputure failed attempts at runtime, only when initially created

It works like this (note the structure of the exceptions table is in the file $ORACLE_HOME/rdbms/admin/utlexcpt, you can rename it but it needs all of the columns as below)

SQL> @/user2/oracle73/rdbms/admin/utlexcpt Table created.
SQL> l
  1 create table exceptions(row_id rowid,

  2                     owner varchar2(30),
  3                     table_name varchar2(30),
  4*                    constraint varchar2(30))

SQL> create table tst( id varchar2(10) ); Table created.

SQL> insert into tst values ( 1);
1 row created.

SQL> insert into tst values ( 1);
1 row created.

SQL> insert into tst values (2);
1 row created.

SQL> alter table tst add constraint tst_pk primary key(id)   2 exceptions into exceptions;
alter table tst add constraint tst_pk primary key(id) *
ERROR at line 1:
ORA-02437: cannot enable (SCOTT.TST_PK) - primary key violated

SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME CONSTRAINT

------------------ ---------------- --------------- -------------------------
00000A4C.0000.0004 SCOTT            TST             TST_PK
00000A4C.0001.0004 SCOTT            TST             TST_PK

SQL> select * from tst where rowid in ( select row_id from exceptions ); ID

---

1
1

>How do you set up and use Constraint Exceptions?
>

 [snip]
>
>Thanks
>Joe
>Oracle Release 703020200
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Mar 09 1998 - 00:00:00 CST