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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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.

Re: Load data using sql loader.. [message #637374 is a reply to message #637321] Fri, 15 May 2015 15:04 Go to previous message
sant_new1
Messages: 46
Registered: June 2014
Member
Thank you so much Barbara. This works perfect..
Previous Topic: How to export all of my procedure and packeges from my schema?
Next Topic: Import /Export from 9i to 12cR2
Goto Forum:
  


Current Time: Sun Sep 22 03:46:07 CDT 2019