iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 20

"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:

   DBA-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
/