asked on

Data Length for Oracle 8i and 9i

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.

rajnadimpalli

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

vbandaru

ASKER

Almost got it..
So If I need to support 4000 Characters for any language what data type should I use??

gulhaugen

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

vbandaru

ASKER

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

ASKER CERTIFIED SOLUTION

gulhaugen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rajnadimpalli

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

vbandaru

ASKER

rajnadimpalli,
Click on question below and take credit for this question. Your answer was helpful

https://www.experts-exchange.com/questions/20967994/http-www-experts-exchange-com-Databases-Oracle-Q-20932018-html.html