Help needed in SQL Loader
538845May 16 2007 — edited May 16 2007Hi,
Am using SQL Loader to insert rows in a table.
I have 5 cols in table A. But the data is not present in all cols always.
Have used TRAILING NULLCOLS but with no help.. few rows are inserted and for many others it throws "ORA-01841: (full) year must be between -4713 and +9999, and not be 0" error
(because the value is null in that particular column position)
How do I handle such cases.Please help asap.
FYI -
CONTROL FILE
-------------------------------
load data
infile '/home/krkanth/test_spam_20070513.dat'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
into table spam_rule_stats
fields terminated by "\t"
optionally enclosed by '"'
TRAILING NULLCOLS
(MAIL_DATE date 'yyyymmdd hh24:mi:ss', METRIC_TYPE, HEURISTIC_TYPE, HEURISTIC_VALUE, REJECTION_COUNT,
RULE_START_DATE DATE 'yyyymmdd hh24:mi:ss',
RULE_END_DATE date 'yyyymmdd hh24:mi:ss',SOURCE)
Am getting error Rejected - Error on table SPAM_RULE_STATS, column RULE_END_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
SAMPLE DATA
---------------------------
20070513 RULE SENDER ipodsweepstakes@ipodsweepstakes.com 534 20070214 22:02:56
SnA_ALERTS
20070513 RULE SENDER austin@spectaculation.xrxlmaal.info 6 20070503 21:19:36 200
70518 21:19:36 RULE_EXTENSIONS
20070513 RULE SENDER grantsma@server.grantsmall.com 519 20061105 05:40:44 SnA
_ALERTS
20070513 RULE SENDER a8rt42rf3keh@haynes08.com 332 20070509 02:44:51 20070524 02
:44:51 JASD_HP_AUTOREJECT
20070513 RULE SENDER apache@jcc.ru 928 20070512 14:49:14 20070527 14:49:14 JAS
D_HP_AUTOREJECT
20070513 RULE SENDER ret@cholesterol-mechanic.net 19875 20070507 22:30:01 20070522 22
:30:01 RULE_EXTENSIONS
20070513 RULE SENDER apache@ns.webmarket.ee 3 20070509 09:08:38 20070524 09:08:38
JASD_HP_AUTOREJECT
20070513 RULE SENDER katherine@spectaculation.xrxlmabl.info 10 20070503 21:19:36 200
70518 21:19:36 RULE_EXTENSIONS
20070513 RULE SENDER actionaidnotices@yahoo.it 4 20070506 04:39:48 20070521 04
:39:48 JASD_HP_AUTOREJECT
Thanks.