3

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.

Bernd
  • 593
  • 2
  • 8
  • 31

1 Answers1

4

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.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • Thanks for your hint. If I do it that way I get this error message: Record 1: Rejected - Error on table PARKSCHEIN_ROTH, column DURATIONINMINUTES. ORA-01722: invalid number – Bernd Feb 10 '19 at 11:19
  • I changed the loader file, the column durationinminutes to DECIMAL(38), now nearly all columns are correct, except DURATIONINMINUTES: load data replace into table parkschein_roth fields terminated by '\t' optionally enclosed by '"' ( bookingdate date 'YYYY-MM-DD' , customerid , locationid , numberofparkingticket , chargeticket , durationinminutes decimal(38)) This is the result: 27-Nov-18 13 Vienna 1 66.9 -310 Why is the last column negativ? – Bernd Feb 10 '19 at 11:30
  • I added a note about numeric field handling to my answer. I can't reproduce your ORA-01722, I'm afraid (Oracle 12.2 on Windows 10). – William Robertson Feb 10 '19 at 11:35
  • Î created my txt file in Excel, exported it to a textfile and then imported it in Oracle on RedHat. So, yes more or less there are some things in Windows, too. So, does it look like in Linux: 0000000 2 0 1 8 - 1 1 - 2 7 \t 1 3 \t V i 0000020 e n n a \t 1 \t 6 6 . 9 0 \t 3 0 \r 0000040 \n 0000041 – Bernd Feb 10 '19 at 11:51
  • Line terminators can be an issue going between Windows and Linux. Perhaps there is a stray carriage return messing up the last field in each record. – William Robertson Feb 10 '19 at 12:04
  • Yeah, I did a dos2unix now, and the last column value is not negativ anymore, it is positiv now instead. I think I have to see what the wrong value could cause. – Bernd Feb 10 '19 at 12:09
  • I do not get it. I dismissed the DECIMAL datatype in my loader file now, and suddenly it works. I still have to find out why it is working now, maybe because of the dos2unix!! – Bernd Feb 10 '19 at 12:15
  • Yes, it was the dos2unix command that was missing. Now, it is working! Thank you very much for your help. – Bernd Feb 10 '19 at 12:17
  • Regarding `DECIMAL`, don't use it. In my answer I use the default variable-length `CHAR` and let it convert on insert. `DECIMAL` would require actual binary values in the C representation of a decimal type. I don't know what that as and as I mentioned I have never seen it used in 30 years. – William Robertson Feb 10 '19 at 12:20
  • You are right. In the beginning as nothing worked, I tried to specify the data type by myself, then after having seen that I had to issue the dos2unix command, the DECIMAL data type was still on its place and in the end I had to get rid of it. So, now it works. – Bernd Feb 10 '19 at 12:29