Hi guys,
with Oracle, there are a few approaches you can take. One is to change all your VARCHAR2 columns from BYTE to CHAR e.g. declare them as VARCHAR2(200 CHAR) instead of VARCHAR2(200). You would also need to do this with every VARCHAR2 variable in every PL/SQL procedure or function - a lot of work as you can imagine. However, it's worth knowing for a quick fix.
Oracle's recommended approach is to set NLS_LENGTH_SEMANTICS to 'CHAR', which basically tells Oracle to treat the length of every VARCHAR2 (and CHAR) column as being characters and not bytes.
However, the problem with both of these is that you'll still run into the maximum column size 4000 bytes. But now you won't be able to tell when you're going to hit it! I guess it depends on what languages you're using and how many double-byte characters they have. Fortunately for me, English has hardly any, but I did run into this problem once with an Irish-language client. I would say as a rule of thumb, if you think you're going to need more than 2000 characters, then why not use a CLOB?
See here for more detail
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref76
and
http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch2charset.htm#i1006683
and a nice introduction
http://www.oracle-base.com/articles/9i/CharacterSemanticsAndGlobalization9i.php
and a good example how-to here:
http://www.oracle.com/technology/oramag/oracle/03-mar/o23sql.html
hope this helps
Brian
