Decimal fields in Oracle external table

Question

I build an external table in Oracle 12c using the following code

CREATE TABLE T_EXT_TABLE
(   
"ENTIDADE_NAME" VARCHAR2(50 BYTE), 
"ENTIDADE_AGE" NUMBER(*,0)
) 
ORGANIZATION EXTERNAL 
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY "EXTTBL_DATA_DIRECTORY"
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE 
    FIELDS TERMINATED BY ';' 
    lrtrim
    (
         ENTIDADE_NAME 
        ,ENTIDADE_AGE
    )
  )
  LOCATION
  (
    'file.csv'
   )
)
REJECT LIMIT UNLIMITED 
PARALLEL 5 ;

And here's some data in my file.csv

PT;200;
PT;200.04;

I'd like to obtain an error in the 2nd record, since I have decimal places and the field in the external table is set as integer (number (*,0)). However, no matter how much I change the field definition in the external table, the record always gets selected, with the ENTIDADE_AGE field being rounded up.

Is there anything I can do about this?

What do you get when you only does `"ENTIDADE_AGE" NUMBER`. Nothing you write for precision. — XING, Nov 07 '17 at 13:09
I wrote NUMBER(*,0) in the external table definition, which I assumed it would prevent decimal numbers from being selected... Also I've tried to write NUMBER(8,0) and still get the rounded result. I actually want the record to be rejected once there are decimal places. — miduarte, Nov 07 '17 at 14:22
If I don't write precision, both records will be selected as they are, i. e., 200 and 200.04 — miduarte, Nov 07 '17 at 15:18
I've tried the code above but removing the precision (*,0) and once I select from the external table I get both records, 200 and 200.04 — miduarte, Nov 07 '17 at 15:54
If i create a table with column as number without precision and the try to insert 4.44 , it only allows 4 to get insert and same should happen with external table as well — XING, Nov 07 '17 at 15:57
If I write NUMBER, with no precision, the record will be selected showing only the integer part, leaving the decimal part out. But i actually want to obtain an error, I want the record to be rejected when it has a decimal part. — miduarte, Nov 08 '17 at 15:22

Marmite Bomber · Answer 1 · 2017-11-08T08:05:02.380

You can't define a NUMBER Format for a CHAR field in external table. It is only possible to define a [date_format_spec][1] (i.e. control the format of a DATE column)

You apparently can't use the LOAD WHEN to skip a number with decimal places as well, as only equal or not equal operators are allowed. (Not really sure as I didn't found this in documentation, but I failed using LIKE )

What should be possible, is to set the decimal point character in a such way, that the conversion fails (in your case other than .) and the record will be rejected with

 ORA-01722: invalid number

Unfortunately you can't use alter session set NLS_NUMERIC_CHARACTERS as this is ignored in the external table.

In Oracle 12r2 there is a parameter TERRITORY that should serve this purpose. Try to set it to territory where the default decimal character is other than . This should work. Tested fine in 12.2.0.1.0

Using this file

 PT;200
 PT;200.04

that has point as a decimal character you make define the expected decimal character in the external table with the TERRITORY parameter

  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE
    TERRITORY AMERICA
    FIELDS TERMINATED BY ';'

For TERRITORY AMERICA both records are selected (as the number can be parsed)

ENTIDADE_NAME                                      ENTIDADE_AGE
-------------------------------------------------- ------------
PT                                                          200 
PT                                                       200,04

Now change the TERRITORY

  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE
    TERRITORY GERMANY
    FIELDS TERMINATED BY ';'

For TERRITORY GERMANY (as en example where comma as a decimal character) only the first record is processed. The second one is rejected as the number can't be parsed

ENTIDADE_NAME                                      ENTIDADE_AGE
-------------------------------------------------- ------------
PT                                                          200

Which is your expected behaviour. In the log file you see

 ORA-01722: Ungültige Zahl

which is German for ORA-01722: invalid number

Thanks for your reply. The TERRITORY clause had occurred to me as well, but the thing is I have another column in the same external table which is a double, and which decimal part should be accepted. this makes it trickier. — miduarte, Nov 08 '17 at 15:19
@miduarte you can allways define the external table that accepts all rows wrapped with a trivial view that suppress the rows with non integer values. — Marmite Bomber, Nov 08 '17 at 16:08

Decimal fields in Oracle external table

1 Answers1