Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Changing decimal separator from infile when using External table

668677Mar 25 2009 — edited Mar 25 2009

Hi all,

I have a little problem,
I have a text file with, two columns one text and one decimal number, the input file separate the decimals with , and the db setting is .

when i try to import this file, see below, via external tables it doesn't work, but if i change all decimals in the infile from , to . it works. and all records is visible.

I have tried to change nls_numeric_character by doing this for the session.

alter session set nls_numeric_characters = ',.'

Any idea?
the supplier of the infile will not change the format for the decimals separator and for the rest of the database we need the decimal separator to be .

Thanks!!
Lasse

infile:

a;1,1
d;1,2
f;1,3
g;1,4
å;1,5

External table:

CREATE TABLE EXT_TEST2
(
A VARCHAR2(30),
B NUMBER(38,4)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY TXTSE
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
( A char ,
B char ))
LOCATION (TXTSE:'Document2.txt')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;