Briefly describe how you can use the exceptions table to find out invalid data

Briefly describe how you can use the exceptions table

This preview shows page 8 - 11 out of 17 pages.

05)Briefly describe how you can use the exceptions table to find out invalid data? (10 Points) ANSWER: Using the EXCEPTIONS Table Create the EXCEPTIONS table by running the utlexcpt1.sql script Execute the ALTER TABLE statement with EXCEPTION OPTION Use subquery on EXCEPTIONS to locate rows with invalid data Recitify the errors Re-execute ALTER TABLE to enable the constraint TO IDENTIFY ROW VIOLATION: The EXCEPTIONS clause helps to identify any row that violates an enabled constraint. Use the following procedure to detect constraint violations, rectify them, and re-enable a constraint: If the EXCEPTIONS is not already created, run the utlexcpt1.sql script: SQL>@?/rdbms/admin/utlexcpt1 SQL>DESCRIBE exceptions Name Null? Type
Background image
--------------------- --------- ------------------------- ROW_ID ROWID OWNER VARCHAR2 (30) TABLE_NAME VARCHAR2 (30) CONSTRAINT VARCHAR2 (30) The exact name and location of the utlexcpt1.sql script is specific to the operating system. Execute the ALTER TABLE command using the EXCEPTIONS clause. SQL> ALTER TABLE hr.employee EBABKE VAKUDATE CONSTRAINT employee_dept_id_fk EXCEPTIONS INTO system.exceptions; ALTER TABLE hr.employee ORA-02298: cannot enable (hr.EMP_DEPT_FK) ·parent keys not found If the EXCEPTIONS table is not qualified with the name of the owner, it must belong to the owner of the table being altered. Rows are inserted into the EXCEPTIONS table. If you are rerunning the command, truncate the EXCEPTIONS table to remove all existing rows. Identify invalid data by using a subquery on the EXCEPTIONS table: SQL> SELECT rowid, id, last_name, dept_id FROM hr.employee WHERE ROWID in (SELECT row_id FROM exceptions) FOR UPDATE; ROWID ID LAST_NAEM DPET_ID --------------------------------- ------- ------------------ ------------- AAAAeyAADAAAA1AAA 1003 Pirie 50 1 row selected Correct the errors in the data: SQL> UPDATAE hr.employee SET id=10 WHERE rowid=’AAAAeyAADAAAA1AAA’; 1 row processed SQL> COMMIT; Statement processed Identify invalid data by using a subquery on the EXCEPTIONS table: SQL> SELECT rowid, id, last_name, dept_id FROM hr.employee WHERE ROWID in (SELECT row_id FROM exceptions) FOR UPDATE; ROWID ID LAST_NAEM DPET_ID --------------------------------- ------- ------------------ ------------- AAAAeyAADAAAA1AAA 1003 Pirie 50
Background image
Background image
Image of page 11

You've reached the end of your free preview.

Want to read all 17 pages?

  • Spring '16
  • administrator, Foreign key, Constraint, resource limits

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture