SQL*Loader and Parent Record key in Child Records
John, March 10, 2004 - 5:01 pm UTC
Hi Tom,
I have a similar problem, except I have multiple parent and child records in the same file. A child record is associated with the parent record that most recently preceded it in the file, but the child record has no field to identify the parent record.
I'm loading this file with SQL*Loader, and generating primary keys for both tables. Is it possible using SQL*Loader to also populate the foreign key column of the child record. At the moment I'm doing this with SQL after the load. I realise this approach relies on all parent records loading successfully.
ORA817> CREATE TABLE TAB1
2 (
3 ID NUMBER,
4 VAL VARCHAR2(30),
5 CONSTRAINT TAB1_PK PRIMARY KEY (ID)
6 );
Table created.
ORA817> CREATE TABLE TAB2
2 (
3 ID NUMBER,
4 TAB1_ID NUMBER,
5 VAL1 VARCHAR2(10),
6 VAL2 VARCHAR2(10),
7 CONSTRAINT TAB2_PK PRIMARY KEY (ID),
8 CONSTRAINT TAB2_TAB1_FK FOREIGN KEY (TAB1_ID) REFERENCES TAB1 (ID)
9 );
Table created.
And control file:
LOAD DATA INFILE * REPLACE
INTO TABLE tab1
WHEN (1)='P '
( id RECNUM,
val POSITION(3:100)
)
INTO TABLE tab2
WHEN (1)='C '
( id RECNUM,
val1 POSITION(3:12),
val2 POSITION(13:100)
)
BEGINDATA
P MasterRecord1
C Detail1 DetailA
C Detail2 DetailB
P MasterRecord2
C Detail3 DetailC
C Detail4 DetailD
C Detail5 DetailE
After loading:
TAB1:
ID VAL
1 MasterRecord1
4 MasterRecord2
TAB2:
ID TAB1_ID VAL1 VAL2
2 null Detail1 DetailA
3 null Detail2 DetailB
5 null Detail3 DetailC
6 null Detail4 DetailD
7 null Detail5 DetailE
Then I populate TAB2.TAB1_ID
UPDATE TAB2 SET tab1_id = (SELECT MAX(ID) FROM TAB1 WHERE ID < TAB2.ID);
TAB2:
ID TAB1_ID VAL1 VAL2
2 1 Detail1 DetailA
3 1 Detail2 DetailB
5 4 Detail3 DetailC
6 4 Detail4 DetailD
7 4 Detail5 DetailE
Is it possible to populate TAB2.TAB1_ID using just SQL*Loader?
March 10, 2004 - 5:33 pm UTC
if you create sequences S and S2, this will do (albeit without ARRAY inserts)
options(rows=1)
LOAD DATA INFILE *
REPLACE
INTO TABLE tab1
WHEN (1)='P '
( id "s.nextval",
val POSITION(3:100)
)
INTO TABLE tab2
WHEN (1)='C '
( id "s2.nextval",
tab1_id "s.currval",
val1 POSITION(3:12),
val2 POSITION(13:100)
)
BEGINDATA
Excellent
Jennifer, December 14, 2004 - 12:27 pm UTC
This was exactly what I needed - thanks for this!
Just load only first record
PET, November 03, 2005 - 8:52 am UTC
How do I load only the first record from a file into a table?. I don't care about other records in that file. I've no way of differenciating each records,they're all the same. So I guess I can't use WHEN clause..
Please let me know.
November 04, 2005 - 2:23 am UTC
sqlldr ..... LOAD=1 .....
Just load only first record
PET, November 03, 2005 - 8:53 am UTC
How do I load only the first record from a file into a table?. I don't care about other records in that file. I've no way of differenciating each records,they're all the same. So I guess I can't use WHEN clause..
Please let me know.
never mind
PET, November 03, 2005 - 8:54 am UTC
I guess I can use load=1.,
Loading with firs row heading
Kiril, June 02, 2020 - 6:41 pm UTC
Hello,
I have issue as mentioned in main post.
We use Oracle 12.2, and my question is:
maybe now exists better decision, with one control file and one table?
June 03, 2020 - 5:47 am UTC
Use an external table, which gives you the entire power of SQL.
There is a video on using SQL to "carry" a header row down through subsequent rows here
External Table definition with header information in first row of data file
Kiril, June 03, 2020 - 4:56 pm UTC
Thanks Connor, but my point is to periodically load many many files.
Ok, I can try the external tables, but I can't imagine the strategy to load many files with different names every time.
And how should look external table definition to represent data file structure provided in first post?
June 04, 2020 - 7:39 am UTC
I can't imagine the strategy to load many files with different names every time.12.2 added the external modify clause for external tables. This allows you to pass the file name at runtime:
select *
from t_ext
external modify (
location ( 'file.txt' )
);