Database Software

Hi,

I would like to load data though SQL*Loader. I get errors, probably because of the order of the fields that are going to be loaded into the Oracle table from the DAT file.

Here is the Oracle table structure (target) and SQL*Loader files (source):
{color:#800000}
{color}{color:#800000}
{color:#800000}### Exisiting in the System (can alter only field order after the AREA field, FID&GEOM&AREA are added by an external application)
CREATE TABLE RLV.DKN
(
FID NUMBER(10),
GEOM MDSYS.SDO_GEOMETRY,
AREA NUMBER(20,8),
SIFKO NUMBER(30),
SIFDELKO NUMBER(30),
SIFVRAB NUMBER(30),
IMEVRAB VARCHAR2(60 BYTE),
OZNVRAB VARCHAR2(10 BYTE),
RAZRED NUMBER(10),
POVRSINA NUMBER(29),
STEV NUMBER(10),
PODD NUMBER(10),
VRSTAP VARCHAR2(60 BYTE),
PARCELA VARCHAR2(60 BYTE)
);
{color}
{color:#800000}### Structure of the table from the SQL*Loader
CREATE TABLE RLV.DKN (
FID NUMBER(38)
PRIMARY KEY,
SIFKO NUMBER,
SIFDELKO NUMBER,
SIFVRAB NUMBER,
IMEVRAB VARCHAR2(21),
OZNVRAB VARCHAR2(10),
RAZRED NUMBER,
POVRSINA NUMBER,
STEV NUMBER,
PODD NUMBER,
VRSTAP NUMBER,
PARCELA VARCHAR2(10),
GEOM MDSYS.SDO_GEOMETRY);

## My CRL file looks like this ...
LOAD DATA
INFILE rlv.dkn.dat
TRUNCATE
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE RLV.DKN
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
FID INTEGER EXTERNAL,
GEOM COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL,
SDO_SRID INTEGER EXTERNAL,
SDO_ELEM_INFO VARRAY TERMINATED BY '|/'
(X FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '|/'
(X FLOAT EXTERNAL)
),
AREA FLOAT EXTERNAL,
SIFKO,
SIFDELKO,
SIFVRAB,
IMEVRAB NULLIF IMEVRAB = BLANKS,
OZNVRAB NULLIF OZNVRAB = BLANKS,
RAZRED,
POVRSINA,
STEV,
PODD,
VRSTAP,
PARCELA NULLIF PARCELA = BLANKS
)

### My data file looks like this (part)
1|948|2|0|NP|NP|0|13267|324|2|0|324/2|
#3|5000000|
#1|3|1|/
#503834.950000|139144.990000|503842.920000|139147.060000|
#503887.080000|139171.730000|503891.830000|139181.550000|
#503891.840000|139191.810000|503632.060000|139618.500000|
#503647.930000|139628.160000|503825.520000|139336.840000|
#504015.950000|139024.840000|504000.940000|139015.220000|
#503953.210000|139089.000000|503954.340000|139093.550000|
#503911.280000|139161.000000|503904.500000|139164.280000|
#503897.400000|139164.800000|503881.280000|139157.870000|
#503853.160000|139141.460000|503842.570000|139131.590000|
#503840.580000|139141.110000|503834.950000|139144.990000|/
2|948|2|0|NP|NP|0|31528|350|2|0|350/2|
#3|5000000|
#1|3|1|/
#503953.960000|139405.050000|503944.320000|139433.200000|
#503925.280000|139473.400000|503891.680000|139519.340000|
#503854.290000|139577.830000|503851.010000|139594.340000|
#503886.060000|139615.600000|503910.000000|139630.130000|
#503925.530000|139618.310000|503949.190000|139602.580000|
#503951.380000|139597.390000|503976.160000|139587.270000|
#504009.120000|139576.550000|504099.760000|139557.400000|
#504124.190000|139546.110000|504146.980000|139521.110000|
#504115.790000|139499.500000|504076.700000|139476.090000|
#504036.110000|139457.540000|503980.870000|139408.030000|
#503972.780000|139420.520000|503953.960000|139405.050000|/

### Here is the error LOG
{color}



{color:#800000}SQL*Loader: Release 11.1.0.7.0 - Production on Tor Dec 2 15:28:16 2008
{color}


{color:#800000}Copyright (c) 1982, 2007, Oracle. All rights reserved.
{color}


{color:#800000}Control File: rlv.dkn.ctl
Data File: rlv.dkn.dat
Bad File: rlv.dkn.bad
Discard File: none specified

(Allow all discards)
{color}


{color:#800000}Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: 1:1 = 0X23(character '#'), in next physical record
Path used: Conventional
{color}


{color:#800000}Table RLV.DKN, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
{color}


{color:#800000}Column Name Position Len Term Encl Datatype
{color}

{color:#800000}-----
----------
-----
---- ----
-----
FID FIRST * | CHARACTER
GEOM DERIVED * COLUMN OBJECT
{color}
{color:#800000}*** Fields in GEOM
SDO_GTYPE NEXT * | CHARACTER
SDO_SRID NEXT * | CHARACTER
SDO_ELEM_INFO DERIVED * VARRAY
Terminator string : '|/'
{color}


{color:#800000}*** Fields in GEOM.SDO_ELEM_INFO
X FIRST * | CHARACTER
*** End of fields in GEOM.SDO_ELEM_INFO
{color}


{color:#800000}SDO_ORDINATES DERIVED * VARRAY
Terminator string : '|/'
{color}


{color:#800000}*** Fields in GEOM.SDO_ORDINATES
X FIRST * | CHARACTER
*** End of fields in GEOM.SDO_ORDINATES
{color}


{color:#800000}*** End of fields in GEOM
{color}


{color:#800000}AREA NEXT * | CHARACTER
SIFKO NEXT * | CHARACTER
SIFDELKO NEXT * | CHARACTER
SIFVRAB NEXT * | CHARACTER
IMEVRAB NEXT * | CHARACTER
NULL if IMEVRAB = BLANKS
OZNVRAB NEXT * | CHARACTER
NULL if OZNVRAB = BLANKS
RAZRED NEXT * | CHARACTER
POVRSINA NEXT * | CHARACTER
STEV NEXT * | CHARACTER
PODD NEXT * | CHARACTER
VRSTAP NEXT * | CHARACTER
PARCELA NEXT * | CHARACTER
NULL if PARCELA = BLANKS
{color}


{color:#800000}value used for ROWS parameter changed from 64 to 58
Record 1: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 2: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 3: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 4: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 5: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 6: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 7: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 8: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 9: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 10: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 11: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 12: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 13: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 14: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 15: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 16: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 17: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 18: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 19: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 20: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 21: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 22: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 23: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 24: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 25: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 26: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 27: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 28: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 29: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 30: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 31: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 32: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 33: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 34: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 35: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 36: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 37: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 38: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 39: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 40: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 41: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 42: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 43: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 44: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 45: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 46: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 47: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 48: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 49: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 50: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}


{color:#800000}Record 51: Rejected - Error on table RLV.DKN, column GEOM.SDO_ELEM_INFO.X.
error converting data
ORA-01722: invalid number
{color}



{color:#800000}MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
{color}


{color:#800000}Table RLV.DKN:
0 Rows successfully loaded.
51 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.
{color}



{color:#800000}Space allocated for bind array: 254852 bytes(58 rows)
Read buffer bytes: 1048576
{color}


{color:#800000}Total logical records skipped: 0
Total logical records read: 51
Total logical records rejected: 51
Total logical records discarded: 0
{color}


{color:#800000}Run began on Tor Dec 02 15:28:16 2008
Run ended on Tor Dec 02 15:28:17 2008
{color}


{color:#800000}Elapsed time was: 00:00:00.26
CPU time was: 00:00:00.06

{color}

{color}