Home » RDBMS Server » Server Utilities » SQL*Loader Help.
SQL*Loader Help. [message #226143] Thu, 22 March 2007 11:48 Go to next message
mystical112
Messages: 2
Registered: March 2007
Junior Member
Hi,
I'm having some problems inserting my data into the tables.

Quote:
CREATE TABLE Employee_2 (EID char(9), Name varchar2(20), Salary number(7,2), MID char(9), PRIMARY KEY (EID), FOREIGN KEY (MID) REFERENCES Employee_2 (EID));


to create the table then:

Quote:

LOAD DATA
INFILE *
REPLACE INTO TABLE Employee_2
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(EID CHAR, Name, Salary, MID CHAR)
BEGINDATA
e01|Wilson|"53000"
e04|Loftus|"41000"
e07|Green|"48000"



to insert the employee data without the managers then:
Quote:

LOAD DATA
INFILE *
APPEND INTO TABLE Employee_2
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(EID CHAR, Name, Salary, MID)
BEGINDATA
e02|Smith|48000|e01
e03|Jones|38000|e01
e05|Wang|4000|e04
e06|Smith|45000|e04
e08|Fox|49000|e04
e09|Wang|41000|e04
e10|Fox|32000|e01
e11|Phillips|33000|e07
e12|Liu|27000|e07



to insert the rest. I get a "ORA-02291: integrity constraint (USER.SYS_C00405826) violated - parent key not found" error for all the entries except e12 which gets accepted into the
table.

And if i dont split the manager and employee data, i still get the same error execpt its for all the entries.

I don't know what im doing wrong.

[Updated on: Thu, 22 March 2007 11:49]

Report message to a moderator

Re: SQL*Loader Help. [message #226147 is a reply to message #226143] Thu, 22 March 2007 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
IMO, you have a chicken & the egg problem.
It likely would be best to load the data with CONSTRAINTS=NO;
otherwise you need to order the data in the load file so that the manager is always loaded before the staff.
But you'll have a problem with getting the 1st record in the table.
Re: SQL*Loader Help. [message #226151 is a reply to message #226147] Thu, 22 March 2007 12:13 Go to previous messageGo to next message
mystical112
Messages: 2
Registered: March 2007
Junior Member
If I put the manager data first I still end up with the same error, and it only accepts the data with NULL for managers.


Is it possible to disable constraints from SQL*LOADER or can I only do it from SQLPLUS?


This is an assignment for school, the PROF told me that I would have to load the PARENT data first (so the table being referenced) I did try that which did not work. Any other ideas?...
Re: SQL*Loader Help. [message #226547 is a reply to message #226151] Sat, 24 March 2007 21:44 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You must be doing something different than what you are describing, because it works for me, as shown below.

-- mgr.ctl:
LOAD DATA
INFILE *
REPLACE INTO TABLE Employee_2
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(EID CHAR, Name, Salary, MID CHAR)
BEGINDATA
e01|Wilson|"53000"
e04|Loftus|"41000"
e07|Green|"48000"



-- emp.ctl:
LOAD DATA
INFILE *
APPEND INTO TABLE Employee_2
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(EID CHAR, Name, Salary, MID)
BEGINDATA
e02|Smith|48000|e01
e03|Jones|38000|e01
e05|Wang|4000|e04
e06|Smith|45000|e04
e08|Fox|49000|e04
e09|Wang|41000|e04
e10|Fox|32000|e01
e11|Phillips|33000|e07
e12|Liu|27000|e07



SCOTT@10gXE> CREATE TABLE Employee_2
  2    (EID	 char(9),
  3  	Name	 varchar2(20),
  4  	Salary	 number(7,2),
  5  	MID	 char(9),
  6  	PRIMARY KEY (EID),
  7  	FOREIGN KEY (MID) REFERENCES Employee_2 (EID))
  8  /

Table created.

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=mgr.ctl

SCOTT@10gXE> SELECT * FROM employee_2
  2  /

EID       NAME                     SALARY MID
--------- -------------------- ---------- ---------
e01       Wilson                    53000
e04       Loftus                    41000
e07       Green                     48000

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=emp.ctl

SCOTT@10gXE> SELECT * FROM employee_2
  2  /

EID       NAME                     SALARY MID
--------- -------------------- ---------- ---------
e01       Wilson                    53000
e04       Loftus                    41000
e07       Green                     48000
e02       Smith                     48000 e01
e03       Jones                     38000 e01
e05       Wang                       4000 e04
e06       Smith                     45000 e04
e08       Fox                       49000 e04
e09       Wang                      41000 e04
e10       Fox                       32000 e01
e11       Phillips                  33000 e07
e12       Liu                       27000 e07

12 rows selected.

SCOTT@10gXE> 



Previous Topic: Oracle impdp
Next Topic: error while exp utility
Goto Forum:
  


Current Time: Sat Dec 10 22:48:18 CST 2016

Total time taken to generate the page: 0.07518 seconds