Home » RDBMS Server » Server Utilities » Are inserts NLS sensitive? (Oracle 10g)
Are inserts NLS sensitive? [message #297456] Thu, 31 January 2008 15:14 Go to next message
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 #297545 is a reply to message #297456] Fri, 01 February 2008 05:23 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member

Your inputs must comply with datatype as bare minimum compliance.

Regards

Manjit Kumar [mkbhati]



Re: Are inserts NLS sensitive? [message #297551 is a reply to message #297456] Fri, 01 February 2008 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just make a little test:
SQL> create table t (id number);

Table created.

SQL> alter session set NLS_NUMERIC_CHARACTERS='#/';

Session altered.

SQL> insert into t values (1.5);

1 row created.

SQL> select * from t;
        ID
----------
       1#5

1 row selected.

SQL> insert into t values (1#5);
insert into t values (1#5)
                       *
ERROR at line 1:
ORA-00911: invalid character

Regards
Michel
Re: Are inserts NLS sensitive? [message #297586 is a reply to message #297551] Fri, 01 February 2008 09:54 Go to previous messageGo to next message
timg
Messages: 4
Registered: January 2008
Junior Member
Thank you - that is what I expected.

Tim
Re: Are inserts NLS sensitive? [message #297588 is a reply to message #297586] Fri, 01 February 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Actually the test was not good, it should be:
SQL> create table t (id number);

Table created.

SQL> alter session set NLS_NUMERIC_CHARACTERS='#/';

Session altered.

SQL> insert into t values ('1.5');
insert into t values ('1.5')
                      *
ERROR at line 1:
ORA-01722: invalid number


SQL> insert into t values ('1#5');

1 row created.

SQL> insert into t values (to_number('1.5'));
insert into t values (to_number('1.5'))
                                *
ERROR at line 1:
ORA-01722: invalid number


SQL> insert into t values (to_number('1#5'));

1 row created.

Using strings and (implicit or explicit) conversion.

Regards
Michel
Re: Are inserts NLS sensitive? [message #297589 is a reply to message #297588] Fri, 01 February 2008 10:42 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
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 Go to previous messageGo to next message
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


Re: Are inserts NLS sensitive? [message #299737 is a reply to message #299640] Wed, 13 February 2008 01:11 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Try to use "to_number(:col)" in control file.

Regards
Michel
Previous Topic: Condition in SqlLoader [Merged]
Next Topic: Error while using IMP
Goto Forum:
  


Current Time: Sat Nov 09 14:45:31 CST 2024