Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Renuka Prasad.

Asked: March 29, 2001 - 11:23 pm UTC

Last updated: July 09, 2008 - 6:30 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I have faced a problem with NUMBER datatype. Currently I am
working on a code dump from Finland. The dump contains
the command "REPLACE(total_sum,'.',',')". This code works in Finland.
But it does not work when I import the dump here. The error I got was
"character to number conversion error". Then I removed the "REPLACE"
command from the code, then it started working. What I feel is the
Oracle loaded in Finland and here might have some difference. What is the reason do you feel? Is this problem related to NLS_LANGUAGE in
anyway?

Waiting for your reply....

Thanx and regards

Renuka Prasad.

and Tom said...

Well, not knowing where "here" is ;)


Some languages use 999,999.00
Others use 999.999,00

it looks like they were taking a STRING "123.456" and turning it into "123,456" and then converting into a number. In your database "123,456" is not a number but "123.456" is.

Yes, this is related to the NLS settings


ops$tkyte@ORA8I.WORLD> select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,

NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY

15 rows selected.

Specifically the nls_numeric_characters.

Rating

  (23 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

NLS problem

zj411@yahoo.com, October 25, 2002 - 1:50 pm UTC

Hi Tom,

I run the follwoing sql code and got errors:
select count(*) from t1@abcd;

ERROR at line 1:
ORA-12705: invalid or unknown NLS parameter value specified
ORA-02063: preceding line from abcd

I am using 9.0.1 sqlplus. However my co-worker can run the select statement above without any trouble.
By the way, The remote database abcd is oracle 7.1.5

Any ideas?

John

Tom Kyte
October 26, 2002 - 11:59 am UTC

Problem:
========

You have Oracle Server version 7.2 or higher

ORA-12705: invalid or unknown NLS parameter value specified
ORA-1810: format code appears twice

The above errors are reported when you use any SQL*Net associated
activity.


Example:
--------

$ SQLPLUS username/password@<alias> -- A SQL*Net connection.

or

SQLPLUS> CONNECT username/password@<alias> -- A SQL*Net connection.

or

SQLPLUS> SELECT * FROM table@<alias>; -- Database links.

or

SQLPLUS> COPY FROM username/password@<alias>... -- COPY command.


Solution:
=========

Change the local session parameters to settings understood by the
earlier version of Oracle that you are using.

1. For example, if you assume the above settings, use the following
commands on the client:

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

2. Set the environment variable NLS_LANG to AMERICAN.


Example:
--------

$ DEFINE NLS_LANG AMERICAN
$ SQLPLUS SCOTT/TIGER
SQLPLUS> ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
SQLPLUS> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
SQLPLUS> ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
SQLPLUS> SELECT * FROM table@<alias>;


Explanation:
============

Several new NLS parameter settings are introduced in Oracle 7.2
and above. When you attempt to establish new NLS settings against
an earlier Oracle release, errors are generated because these settings
are not valid for earlier versions of Oracle.

Here are examples of settings that result in errors:

NLS_LANGUAGE set to ENGLISH - ORA-12705 error.
NLS_DATE_FORMAT set to DD-Mon-RRRR - ORA-1810 error.
NLS_DATE_LANGUAGE set to ENGLISH - ORA-12705 error.
NLS_LANG set to ENGLISH - ORA-12705 error.

When you establish a SQL*Net connection, the NLS parameters
associated with the local (client) session have to be established
against the resulting remote (server) session.




login.sql

Marcio, August 06, 2003 - 1:31 pm UTC

Tom, is possible set up nls_numeric_characters='.,' just to my session?

I mean want to keep database nls parameter as is. But in my session I must nls_numeric_characters = ',.'

Look:
sre@MRP9I1> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.2.1

Always I have to "alter session set nls_numeric_characters=',.'" on my sql*plus.
I've tried put this on login.sql, but doesn't seem work.

Any suggest?
Thks, Marcio

Tom Kyte
August 09, 2003 - 11:45 am UTC

then you login.sql isn't firing as login.sql is just a script that you could have typed in by hand.

run it manually:

SQL> @login

and see what happens.  if it doesn't change the nls settings, it is not the right login.sql you changed!
 

May be a bug -- My bug!

Marcio, August 11, 2003 - 9:09 am UTC

It was missing ";"

...
set pagesize 9999
set linesize 140
set verify off
alter session set nls_date_format='dd-mo-yyyy'
alter session set nls_numeric_characters='.,'
...

When i put ";" semicolon works... Is it possible hide a message "session altered" from output sql*plus? -- Just first time when I login.

C:\migracao\loads>plus /

SQL*Plus: Release 9.2.0.2.0 - Production on Mon Aug 11 10:03:59 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production


Session altered. <<--------------


Session altered. <<--------------

Thks, Marcio


Tom Kyte
August 11, 2003 - 10:01 am UTC

just stick a set termout off/set termout on before/after the alters

ORA-12705

reader, November 04, 2003 - 9:22 am UTC

I have oracle 8i and 9i developer in tow different homes.
now i installed oracle 9i developer suite complete installation.after installation iam not able to access any of my oracle tools sql*plus or pl/sql developer .
iam getting ORA-12705 error.
i modified NLS_LANG to AMERICAN using regedit. (is this valid )
but i couldnt get out of this error.
any advice ?
thanks in advance
reader




Tom Kyte
November 04, 2003 - 10:19 am UTC



a valid setting would be something like

AMERICAN_AMERICA.WE8ISO8859P1


suggest you check out support note <Note:158654.1> on metalink.oracle.com

date language setting has no effect

A reader, November 17, 2003 - 3:23 am UTC

I am using Oracle 9i. I've set the nls_language=english and nls_date_languaage=english in init.ora. And I've checked the parameters are loaded by using "show parameters". But if I type "select to_char(sysdate, 'mon') the month is still not English. However, if I use "alter session set nls_language=english" then the month will be in English. Why does the language settings in init.ora has no effect?

Tom Kyte
November 17, 2003 - 6:44 am UTC

</code> http://asktom.oracle.com/Misc/NLSDateFormat.html <code>

client must have some NLS settings made and thus all init.ora NLS parameters are re-defaulted (not used from the init.ora)

Urdu Language Support

Masroor, March 20, 2004 - 4:58 pm UTC

Hi sir
I want to insert data in urdu
Please guide me on back end and front end level
how can i use UTF8 character set and how can i handle on front end. i use database oracle 8i (8.1.7) and developer 6i

I shall be thankful to u

Yours obediently

Tom Kyte
March 21, 2004 - 9:47 am UTC

sorry, i've never worked with that. try otn.oracle.com -> getting help -> discussion forums.

NLS_LENGTH_SEMANTICS while using UTF8 for NLS_CHARACTERSET

Moorthy Rekapalli, May 06, 2004 - 4:06 pm UTC

Tom,

I request you to correct my understanding if it is wrong.

I usually set NLS_LENGTH_SEMANTICS='CHAR' in init.ora before the database is created with the character set "UTF8".

My understanding is that varchar2/char/clob columns will store data in NLS_CHARACTERSET (character set in create database) and nvarchar2/nchar/nclob will store data in NLS_NCHAR_CHARACTERSET (national character set in create database).

In character semantics, when I do substr(varchar2_column,1,5) I will get the 5 characters instead of 5 bytes, which is the case when we use nls_length_semantics='BYTE'.

But, the information in Globalization Support Guide is throwing me off. Hence this question...

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch7.htm#6855 <code>

In the above URL, if you look at the Calculation Method in Table 7-2 for SUBSTR, I am kind of understanding that semantics are based on character set of a datatype. So, if I leave the nls_length_semantics to the default of 'BYTE' and if I store multi-byte characters in varchar2(30) column, do I get 5 characters OR 5 bytes when I do substr(varchar2_column,1,5)?

Thanks for your help and I am learning a lot from your site. In my view, you should be charging people who ask lot of questions. But, don't start with me :)

Thanks,
Moorthy.







Tom Kyte
May 06, 2004 - 10:06 pm UTC

The semantics of SUBSTR, LENGTH, and INSTR are always in characters and not in bytes.  NLS_LENGTH_SEMANTICS has no bearing on this.  In your example, if you use SUBSTR, you'll get 5 characters (assuming varchar2_column has a value of at least 5 characters).


As an example of this in an AL32UTF8 database:

--
-- First, test with CHARACTER semantics
--

SQL> alter session set nls_length_semantics = char;

Session altered.

SQL> create table foo( text varchar2(4000))
  2  /

Table created.

SQL> insert into foo values('aouäöü')
  2  /

1 row created.

SQL> select * from foo;

TEXT
--------------
aouäöü


SQL> select length(text) from foo;

LENGTH(TEXT)
------------
           6

SQL> select lengthb(text) from foo;

LENGTHB(TEXT)
-------------
           11

SQL> select substr(text,1,5) from foo;

SUBST
-----
aouäö

SQL> select lengthb(substr(text,1,5)) from foo;

LENGTHB(SUBSTR(TEXT,1,5))
-------------------------
                        9




--
-- Now let's test with BYTE semantics
--

SQL> drop table foo;

Table dropped.

SQL> alter session set nls_length_semantics = byte;

Session altered.

QL> create table foo( text varchar2(4000))
  2  /

Table created.

SQL> insert into foo values('aouäöü')
  2  /

1 row created.

SQL> select * from foo;

TEXT
--------------
aouäöü


SQL> select length(text) from foo;

LENGTH(TEXT)
------------
           6

SQL> select lengthb(text) from foo;

LENGTHB(TEXT)
-------------
           11

SQL> select substr(text,1,5) from foo;

SUBST
-----
aouäö

SQL> select lengthb(substr(text,1,5)) from foo;

LENGTHB(SUBSTR(TEXT,1,5))
-------------------------
                        9



What the Globalization guide is referring to is if you perform a SUBSTR of a VARCHAR2 versus an NVARCHAR2 column.  Depending upon your character set, supplementary characters are counted as either 1 or 2 characters.  All that's being suggested is to normalize comparisons using SUBSTR2 or SUBSTR4.


NLS_LENGTH_SEMANTICS is relevant for declarations of database columns and PL/SQL variables.  As an example, I was recently working on some PL/SQL code which would take in multibyte characters, compute the length of them in PL/SQL, and then create a database column to store these values.  But I was using LENGTH in PL/SQL (which as we know, always uses character semantics) and the NLS_LENGTH_SEMANTICS of the database was BYTE.  So as an example, even though I computed a length in characters of 30 for the widest value to be stored for some row, when the database column was created with a size of 30 with byte semantics, it wasn't large enough to hold 30 characters of Japanese data (in this case, it was 90 bytes of data). 

nls length semantics

reader, June 22, 2004 - 3:07 am UTC

Tom,

The character set of our database is UTF8. The version is 9.2.0.1. We like to store multilingual data. On the field width specification, my suggestion is

For new devlopment:

- in the init file, set nls_length_semantics = char

- define char column width as per the character storage requirements. for e.g. a varchar2(3) will be able to hold 3 characters instead of 3 bytes.

For existing applications:

- take schema level export
- change the nls_length_semantics to CHAR
- bounce the database
- drop the old schemas, create new schemas.
- Run the DDL scripts and do import with ignore=Y option.

Is the above approach correct? I feel that changing the column definition
in every table like a varchar2(3) to a varchar2(3 char) can be avoided towards
change from byte to char semantics. Kindly give your opinion on this.


Regards



Tom Kyte
June 22, 2004 - 8:04 am UTC

I would prefer to be explicit myself -- it would be a bummer to have someone forget to set a relatively obscure init.ora parameter in order to install your schema -- that and it would change the default behaviour making your database "unfriendly" towards other applications (a database is far to precious to host just a single application after all :)

I would (short of changing the column datatypes) opt for an alter session in my DDL script.  Makes it more "install friendly"
 
ops$tkyte@ORA9IR2> create table t1 ( x varchar2(50) );
 
Table created.
 
ops$tkyte@ORA9IR2> alter session set nls_length_semantics=char;
 
Session altered.
 
ops$tkyte@ORA9IR2> create table t2 ( x varchar2(50) );
 
Table created.
 
ops$tkyte@ORA9IR2> set linesize 70
ops$tkyte@ORA9IR2> desc t1;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 X                                            VARCHAR2(50 BYTE)
 
ops$tkyte@ORA9IR2> desc t2
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 X                                            VARCHAR2(50)
 
ops$tkyte@ORA9IR2> alter session set nls_length_semantics=byte;
 
Session altered.
 
ops$tkyte@ORA9IR2> desc t1
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 X                                            VARCHAR2(50)
 
ops$tkyte@ORA9IR2> desc t2
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 X                                            VARCHAR2(50 CHAR)
 

nls length semantics

reader, June 23, 2004 - 12:01 am UTC

Tom,

Very good explanation. Thanks....

Like to know when this requirement (storing multilingual data) is same across our applications in the same database, db level setting of nls_length_semantics is okay or session level is still the recommended way of doing it.

Regards

Tom Kyte
June 23, 2004 - 8:41 am UTC

you have my opinion already?

I would prefer to let the defaults be the defaults -- in order to not create confusion down the road. This is a rather obscure setting, people would not be expecting it to be the default.

at the end of the day, it is up to you of course.

NLS_LENGTH_SEMANTICS

A reader, May 10, 2005 - 11:12 am UTC

Hi Tom,
We use Siebel application and the datatype of the table columns is like

INTEGRATION2_ID VARCHAR2(30 CHAR)
INTEGRATION3_ID VARCHAR2(30 CHAR)
LANDLORD_NAME VARCHAR2(100 CHAR)
LANDLORD_PH_NUM VARCHAR2(40 CHAR)
OWNERSHIP_CD VARCHAR2(30 CHAR)
etc....


Our database character set is WE8MSWIN1252 and NLS_LENGTH_SEMANTICS is set to 'BYTE'. Is this seeting correct?

What happen is I set NLS_LENGTH_SEMANTICS to 'char'?

Thanks


Tom Kyte
May 10, 2005 - 1:14 pm UTC

you have a single byte database, don't need to change a thing. byte and char are synonymous for you.

Solved problem with umlauts (NLS)

Edvhans, May 23, 2006 - 12:07 pm UTC

in advance - I managed to learn english in about 3 months, so it is still 'under construction'

After solaris9 patch install we got many problems accessing Oracle 9.2.0.2 using sqlplus. The variable NLS_LANG solved our problem after about 12 Days hard work dealing with Exlibris-Ltd. (ALEPH 16.2, library system).

I am very glad!

Many thanks, Tom.

3 different results

Sean, July 11, 2006 - 3:32 pm UTC

Tom,
I have 8.1.7.4 database on the solaris 5.8.
use the same query get 3 different results:
1) on the unix server
SQL> select phys_complete_pct from task where proj_id=140307
  2  and user_text7 = 'RMS_A_CONTRACT'
