Boolean Field in Oracle

ghz 1years ago ⋅ 10128 views

Question

Yesterday I wanted to add a boolean field to an Oracle table. However, there isn't actually a boolean data type in Oracle. Does anyone here know the best way to simulate a boolean? Googling the subject discovered several approaches

  1. Use an integer and just don't bother assigning anything other than 0 or 1 to it.

  2. Use a char field with 'Y' or 'N' as the only two values.

  3. Use an enum with the CHECK constraint.

Do experienced Oracle developers know which approach is preferred/canonical?

Answer

I found [this](http://www.techrepublic.com/article/oracle-tip-choosing-an- efficient-design-for-boolean-column-values/) link useful.

Here is the paragraph highlighting some of the pros/cons of each approach.

The most commonly seen design is to imitate the many Boolean-like flags that Oracle's data dictionary views use, selecting 'Y' for true and 'N' for false. However, to interact correctly with host environments, such as JDBC, OCCI, and other programming environments, it's better to select 0 for false and 1 for true so it can work correctly with the getBoolean and setBoolean functions.

Basically they advocate method number 2, for efficiency's sake, using

  • values of 0/1 (because of interoperability with JDBC's getBoolean() etc.) with a check constraint
  • a type of CHAR (because it uses less space than NUMBER).

Their example:

create table tbool (bool char check (bool in (0,1));
insert into tbool values(0);
insert into tbool values(1);`
#oracle