Re: SQL*Loader ignoring constraints

Hi!

I get the same result as you in 8.1.5.0.0 on Windows98 Personal Oracle. A query in SQLPlus afterwards shows the result.

Looking forward to the explanation :-||

Frank

<aharris1_at_my-deja.com> wrote in message news: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 Tue Jan 09 2001 - 21:38:25 CET