Home » RDBMS Server » Server Utilities » SQL Loader
SQL Loader [message #146029] Tue, 08 November 2005 04:47 Go to next message
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.

Report message to a moderator

Re: SQL Loader [message #146032 is a reply to message #146029] Tue, 08 November 2005 04:49 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Moved to Server Utilities.

MHE

Report message to a moderator

Re: SQL Loader [message #146072 is a reply to message #146029] Tue, 08 November 2005 07:52 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Data?

Report message to a moderator

Re: SQL Loader [message #146075 is a reply to message #146072] Tue, 08 November 2005 08:24 Go to previous messageGo to next message
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

Report message to a moderator

Re: SQL Loader [message #146166 is a reply to message #146075] Tue, 08 November 2005 23:55 Go to previous messageGo to next message
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
)

Report message to a moderator

icon12.gif  Re: SQL Loader [message #146487 is a reply to message #146029] Thu, 10 November 2005 16:20 Go to previous message
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.

Report message to a moderator

Previous Topic: Oracle stats
Next Topic: User Migration
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 01 13:20:57 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.