Oracle's lack of a Bit datatype for table columns

Oracle does not support a bit datatype or any other type for true/false scenarios. Does one use the char(1) field instead by using a specific letter to denote yes / true regardless of culture specific issues?

Instead of char should it be Number(1,0) for this instead - 0 being considered false / no, anything else being interpreted as true / yes?

Is this viable?

Why does Oracle not support a simple boolean type?

Use a CHAR(1), and a constraint to allow only 1 and 0.

...

col CHAR(1),
CONSTRAINT cons_atable_col1 CHECK (col1 IN ('1','0'))

I'm not an English native so I tend to use either 1 and 0 or '1' and '0'. Using 'Y' and 'N' make little sense if you aren't coding in English (yes, native language coding does exist). Using 'SI' and 'NO' or 'S' and 'N' doesn't look professional (just like naming variables with accented letters). Ones and zeroes, on the contrary, are pretty standard if you've coded in C, PHP or JavaScript. In any case, I always add the appropriate constraint to disallow any other character. Apart from subjective issues, I don't think there're noticeable performance gain in choosing CHAR or NUMBER. I like numbers a little more because I don't need to quote them :)

I agree it's a glaring omission but I've read seriously heated discussions on the subject in some Oracle forums; it's a kind of religious issue. Some claim that booleans belong to application data types and have no place in the database core. Honestly, I believe it's one of those We Have Been So Long Without It That We Had Better Say It Was On Purpose things.

By the way, MySQL has a BOOLEAN type but it's a synonym for TINYINT(1) so it eventually equals to 1 and 0; which is fine, because it also has the constants TRUE and FALSE that evaluate to 1 and 0.

I prefer char(1) over number(1), since with some reasonable choice of characters, it is obvious which character has which boolean meaning.

Of course you should fight all the different varations, choose one and ensure it's use by putting check constraints on the columns.

Although it probably is to late in your case, generating the schema from another tool often takes care at least of the consistency issue. I personally prefer hibernate for this purpose, but that is very situation specific.

And of course that is a glaring obmission. To make it worse, PL/SQL has a boolean, but you can't use it in SQL statements.

Here is an Ask Tom discussion on the topic. Gives an Oracle-centric view on the issue.

As for storage, char(1) is actually a bit (no pun intended) more efficient:

SQL> CREATE TABLE xx (c CHAR(1), n NUMBER);

Table created

SQL> insert into xx values('T', 1);

1 row inserted

SQL> select dump(c), dump(n) from xx;

DUMP(C)             DUMP(N)
------------------- -------------
Typ=96 Len=1: 84    Typ=2 Len=2: 193,2

According to this Oracle guide - you should use NUMBER(3). Crazy, but true.

http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm

Number(1) is no better than char(1). Especially if it will be in addition to the existing char(1). That will just add to the confusion.

FWIW, Oracle in internal views (such as USER_TAB_COLUMNS) uses varchar2(3) (YES and NO). Not sure if they are 100% consistent here, though.

The question is old but until the latest release of oracle is used it is still a valid question.

I would solve the problem this way: Create a table which holds the possible values for true/false plus localized display text f.e. T$KEYWORDS ITEMNO ITEMTEXT ITEMTEXT_DE ITEMTEXT_FE ... 0 False Falsch 1 True Wahr

Instead of True/False this could be also Selected, Not Selected, etc.

And then add a foreigh key to your column to this table. That way you have only valid values and they do not change with localization.

ANother good solution imho is using a check constraint on your data column. This ofc does not work if your values could be different in the same database/column depending on localization of clients.

alter table tblLocations add flag number CONSTRAINT <constraintname> CHECK (flag IN (1,0));

Oracle internally uses "bits" (not a datatype per se) in different Data Dictionary views.

For example, dba_users view has :

..
        , DECODE (BITAND (u.spare1, 128), 128, 'YES', 'NO')
..
        , DECODE (BITAND (u.spare1, 256), 256, 'Y', 'N')
..

which shows a way to workaround this in a way. If you don't have to modify "boolean" bits often, you could employ the same approach that Oracle had since Oracle 6 (at least). Create a table with a NUMBER column, and a VIEW on top of that that hides complexity of BITAND operations.

ps. On a side note, Oracle JDBC has a "Bit" datatype https://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/jdbc/OracleTypes.html#BIT as well as you already know PL/SQL has Boolean. Although it probably doesn't help you much. See BITAND approach above if it suites your case.

https://docs.oracle.com/cd/E17952_01/refman-5.5-en/char.html

As DCookie said, char(1) is more efficient. Because VARCHAR2(VARCHAR) empty contains 1 byte, but when we store 1 character then empty 1 byte size + with character 1 byte size --> 2 byte need to store 1 character in varchar