/
  3  
PHYS_COMPLETE_PCT
-----------------
              .Ù×
2) on my XP pc, Oracle 8.1.7 client 
admuser@XXXX> select phys_complete_pct from task where proj_id=140307
  2  and user_text7 = 'RMS_A_CONTRACT'
  3  /

PHYS_COMPLETE_PCT
-----------------
              .ÃÄ

3) on my XP pc, toad
select phys_complete_pct from task where proj_id=140307
and user_text7 = 'RMS_A_CONTRACT';

PHYS_COMPLETE_PCT:
2.54

the NLS set on the db:
admuser@XXXX> select * from NLS_DATABASE_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               US7ASCII
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_NCHAR_CHARACTERSET         US7ASCII
NLS_RDBMS_VERSION              8.1.7.4.0

18 rows selected.

The NLS_LANG on the local PC set as 
WE8ISO8859PI

Could you explain for the reasons of the different results from the same query? 

Tom Kyte
July 12, 2006 - 3:11 pm UTC

is that column a string, a number, what?

and if you select dump(column) from table... what do you see.

A reader, July 12, 2006 - 3:32 pm UTC


admuser@sql> desc task
Name Null? Type
----------------------------------------------------- -------- --------------
TASK_ID NOT NULL NUMBER(10)
PHYS_COMPLETE_PCT NOT NULL NUMBER(10,2)
REV_FDBK_FLAG NOT NULL VARCHAR2(1)

