Re: How do I change the interpreted decimal character in an external table file

On Wed, 31 Aug 2005 21:16:03 +0200, Fabrizio <fabrizio.magni_at_mycontinent.com> wrote:

>Jaap W. van Dijk wrote:
>> On Wed, 31 Aug 2005 12:07:47 +0200, Fabrizio
>> <fabrizio.magni_at_mycontinent.com> wrote:
>>
>> ....
>>
>>> ACCESS PARAMETERS
>>> ( records delimited by newline
>>> badfile admin_bad_dir:'foo_sa%a_%p.bad'
>>> logfile admin_log_dir:'foo_sa%a_%p.log'
>>> CHARACTERSET ITALIAN_ITALY.WE8MSWIN1252
>>
>> ....
>>
>
>Jaap,
>now I'm the one puzzled.
>I'm testing the code on a new 10gR2 on linux obtaining an error (the
>table is created succesfully but cannot be selected).
>
>The code was working on a developing 9.2 on windows...
>
>On a bigger datawarehouse on solaris I found I added:
>
>ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,';
>
>before my mappings. But the ITALIAN_ITALY is missing.
>
>The codes are at least three years old and I need the notes of that
>period to understand what I was doing.
>
>On another windows (8.1.7.4) I had the same problem even if related to HS.
>
>My solution was to had:
>
>HS_LANGUAGE= ITALIAN_ITALY.WE8MSWIN1252
>
>in the HS config file.
>
>Sorry if I gave you a false hope.

Fabrizio,

A colleague of mine found the following Metalink note, which answers all our questions, and comes with a workaround: Note 268906.1. The gist:
- you can only set the decimal character for external tables at instance level, by specifying the relevant NLS initialisation parameters.
- as a workaround, specify your number fields as VARCHAR2 in the external table, and define a view on the external table that performs TO_NUMBER conversions for these fields (without a format mask). What character is interpreted as the decimal character, depends on the NLS-settings of the session in which you use the view.

Jaap. Received on Thu Sep 01 2005 - 12:02:54 CDT