|
"There are
people in the world so hungry, that God cannot appear to them
except in the form of bread." - Mahatma Gandhi (1869-1948) |
Read
first then play the video:
DBA019(VIDEO)-EXCEPTIONS
INTO EXCEPTIONS
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 iself user.
SQL> CONNECT iself/schooling
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
/
|
"It has become
appallingly obvious that our technology has exceeded our
humanity." - Albert Einstein (1879-1955) |
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
/
|