Home » Other » General » Storing more than 2000 characters in a varchar2 column in Oracle 11g? (11g)
icon5.gif  Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #459669] Mon, 07 June 2010 19:23 Go to next message
saifora21
Messages: 7
Registered: August 2008
Junior Member
We have a table in Oracle 11g with a varchar2 column. We use a proprietary programming language where this column is defined as string. Maximum we can store 2000 characters (4000 bytes) in this column. Now the requirement is such that the column needs to store more than 2000 characters. The DBAs don't like BLOB, CLOB or LONG datatypes for maintenance reasons.

There are 2 solutions I can think of -

1. Remove this column from the original table and have a separate table for this column and then store each character in a row, in order to get more than 2000 characters. This table will be joined with the original table for queries.

2. If maximum I need is 8000 characters, can I just add 3 more columns so that I will have 4 columns with 2000 char each to get 8000 chars. So when the first column is full, values would be spilled over to the next column and so on.

Which one is a better and easier approach? Please suggest.
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #459674 is a reply to message #459669] Mon, 07 June 2010 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Which one is a better and easier approach? Please suggest.
Both have downside costs & should be avoided.
You REALLY will be better off in the long run to use CLOB.
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #459678 is a reply to message #459669] Mon, 07 June 2010 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The DBAs don't like BLOB, CLOB or LONG datatypes for maintenance reasons.

Change the DBA for a skilled one.
You need CLOB, DBA has nothing to say about this, he has to support them.

Regards
Michel
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #459820 is a reply to message #459669] Tue, 08 June 2010 07:25 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Definitely NOT BLOB. (Blobs are for binary data)
Definitely NOT LONG. (LONGS are no longer supported)
Definitely NOT Option 1 (A performance nightmare waiting to happen)

The right thing to do would be to use a CLOB.

If the DBA can come up with a more convincing reason (one I could think of for example is that the application has to support multiple RDBMs and the custom driver for the custom programming language doesn't support CLOBs) then Option 2 would be the "least bad" and easiest workaround.
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #459883 is a reply to message #459820] Tue, 08 June 2010 12:43 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You should ask your DBA's for specific objections to CLOBS. They aren't without limitations and do need to be understood for performance and space usage considerations (inline vs out of line storage). As of 10g, they still aren't supported over DB links (for somewhat understandable reasons when you consider the size they can hold). I use them all the time.

I'd definitely avoid chuncking the text into N pieces if you need to support updates and search (think about the boundaries where the split occurs).

Unsure if I understand your 2000 char limit, but be aware that Oracle varchar2 goes up to 4000 in recent versions...
SQL> create table t_4k (col1 varchar2(4000 char))
Table created.
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #459893 is a reply to message #459883] Tue, 08 June 2010 15:33 Go to previous messageGo to next message
saifora21
Messages: 7
Registered: August 2008
Junior Member
Thanks for all the responses...

Is it 4000 characters or 4000 bytes (which would be equal to 2000 char)?

Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #459921 is a reply to message #459893] Wed, 09 June 2010 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is 4000 bytes and the number of characters depends on your character set and, maybe, data.

Regards
Michel
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #460112 is a reply to message #459921] Wed, 09 June 2010 13:16 Go to previous messageGo to next message
saifora21
Messages: 7
Registered: August 2008
Junior Member
If it is English character set (a-z 0-9 special chars), can it store 4000 such characters?
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #460113 is a reply to message #460112] Wed, 09 June 2010 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ir depends on your database character set and what "special chars" means.

Regards
Michel
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #460115 is a reply to message #460113] Wed, 09 June 2010 13:49 Go to previous messageGo to next message
saifora21
Messages: 7
Registered: August 2008
Junior Member
How do I find out the database char set?

By special char I meant symbols like #$%^&*

If at all Oracle stores 4000 chars, will it be stores as char or blob?
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #460119 is a reply to message #460115] Wed, 09 June 2010 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

Regards
Michel
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #460121 is a reply to message #460115] Wed, 09 June 2010 14:43 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Your database may have been installed with either a single-byte database characterset like WE8ISO8859P1 (western european, 8 bit, ISO 8859 point 1), or it might use something like UTF-8 which uses varying length encoding. All ASCII7 characters in UTF-8 are stored in 1 byte. Other characters take 2, 3 or more bytes. #$%^&* are regular ASCII7 characters so use 1 byte in most charactersets, incl ISO-8859.1 and UTF-8.
http://www.asciitable.com/

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/appb.htm#952448
SQL> -- Oracle 11.1.x
SQL> -- UTF-8 database characterset
SQL> select value from nls_database_parameters
  2  where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8

SQL> create table t_4k (id number, col1 varchar2(4000 char));

Table created.

SQL> -- regular ascii7 characters use in 1 byte in UTF-8
SQL> insert into t_4k values(10, 'Z');

1 row created.

SQL> -- Small Greek Gamma "U+03B3" is 2 bytes in UTF-8
SQL> insert into t_4k values(11, unistr('\03B3'));

1 row created.

SQL> -- euro sign "U+20AC" is 3 bytes in UTF-8
SQL> insert into t_4k values(12, unistr('\20AC'));

1 row created.

SQL> -- char length vs byte length
SQL> select id, length(col1) char_len, lengthb(col1) byt_len, dump(col1, 1016) dmp
  2  from t_4k order by 1;

        ID   CHAR_LEN    BYT_LEN DMP
---------- ---------- ---------- --------------------------------------------
        10          1          1 Typ=1 Len=1 CharacterSet=AL32UTF8: 5a
        11          1          2 Typ=1 Len=2 CharacterSet=AL32UTF8: ce,b3
        12          1          3 Typ=1 Len=3 CharacterSet=AL32UTF8: e2,82,ac


Michel is right - 4000 byte limit.
SQL> delete t_4k;

3 rows deleted.

SQL> -- insert 4000 regular ascii7 chars (single byte in UTF-8)
SQL> insert into t_4k values(1, lpad('x', 4000, 'x'));

1 row created.

SQL> -- try to insert large multi-byte string
SQL> insert into t_4k values(2, lpad('x', 4000, unistr('\20AC')));

1 row created.

SQL> insert into t_4k values(3, lpad('xx', 4000, unistr('\20AC')));

1 row created.

SQL> insert into t_4k values(4, lpad('xxx', 4000, unistr('\20AC')));

1 row created.

SQL> -- multi-byte string gets truncated to 4000 bytes
SQL> select id, length(col1), lengthb(col1) from t_4k order by 1;

        ID LENGTH(COL1) LENGTHB(COL1)
---------- ------------ -------------
         1         4000          4000
         2         1334          4000
         3         1335          3999
         4         1336          4000

SQL> -- conclusion: varchar2(4000 char) can only store 4000 characters if they are single byte.
SQL> -- mutli-byte strings are truncated (at character level) to limit the byte length to 4000
Re: Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #460138 is a reply to message #460121] Wed, 09 June 2010 16:24 Go to previous message
saifora21
Messages: 7
Registered: August 2008
Junior Member
Thanks a lot. That was very helpful.
Previous Topic: PL/SQL and asp/jsp
Next Topic: Oracle's unlimited license agreements
Goto Forum:
  


Current Time: Thu Mar 28 16:18:02 CDT 2024