Re: SQL*Loader ignoring constraints

From: <aharris1_at_my-deja.com>
Date: Wed, 20 Dec 2000 18:25:23 GMT
Message-ID: <91qti9$dml$1_at_nnrp1.deja.com>

I have created a simple example that illustrates my problem. I can only recreate my problem using Oracle 8.1.5 or 8.1.6. I tried the same test case on Oracle 8.0.5, and the foreign key constraints were always recognized.

Here is the SQL to create two tables with the necessary constraints. It also inserts two records into the master table.

CREATE TABLE table1
(pd VARCHAR2(2) NOT NULL);
ALTER TABLE table1 ADD (CONSTRAINT pk1 PRIMARY KEY (pd)); CREATE TABLE table2 (

       IM                   VARCHAR2(3) NOT NULL,
       PD                   VARCHAR2(2) NULL,
       LNAME                VARCHAR2(20) NULL,
       OFFICE_SYM           VARCHAR2(4) NULL,
       UPDATED_BY           VARCHAR2(30) NULL,
       UPDATED_DT           DATE NULL

);
ALTER TABLE table2

       ADD ( CONSTRAINT PK_table2 PRIMARY KEY (IM) ) ; ALTER TABLE table2

       ADD  ( CONSTRAINT FK_table2
              FOREIGN KEY (PD)
                             REFERENCES table1 ) ;

INSERT INTO table1 VALUES ('LN');
INSERT INTO table1 VALUES ('LF');
COMMIT; Here is the trigger that exists on table2:

CREATE OR REPLACE TRIGGER table2_bui_trg

        BEFORE INSERT OR UPDATE ON table2 FOR EACH ROW DECLARE
V_SQLCODE NUMBER;
V_SQLERR VARCHAR2(100); BEGIN
   :new.updated_by := USER;
   :new.updated_dt := SYSDATE;

EXCEPTION

	WHEN OTHERS THEN
	V_SQLCODE := SQLCODE;
	V_SQLERR  := SUBSTR(SQLERRM,1,100);

END; Here is the control file to use for SQL*Loader:

OPTIONS (ERRORS=1000)
LOAD DATA
INFILE 'D:\table2.txt'
INTO TABLE table2
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(IM           position(2:4),
 LNAME        position (8:27),
 OFFICE_SYM   position(35:38),
 PD           CHAR "substr(:OFFICE_SYM,1,2)"

)

Here is the data in table2.txt to be loaded with SQL*Loader:

 B2C   HERRING, LENA              LNXC
 B2D   HERRING, LENA              LNXC
 E3A   RIOS, RUBY L.         945  LDAK
 E3B   RIOS, RUBY L.         945  LDAK
 FAA   JOHNSON, LIBBY             LFLL-2
 FAB   JONES, MARGARET C.         LFLA

After creating the two tables and the trigger, SQL*Loader loads all rows in the .txt file into table2. This is incorrect because the lines containing 'LD' as the first two characters in the office symbol should be written to the .bad file. The 'LD' value does not exist in table1.

Please let me know if you are unable to recreate this problem using Oracle 8.1.6.

Thanks,
Angela Harrison

In article <91o9g8$907$1_at_nnrp1.deja.com>,   Mike Krolewski <mkrolewski_at_rii.com> wrote:
> In article <91o3sb$3e7$1_at_nnrp1.deja.com>,
> aharris1_at_my-deja.com wrote:
> > When I load a table with foreign key constraints and a before insert
> > trigger with SQL*Loader, my foreign key constraints are ignored. The
> > end result is a detail table that contains invalid rows (where some
> > column values do not exist in the master table). If I drop the
 trigger
> > and just load with SQL*Loader, the foreign key constraints are
> > recognized. Any ideas why this is happening? The trigger simply
> > populates the last updated date and user columns.
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >

>

> Display the control file, a row or two of sample data, and the
> corresponding rows of the table with the invalid foreign keys. If you
> have stated the problem and evidence correctly, this is of course
> impossible. Something in your processing is incorrect.

>

> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers

>

> Sent via Deja.com
> http://www.deja.com/

>

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 20 2000 - 19:25:23 CET