How to create a Boolean column on Oracle?

Question

As far as I know, Oracle does not provide a valueType of the kind Boolean. I am working a short time with Oracle and would like to know what is the best solution to "simulate" a true/false value in the Oracle database?

CHAR(1) (Y/N) ?
INTEGER (0/1) ?

Answer 1

NUMBER (0/1) is the default.

Frames like Entityframework, Nhibernate, Dapper, etc., identify by default when running a command in the Oracle database, that 0 = false and 1 = true.

Answer 2

Good afternoon, Deramon.

I researched here and saw that there are people who also use RAW(1), seems to be a good practice.

However, some argue that one can use CHAR(1) or even NUMBER(1), and still create a Constraint, allowing only 0 and 1 input in this numeric field.

I would opt for CHAR(1), I imagine it will make your database cleaner and easier to understand.

I hope I’ve helped.

Answer 3

To create follows

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

Answer 4

To create a boolean field on Oracle, you can create it this way:

Create a field to receive the boolean value:

ALTER TABLE table_name
ADD column_name_check char(1) DEFAULT '1';

When entering a record, by default, if not informed, it will be filled as 1.

Now, to ensure that it only has values like 1 or 0, add the Constraint:

ALTER TABLE table_name ADD
CONSTRAINT name_constraint 
column_name_check (ONOFF in ( '1', '0' ));