Solved

Data Length for Oracle 8i and 9i

Posted on 2004-03-25
7
2,315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Query with Function 3 65
oracle collections 2 28
plsql job on oracle 18 75
error starting form builder in 11g 2 25
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

756 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