OraFAQ Forum: Server Utilities » Are inserts NLS sensitive?

Home » RDBMS Server » Server Utilities » Are inserts NLS sensitive? (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Are inserts NLS sensitive? [message #297545 is a reply to message #297456]

Fri, 01 February 2008 05:23

mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai

Member

Your inputs must comply with datatype as bare minimum compliance.

Regards

Manjit Kumar [mkbhati]

Report message to a moderator

Re: Are inserts NLS sensitive? [message #297551 is a reply to message #297456]

Fri, 01 February 2008 05:56

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: Are inserts NLS sensitive? [message #297586 is a reply to message #297551]

Fri, 01 February 2008 09:54

timg
Messages: 4
Registered: January 2008

Junior Member

Thank you - that is what I expected.

Tim

Report message to a moderator

Re: Are inserts NLS sensitive? [message #297588 is a reply to message #297586]

Fri, 01 February 2008 10:20

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

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

Report message to a moderator

Re: Are inserts NLS sensitive? [message #297590 is a reply to message #297589]

Fri, 01 February 2008 10:49

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

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

Report message to a moderator

Re: Are inserts NLS sensitive? [message #299737 is a reply to message #299640]

Wed, 13 February 2008 01:11

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Try to use "to_number(:col)" in control file.

Regards
Michel

Report message to a moderator

Previous Topic:	Condition in SqlLoader [Merged]
Next Topic:	Error while using IMP

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 15 23:50:43 CDT 2024