exceptions clause

hi

i tried using the exceptions clause so as to redirect all the invalid queries into the exceptions table . i tried the following steps.
step 1:
SQL> @E:\oracle\ora90\rdbms\admin\utlexcpt.sql

Table created.

SQL> desc exceptions;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)

SQL> alter table hr.emp
2 enable validate constraint emp_deptno_fk
3 exceptions into system.exceptions;

Table altered.

SQL> set linesize 1000

SQL> select * from hr.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 3899 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20

7788 nishant manager 7698 17-NOV-01 5000 0 30
333 20

18 rows selected.

SQL> update hr.emp set deptno=90 where empno=333;
update hr.emp set deptno=90 where empno=333
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPTNO_FK) violated - parent key not found

SQL> select row_id from exceptions;

no rows selected

what could be the problem

Juat wild guessing...

Maybe you have an existing exception table in your schema ?

Did you try "select row_id from system.exceptions;" ?

CVM.

Ser :

I think, u have misread or confused with the Exception Clause.

It says, when Exception is raised during ENABLE clause, then the ROWIDS are populated into EXCEPTION table.

Its not when you Issue SQL DML commands.

PS Below Demo, Hope it will clear your doubt.

Code:

TEST_ABHAY> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- -------------------- EXCEPTION_TABLE TABLE TEST_EXCEPTION TABLE TEST_ABHAY> desc TEST_EXCEPTION Name ----------------------------------------------------------------------------------------------------------------------- ID NAME TEST_ABHAY> desc EXCEPTION_TABLE Name ----------------------------------------------------------------------------------------------------------------------- ROW_ID OWNER TABLE_NAME CONSTRAINT TEST_ABHAY> select * from TEST_EXCEPTION; ID NAME -------------------- -------------------- 1 Abhay 2 Bhat TEST_ABHAY> select * from EXCEPTION_TABLE; no rows selected TEST_ABHAY> alter table TEST_EXCEPTION Disable Constraint PK_TEST; Table altered. TEST_ABHAY> Select Column_Name from dba_cons_columns where constraint_Name='PK_TEST'; COLUMN_NAME ------------------------------------------------------------------------------------------------------------------------ ID TEST_ABHAY> update TEST_EXCEPTION set ID=1 where ID=2; 1 row updated. TEST_ABHAY> commit; Commit complete. TEST_ABHAY> alter table TEST_EXCEPTION enable validate constraint PK_TEST exceptions into EXCEPTION_TABLE; alter table TEST_EXCEPTION enable validate constraint PK_TEST exceptions into EXCEPTION_TABLE * ERROR at line 1: ORA-02437: cannot validate (ABHAY.PK_TEST) - primary key violated TEST_ABHAY> select * from EXCEPTION_TABLE; ROW_ID ------------------------------------------------------------------------------------------------------------------------ OWNER TABLE_NAME CONSTRAINT ------------------------------ ------------------------------ ------------------------------ AAABbrAADAAAAADAAB ABHAY TEST_EXCEPTION PK_TEST AAABbrAADAAAAADAAC ABHAY TEST_EXCEPTION PK_TEST TEST_ABHAY>

Abhay.

thanks for clearing my doubt