Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to load NULL in a numeric field

drbiloukosOct 8 2013 — edited Oct 8 2013

Hello people,

I did some research but still cannot solve it. Please help:

CTL:

LOAD DATA


INFILE *


APPEND


INTO TABLE DATA


FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'


TRAILING NULLCOLS


(


 source,                              


 sampled          DATE "RRMMDDHH24",


 bsccumms         INTEGER EXTERNAL,


 bscmaxms         INTEGER EXTERNAL,


 dcscumms         INTEGER EXTERNAL,


 dcsmaxms         INTEGER EXTERNAL,


 gsmcumms         INTEGER EXTERNAL,


 gsmmaxms         INTEGER EXTERNAL,


 totpag           INTEGER EXTERNAL,


 totcongpag       INTEGER EXTERNAL,


 tmassall         INTEGER EXTERNAL,


 tmcassall        INTEGER EXTERNAL,


 tmcncmatt        INTEGER EXTERNAL,


 tmcncmsucc       INTEGER EXTERNAL,


 tmcncbatt        INTEGER EXTERNAL,


 tmcncbsucc       INTEGER EXTERNAL,


 tmhoatt          INTEGER EXTERNAL,


 tmhosucc         INTEGER EXTERNAL,


 tmschrq          DECIMAL EXTERNAL,


 tmschre          DECIMAL EXTERNAL,


 tmchscan         INTEGER EXTERNAL,


 LCCELLMOV        INTEGER EXTERNAL,


 LCCELLMOVREJ     INTEGER EXTERNAL,


 LCHIRPPLOAD      INTEGER EXTERNAL,


 LCPARREJ         INTEGER EXTERNAL,


 LCMSSUPRFC       INTEGER EXTERNAL,


 LCRELBUSYHI3     INTEGER EXTERNAL,


 LCRELIDLEHI3     INTEGER EXTERNAL


)

Table:

CREATE TABLE DATA


(


  SAMPLED       DATE                            NOT NULL,


  SOURCE        VARCHAR2(8 BYTE)                NOT NULL,


  BSCCUMMS      NUMBER,


  BSCMAXMS      NUMBER,


  DCSCUMMS      NUMBER,


  DCSMAXMS      NUMBER,


  GSMCUMMS      NUMBER,


  GSMMAXMS      NUMBER,


  TOTPAG        NUMBER,


  TOTCONGPAG    NUMBER,


  TMASSALL      NUMBER,


  TMCASSALL     NUMBER,


  TMCNCMATT     NUMBER,


  TMCNCMSUCC    NUMBER,


  TMCNCBATT     NUMBER,


  TMCNCBSUCC    NUMBER,


  TMHOATT       NUMBER,


  TMHOSUCC      NUMBER,


  TMSCHRQ       NUMBER(*,3),


  TMSCHRE       NUMBER(*,3),


  TMCHSCAN      NUMBER,


  LCCELLMOV     NUMBER,


  LCCELLMOVREJ  NUMBER,


  LCHIRPPLOAD   NUMBER,


  LCPARREJ      NUMBER,


  LCMSSUPRFC    NUMBER,


  LCRELBUSYHI3  NUMBER,


  LCRELIDLEHI3  NUMBER


)

CSV:

"BEAFS","13100711","164970","1707","23637","357","164970","1707","40605","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","0","0"


"BEAKR","13100711","210342","1988","53696","669","210342","1988","47152","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEAMF","13100711","186167","1615","47412","572","186167","1615","34784","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEAPI","13100711","296364","2856","93301","1079","296364","2856","65541","0","0","0","0","0","0","0","0","0","0.000","0.000","360","15","26","167","0","0","NULL","NULL"


"BEAPS","13100711","262759","2418","80279","998","262759","2418","62878","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBDO","13100711","323512","2762","60516","674","323512","2762","70817","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBDS","13100711","109853","1195","16724","270","109853","1195","28910","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBFN","13100711","162251","1595","28319","461","162251","1595","37535","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","0","0"


"BEBIO","13100711","177909","1597","33850","447","177909","1597","44547","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBKF","13100711","197979","2198","65732","884","197979","2198","44983","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBKR","13100711","124237","1050","20482","241","124237","1050","27107","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBKS","13100711","233854","1909","79186","690","233854","1909","40744","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBLA","13100711","118123","1289","18141","370","118123","1289","31450","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBPI","13100711","236944","2086","83843","798","236944","2086","47726","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBPR","13100711","273649","2758","90274","988","273649","2758","57197","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","0","0"


"BEBPS","13100711","156916","1554","57304","637","156916","1554","34239","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEBSY","13100711","132265","1272","23787","381","132265","1272","35745","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BECDS","13100711","95551","974","22340","340","95551","974","24138","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BECFN","13100711","61341","529","6801","107","61341","529","12466","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BECIO","13100711","147827","1419","17676","242","147827","1419","33544","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BECKR","13100711","139357","1468","20635","407","139357","1468","38501","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BECLA","13100711","104760","1038","15225","229","104760","1038","24362","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BECMS","13100711","226905","2114","83072","832","226905","2114","47033","0","0","0","0","0","0","0","0","0","0.000","0.000","359","0","0","0","0","0","NULL","NULL"


"BECPT","13100711","0","0","0","0","0","0","12","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BECSY","13100711","184825","1551","22541","325","184825","1551","47247","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"


"BEDSY","13100711","54166","553","7818","108","54166","553","12801","0","0","0","0","0","0","0","0","0","0.000","0.000","360","0","0","0","0","0","NULL","NULL"

LOG:

Record 2: Rejected - Error on table BDATA, column LCRELBUSYHI3.
ORA-01722: invalid number


Record 3: Rejected - Error on table DATA, column LCRELBUSYHI3.
ORA-01722: invalid number


Record 4: Rejected - Error on table DATA, column LCRELBUSYHI3.
ORA-01722: invalid number


...
...
...



Table DATA:
  3 Rows successfully loaded.
  23 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
This post has been answered by DK2010 on Oct 8 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 5 2013
Added on Oct 8 2013
#database-key-features, #export-import-sql-loader-external-tables
1 comment
504 views