Oracle's lack of a Bit datatype for table columns.

Question

Hi Everyone,

I work as a developer on a small development team, and something has annoyed me to the point where I have decided to act...

Oracle does not support a Bit datatype - or indeed anything else that makes obvious sense in true/false scenarios. Undeterred however, before I joined the team my forebears decided to use char(1) fields instead, using a specific letter to denote yes / true. Unfortunately though, our application is used to by people all over the world, and for reasons which frankly defy all my attempts at understanding, the value used varies through localisation.

Yes, I do know that's totally unnecessary for values in the back-end that the user never sees - however...

I've noticed that this practice seems to be getting carried forward into new development, which strikes me as mad - so I am thinking of proposing Number(1,0) for this instead - 0 being considered false / no, anything else being interpretted as true / yes...

Simple question - can anyone think of a reason this might be a bad idea?

While we're about it, does anyone know why Oracle doesn't support a simple boolean type? Isn't it a GLARING ommission?

Cheers in anticipation,

Martin.

Answer 1

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.

Answer 2

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.

Answer 3

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. 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 becausee 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.

Answer 4

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

Answer 5

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'))