Re: How to tell oracle SQL*Loader thousand seperating character?

On Sun, 25 Sep 2005 11:37:41 -0700, Christian Meier interested us by writing:

COmments embedded. All my references are from Oracle9i Release 2 documentation. You don't bother specifying Oracle version or OS, although that can make a difference. (BTW: Long winded, but I do answer your question, as well as address some of your myths.)

> But NLS_LANG is about character set isn't it? And do I realy have to

No. Your interpretation of NLS_LANG is much too narrow. NLS_LANG and globalization is all about the following:

        Language Support
        Territory Support
        Date and Time Formats
        Monetary and Numeric Formats
        Calendars Feature
        Linguistic Sorting
        Character Set Support
        Character Semantics
        Customization of Locale and Calendar Data
        Unicode Support

Specifically note the Numeric Formats. (This taken from the Globalization Support Guide). Recommend you read chapter 3 of that manual for more information. Each of these areas can be handled independently.

> change enviroments or Database globals? Isn't there a way like:

It really isn't all that difficult to set an environment variable immediately before the SQL Loader run, is it? We are not talking about the whole database, just one program.

> FIELDS TERMINATED BY ";" DECIMAL ".," ?
> define NLS_LANG in the Loader syntax fails
> LOAD DATA INFILE * and so on

Can I assume your data-to-load is "numbers represented as characters, so you can read them in a typical editor"? If so, you can use conversion functions to your heart's content, as shown in Case Study 7 in the Utilities manual Chapter 10.

Perhaps the TO_NUMBER function and associated format specification could help. For example (from the SQL Reference, to_number section in chapter 5)

SELECT TO_NUMBER('-AusDollars100','L9G999D99',

   ' NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY = ''AusDollars''    ') "Amount"
     FROM DUAL;     Amount

      -100

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***