|
EXCEPTIONS INTO EXCEPTIONS
Introduction
As a DBA, you
want to use the advantages of the EXCEPTIONS
clause. You can use this clause to identify
duplication or any constraint violations and
delete these records. In this hands-on
exercise, your organization wants you to
separate your salesmen using the EXCEPTIONS
clause. Your job�s responsibilities dictate
that you should at least be informed of the
following basic fundamental subjects:
Using the
EXCEPTIONS table in a schema
Adding a
disabled constraint
Describing the
EXCEPTIONS table
Running the
UTLEXCPT.SQL script
Enabling a
disabled constraint
Dropping a
constraint
Commands:
ALTER TABLE
ADD
(CONSTRAINT
CHECK (DISABLE)
DESC
exceptions
SET ECHO
START %ORACLE_HOME%.sql
ALTER TABLE
ENABLE VALIDATE CONSTRAINT
EXCEPTIONS
INTO EXCEPTIONS
DELETE FROM
COMMIT
ALTER TABLE
DROP CONSTRAINT
Hands-on
In this
exercise you will learn how to use the
EXCEPTIONS table in your schema to separate
a group of rows with a specific constraint.
Also, we can use the EXCEPTIONS table to
detect the database integrity problems such
as data duplications, unique keys, check
constraints, etc.
Now let's connect to SQLPlus as the oracle
user.
SQL> CONNECT
oracle/learning
Add a
constraint
We
decided not to have any more salesmen in the
company. So, let's create a constraint not
to have any more SALESMEN.
SQL> ALTER TABLE
emp
ADD (CONSTRAINT ck_emp
CHECK (job <> 'SALESMEN') DISABLE)
/
We have to disable the constraint
since we have SALESMEN in the table.
Create an EXCEPTIONS table
Now, check to
see if we have the EXCEPTIONS table in our
schema.
SQL> DESC exceptions
If not, then create one. To do so, you
should run the utlexcpt.sql script. The
script is in the %ORACLE_HOME%sub-directory.
SQL>-- START %ORACLE_HOME%.sql
SQL> SET ECHO
OFF
SQL> START %ORACLE_HOME%.sql
SQL> SET ECHO ON
Alter a table
Now, enable
the CK_EMP constraint using the EXCEPTIONS
INTO EXCEPTIONS clause.
SQL> ALTER TABLE
emp
ENABLE VALIDATE CONSTRAINT ck_emp
EXCEPTIONS INTO EXCEPTIONS
/
Ignore the violation message.
Create a table
Now, create a table called
SALESMEN and then insert all of the records
into the SALESMEN table.
SQL> CREATE TABLE
salesmen
AS SELECT *
FROM emp
WHERE rowid IN (SELECT row_id
FROM exceptions)
/
Query all of the records from the SALESMEN
table.
SQL> SELECT * FROM salesmen
/
Remove records
Now, we can
delete all of the salesmen records, since
they have been stored in the SALESMEN table.
SQL> DELETE FROM
emp
WHERE rowid IN (SELECT row_id FROM
exceptions)
/
SQL> COMMIT
/
Query the EMP table.
SQL> SELECT * FROM
emp
/
Notice that there are no
salesmen.
Enable a
constraint
Now, we should be able to enable the CK_EMP
constraint.
SQL> ALTER
TABLE emp ENABLE VALIDATE
CONSTRAINT
ck_emp
/
From now on,
no salesmen can be entered in the EMP table.
Back to
original data
Drop the constraint and restore all of the
salesmen into the EMP table.
First, drop the constraint.
SQL> ALTER TABLE
emp DROP CONSTRAINT ck_emp
/
The CK_EMP constraint should be
dropped.
And now, put all the salesmen back into the
EMP table.
SQL> INSERT INTO
emp
SELECT * FROM salesmen
/
Query all the salesman employees in the EMP
table.
SQL> SELECT * FROM
emp
WHERE job = 'SALESMEN'
/
Drop the SALESMEN and EXCEPTIONS tables.
SQL> DROP
TABLE salesmen
/
SQL> DROP TABLE exceptions
/
Questions:
Q: What does
the EXCEPTIONS INTO EXCEPTIONS clause
perform in the ALTER TABLE statement?
Q: How do you
disable a constraint?
Q: How do you
enable a constraint?
Q: How do you
create the EXCEPTIONS table?
Q: Describe
the UTLEXCPT.SQL script.
Q: How do you
find duplicate records using the EXCEPTIONS
INTO EXCEPTIONS clause?
Q: How do you
drop a constraint?
Q: What do the
following SQL statements do?
SQL> ALTER
TABLE emp
ENABLE VALIDATE CONSTRAINT ck_emp
EXCEPTIONS INTO EXCEPTIONS
/ |
