Solved

Data Length for Oracle 8i and 9i

Posted on 2004-03-25
7
2,296 Views
Last Modified: 2012-08-14
Will a field that currently is X characters long accommodate X characters from a different language.(For example if the  description field is 4000 characters  of type VARCHAR, it would accommodate 4000 English characters. Would it also accommodate 4000 other Language characters) Or should we double the length of the field to be on the safe side.
0
Comment
Question by:vbandaru
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Expert Comment

by:rajnadimpalli
ID: 10680512
SQL> create table DEMO
  2  (
  3  COL1 varchar2(4000)
  4  )
  5  /

Table created.

SQL> desc DEMO
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(4000)

SQL> create table NDEMO
  2  (
  3  COL2 nvarchar2(4000)
  4  )
  5  /
)
*
ERROR at line 4:
ORA-00910: specified length too long for its datatype


SQL> create table NDEMO
  2  (
  3  COL2 nvarchar2(2000)
  4  )
  5  /

Table created.

SQL> desc NDEMO
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL2                                               NVARCHAR2(2000)

SQL> create table NDEMO
  2  (
  3  COL2 nvarchar2(2001)
  4  )
  5  /
)
*
ERROR at line 4:
ORA-00910: specified length too long for its datatype


SQL>

I hope you got it.... If you..let me know...

-R
0
 
LVL 7

Author Comment

by:vbandaru
ID: 10680606
Almost got it..
So If I need to support 4000 Characters for any language what data type should I use??
0
 

Expert Comment

by:gulhaugen
ID: 10680933
I believe 4000 of any language is not possible.

varchar2(length B) gives byte semantics, and validates length with that.
varchar2(length C) gives char semantics, and validates characters.
However, the underlying storage is still restricted to 4000 bytes
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Author Comment

by:vbandaru
ID: 10687535
rajnadimpalli,
I tried this on Oracle 8i and it did not give me any error.

SQL> create table NDEMO
  2  (
  3  COL2 nvarchar2(4000)
  4  )
  5  /
)

Thanks
0
 

Accepted Solution

by:
gulhaugen earned 125 total points
ID: 10689296
From Oracle Doc:

European data in UTF8 is usually more compact than in AL16UTF16. As a result, you will save disk space and have better response time when most of the multilingual data stored in the database is European data.
The maximum lengths for the NCHAR and NVARCHAR2 columns are 2000 and 4000 characters respectively, which is more than those for NCHAR (1000) and NVARCHAR2 (2000) in AL16UTF16. Although the maximum lengths of the NCHAR and NVARCHAR2 columns are larger in UTF8, the actual storage size is still bound by the byte limits of 2000 and 4000 bytes, respectively. For example, you can store 4000 UTF8 characters in an NVARCHAR2 column if all the characters are single byte, but only 4000/3 characters if all the characters are three bytes.

Link:
http://www.cise.ufl.edu/help/database/oracle-docs/server.920/a96529/ch5.htm#1019808
0
 
LVL 3

Expert Comment

by:rajnadimpalli
ID: 10696762
I was looking for non-multi-byte nlscharacterset database in my network to give better answer to your question and finally found one...Ok!.Your question is...

"HOW-To store 4000 Other language characters (assuming 2bytes per character ) in Oracle database..

1. First thing to store 4000 chars of 2 bytes per character ..you need 4000*2 = 8000 bytes length data type to address...
2. So with nvarchar2 datatype(Max: 4000 bytes) its not possible and even with nclob(Max: 4000 bytes) not possible.
3.We can address this with BLOB (LONG,LONG RAW) ,well I guess we can address any thing with this datatype,becuase its all binary.If any one can able to find alternate..I am interested in it...

4. See below for details on why you are able create nvarchar2(4000) on your database...other details on my comments 2,3 :

SQL> select version from v$instance;

VERSION
-----------------
8.1.7.0.0

SQL>

SQL> select * from nls_database_parameters
  2  where
  3  parameter like '%CHARACTERSET%'
  4  /

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8ISO8859P1
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1   <--- set to single-byte characterset.

Comments : Database cannot handle multi-byte characters even using nchar,nvarchar2 datatype.I guess this is why to can able to use 4000 for nvarchar datatype.See below....

SQL> ;
  1  create table single_byte_demo
  2  (
  3  COL4000  VARCHAR2(4000), <--- detault datatype semantics will be byte ( 4000 Bytes)
  4  NCOL4000 NVARCHAR2(4000) <--- Default N* datatype semantics will be in charcters.( 4000 chars )
  5* )
SQL> /

Table created.

SQL> desc single_byte_demo
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL4000                                            VARCHAR2(4000)
 NCOL4000                                           NVARCHAR2(4000) <--

comments: In "NCOL400O" ..Can you store other all langauge characters.Seems YES!. But due to NLS_NCHAR_CHARACTERSET = WE8ISO8859P1 , you CAN NOT hold any multi-byte/other langauge chars...so that's the reason the database is able to create nvarchar2(4000) just to hold single-byte characters only...

