Home » RDBMS Server » Server Utilities » Master child sequence load in sql loader
| Master child sequence load in sql loader [message #664794] |
Fri, 04 August 2017 14:57  |
 |
karth
Messages: 2
Registered: August 2017
|
Junior Member |
|
|
I have a 2 file data.dat and control.ctl files need to load in 2 different tables.
data.dat file need to load in table A
control.ctl file need to load in table b
source id is a common column in both the tables its a primary and foreign key relationship.
I need to load a sequence number in the source id column to have master child relationship.
Please advise
|
|
|
|
|
|
|
|
|
|
| Re: Master child sequence load in sql loader [message #665022 is a reply to message #664877] |
Thu, 17 August 2017 18:42  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You could create a sequence, then use that sequence for loading of t2.source, then select the max(source) from t2 when loading t1. The following example assumes that your dates in your file names are the sysdate, therefore they don't match the actual names used.
-- data files you provided:
SCOTT@orcl_12.1.0.2.0> host type 010317.dat
Amount date
500 1-03-17
300 1-03-17
SCOTT@orcl_12.1.0.2.0> host type 010317.ctl
count sum
2 800
SCOTT@orcl_12.1.0.2.0> host type 010417.dat
Amount date
100 1-04-17
200 1-04-17
SCOTT@orcl_12.1.0.2.0> host type 010417.ctl
count sum
2 300
-- SQL*Loader control files:
SCOTT@orcl_12.1.0.2.0> host type t1.ctl
options(skip=1)
load data
append into table t1
fields terminated by whitespace trailing nullcols
( amount
, date_col date 'dd-mm-yy'
, file_name "to_char (sysdate, 'ddmmyy') || '.dat'"
, source expression "(select max(source) from t2)" )
SCOTT@orcl_12.1.0.2.0> host type t2.ctl
options(skip=1)
load data
append into table t2
fields terminated by whitespace trailing nullcols
( count
, sum
, file_name "to_char (sysdate, 'ddmmyy') || '.ctl'"
, source "test_seq.nextval" )
-- tables:
SCOTT@orcl_12.1.0.2.0> create table t1
2 (source number,
3 file_name varchar2(10),
4 amount number,
5 date_col date)
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> create table t2
2 (source number,
3 file_name varchar2(10),
4 count number,
5 sum number)
6 /
Table created.
-- sequence:
SCOTT@orcl_12.1.0.2.0> create sequence test_seq
2 /
Sequence created.
-- first load, results, and reconciliation:
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=t2.ctl log=test2.log data=010317.ctl
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Aug 17 16:44:52 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Table T2:
1 Row successfully loaded.
Check the log file:
test2.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> select * from t2
2 /
SOURCE FILE_NAME COUNT SUM
---------- ---------- ---------- ----------
1 170817.ctl 2 800
1 row selected.
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=t1.ctl log=test1.log data=010317.dat
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Aug 17 16:44:53 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 2
Table T1:
2 Rows successfully loaded.
Check the log file:
test1.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> select * from t1
2 /
SOURCE FILE_NAME AMOUNT DATE_COL
---------- ---------- ---------- ---------------
1 170817.dat 500 Wed 01-Mar-2017
1 170817.dat 300 Wed 01-Mar-2017
2 rows selected.
SCOTT@orcl_12.1.0.2.0> select t2.source, count(*) t1count, t2.count t2count, sum(t1.amount) t1sum, t2.sum t2sum
2 from t1, t2
3 where t1.source = t2.source
4 group by t2.source, t2.count, t2.sum
5 /
SOURCE T1COUNT T2COUNT T1SUM T2SUM
---------- ---------- ---------- ---------- ----------
1 2 2 800 800
1 row selected.
-- second load, results, and reconciliation:
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=t2.ctl log=test4.log data=010417.ctl
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Aug 17 16:44:55 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Table T2:
1 Row successfully loaded.
Check the log file:
test4.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> select * from t2
2 /
SOURCE FILE_NAME COUNT SUM
---------- ---------- ---------- ----------
1 170817.ctl 2 800
2 170817.ctl 2 300
2 rows selected.
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=t1.ctl log=test3.log data=010417.dat
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Aug 17 16:44:56 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 2
Table T1:
2 Rows successfully loaded.
Check the log file:
test3.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> select * from t1
2 /
SOURCE FILE_NAME AMOUNT DATE_COL
---------- ---------- ---------- ---------------
1 170817.dat 500 Wed 01-Mar-2017
1 170817.dat 300 Wed 01-Mar-2017
2 170817.dat 100 Sat 01-Apr-2017
2 170817.dat 200 Sat 01-Apr-2017
4 rows selected.
SCOTT@orcl_12.1.0.2.0> select t2.source, count(*) t1count, t2.count t2count, sum(t1.amount) t1sum, t2.sum t2sum
2 from t1, t2
3 where t1.source = t2.source
4 group by t2.source, t2.count, t2.sum
5 /
SOURCE T1COUNT T2COUNT T1SUM T2SUM
---------- ---------- ---------- ---------- ----------
1 2 2 800 800
2 2 2 300 300
2 rows selected.
[Updated on: Thu, 17 August 2017 18:51] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Dec 23 02:50:00 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