***

***

SQlplus agrees this one with Toad:

admuser@sql> select dump(phys_complete_pct) from task where proj_id=140307 and
user_text7 = 'RMS_A_CONTRACT';

DUMP(PHYS_COMPLETE_PCT)
----------------------------------------------------------------------------------------------------
Typ=2 Len=2: 192,255





Tom Kyte
July 12, 2006 - 4:10 pm UTC

and unfortunately, you have junk in that field

ops$tkyte@ORA10GR2> create table t ( x number(10,2) );

Table created.

ops$tkyte@ORA10GR2> insert into t values ( 2.54 );

1 row created.

ops$tkyte@ORA10GR2> select dump(x) from t;

DUMP(X)
-------------------------------------------------------------------------------
Typ=2 Len=3: 193,3,55


so, what puts that data in there in the first place - looks like it is binding a binary NUMBER type and putting in bad bytes that work by accident sometimes.


 

Sean, July 12, 2006 - 5:22 pm UTC

Thanks a lot.
Is it possible to reproduce the "junk"?

ps. it's 8174.

Tom Kyte
July 12, 2006 - 5:48 pm UTC

sure with a pro*c program I can do so in an instant.

but answer MY QUESTION

what puts the data in there in the first place, and how do they do it.

Sean, July 12, 2006 - 5:54 pm UTC