SQL> get q.sql
  1  select COLUMN_NAME, DATA_TYPE,DATA_LENGTH, CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH
  2  from user_tab_columns
  3* where table_name=upper('&1')
SQL> @q.sql
Enter value for 1: single_byte_demo
old   3: where table_name=upper('&1')
new   3: where table_name=upper('single_byte_demo')

COLUMN_NAME                    DATA_TYPE    DATA_LENGTH CHARACTER_SET_NA CHAR_COL_DECL_LENGTH
------------------------------ ------------ ----------- ---------------- --------------------
COL4000                        VARCHAR2            4000 CHAR_CS                          4000 <-- 4000 Bytes
NCOL4000                       NVARCHAR2           4000 NCHAR_CS                         4000 <-- 4000 Bytes

SQL>

==========
SQL> select version from v$instance;

VERSION
-----------------
9.2.0.1.0

SQL> select * from nls_database_parameters
  2  where
  3  parameter like '%CHARACTERSET%'
  4  /

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16    <--- set to mutli-byte characterset. (Fixed-length in this case)

SQL>

Comments : Database can handle multi-byte characters using nchar,nvarchar2 datatype.see below...

SQL> show parameter nls_length_semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE        <--- Semantics by default is in BYTES.
SQL>

Comments :
NOTE : The default sizing of character data types (CHAR, VARCHAR2, and LONG) is in bytes by default, only in CHARacters for NCHAR and NVARCHAR2.

SQL> ed
Wrote file afiedt.buf

  1  create table multi_byte_demo
  2  (COL4000 VARCHAR2(4000),
  3  NCOL4000 NVARCHAR2(4000) <-- Due to fixed-length (2bytes) multi-byte NLS characterset..Max value 4000bytes/2 =2000 bytes
  4* )
SQL> /
)
*
ERROR at line 4:
ORA-00910: specified length too long for its datatype

SQL>

Comments :For NCHAR and NVARCHAR2, the limits of the characters stored remain the same
=> 2000 bytes for NCHAR   ;  => 4000 bytes for NVARCHAR2  (Watch its 4000 BYTES).

SQL> ed
Wrote file afiedt.buf

  1  create table multi_byte_demo
  2  (COL4000 VARCHAR2(4000),
  3  NCOL4000 NVARCHAR2(2000) <-- 2000 Chars ot 2 bytes which makes 2000*2 = 4000 bytes
  4* )
SQL> /

Table created.

SQL> desc multi_byte_demo
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL4000                                            VARCHAR2(4000)
 NCOL4000                                           NVARCHAR2(2000)

SQL>

Comments : NVARCHAR2 can only able to store upto 2000 other langage characters(of 2bytes Multi-byte character)..

SQL> ed
Wrote file afiedt.buf

  1  create table multi_byte_demo2
  2  (COL4000 VARCHAR2(4000),
  3  NCOL4000 NVARCHAR2(2000),
  4  NCLOB_COL NCLOB
  5* )
SQL> /

Table created.

SQL> desc multi_byte_demo2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL4000                                            VARCHAR2(4000)
 NCOL4000                                           NVARCHAR2(2000)
 NCLOB_COL                                          NCLOB <---- This can store 4000 bytes only..

SQL>

SQL> get q2.sql
  1  select table_name, COLUMN_NAME,DATA_TYPE,DATA_LENGTH,CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH,
  2  CHAR_LENGTH,CHAR_USED
  3  from user_tab_columns
  4  where
  5* table_name in ('MULTI_BYTE_DEMO','MULTI_BYTE_DEMO2')
SQL> /

TABLE_NAME       COLUMN_NAM DATA_TYPE    DATA_LENGTH CHARACTE CHAR_COL_DECL_LENGTH CHAR_LENGTH C
---------------- ---------- ------------ ----------- -------- -------------------- ----------- -
MULTI_BYTE_DEMO  COL4000    VARCHAR2            4000 CHAR_CS                  4000        4000 B
MULTI_BYTE_DEMO  NCOL4000   NVARCHAR2           4000 NCHAR_CS                 2000        2000 C
MULTI_BYTE_DEMO2 COL4000    VARCHAR2            4000 CHAR_CS                  4000        4000 B
MULTI_BYTE_DEMO2 NCOL4000   NVARCHAR2           4000 NCHAR_CS                 2000        2000 C
MULTI_BYTE_DEMO2 NCLOB_COL  NCLOB               4000 NCHAR_CS                 2000           0

SQL>

-R
 
0
 
LVL 7

Author Comment

by:vbandaru
ID: 10920622
rajnadimpalli,
Click on question below and take credit for this question. Your answer was helpful

http://www.experts-exchange.com/Databases/Oracle/Q_20967994.html
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now