2007/03/14
How to store boolean values to Oracle database table? Or perhaps a field that can have a few predefined values, like 'A', 'B', 'C', 'D'? A table in Oracle cannot have a data type BOOLEAN. So the first solution that comes to mind is using CHAR(1) data type, making it NOT NULL and limiting the allowed values with a CHECK constraint (remember that a CHECK constraint allows NULL values). Works almost always. Almost.
We had a problem once in Hansa project with a certain combination of VJDBC, Oracle JDBC driver and database configuration. When reading a CHAR(1) field we got a three character long string like "A " (letter 'A' followed by two spaces). Since the database character set was set to UTF-8, all character data type fields could contain three times more bytes. That is, three bytes were reserved for a CHAR(1) field; the same applied to VARCHAR2 fields too. Additionally, a CHAR field is always right padded with spaces to fill its max length (this does not depend on the database charset).
Our application connected to the database via VJDBC and started receiving three byte strings from fields that should by definition have just one character. When accessing the same data via SQLPlus or from Java program directly to database (without VJDBC), everything worked fine.
The solution was to always use VARCHAR2(1) instead of CHAR(1). In that case no right padding with spaces happens.
I have used CHAR(1) many times before in different applications without problems since it "should have worked as good as VARCHAR(1)". Well, it doesn't. 
P.S. I don't remember what Oracle driver was used, OCI or thin. Doesn't matter though, I reckon.