it has been populated by another agency's application. it will take long time to get the answer from the agency.

Could you please show me by suing pro*c to populate the "junk"?

Tom Kyte
July 12, 2006 - 6:18 pm UTC

it would look something like this:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:21127620973437 <code>

that is a date, but concept the same.

Sean, July 12, 2006 - 8:26 pm UTC

Tom,
Thanks a lot again.
because of the "junk" data in the TASK, we had the application failed with ora-7445 no clue what caused for a couple of weeks (we had the Tar).
Finially we found the following query in the application failing which caused 7445:
select phys_complete_pct(p.segment1)
from projects_all p
where p.template_flag = 'N' and p.org_id = '81'
/

phys_complete_pct is the function:
create or replace
FUNCTION Phys_Complete_Pct(p_segment1 IN VARCHAR2)
RETURN VARCHAR2
IS
vPctComplete VARCHAR2(40);
BEGIN
SELECT t.phys_complete_pct
INTO
vPctComplete
FROM
TASK t,
project p
WHERE
p.proj_id = t.proj_id
AND t.user_text7 = 'RMS_A_CONTRACT'
AND EXISTS (SELECT 'x'
FROM actvcode ac, taskactv ta
WHERE ac.short_name = 'Prim'
AND ta.actv_code_id = ac.actv_code_id
AND 241 = ta.actv_code_type_id
AND t.task_id = ta.task_id)
AND p.proj_short_name = p_segment1;
RETURN(vPctComplete);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
RETURN('err:multiple prim found');
WHEN OTHERS THEN
RETURN('0');
END phys_complete_pct;
/

