SQL*Loader FLOATING POINT CORRUPTION IN DIRECT PATH MODE.

From: Peter Hanlon <pete_at_hanlon.co.uk>
Date: Sat, 21 Aug 1999 12:05:56 +0100
Message-ID: <935233685.16274.0.nnrp-04.9e98b813@news.demon.co.uk>

SQLLDR Problem

---

During some recent performance tests comparing our companies own loading tool DataMill and SQL*Loader, we discovered that SQL*Loader was corrupting floating point data when it was run in direct path mode under Oracle 8.1.5.

The problem seems to be that sqlldr incorrectly truncates the floating point data. On a table with a column defined as float (25), we should be able to store values upto 6/7 digits long. When a value greater than 1000 is loaded using direct path, the number is truncated (as if the column had been defined as a float(13)), there is no warning of the truncation given
in the sqlldr logs, it just happens. If however you load the same data using a conventional load or DataMill, the data is inserted correctly.

If you are interested in trying DataMill, a trial copy can be downloaded from
http://www.hanlon.co.uk. The supported platforms are currently NT, Solaris, Linux and
HP-UX.

Floating point example

---

1. Create the following table. CREATE TABLE SQLLDR (KEY NUMBER(3), VAL FLOAT(25));
2. Create the control file shown below and run sqlldr in direct mode.

 ------Test.ctl--------
 LOAD DATA
 INFILE *
 TRUNCATE INTO TABLE SQLLDR
 FIELDS TERMINATED BY ','
(

 key,
 val
 )
 BEGINDATA

 1,111111
 2,1112
 3,1111
 4,1000
 5,9999
 6,999

 ------Test.ctl--------

 sqlldr / control=Test.ctl direct=y

3. Select the results back from the SQLLDR table. The results from my

    load are shown below. As you can see that values greater than 1000     have been truncated.

 SQL*Plus: Release 8.1.5.0.0 - Production on Sat Aug 21 07:23:59 1999

(c) Copyright 1999 Oracle Corporation. All rights reserved.

 Error accessing PRODUCT_USER_PROFILE
 Warning: Product user profile information not loaded!  You may need to run PUPBLD.SQL as SYSTEM

 Connected to:
 Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production  With the Partitioning and Java options  PL/SQL Release 8.1.5.0.0 - Production

 SQL> select * from sqlldr;

        KEY VAL

• ---------- 1 111000 2 1110 3 1110 4 1000 5 10000 6 999

 6 rows selected.

4. Now run sqlldr again but in conventional mode and select back the results

   when the load has finished. This time the results are as you would expect.

 sqlldr / control=Test.ctl

 SQL*Plus: Release 8.1.5.0.0 - Production on Sat Aug 21 07:25:22 1999

(c) Copyright 1999 Oracle Corporation. All rights reserved.

 Error accessing PRODUCT_USER_PROFILE
 Warning: Product user profile information not loaded!  You may need to run PUPBLD.SQL as SYSTEM

 Connected to:
 Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production  With the Partitioning and Java options  PL/SQL Release 8.1.5.0.0 - Production

 SQL> select * from sqlldr;

        KEY VAL

• ---------- 1 111111 2 1112 3 1111 4 1000 5 9999 6 999

 6 rows selected.

--

Peter Hanlon.............................Email :  pete_at_hanlon.co.uk

Hanlon Consulting Ltd.............Phone : +44 (0)20 8391 4021 http://www.hanlon.co.uk/ ........Fax : +44 (0)870 056 7283 Received on Sat Aug 21 1999 - 06:05:56 CDT