-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
thanks for clearing my doubt
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|