I found one record in TASK caused the function crashing with 7445


admuser> select phys_complete_pct from task where proj_id=140307
2 and user_text7 = 'RMS_A_CONTRACT';

PHYS_COMPLETE_PCT
-----------------
.ÃÄ

phys_complete_pct should be returned as a number ( number(10,2)) as defined in the table TASK. Instead it’s a junk – special character.
Looked into the function in question,



create or replace

FUNCTION Phys_Complete_Pct(p_segment1 IN VARCHAR2)

RETURN VARCHAR2

IS

vPctComplete VARCHAR2(40);

BEGIN

SELECT t.phys_complete_pct

INTO

vPctComplete

FROM

……

The function defines vPctComplete as VARCHAR2 and try to convent the number of t.phys_complete_pct to Varchar2. The function shall not convert it VARCHAR, since it should return a number for phys_complete_pct as supposed.

But the PHYS_COMPLETE_PCT is the junk character for proj_id =140307

The query crashes when the function tries to convent Varchar2 for the junk character defined in number(10,0).
it has been corrected with recreated the function with the correct defines in the function.

Though the issue has been corrected, I have the question and still cannot be answered by the support:

I have cloned the database in question to another server wtih the same OS and the same Oracle 8174, patchsets and init.ora.
On the cloned database the query returns the same "junk". But the failure of query with ora7445 has not been reproduced.
Tom, why I cannot reproduce the failure on the clone db on different server? what I should look?




Tom Kyte
July 12, 2006 - 8:54 pm UTC

ora-7445 is segmentation fault, invalid memory addressing. Moving to different hardware, changing *anything* can cause that problem to "move".

dump()

A reader, July 14, 2006 - 1:21 am UTC

Tom,
interested in the dump() used here. looked up metalink and could not find good explanation on. could you please elabrote the follwoing results?

