Hands-On 19 (EXCEPTIONS INTO EXCEPTIONS)

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%\rdbms\admin\utlexcpt.sql

ALTER TABLE ENABLE VALIDATE CONSTRAINT

   EXCEPTIONS INTO EXCEPTIONS

DELETE FROM

COMMIT

ALTER TABLE DROP CONSTRAINT

-- Hands-On 19 (EXCEPTIONS INTO EXCEPTIONS)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a30
col segment_name format a40
col file_name format a50
col extent_management format a20
col username format a10
col member format a50

col used format a4
col index_name format a15
col table_name format a15
col monitoring format a10

pause

--Start

CLEAR SCR

-- 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.

pause


CONNECT iself/schooling

pause

CLEAR SCR
-- We decided not to have any more salesmen 
-- in the company. So, let's create a constraint 
-- not to have any more SALESMEN.

pause


ALTER TABLE emp 
ADD (CONSTRAINT ck_emp 
CHECK (job <> 'SALESMEN') DISABLE)
/

-- We have to disable the constraint since we
-- have SALESMEN in the table. 

pause

CLEAR SCR
-- Now, check to see if we have the EXCEPTIONS table
-- in our schema.

pause


DESC exceptions

pause

CLEAR SCR
-- If not, then create one. To do so, you should
-- Run the utlexcpt.sql script. The script is in the 
-- %ORACLE_HOME%\rdbms\admin sub-directory.

pause


--\/-- START %ORACLE_HOME%\rdbms\admin\utlexcpt.sql
SET ECHO OFF
START %ORACLE_HOME%\rdbms\admin\utlexcpt.sql
SET ECHO ON

pause

CLEAR SCR
-- Now, enable the CK_EMP constraint using the EXCEPTIONS
-- INTO EXCEPTIONS clause.

pause


ALTER TABLE emp 
ENABLE VALIDATE CONSTRAINT ck_emp 
EXCEPTIONS INTO EXCEPTIONS
/

-- Ignore the violation message.

pause

CLEAR SCR
-- Now, create a table called SALESMEN and then insert
-- all of the records into the SALESMEN table.

pause


CREATE TABLE salesmen
AS SELECT * 
FROM emp 
WHERE rowid IN (SELECT row_id 
FROM exceptions)
/

pause


CLEAR SCR
-- Query all of the records from the SALESMEN table.

pause


SELECT * FROM salesmen
/

pause

CLEAR SCR
-- Now, we can delete all of the salesmen records, since 
-- they have been stored in the SALESMEN table.

pause


DELETE FROM emp
WHERE rowid IN (SELECT row_id FROM exceptions)
/

COMMIT
/

pause

CLEAR SCR
-- Query the EMP table.

pause


SELECT * FROM emp
/

-- Notice that there are no salesmen.

pause


CLEAR SCR
-- Now, we should be able to enable the CK_EMP
-- constraint.

pause


ALTER TABLE emp ENABLE VALIDATE CONSTRAINT ck_emp
/

-- From now on, no salesmen can be entered in 
-- the EMP table.

pause

CLEAR SCR
-- Drop the constraint and restore all of the salesmen
-- into the EMP table.

-- First, drop the constraint.

pause


ALTER TABLE emp DROP CONSTRAINT ck_emp
/

-- The CK_EMP constraint was dropped.

pause


CLEAR SCR
-- And now, put all the salesmen back into the EMP table.

pause

INSERT INTO emp
SELECT * FROM salesmen
/

pause

CLEAR SCR
-- Query all the salesman employees in the EMP table.

pause


SELECT * FROM emp
WHERE job = 'SALESMEN'
/

pause

CLEAR SCR
-- Drop the SALESMEN and EXCEPTIONS tables.

pause

DROP TABLE salesmen
/

DROP TABLE exceptions
/

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause