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
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
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
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
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?
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
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.
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
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
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
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?
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
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.
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"?
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 its 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?
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?
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 typethat 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. Well create a table with two NUMBER columns and populate the first column with many numbers that have 2, 4, 6,
28 significant digits. Then, well 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
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.
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 :)
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
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.
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. :]