sql> create table dump_test ( x number(10,2) );

Table created.

sql> insert into dump_test values (1233.01)
2 /

1 row created.

sql> insert into dump_test values (1233)
2 /

1 row created.

sql> insert into dump_test values (100000)
2 /

1 row created.

sql> commit;

Commit complete.

sql> select dump(x) dx, x from dump_test;

DX X
---------------------------------------- ---------
Typ=2 Len=4: 194,13,34,2 1233.01
Typ=2 Len=3: 194,13,34 1233
Typ=2 Len=2: 195,11 100000

could you please explain each DX?
example, why 100000 has len=2 vs. 1233 has 3?


Tom Kyte
July 14, 2006 - 8:26 am UTC

because Oracle numbers are varying length fields - they are not 2 and 4 byte "integers" or 4 and 8 byte floats/doubles.

they are numbers stored using 0 to 22 bytes with 38 digits of precision.

<quote SRC=Expert Oracle Database Architecture>

It is interesting and useful to note that the NUMBER type is, in fact, a variable length datatype on disk and will consume between 0 and 22 bytes of storage. Many times, programmers consider a numeric datatype to be a fixed-length type—that is what they typically see when programming with 2- or 4-byte integers and 4- or 8-byte floats. The Oracle NUMBER type is similar to a variable length character string. We can see what happens with numbers that contain differing amounts of significant digits. We’ll create a table with two NUMBER columns and populate the first column with many numbers that have 2, 4, 6, … 28 significant digits. Then, we’ll simply add 1 to each of them:

ops$tkyte@ORA10GR1> create table t ( x number, y number );
Table created.
 
ops$tkyte@ORA10GR1> insert into t ( x )
  2  select to_number(rpad('9',rownum*2,'9'))
  3    from all_objects
  4   where rownum <= 14;
14 rows created.
 
ops$tkyte@ORA10GR1> update t set y = x+1;
14 rows updated.

Now, if we use the built-in VSIZE function that shows how much storage the column takes, we can review the size differences between the two numbers in each row:

ops$tkyte@ORA10GR1> set numformat 99999999999999999999999999999
ops$tkyte@ORA10GR1> column v1 format 99
ops$tkyte@ORA10GR1> column v2 format 99
ops$tkyte@ORA10GR1> select x, y, vsize(x) v1, vsize(y) v2
  2    from t order by x;
 
                             X                              Y  V1  V2
------------------------------ ------------------------------ --- ---
                            99                            100   2   2
                          9999                          10000   3   2
                        999999                        1000000   4   2
                      99999999                      100000000   5   2
                    9999999999                    10000000000   6   2
                  999999999999                  1000000000000   7   2
                99999999999999                100000000000000   8   2
              9999999999999999              10000000000000000   9   2
            999999999999999999            1000000000000000000  10   2
          99999999999999999999          100000000000000000000  11   2
        9999999999999999999999        10000000000000000000000  12   2
      999999999999999999999999      1000000000000000000000000  13   2
    99999999999999999999999999    100000000000000000000000000  14   2
  9999999999999999999999999999  10000000000000000000000000000  15   2
 
14 rows selected.

We can see that as we added significant digits to X, the amount of storage required took increasingly more room. Every two significant digits added another byte of storage. But a number just one larger consistently took 2 bytes. When Oracle stores a number, it does so by storing as little as it can to represent that number. It does this by storing the significant digits, an exponent used to place the decimal place, and information regarding the sign of the number (positive or negative). So, the more significant digits a number contains, the more storage it consumes. 

That last fact explains why it is useful to know that numbers are stored in varying width fields. When attempting to size a table (e.g., to figure out how much storage 1,000,000 rows would need in a table), you have to consider the NUMBER fields carefully. Will your numbers take 2 bytes or 20 bytes? What is the average size? This makes accurately sizing a table without representative test data very hard. You can get the worst-case size and the best-case size, but the real size will likely be some value in between.
</quote> 

Scale

Michel Cadot, July 15, 2006 - 4:01 pm UTC

HI,

And this is a reason to always give a scale on a column number datatype to avoid wasting space with too much decimals and have a reasonable maximum for the storage column size. 
I have this one in one of my files, I think it comes from Steve Adams more than 10 years ago:

SQL> create table numbers (n1 number, n2 number(10,4));

