Should I byte or char tables?

At least since 11g you can create a table with string attributes either in byte or in character. If you do byte, then the length of the data reserved equals the value given, otherwise it is reserved 4 times the length given... to a maximum of 1000. Values greater than 1000 make a reservation of 4000 nonetheless. Run ...

create table THIEMO_TEST_BYTE (V varchar2(2000 byte));
create table THIEMO_TEST_CHAR (V varchar2(2000 char));
create table THIEMO_TEST_CHAR_2 (V varchar2(999 char));

... and get

Let's assume you wanted to put text that contains 2000 characters from japanese and russian into a VARCHAR2(2000 char) attribute. Sounds straight forwards but you are going to fail because those languages are not encoded in a single-byte codepage. You would probably use UTF-8 but there the more extraordinary (from the snobish point of view of the average westerner) characters are encoded with more than one byte... summing up to more than 4000 bytes in length.

• In grid-editing in DB Visualizer and TOAD into any of the 2000-legth-tables returns ORA-01461 [gibbering something about LONG data type]
• As script in DB Visualizer and in TOAD returns "ORA-01704: string literal too Long" [strictly speaking this is wrong as I forwarded only 2000 characters that where 6000 bytes Long]
• PowerCenter returns with "ORA-12899: value too large for column" when trying to insert into the byte table, but when trying to insert into the character table, it does not fail! The data gets clipped!! To me this is outright dangerous behavior!

byte

Advantages

• if data is too long an error get's thrown

Disadvantages

• you do not exactly know how many characters you can fit in a given attribute

char

Advantages

• you can exactly tell how many characters you can fit into an attribute up to the attribute length of 1000

Disadvantages

• under specific circumstances data gets silently clipped if it is larger than the data length
• you cannot tell how many characters you can fit into an attribute longer than 1000 characters

However

It should be possible to avoid those problems using nvarchar2 data type, see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9462837200346048883 and http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020