CODEWITHSUNDEEP
oracle-databasesql-loader
Bernd
Bernd

Reputation: 657

Load data in Oracle via SQLLDR

I would like to load data in Oracle via the sqlldr, however, it is always loading the data in another format.

So does my data from my file look like:

2018-11-27      13      Vienna  1       66.90   1

This is the result after having loaded the data:

27-Nov-17 1443443505 ienna 909510961 0.9 3377

All columns except the date column are wrong

This is my table structure:

BOOKINGDATE DATE
CUSTOMERID NUMBER(38,0)
LOCATIONID VARCHAR(255 BYTE)
NUMBEROFPARKINGTICKET NUMBER(38,0)
CHARGETICKET NUMBER(18,2)
DURATIONINMINUTES NUMBER(38)

This is my table definition in my file:

LOAD DATA
APPEND
INTO TABLE ROTH.PARKSCHEIN_ROTH
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"'
(
BOOKINGDATE DATE 'YYYY-MM-DD',
CUSTOMERID INTEGER,
LOCATIONID CHAR(255),
NUMBEROFPARKINGTICKET INTEGER,
CHARGETICKET DECIMAL EXTERNAL,
DURATIONINMINUTES INTEGER
)

Can someone please tell me which datatypes do I have to use? I thought Oracle is find all the types by itself except the date?

Thank you very much in advance for you help.

Upvotes: 3

Views: 4155

Answers (1)

William Robertson
William Robertson

Reputation: 15991

It's generally easiest to leave it to the default and let conversion happen in the database:

load data
replace
into table parkschein_roth
fields terminated by '\t'
optionally enclosed by '"'
( bookingdate date 'YYYY-MM-DD'
, customerid
, locationid
, numberofparkingticket
, chargeticket
, durationinminutes )

The log then shows it did this:

Column Name                      Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
BOOKINGDATE                         FIRST     *  WHT O(") DATE YYYY-MM-DD      
CUSTOMERID                           NEXT     *  WHT O(") CHARACTER            
LOCATIONID                           NEXT     *  WHT O(") CHARACTER            
NUMBEROFPARKINGTICKET                NEXT     *  WHT O(") CHARACTER            
CHARGETICKET                         NEXT     *  WHT O(") CHARACTER            
DURATIONINMINUTES                    NEXT     *  WHT O(") CHARACTER

Note that specifying a numeric datatype without the EXTERNAL keyword instructs SQL*Loader to read the binary data in the file directly, not its character representation: for example, what's displayed as 1 in a text editor is character 49 (that is, the symbol representing the bit sequence 00110001), and not an actual numeric 1. (I have never seen a data file formatted with binary encoded numbers but I suppose they must exist.) See Numeric EXTERNAL in the SQL*Loader Field List Reference.

Note following comments: it seems the line actually ended with 00110001 (the character '1') followed by 00001101 (Windows carriage return) before the linefeed. Looking at the result you got, it must have read those two bytes and interpreted them as 0000110100110001 to get decimal 3377.

Upvotes: 4

Related Questions