Home » RDBMS Server » Server Utilities » How to get parent table column reference and put in child table while loding data using sql loader (Oracle-11.2 - windows)
| How to get parent table column reference and put in child table while loding data using sql loader [message #596538] |
Tue, 24 September 2013 08:01  |
 |
sureshpediveeti
Messages: 3
Registered: September 2013
Location: delhi
|
Junior Member |
|
|
Hi, I am new to SQL loader utility.
I have csv file which having parent and child related data.i need to load parent data in to parent table and get parent reference id and
store in child table with child data. i am not able to find how to get parenet refernce id using control file.
In my csv file i have 2 parent name rows. I need to create one A record in parent table and get that parent primary(P_ID) for 'A' record
and put into child table for c_name test1 and test2 records.
Please suggest any solution. Please refere.
mydata.csv
---------------
P_Name C_Name
------ ------
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;
1. table parent (P_ID, P_Name)
2. table child (C_ID, C_Name, P_ID)
i need data bello way.
Parent Table data:
P_ID P_Name
----- ------
1 A
2 B
3 C
4 D
Child Table data:
C_ID C_Name P_ID
---- ------ ----
1 test1 1
2 test2 1
3 test3 2
4 test4 2
5 test5 3
6 test5 3
7 test6 4
8 test6 4
My controll file
-------------------
LOAD DATA
INFILE 'mydata.data'
APPEND
INTO TABLE parent
when (1:1) = 'p'
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(p_id SEQUENCE(COUNT, 1),
p_name position(3:15))
INTO TABLE child
when (1:1) = 'c'
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(c_id SEQUENCE(COUNT, 1),
c_name position(3:15),
p_id EXPRESSION "(SELECT MAX(P_ID) FROM PARENT WHERE P_name='A' AND P_name='B' AND P_name='C' AND P_name='D')"
)
able to load parent data and generate P_ID, but i am not able to get P_ID for child records. Please help me.
Thnaks in advance.
[Updated on: Tue, 24 September 2013 08:15] Report message to a moderator |
|
|
|
|
|
| Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596545 is a reply to message #596539] |
Tue, 24 September 2013 11:48  |
|
sureshpediveeti
Messages: 3
Registered: September 2013
Location: delhi
|
Junior Member |
|
|
Parent Table :
create table parent(p_id number primary key, p_name varchar(30))
Child Table :
create table child(c_id number primary key, c_name varchar(30), P_Id number FOREIGN KEY REFERENCES Persons(P_Id))
Relation : One to Many
mydata.csv
---------------
P_Name,C_Name;
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;
I need to load 'mydata.csv' data bellow parent and child table way.
Parent:
P_ID P_Name
1 A
2 B
3 C
4 D
Child Table :
C_ID C_Name P_ID (Parent P_ID reference)
1 test1 1
2 test2 1
3 test3 2
4 test4 2
5 test5 3
6 test6 3
7 test7 4
8 test8 4
i hope you you understand my issue.
[Updated on: Tue, 24 September 2013 13:36] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596572 is a reply to message #596547] |
Tue, 24 September 2013 16:04 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is how I managed to do that. I slightly modified both of your tables; PARENT by adding the UNIQUE key (which rejects duplicates) and CHILD table by adding additional column which is supposed to accept the leftmost value from the CSV file; as we don't need it, I used BOUNDFILLER and used its value later when calling a function.
(By the way, CREATE TABLE statements you posted are invalid; pay attention to what you post next time.)
SQL> create table parent
2 (p_id number primary key,
3 p_name varchar(30) unique
4 );
Table created.
SQL> create table child
2 (c_id number primary key,
3 ignore_me varchar2(30),
4 c_name varchar(30),
5 P_Id number constraint fk_cp REFERENCES parent(P_Id)
6 );
Table created.
SQL> create or replace function f_id (par_name in varchar2)
2 return parent.p_id%type
3 is
4 retval parent.p_id%type;
5 begin
6 select p.p_id
7 into retval
8 from parent p
9 where p.p_name = par_name;
10 return (retval);
11 exception
12 when no_data_found then
13 return null;
14 end;
15 /
Function created.
SQL>
A control file that loads the PARENT table (test4.ctl):LOAD DATA
INFILE *
REPLACE
INTO TABLE parent
FIELDS TERMINATED BY ","
trailing nullcols
( p_id sequence(1, 1),
p_name
)
begindata
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;
A control file that loads the CHILD table (test4_a.ctl):LOAD DATA
INFILE *
REPLACE
into table child
fields terminated by ","
trailing nullcols
(c_id sequence(100, 1),
ignore_me boundfiller,
c_name terminated by ";",
p_id expression "f_id(:ignore_me)"
)
begindata
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;
Execution:SQL> $sqlldr scott/tiger@xe control=test4.ctl log=test4.log
SQL*Loader: Release 11.2.0.2.0 - Production on Uto Ruj 24 22:58:04 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 7
Commit point reached - logical record count 8
SQL> $sqlldr scott/tiger@xe control=test4_a.ctl log=test4_a.log
SQL*Loader: Release 11.2.0.2.0 - Production on Uto Ruj 24 22:58:11 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 7
Commit point reached - logical record count 8
SQL> select * from parent;
P_ID P_NAME
---------- ------------------------------
1 A
3 B
5 C
7 D
SQL> select * from child;
C_ID IGNORE_ME C_NAME P_ID
---------- ------------------------------ ------------------------------ ----------
100 test1 1
101 test2 1
102 test3 3
103 test4 3
104 test5 5
105 test6 5
106 test7 7
107 test8 7
8 rows selected.
SQL>
A non-working solution that uses only one control file
Why do I have 2 control files? Because I couldn't make one control file load all child columns - it kept rejecting the same "duplicate" records rejected by the PARENT's unique key constraint. I did use POSITION(1) for the child table, but it didn't help. The control file that does NOT work: LOAD DATA
INFILE *
REPLACE
INTO TABLE parent
FIELDS TERMINATED BY ","
trailing nullcols
( p_id sequence(1, 1),
p_name
)
into table child
fields terminated by ","
trailing nullcols
(c_id sequence(100, 1),
ignore_me boundfiller position (1),
c_name terminated by ";",
p_id expression "f_id(:ignore_me)"
)
begindata
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;
Execution: SQL> $sqlldr scott/tiger@xe control=test4.ctl log=test4.log
SQL*Loader: Release 11.2.0.2.0 - Production on Uto Ruj 24 23:00:24 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 7
Commit point reached - logical record count 8
SQL> select * from parent;
P_ID P_NAME
---------- ------------------------------
1 A
3 B
5 C
7 D
SQL> select * from child;
C_ID IGNORE_ME C_NAME P_ID
---------- ------------------------------ ------------------------------ ----------
100 test1 1
102 test3 3
104 test5 5
106 test7 7
SQL>
Log file (excerpt): Record 2: Rejected - Error on table PARENT.
ORA-00001: unique constraint (SCOTT.SYS_C007491) violated
Record 4: Rejected - Error on table PARENT.
ORA-00001: unique constraint (SCOTT.SYS_C007491) violated
Record 6: Rejected - Error on table PARENT.
ORA-00001: unique constraint (SCOTT.SYS_C007491) violated
Record 8: Rejected - Error on table PARENT.
ORA-00001: unique constraint (SCOTT.SYS_C007491) violated
Table PARENT:
4 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CHILD:
4 Rows successfully loaded.
4 Rows not loaded due to data errors. --> WHAT DATA ERRORS ?????
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 52480 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 8
Total logical records rejected: 4
Total logical records discarded: 0
I don't know the reason for 4 Rows not loaded due to data errors. --> WHAT DATA ERRORS ????? If someone does, that would be nice.
Sorry for not knowing how to do that any better using SQL*Loader.
|
|
|
|
| Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596683 is a reply to message #596572] |
Wed, 25 September 2013 11:58 |
|
sureshpediveeti
Messages: 3
Registered: September 2013
Location: delhi
|
Junior Member |
|
|
Thanks for quick solution. It is really helpful to me.
But in my schema parent and child tables are already exist with data.
In my parent table p_name field does not have unique constraint and in child table can not add extra column(ignore_me).
I don't have permission to disturb existing tables structure. My tables exist as shown bellow.
create table parent
(p_id number primary key,
p_name varchar(30)
);
create table child
(c_id number primary key,
c_name varchar(30),
P_Id number constraint fk_cp REFERENCES parent(P_Id)
);
Data
-------
col1 col2
---- ----
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;
I need to avoid to load repeated data in col1, this data goes to parent table p_name. Please suggest me any solution based on my requirement.
Thanks in advance.
[Updated on: Wed, 25 September 2013 12:00] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 18:17:38 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
