I'm developing a Java EE application that utilizes Hibernate/JPA. Some of the data I want to use comes from another Business unit's spreadsheet that I need to periodically (re)load into 5 Oracle tables, overwriting any existing data. I'm using a shell script that kicks off 5 sql*loader control files, where each control file specifies filler fields to skip because the excel file is not normalized.

OPTIONS(skip=2)
load data
REPLACE
--#########################################
into table TABLE_NAME
 fields terminated by "," optionally enclosed by '"'       
TRAILING NULLCOLS
  (
ACCOUNT_ID ,
REST_STOP_ID ,
DESCRIPTION FILLER ,
GROUP FILLER ,
FUNCTION  ,
JOB_ID "substr(:JOB_ID, 0, INSTR(:JOB_ID,' - ',1))", 
<35 other fields>
etc...

First time import of this data into empty tables using SQL*Loader and a csv file from this spreadsheet works fine. But then any subsequent run sql*loader fails because either control file option:

when using REPLACE:

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword)
**ORA-02292:** integrity constraint (SCHEMA.FK5BA979794B0A176A) violated - child record found

when using TRUNCATE:

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

I've also tried reordering which the tables are populated but no luck.

I've also tried disabling constraints

ALTER TABLE TABLE_NAME DISABLE CONSTRAINT SYS_C0090398


ORA-02297: cannot disable constraint (SCHEMA.SYS_C0090398) - dependencies exist
(0 rows affected)

My work around so far is to drop all the tables restart my application and have Hibernate recreate my 5 tables and then run my sql*loader script. Right now I'm in development so not a big deal. But when we go to production, I can't keep restarting and creating tables. Actually when I go to production, I'll create the tables outside of hibernate but right now I have the persistence option hibernate.hbm2ddl.auto set to update.

I'm looking for an elegant (semi)automatic solution to periodically repopulate these 5 tables with data provided to me in the csv/Excel file.

migrated from stackoverflow.com Sep 22 '14 at 10:58

This question came from our site for professional and enthusiast programmers.

  • There is no option to disable referential integrity within sql loader and referential integrity will stop you from using a direct method of loading. Have you considered disabling referential constraints prior to loading and then re-enable? Probably the best approach is to load sequentially from parent to child according to referential constraints (with or without disabling constraints). – Patrick Sep 8 '14 at 15:31
  • I have not tried disabling constraints. How do you do that, in the Control file?? I'm developing web application using JPA/Hibernate which automatically created my tables and relationships, btw. – jeff Sep 8 '14 at 15:40
  • Provided an example in my answer. – Patrick Sep 8 '14 at 16:00
  • You probably need a two step process, 1. Load spreadsheet into a staging table (e.g. STG) with a unique identifier. 2. Once spreadsheet is loaded into STG, you should run either a merge into main table or DELETE (children first then parent) then INSERT and commit – Raj May 18 '16 at 12:13

For reusable data loads, I prefer defining static external tables that read from CSV files. You'll need an Oracle DIRECTORY object to read the file.

This example creates a five-column external table called FOO_TAB that reads the FOO_TAB.CSV file from the FOO_DIR directory.

CREATE TABLE foo_tab
(
   a1   VARCHAR2(4000),
   a2   VARCHAR2(4000),
   a3   VARCHAR2(4000),
   a4   VARCHAR2(4000),
   a5   VARCHAR2(4000),
)
ORGANIZATION EXTERNAL
(
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY foo_dir
   ACCESS PARAMETERS
   (
      RECORDS DELIMITED BY newline
      BADFILE 'foo_tab.bad'
      DISCARDFILE 'foo_tab.dis'
      LOGFILE 'foo_tab.log'
      SKIP 1 /* first row just has column headers */
      FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
      LTRIM
      MISSING FIELD VALUES ARE NULL
      (
         a1   CHAR(4000),
         a2   CHAR(4000),
         a3   CHAR(4000),
         a4   CHAR(4000),
         a5   CHAR(4000),
      )
   )
   LOCATION (foo_dir:'foo_tab.csv')
)
REJECT LIMIT UNLIMITED;

The only way is to disable the referential constraints before you run the loader. Then enable them afterwards - hoping the new data is consistent.

There is no option to disable referential integrity within sql loader and referential integrity will stop you from using a direct method of loading.

Have you considered disabling referential constraints prior to loading and then re-enable?

The best approach is to load sequentially according to referential constraints (with or without disabling constraints).

Example

Here, child_tab_fk1 will be a foreign key constraint associated with the parent table, parent_tab.

disable constraints

 ALTER TABLE parent_tab DISABLE CONSTRAINT pk_parent;
 ALTER TABLE child_tab DISABLE CONSTRAINT child_tab_fk1;

load parent table, parent_tab, which has no referential constraints

load child table, child_tab, which has referential constraint to the parent table, parent_tab.

enable constraints

ALTER TABLE parent_tab ENABLE CONSTRAINT pk_parent;
ALTER TABLE child_tab ENABLE CONSTRAINT child_tab_fk1;

Addendum

If referential constraint errors are still being thrown while trying to sequence these table loads, this means that the table loading sequence is not exactly correct yet (deferential to the referential integrity).

Also, direct loads are incompatible with enabled referential constraints (see here documentation). Thus, perhaps you omitted disabling a referential constraint with a direct load.

I would start with parent tables not having foreign key constraints and progressively add tables to your approach (it is very hard to throw a referential constraint error when a table does not have a foreign key).

Next, I would consider procedurally disabling/enabling foreign key constraints and then schedule this activity to occur before and after this chron job.

  • I'll try reordering and see if I can get things to load. – jeff Sep 8 '14 at 15:42
  • reordering didn't work. all those alter statements seem like a pain since I didn't explicitly create them, Hibernate/JPA created them. And then I still don't know how to incorporate the alter table statement. Doesn't look like I can put them in the control file. I'm trying to automate the data import by using unix CRON to schedule executing a ksh unix shell script which runs sql loader using 5 separate control files. – jeff Sep 8 '14 at 17:05

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.