 Storing more than 2000 characters in a varchar2 column in Oracle 11g? [message #459669] |
Mon, 07 June 2010 19:23  |
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 #459820 is a reply to message #459669] |
Tue, 08 June 2010 07:25  |
ThomasG
Messages: 3212
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 |
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 #460121 is a reply to message #460115] |
Wed, 09 June 2010 14:43 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
