| SQL*Loader Help. [message #226143] |
Thu, 22 March 2007 11:48  |
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 #226151 is a reply to message #226147] |
Thu, 22 March 2007 12:13  |
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 |
 |
Barbara Boehmer
Messages: 9097
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
