Skip to Main Content

Breadcrumb

Question and Answer

Sergiusz Wolicki

Thanks for the question, naveen.

Asked: September 01, 2017 - 10:43 am UTC

Last updated: September 04, 2017 - 9:23 am UTC

Version: 11g r1

Viewed 10K+ times! This question is

You Asked

I ran sqlldr to import the data to table .i got the following error.

control file:

OPTIONS (SKIP=1)
LOAD DATA
INFILE '.\..\DATA\APPLICATION_MASTER.sql'
BADFILE '.\..\CONTROL\APPLICATION_MASTER\APPLICATION_MASTER'
DISCARDFILE '.\..\CONTROL\APPLICATION_MASTER\APPLICATION_MASTER'
TRUNCATE
INTO TABLE APPLICATION_MASTER
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
APP_ID "DECODE(:APP_ID,'(null)',0,TO_NUMBER(:APP_ID))" ,
APP_NAME ,
APP_DESCRIPTION ,
UPDATEDDATE "DECODE(:UPDATEDDATE,'(null)','',TO_DATE(:UPDATEDDATE,'yyyy/mm/dd:hh:mi:ss PM'))",
UPDATEDBY
)

error:
SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table APPLICATION_MASTER
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Please help me.

and we said...

The error says it all. The SQL*Loader control file specifies TRUNCATE, which means that SQL*Loader issues a TRUNCATE TABLE APPLICATION_MASTER statement before trying to load data into that table. The table has a unique or primary key constraint and this constraint is referenced by a foreign key in another table, e.g.:

CREATE TABLE some_other_table (... , some_column REFERENCES application_master(app_id))

Truncating a table with enabled foreign key constraints is only possible if the TRUNCATE TABLE statement includes the CASCADE clause and the offending foreign key constraints have the ON DELETE CASCADE option (the default is "on delete restrict"). SQL*Loader does not use the CASCADE clause.

Use the ALL_CONSTRAINTS view to generate statements to disable the offending foreign key (referential) constraints:

SELECT 'ALTER TABLE "'||owner||'"."'||table_name||'" DISABLE CONSTRAINT "'||constraint_name||'";'
  FROM all_constraints
 WHERE constraint_type = 'R'
   AND status = 'ENABLED'
   AND (r_owner, r_constraint_name) IN
       (SELECT owner, constraint_name
          FROM all_constraints
         WHERE owner = '<owner of APPLICATION_MASTER>'
           AND table_name = 'APPLICATION_MASTER'
           AND constraint_type IN ('P', 'U'));


Change DISABLE to ENABLE and re-enable the constraints after the load. If your loading strategy includes re-loading the child tables as well, TRUNCATE and reload the child (dependent) tables before re-enabling the constraints.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.