Home » RDBMS Server » Server Utilities » Load data using sql loader.. (11.2.0.4, Windows 2008 R2)
| Load data using sql loader.. [message #637313] |
Thu, 14 May 2015 16:32  |
 |
sant_new1
Messages: 46
Registered: June 2014
|
Member |
|
|
Hi friends,
I've been working on loading customer data onto our tables for a development project. I have successfully loaded several tables with all your experts help.
I need some help loading the table 'acc_role' from customer data(tmp_tkt) below using sql loader..
CREATE TABLE TMP_TKT (
PROD_NO VARCHAR(20),
ORIG_KEY VARCHAR(10),
PROC_ID VARCHAR(30),
DESCRIPTION VARCHAR(255),
PROD_UNIT VARCHAR(255),
TKT_CODE1 VARCHAR(10),
TKT_GRP1 VARCHAR(10),
TKT_LAN1 VARCHAR(10),
TKT_CH1 VARCHAR(10),
TKT_TY1 VARCHAR(10),
TKT_CODE2 VARCHAR(10),
TKT_GRP2 VARCHAR(10),
TKT_LAN2 VARCHAR(10),
TKT_CH2 VARCHAR(10),
TKT_TY2 VARCHAR(10),
TKT_CODE3 VARCHAR(10),
TKT_GRP3 VARCHAR(10),
TKT_LAN3 VARCHAR(10),
TKT_CH3 VARCHAR(10),
TKT_TY3 VARCHAR(10)
);
We will be getting big load of data for this table(xls or dat format) from the customer which I will load onto tmp_tkt table. This table is created for users reviewing/comparing the data sent by customer. I have just created sample data file below, loaded this on tmp_tkt table.
PROD_NO|ORIG_KEY|PROC_ID|DESCRIPTION|PROD_UNIT|TKT_CODE1|TKT_GRP1|TKT_LAN1|TKT_CH1|TKT_TY1|TKT_CODE2|TKT_GRP2|TKT_LAN2|TKT_CH2|TKT_TY2|TKT_CODE3|TKT_GRP3|TKT_LAN3|TKT_CH3|TKT_TY3
'sale_1'|'ILM'|'CLRC'|'Comp sales'|'1230K'|'CAR1'|'C1'|'EN1'||'NYC1'|'CAR2'||'EN2'|'GW2'|'NYC2'|'CAR3'||'EN3'|'GW3'|'NYC3'
'sale_2'|'ILM'|'TRRC'|'Monr sales'|'100K'||'B1'|'JP1'|'JW1'|'KY1'||'B2'|'JP2'|'JW2'|'KY2'||'B3'|'JP3'|'JW3'|'KY3'
'sale_3'|'ILM'|'BNMC'|'Keys'|'20K'|'BIK1'|'H1'|'TM1'|'MW1'|'MA1'||'BI2'|'TM2'|'MW2'|'MA2'|||'TM3'|'MW3'|'MA3'
'sale'|'HLC'|'YKNJ'|'Scrn'|'800K'|||||||||||||||
Using the above data, table acc_role needs to be loaded for our project. I'm not sure if it is easier to load acc_role from the data file sent by customer(used to load tmp_tkt) or directly from tmp_tkt table.. We are expecting huge data from customer, so I'm thinking sql loader will be the fastest..
CREATE TABLE ACC_ROLE (
PROD_NO VARCHAR(20),
ACC_TYPE VARCHAR(1),
ACC_CODE1 VARCHAR(10),
ACC_GRP1 VARCHAR(10),
ACC_LAN1 VARCHAR(10),
ACC_CH1 VARCHAR(10),
ROLE VARCHAR(5),
ACC_TY1 VARCHAR(10),
ROLE_ID VARCHAR(1)
);
Here is the criteria to load acc_role based on the mapping to tmp_tkt below:
For every tmp_tkt.orig_key='ILM' in tmp_tkt table, insert 2 records in acc_role table. 1st record with acc_type='P'and 2nd record with acc_type='S'.
For inserting acc_role.acc_type='P', there should be only 1 record with the default values below for acc_type='P'.
For inserting acc_type='S':
1.Insert one record each when either tkt_codeX or tkt_grpX are populated in tmp_tkt table. (When tmp_tkt.tkt_codeX is populated, acc_grp1 should be null. When tmp_tkt.tkt_grpX is populated then acc_code1 should be null.)
2.There could be 3 records(or more) for every prod_no in tmp_tkt table based on tkt_codeX and tkt_grpX values.
3.If both tkt_codeX and tkt_grpX are null then stop insert.
ACC_ROLE VALUES/DEFAULT VALUES/DEFAULT
WHEN acc_type='P' WHEN acc_type='S'
prod_no: tmp_tkt.prod_no tmp_tkt.prod_no
acc_type: 'P' 'S'
acc_code1: 'PTS' tkt_codeX {tkt_code1,tkt_code2,tkt_code3 from tmp_tkt
acc_grp1: 'OT' tkt_grpX {tkt_grp1,tkt_grp2,tkt_grp3 from tmp_tkt
acc_lan1: 'EN' tkt_lanX {tkt_lan1,tkt_lan2,tkt_lan3 from tmp_tkt
acc_ch1: 'OT' tkt_chX {tkt_ch1,tkt_ch2,tkt_ch3 from tmp_tkt
role: 'PTS' 'ST'
acc_ty1: 'PTS' tkt_ty3
role_id: NULL 'U'
Result in acc_role after the load should be :
PROD_NO ACC_TYPE ACC_CODE1 ACC_GRP1 ACC_LAN1 ACC_CH1 ACC_TY1 ROLE_ID ROLE
sale_1 P PTS OT EN OT PTS PTS
sale_2 P PTS OT EN OT PTS PTS
sale_3 P PTS OT EN OT PTS PTS
sale_1 S CAR1 EN1 NYC3 U ST
sale_1 S C1 EN1 NYC3 U ST
sale_1 S CAR2 EN2 GW2 NYC3 U ST
sale_1 S CAR3 EN3 GW3 NYC3 U ST
sale_2 S B1 JP1 JW1 KY3 U ST
sale_2 S B2 JP2 JW2 KY3 U ST
sale_2 S B3 JP3 JW3 KY3 U ST
sale_3 S BIK1 TM1 MW1 MA3 U ST
sale_3 S H1 TM1 MW1 MA3 U ST
sale_3 S BI2 TM2 MW2 MA3 U ST
Appreciate your time and help... Thank you so much
|
|
|
|
| Re: Load data using sql loader.. [message #637321 is a reply to message #637313] |
Thu, 14 May 2015 21:48  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl> host type test.ctl
options(skip=1)
load data
into table tmp_tkt
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(PROD_NO,ORIG_KEY,PROC_ID,DESCRIPTION,PROD_UNIT,TKT_CODE1,TKT_GRP1,TKT_LAN1,TKT_CH1,TKT_TY1
,TKT_CODE2,TKT_GRP2,TKT_LAN2,TKT_CH2,TKT_TY2,TKT_CODE3,TKT_GRP3,TKT_LAN3,TKT_CH3,TKT_TY3)
into table acc_role
when orig_key='ILM'
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(PROD_NO position(1)
,ORIG_KEY boundfiller
,acc_type constant 'P'
,acc_code1 constant 'PTS'
,acc_grp1 constant 'OT'
,acc_lan1 constant 'EN'
,acc_ch1 constant 'OT'
,role constant 'PTS'
,acc_ty1 constant 'PTS')
into table acc_role
when orig_key='ILM'
and acc_code1 != BLANKS
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(PROD_NO position(1)
,ORIG_KEY boundfiller
,PROC_ID filler
,DESCRIPTION filler
,PROD_UNIT filler
,acc_CODE1
,TKT_GRP1 filler
,acc_LAN1
,acc_CH1
,TKT_TY1 filler
,TKT_CODE2 filler
,TKT_GRP2 filler
,TKT_LAN2 filler
,TKT_CH2 filler
,TKT_TY2 filler
,TKT_CODE3 filler
,TKT_GRP3 filler
,TKT_LAN3 filler
,TKT_CH3 filler
,acc_ty1
,acc_type constant 'S'
,role constant 'ST'
,role_id constant 'U')
into table acc_role
when orig_key='ILM'
and acc_code1 != BLANKS
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(PROD_NO position(1)
,ORIG_KEY boundfiller
,PROC_ID filler
,DESCRIPTION filler
,PROD_UNIT filler
,tkt_CODE1 filler
,TKT_GRP1 filler
,tkt_LAN1 filler
,tkt_CH1 filler
,TKT_TY1 filler
,acc_CODE1
,TKT_GRP2 filler
,acc_LAN1
,acc_CH1
,TKT_TY2 filler
,TKT_CODE3 filler
,TKT_GRP3 filler
,TKT_LAN3 filler
,TKT_CH3 filler
,acc_ty1
,acc_type constant 'S'
,role constant 'ST'
,role_id constant 'U')
into table acc_role
when orig_key='ILM'
and acc_code1 != BLANKS
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(PROD_NO position(1)
,ORIG_KEY boundfiller
,PROC_ID filler
,DESCRIPTION filler
,PROD_UNIT filler
,tkt_CODE1 filler
,TKT_GRP1 filler
,tkt_LAN1 filler
,tkt_CH1 filler
,TKT_TY1 filler
,tkt_CODE2 filler
,TKT_GRP2 filler
,tkt_LAN2 filler
,tkt_CH2 filler
,TKT_TY2 filler
,acc_CODE1
,TKT_GRP3 filler
,acc_LAN1
,acc_CH1
,acc_ty1
,acc_type constant 'S'
,role constant 'ST'
,role_id constant 'U')
into table acc_role
when orig_key='ILM'
and acc_grp1 != BLANKS
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(PROD_NO position(1)
,ORIG_KEY boundfiller
,PROC_ID filler
,DESCRIPTION filler
,PROD_UNIT filler
,tkt_CODE1 filler
,acc_GRP1
,acc_LAN1
,acc_CH1
,TKT_TY1 filler
,TKT_CODE2 filler
,TKT_GRP2 filler
,TKT_LAN2 filler
,TKT_CH2 filler
,TKT_TY2 filler
,TKT_CODE3 filler
,TKT_GRP3 filler
,TKT_LAN3 filler
,TKT_CH3 filler
,acc_ty1
,acc_type constant 'S'
,role constant 'ST'
,role_id constant 'U')
into table acc_role
when orig_key='ILM'
and acc_grp1 != BLANKS
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(PROD_NO position(1)
,ORIG_KEY boundfiller
,PROC_ID filler
,DESCRIPTION filler
,PROD_UNIT filler
,tkt_CODE1 filler
,TKT_GRP1 filler
,tkt_LAN1 filler
,tkt_CH1 filler
,TKT_TY1 filler
,tkt_CODE2 filler
,acc_GRP1
,acc_LAN1
,acc_CH1
,TKT_TY2 filler
,TKT_CODE3 filler
,TKT_GRP3 filler
,TKT_LAN3 filler
,TKT_CH3 filler
,acc_ty1
,acc_type constant 'S'
,role constant 'ST'
,role_id constant 'U')
into table acc_role
when orig_key='ILM'
and acc_grp1 != BLANKS
fields terminated by '|'
optionally enclosed by "'"
trailing nullcols
(PROD_NO position(1)
,ORIG_KEY boundfiller
,PROC_ID filler
,DESCRIPTION filler
,PROD_UNIT filler
,tkt_CODE1 filler
,TKT_GRP1 filler
,tkt_LAN1 filler
,tkt_CH1 filler
,TKT_TY1 filler
,tkt_CODE2 filler
,TKT_GRP2 filler
,tkt_LAN2 filler
,tkt_CH2 filler
,TKT_TY2 filler
,tkt_CODE3 filler
,acc_GRP1
,acc_LAN1
,acc_CH1
,acc_ty1
,acc_type constant 'S'
,role constant 'ST'
,role_id constant 'U')
SCOTT@orcl> host sqlldr scott/tiger control=test.ctl data=tmp_tkt.dat log=test.log
SQL*Loader: Release 11.2.0.1.0 - Production on Thu May 14 19:46:38 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
SCOTT@orcl> column acc_type format a8
SCOTT@orcl> column role_id format a7
SCOTT@orcl> select * from acc_role order by acc_type, prod_no, acc_lan1
2 /
PROD_NO ACC_TYPE ACC_CODE1 ACC_GRP1 ACC_LAN1 ACC_CH1 ACC_TY1 ROLE_ID ROLE
-------------------- -------- ---------- ---------- ---------- ---------- ---------- ------- -----
sale_1 P PTS OT EN OT PTS PTS
sale_2 P PTS OT EN OT PTS PTS
sale_3 P PTS OT EN OT PTS PTS
sale_1 S CAR1 EN1 NYC3 U ST
sale_1 S C1 EN1 NYC3 U ST
sale_1 S CAR2 EN2 GW2 NYC3 U ST
sale_1 S CAR3 EN3 GW3 NYC3 U ST
sale_2 S B1 JP1 JW1 KY3 U ST
sale_2 S B2 JP2 JW2 KY3 U ST
sale_2 S B3 JP3 JW3 KY3 U ST
sale_3 S BIK1 TM1 MW1 MA3 U ST
sale_3 S H1 TM1 MW1 MA3 U ST
sale_3 S BI2 TM2 MW2 MA3 U ST
13 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 16:52:29 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
