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

Thread: EXCEPTIONS INTO

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    Hi All

    Whats Exception Into clause? How Am I able to use it? How it's used to identify check constraint

    Amol

  2. #2
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,
    Exception into clause is used while changing the constraints status to enable validate with duplicate data in the table.After this clause is added all the duplicate data will go in this exception table.

    Eg.

    Create this table

    create table exceptions(row_id rowid,
    owner varchar2(30),
    table_name varchar2(30),
    constraint varchar2(30));

    ALTER TABLE emp
    ENABLE VALIDATE CONSTRAINT pk_emp
    EXCEPTIONS INTO exceptions;

    Now join this table row_id column with emp tables rowid to get the details of the records.

    Hope it helps,
    Take Care,
    Nishant.

  3. #3
    Join Date
    Dec 2000
    Posts
    255
    Thanks Nishant

    Does it mean that if I enter any record in EMP with duplicate empno then it will add that rowid into EXCEPTIONS table ?

    Amol

  4. #4
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,

    Before running the EXCEPTIONS INTO

    1.The table contains duplicate records.
    2.The status of constraint is not ENABLE VALIDATE (only then u can insert duplicate records)

    After running the EXCEPTION INTO clause in ALTER statement

    1.All the duplicate records will go in the exceptions table.
    2.After deleting all these records from you main table you can bring the constraint to ENABLE VALIDATE status.

    It has nothing to do with insertion of new records.

    If you want that previous data to remain duplicate but from new transactions it should check by the constraint then bring the constraint into ENABLE NOVALIDATE state.

    Hope its clear now.
    Take Care,
    Nishant.

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