Load float data into external table in Oracle

Question

I'm having a troubles with loading FLOAT data into the external table... Data like that: +00000700000,00. Any ideas how to do this? FLOAT EXTERNAL or FLOAT(15) does not work... Of course in CREATE statement I have FLOAT column.

FLOAT will only load first 4 characters... Leaving it as a CHAR(15) also does not work...

create or replace PROCEDURE LOAD_TO_EXTERNAL AS
  PATH_FILE VARCHAR2(100) := 'TEST_FILE.TXT';
  DELIMETER VARCHAR2(100) := '-';
  DATE_FORMATTING VARCHAR2(100) := 'YYYY-MM-DD';
  STMT VARCHAR2(5000);
BEGIN
  STMT := 'CREATE TABLE "TEST_EXT"
    (
      DATA DATE,
      NAME VARCHAR2(40),
      AGGR_NUM FLOAT(27)
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER DEFAULT DIRECTORY MY_DIR ACCESS PARAMETERS
      (RECORDS DELIMITED BY ''' || DELIMETER || '''
      FIELDS LRTRIM (
          DATA CHAR(10) DATE_FORMAT DATE MASK ''' || DATE_FORMATTING || ''',
          NAME CHAR(40),
          AGGR_NUM FLOAT         )
      ) LOCATION (''' || PATH_FILE || ''')
    )
    REJECT LIMIT UNLIMITED';

    EXECUTE IMMEDIATE STMT;
END LOAD_TO_EXTERNAL;

kfinity · Answer 1 · 2017-09-29T14:02:26.433

First, to convert a string like +00000700000,00 into an Oracle number value, you want something like to_number('+00000700000,00', 'S00000000000,00').

Second, you need to do any character-to-number conversion in the FIELDS section of your loader.

Third, you probably don't want to specify FLOAT precision yourself - the precision for FLOAT is specified in binary digits, with a default of 126, which is equivalent to 38 decimal places. You're using FLOAT(27), which looks like about 10 decimal places, but you're loading 13 digit numbers. If you aren't concerned with ANSI portability, you might as well use the NUMBER datatype, which is what Oracle uses behind the scenes anyway.

Try this, and let me know how it works.

create or replace PROCEDURE LOAD_TO_EXTERNAL AS
  PATH_FILE VARCHAR2(100) := 'TEST_FILE.TXT';
  DELIMETER VARCHAR2(100) := '-';
  DATE_FORMATTING VARCHAR2(100) := 'YYYY-MM-DD';
  STMT VARCHAR2(5000);
BEGIN
  STMT := 'CREATE TABLE "TEST_EXT"
    (
      DATA DATE,
      NAME VARCHAR2(40),
      AGGR_NUM FLOAT
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER DEFAULT DIRECTORY MY_DIR ACCESS PARAMETERS
      (RECORDS DELIMITED BY ''' || DELIMETER || '''
      FIELDS LRTRIM (
          DATA CHAR(10) DATE_FORMAT DATE MASK '' || DATE_FORMATTING || '',
          NAME CHAR(40),
          AGGR_NUM "to_number(:AGGR_NUM, '''S00000000000,00''')" )
      ) LOCATION (''' || PATH_FILE || ''')
    )
    REJECT LIMIT UNLIMITED';

    EXECUTE IMMEDIATE STMT;
END LOAD_TO_EXTERNAL;

Throws `error encountered while parsing access parameters` when I want to select the data. I tried different variant with quotes, adding type before function, but without success. — Lui, Sep 29 '17 at 08:54
Ah, you're right. I forget that SQLLDR parameters aren't all valid in external tables. My mistake. — kfinity, Sep 29 '17 at 14:07

score 0 · Answer 2 · answered Sep 28 '17 at 20:28

The functionality for creating external tables in Oracle has limited functionality concerning data conversion. If you want a more powerful solution, you can use sql loader and a specific control file to convert the data.

An alternative solution might be to create the external table to access the data from the file and then create an additional table, loading the data into it using create table as select ... and do the appropriate conversion. Depending on your performance requirements, you can also create a view to do the conversion.

The problem with your data is, that oracle doesn't like the plus sign when converting the text to the float. The following example would work, but leaves you with a separate sign column, which needs to be handled separately.

CREATE TABLE TEST_EXT
(
  DATEX date,
  NAMEX VARCHAR2(5),
  SGN VARCHAR2(1),
  FLT float
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER DEFAULT DIRECTORY MY_DIR 
  ACCESS PARAMETERS 
    (
        RECORDS FIXED 30 FIELDS
        (
            DATEX CHAR(10) date_format date mask 'YYYY-MM-DD',
            NAMEX CHAR(5),
            SGN CHAR(1),
            FLT CHAR(14) 
        )
    ) LOCATION ('test.txt')
)
REJECT LIMIT UNLIMITED;

test.txt

2017-09-24ABCDE+00000700000.99

Query the table:

select datex, namex, sgn, flt from test_ext;

Output:

DATEX      NAMEX S        FLT
---------- ----- - ----------
2017-09-24 ABCDE +  700000.99

The problem is the comma in the value... With comma instead of dot it is not working... — Lui, Sep 29 '17 at 08:45
I would assume, that the comma problem can be worked around by setting the locale to match the number format. — Guenther, Sep 29 '17 at 12:48

Load float data into external table in Oracle

2 Answers2