Hi all,

specs
IS 6.0.1 SP2
JDBC 6.0.3
Oracle 9.0.1

I have a small problem I would like to share with you. The problem is the following; At Oracle side I specified a VARCHAR2 (2048) to hold a variable length string. Because I never know how long the string is (and the first part is the most relevant) I cut the string down to 2048 characters, this is done by a ‘substring’ transformer. Until now this worked perfectly. A problem arises when these characters are encoded (utf-8)at Oracle side and turn our to be stored as multibyte… ooops. now they don�t fit anymore and I receive a nice ‘inserted value is to large for column’ error from Oracle.

The dirty solution would be to cut the string down into 2000-2020 characters.

Anyone??

Dave Walschot
Accenture Technology Solutions

Either the dirty solution or increase the table column width.

I don’t think that previous post provided any insight whatsoever, so I will share what we had done for double byte characters that was actually a quite simple fix…simply rename the DB field to NVARCHAR.

I think the database column can be managed as varchar2. Please read below if further explanation helps.

You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:

VARCHAR2(maximum_size [CHAR | BYTE])

You may not be able to use a symbolic constant or variable to specify the maximum size; you will have to use an integer literal in the range 1 … 32767.

Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

If you specify the maximum size in bytes rather than characters, a VARCHAR2(n) variable might be too small to hold n multibyte characters. To avoid this possibility, use the notation VARCHAR2(n CHAR)so that the variable can hold n characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.

I have used 4000 bytes columns with 8i and 9i databases.

HTH.

Brian, Muthu,

Thanks for the provided solution. The Varchar2(n char) option works. the NTEXT and NVARCHAR does not seems to be recognized by Oracle 9.0.1.

Just a word of caution. According to our Oracle DBA, the way nvarchar(n char) is
interpreted depends on the underlying character set of the database. If the character set always uses eg. 2 bytes per character, then nvarchar(n char) works as expected. But if the character set uses a varying number of bytes per character (depending on which character it is), then nvarchar(n char) is equivalent to nvarchar(n) or nvarchar(n byte).