Solved

EXCEPTIONS INTO IN ORACLE

Posted on 2004-09-17
1
1,369 Views
Last Modified: 2012-06-21
Hi All,
        I have created a table in oracle 9.2.0.5 table name Test columns Id, name.  I have anothe table Audit_test

CREATE TABLE AUDIT_TEST (
   ROW_ID ROWID,
   OWNER VARCHAR2(30),
   TABLE_NAME VARCHAR2(30),
CONSTRAINT VARCHAR2(30));

I have create primay key on the test table like this

ALTER TABLE TEST
ADD CONSTRAINT PK_TEST_ID
PRIMARY KEY(TEST_ID)
EXCEPTIONS INTO AUDIT_TEST;

After that  I have tried to insert into Test table with the same Id  twice like below
INSERT INTO TEST(TEST_ID, TEST_NAME)
VALUES(1,'TEST_1');

It fails to insert into the record but there is no record in Audit_Test. The way I understood the EXCEPTIONS INTO is when ever there is an exception in inserts the record into AUDIT_TEST table but this is not happing.

Can you let me know what does EXCEPTIONS INTO  do and where I am doing wrong.

Thanks,
Cavaturu


0
Comment
Question by:cavaturu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
ID: 12086504
well, the "EXCEPTIONS INTO" does not work this way....

It only works when you have an EXISTING condition (in which you have a table loaded with duplicate data).

when you try to enable the UNIQUE or PK, then the exceptioned rows go to audit table.

in order for you to make this work.
you have to disable the PK first.

then insert into a bunch of duplicates into the TEST table.
then enable the PK.

then you will see all the exceptioned rows in AUDIT_TEST Table.

assumeing you have issued

ALTER TABLE TEST
ADD CONSTRAINT PK_TEST_ID
PRIMARY KEY(TEST_ID)
EXCEPTIONS INTO AUDIT_TEST;
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question