-
I'm running a SQL*Loader CTL script that is reading a fixed position ASCII file into multiple tables using the Direct Load method. Everything is working great except for the fact that all of my foreign key constraints are being disabled.
I've added the clause "REENABLE DISABLED_CONSTRAINTS EXCEPTIONS myexcepttable" so that it will reenable the constraints at the end, but it doesn't appear to be reenabling the constraints. Additionally, the documentation says that the log file will indicate which constraints have been disabled and reenabled, but my log file isn't reporting anything related to the constraints.
BTW..I'm running Oracle 8.1.6.
Anyone have any ideas?
Chris
-
Problem Description:
====================
You are going to perform a direct load and would like to use the REENABLE clause for disabled constraints.
You are not sure how to build an exceptions table in order to use that clause.
Search Words: =============
SQL*Loader, SQLLOADER Solution
Description: =====================
Here is an example on how to create an exceptions table:
create table dept2
(deptno char(4) primary key check (DEPTNO < 22),
dname char(20), loc char(20));
CREATING THE EXCEPTIONS TABLE:
create table except_table
(row_id char(18), owner char(20),
tablename char(20), constraint char(15));
SQL> select constraint_name, constraint_type, search_condition, status from dba_constraints where table_name = 'DEPT2';
CONSTRAINT_NAME C SEARCH_CONDITION STATUS
SYS_C001737 P ENABLED
SYS_C001738 C DEPTNO < 22 ENABLED
SAMPLE CONTROL FILE:
LOAD DATA INFILE *
TRUNCATE INTO TABLE DEPT2
REENABLE DISABLED_CONSTRAINTS EXCEPTIONS EXCEPT_TABLE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(DEPTNO , DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
STATUS OF CONSTRAINTS AFTER LOAD:
CONSTRAINT_NAME C SEARCH_CONDITION STATUS
SYS_C001737 P ENABLED
SYS_C001738 C DEPTNO < 22 DISABLED
SQL> select rowid, deptno, dname, loc from dept2;
ROWID DEPT DNAME LOC
00000071.0000.0005 12 RESEARCH SARATOGA
00000071.0001.0005 10 ACCOUNTING CLEVELAND
00000071.0002.0005 11 ART SALEM
00000071.0003.0005 13 FINANCE BOSTON
00000071.0004.0005 21 SALES PHILA.
00000071.0005.0005 22 SALES ROCHESTER
00000071.0006.0005 42 INT'L SAN FRAN
SQL> select * from EXCEPT_TABLE;
ROW_ID OWNER TABLENAME CONSTRAINT
00000071.0005.0005 BLS DEPT2 SYS_C001738 00000071.0006.0005 BLS DEPT2 SYS_C001738
In this case the exceptions table has a listing of the rows that have violated the check constraint. The you will need to manually delete the rows and reenable the constraint.
For example:
SQL> DELETE FROM DEPT2 WHERE ROWID = '00000071.0005.0005' AND ROWID = '00000071.0006.0005';
SQL> ALTER TABLE DEPT2 ENABLE CONSTRAINT 'SYS_C001738'
