Home » RDBMS Server » Server Utilities » SQL Loader
SQL Loader [message #146029] |
Tue, 08 November 2005 04:47 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ksmatharu
Messages: 8 Registered: November 2005
|
Junior Member |
|
|
Hi
I am having problem running the following .ctl code.
LOAD DATA
INFILE 'D0019.dat'
BADFILE 'D0019.bad'
INTO TABLE E_D0019_METER_POINTS
WHEN (1:2) = "20"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(FILLER_1 FILLER,
D0019_DATETIME date 'YYYYMMDD',
FILE_SEQUENCE_NUMBER integer external,
INSTRUCTION_NUMBER integer external,
MPAN integer external,
NHHDA_ID char,
SUPPLIER_ID char,
SIGNIFICANT_DATE date 'YYYYMMDD',
SSC_ID char,
PC_SSC_FROM_DATE date 'YYYYMMDD',
NHHDC_FROM_DATE date 'YYYYMMDD'
)
INTO TABLE E_D0019_AA
WHEN (1:2) = "30"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(FILLER_2 FILLER,
D0019_DATETIME date 'YYYYMMDD',
FILE_SEQUENCE_NUMBER integer external,
INSTRUCTION_NUMBER integer external,
MPAN integer external,
NHHDA_ID char,
SUPPLIER_ID char,
FILLER_3 FILLER,
SIGNIFICANT_DATE date 'YYYYMMDD',
TPR char,
AA_FROM_DATE date 'YYYYMMDD',
AA_TO_DATE date 'YYYYMMDD',
AA decimal external
)
INTO TABLE E_D0019_EAC
WHEN (1:2) = "40"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(FILLER_4 FILLER,
D0019_DATETIME date 'YYYYMMDD',
FILE_SEQUENCE_NUMBER integer external,
INSTRUCTION_NUMBER integer external,
MPAN integer external,
NHHDA_ID char,
SUPPLIER_ID char,
SIGNIFICANT_DATE date 'YYYYMMDD',
TPR char,
EAC_FROM_DATE date 'YYYYMMDD',
EAC decimal external
)
When executed the code functoinality fails complaining about the format of data in E_D0019_AA:
"Record 3: Rejected - Error on table E_D0019_AA, column D0019_DATETIME.
ORA-01858: a non-numeric character was found where a numeric was expected"
&
"Record 14: Discarded - all columns null."
However when I break down the load into the individual tables i.e. 3 .ctl files for each table the loads are successfull.
Not sure what is going on here, so any recommendations/explanations would be greatly appreciated.
|
|
|
|
|
Re: SQL Loader [message #146075 is a reply to message #146072] |
Tue, 08 November 2005 08:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ksmatharu
Messages: 8 Registered: November 2005
|
Junior Member |
|
|
I don't think its data because it loads when I execute the three .ctl scripts on their own.
Here is a sample of the data:
20|20051025|1336|830336|1012345913320|EELC|MIDE|20010705|0393|19970801|20010705
30|20051025|1336|830336|1012345913320|EELC|MIDE|MIDE|20010705|00001|||5226.9
40|20051025|1336|830336|1012345913320|EELC|MIDE|20010705|00001||2645.5
30|20051025|1336|830336|1012345913320|EELC|MIDE|MIDE|20010705|00001|||2645.5
40|20051025|1336|830336|1012345913320|EELC|MIDE|20010705|00001||2645.5
20|20051025|343|163246|1012345926203|EELC|MIDE|20001009|0188|19970801|20000906
30|20051025|343|163246|1012345926203|EELC|MIDE|MIDE|20001009|01191|||101.4
40|20051025|343|163246|1012345926203|EELC|MIDE|20001009|01191||101.4
30|20051025|343|163246|1012345926203|EELC|MIDE|MIDE|20001009|01192|||273
40|20051025|343|163246|1012345926203|EELC|MIDE|20001009|01192||273
20|20051025|343|163247|1012345932182|EELC|MIDE|20001006|0151|19970801|20000906
30|20051025|343|163247|1012345932182|EELC|MIDE|MIDE|20001006|00043|||1705.3
40|20051025|343|163247|1012345932182|EELC|MIDE|20001006|00043||1705.3
30|20051025|343|163247|1012345932182|EELC|MIDE|MIDE|20001006|00210|||211.1
40|20051025|343|163247|1012345932182|EELC|MIDE|20001006|00210||211.1
20|20051025|343|163248|1012345934941|EELC|MIDE|20001006|0393|19970801|20000906
30|20051025|343|163248|1012345934941|EELC|MIDE|MIDE|20001006|00001|||981.4
40|20051025|343|163248|1012345934941|EELC|MIDE|20001006|00001||981.4
20|20051025|343|163249|1012346308926|EELC|MIDE|20001009|0151|19970801|19990101
30|20051025|343|163249|1012346308926|EELC|MIDE|MIDE|20001009|00043|||58536.8
40|20051025|343|163249|1012346308926|EELC|MIDE|20001009|00043||58536.8
30|20051025|343|163249|1012346308926|EELC|MIDE|MIDE|20001009|00210|||0
40|20051025|343|163249|1012346308926|EELC|MIDE|20001009|00210||0
20|20051025|1330|826953|1012346503043|EELC|MIDE|20021007|0188|19970801|20020512
30|20051025|1330|826953|1012346503043|EELC|MIDE|MIDE|20021007|01191|||1104.6
40|20051025|1330|826953|1012346503043|EELC|MIDE|20021007|01191||4636.1
30|20051025|1330|826953|1012346503043|EELC|MIDE|MIDE|20021007|01192|||5043.1
40|20051025|1330|826953|1012346503043|EELC|MIDE|20021007|01192||4137.7
20|20051025|1336|830338|1012346507181|EELC|MIDE|20010728|0244|19980911|20010728
30|20051025|1336|830338|1012346507181|EELC|MIDE|MIDE|20010728|00040|||2092.4
40|20051025|1336|830338|1012346507181|EELC|MIDE|20010728|00040||2081.2
30|20051025|1336|830338|1012346507181|EELC|MIDE|MIDE|20010728|00206|||3731.2
40|20051025|1336|830338|1012346507181|EELC|MIDE|20010728|00206||3707
|
|
|
Re: SQL Loader [message #146166 is a reply to message #146075] |
Tue, 08 November 2005 23:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
There is a pecuiliarity when you use multiple when clauses with a delimited data file. In order to get Oracle to read from the right position, you need to add the position to the first non-filler column, as shown below.
LOAD DATA
INFILE 'D0019.dat'
BADFILE 'D0019.bad'
INTO TABLE E_D0019_METER_POINTS
WHEN (1:2) = "20"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(FILLER_1 FILLER,
D0019_DATETIME date 'YYYYMMDD',
FILE_SEQUENCE_NUMBER integer external,
INSTRUCTION_NUMBER integer external,
MPAN integer external,
NHHDA_ID char,
SUPPLIER_ID char,
SIGNIFICANT_DATE date 'YYYYMMDD',
SSC_ID char,
PC_SSC_FROM_DATE date 'YYYYMMDD',
NHHDC_FROM_DATE date 'YYYYMMDD'
)
INTO TABLE E_D0019_AA
WHEN (1:2) = "30"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(FILLER_2 FILLER,
D0019_DATETIME POSITION(4) date 'YYYYMMDD',
FILE_SEQUENCE_NUMBER integer external,
INSTRUCTION_NUMBER integer external,
MPAN integer external,
NHHDA_ID char,
SUPPLIER_ID char,
FILLER_3 FILLER,
SIGNIFICANT_DATE date 'YYYYMMDD',
TPR char,
AA_FROM_DATE date 'YYYYMMDD',
AA_TO_DATE date 'YYYYMMDD',
AA decimal external
)
INTO TABLE E_D0019_EAC
WHEN (1:2) = "40"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(FILLER_4 FILLER,
D0019_DATETIME POSITION(4) date 'YYYYMMDD',
FILE_SEQUENCE_NUMBER integer external,
INSTRUCTION_NUMBER integer external,
MPAN integer external,
NHHDA_ID char,
SUPPLIER_ID char,
SIGNIFICANT_DATE date 'YYYYMMDD',
TPR char,
EAC_FROM_DATE date 'YYYYMMDD',
EAC decimal external
)
|
|
|
Re: SQL Loader [message #146487 is a reply to message #146029] |
Thu, 10 November 2005 16:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
ksmatharu
Messages: 8 Registered: November 2005
|
Junior Member |
|
|
Apologies for not replying earlier..hope you appreciate deadlines etc.
Your solution worked a treat.
Many Thanks.
|
|
|
Goto Forum:
Current Time: Fri Sep 06 17:04:34 CDT 2024
|