Table created.

SQL> insert into numbers values (3*(1/3), 3*(1/3));

1 row created.

SQL> select * from numbers;

        N1         N2
---------- ----------
         1          1

1 row selected.

SQL> select vsize(n1), vsize(n2) from numbers;

 VSIZE(N1)  VSIZE(N2)
---------- ----------
        21          2

1 row selected.

Regards
Michel
 

Tom Kyte
July 16, 2006 - 9:32 am UTC

just be careful of what you ask for in the end.  

I don't want people to thing "we should use ,4" - you need to think about what you are going to store in there and why.  I would not be a fan of using this technique as a space saving device.  

precision,scale - they are edits of the data, integrity constraints.




ops$tkyte%ORA10GR2> select n1, n2 from numbers where n1 <> n2;

        N1         N2
---------- ----------
         1          1



 

Scale

Michel Cadot, July 16, 2006 - 9:43 am UTC


This was not my purpose to think of scale as a way to save space but to warn that not giving a correct one may waste space.
Of course, you have to give the scale for the precision your business needs but not more.


Tom Kyte
July 16, 2006 - 9:57 am UTC

... you have to give the scale for the precision your business needs but
not more. ..


we agree :)

languange changed in the session

A reader, July 04, 2008 - 2:59 pm UTC

tom I'm confused, one customer had the problem sometimes the date on the invoice were printed in english instead of spanish.

So we set the language, supposing the problem was it was installed on an english window and they didn't note that before and only some months are differents for ejemplo january and enero JAN/ENE, other are not for example JUNE JUNIO JUN/JUN.

So in the session we set the variable, after that in a loop all the invoices were printed correctly using the spanish date, 2 hours after, in some moment the date started to be printed on english, in the same session!!!!

We set the language in the to_char statement.

I looked it like something unusual, because it was the only one, but I found the same happed in other customer, the first impression is in english and the others are in spanish, after that all were in spanish, after the first impression is like oracle look that date and say "Oh I have to print in spanish" and start printing in spanish, and never reproduce, neither reconnecting to the database, etc."

The question is what could be causing that could be something related to the awr. thre is no something like alter system set nsl_numeric_characters for example, Any idea?

Thank you :)
Tom Kyte
July 07, 2008 - 11:35 am UTC

are you saying that you execute something like:

ops$tkyte%ORA10GR2> select to_char(sysdate,'dd-month-yyyy', 'nls_date_language=spanish') from dual;

TO_CHAR(SYSDATE,'D
------------------
07-julio     -2008


and it sometimes comes out in english anyway?

A reader, July 07, 2008 - 6:22 pm UTC

Hi Tom thanks,

No, my friend put in this way to solve the problem, because the problem I explained previously, but anyway I don't understand how can change the language of a session after I saw it twice in differents customers I decide to ask for.

Oracle 10.2.3 on windows
Tom Kyte
July 07, 2008 - 9:09 pm UTC

If the language changed - then something in your code somewhere changed the language.

Meaning, some program in your connection pool is changing the NLS settings.

Meaning, if you DEPEND on some nls settings to be in place FOR SURE, then you need to issue the alter session settings everytime you grab a connection.

A reader, July 09, 2008 - 6:20 pm UTC

Hi tom, there is nothing in the code that changed that, for example the last error was where there are several databases in the same server, after something happens, i suppose something related to awr (i tried reconnecting, reentering the aplication and restarting the customer client computer and couldn't reproduce it), you send the same report from all the database, the fist report you send, shows the date in english if you print from another database or reprint from the same database the date is in spanish, it is weird, there must be some kind of bug, the customer is trying to find the way to reproduce when he has time, this is not too important, so i'll comment if we find the way to reproduce it.

Thanks.
Tom Kyte
July 09, 2008 - 6:30 pm UTC

not that I don't believe you but ....

Have you really read all of the code your application server uses - both in the middle tier as well as any database stuff it invokes...

someone changed the session settings and you got their connection from the connection pool later.

A reader, July 10, 2008 - 2:08 pm UTC

I either believe this is true, I'm going to see how to reproduce it, but when you send in oracle reports (one report having several pages), and in the middle it changes the language (it uses a function table) then something don't seems to make sense I use dedicated connections.

I'll tell you if found the way to reproduce it.
Thank you. :]