CODEWITHSUNDEEP
oracle-databasesql-loader
Kris
Kris

Reputation: 5792

SQLLDR with mix of fixed and variable-length fields

I'm trying to load an example datafile with records like this:

12312^123456^0015GRAJ1M7J0002^SOME GIBBERISH                    ^123

I need to associate this 5 fields with 6 database columns, where the first record is broken into 2:

'12312' -> '123' and '12'

and the rest of the fields are "^" separated.

What I have now is:

    COL_ONE POSITION(1:3) INTEGER EXTERNAL,
    COL_TWO POSITION(4:5) CHAR TERMINATED BY "^",
    COL_THREE INTEGER EXTERNAL TERMINATED BY "^",
    COL_FOUR CHAR TERMINATED BY "^",
    COL_FIVE CHAR TERMINATED BY "^",
    COL_SIX CHAR TERMINATED BY "^"
    ....

but this doesn't work as expected as for some reason field

"SOME GIBBERISH                    "

ends up being inserted in COL_SIX instead of COL_FIVE, which gives me an error.

My log shows:

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL_ONE                               1:3     3           CHARACTER            
COL_TWO                               4:5     2   ^       CHARACTER            
COL_THREE                            NEXT     *   ^       CHARACTER            
COL_FOUR                             NEXT     *   ^       CHARACTER            
COL_FIVE                             NEXT     *   ^       CHARACTER            
COL_SIX                              NEXT     *   ^       CHARACTER

any help would be highly appreciated.

Upvotes: 1

Views: 1943

Answers (2)

Kris
Kris

Reputation: 5792

Ok, problem solved with trial and error:

COL_ONE POSITION(1:3) INTEGER EXTERNAL,
COL_TWO CHAR TERMINATED BY "^",
.....

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

The problem comes from your third field:

COL_THREE INTEGER EXTERNAL TERMINATED BY "^"

This field starts at position 6, which unfortunately contains a ^ so this field will be filled with a NULL value, pushing all following fields into the wrong columns.

You could use a filler to ignore the ^ character at position 6:

COL_ONE POSITION(1:3) INTEGER EXTERNAL,
COL_TWO POSITION(4:5) CHAR,
dummy FILLER POSITION(6:6),
COL_THREE INTEGER EXTERNAL TERMINATED BY "^",
COL_FOUR CHAR TERMINATED BY "^",
COL_FIVE CHAR TERMINATED BY "^",
COL_SIX CHAR TERMINATED BY "^"

Upvotes: 2

Related Questions