Are inserts NLS sensitive? [message #297456] |
Thu, 31 January 2008 15:14 |
timg
Messages: 4 Registered: January 2008
|
Junior Member |
|
|
I know about using NLS_TERRITORY and NLS_NUMERIC_CHARACTERS to format numeric output. Is input - both via
INSERT INTO COL VALUES (number) and using SQL Loader sensitive to these?
My experiments indicate not - that all numbers must be entered with no group separator and the period as decimal separator. e.g. 1000.00
Thanks
Tim
|
|
|
|
|
|
|
Re: Are inserts NLS sensitive? [message #297589 is a reply to message #297588] |
Fri, 01 February 2008 10:42 |
timg
Messages: 4 Registered: January 2008
|
Junior Member |
|
|
Thanks again. This is almost, but not entirely consistent. By its very nature everything typed into a SQL statement is a string
insert into col values (5.3)
insert into col values ('5.3')
In both cases the 5.3 is being converted from a string to a number, but in the second example the quotes explicitly make it a string.
I have not tried sqlldr yet - or even know how to use it. I did read somewhere that it turned each insert into an insert statement (which seems very inefficient, so I may have misread). Does the same occur - when a number is quited it will convert correctly, but if not quoted then it will not?
Tim
|
|
|
Re: Are inserts NLS sensitive? [message #297590 is a reply to message #297589] |
Fri, 01 February 2008 10:49 |
|
Michel Cadot
Messages: 68728 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | In both cases the 5.3 is being converted from a string to a number
|
No, in the first case, a number is passed to Oracle SQL engine, in the second one a string is passed and then converted to a number by this engine. (I made this error when I posted the first example.)
Quote: | I did read somewhere that it turned each insert into an insert statement (which seems very inefficient, so I may have misread).
|
It depends if you use direct mode or not. In old standard mode, each rows is converted to an insert statement.
Quote: | Does the same occur - when a number is quited it will convert correctly, but if not quoted then it will not?
|
For sqlldr, what you give is string (unless you use internal or binary format but this is not the case here).
Regards
Michel
|
|
|
Re: Are inserts NLS sensitive? [message #299640 is a reply to message #297590] |
Tue, 12 February 2008 16:19 |
timg
Messages: 4 Registered: January 2008
|
Junior Member |
|
|
Quote: | For sqlldr, what you give is string (unless you use internal or binary format but this is not the case here).
|
I tried an experint with a data file like
"123,45"
setting NLS_TERRITORY=GERMAN on the client and using sqlldr. It failed to load.
Is there any way that I can use a comma as decimal separator with sqlldr (as I can in an insert statement by quoting the number)
Thanks
Tim
|
|
|
|