exceptions clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: exceptions clause

Hybrid View

  1. #1
    Join Date
    Apr 2003
    Posts
    46

    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

  2. #2
    Join Date
    Mar 2003
    Location
    Switzerland
    Posts
    1
    Juat wild guessing...

    Maybe you have an existing exception table in your schema ?

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

    CVM.

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  4. #4
    Join Date
    Apr 2003
    Posts
    46
    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
  •  



Click Here to Expand Forum to Full Width