4

I faced again the serious problem, that Oracle simply doesn't have a boolean column type. I need to use char(1), or smallint, or some other.

What is the best practice, how to emulate boolean values in Oracle?

(Space consumption isn't important now - but a good cooperation with java/hibernate line were important).

Improve this question
7
  • 1
    Why do you think not having the boolean data type is a serious problem?
    – mustaccio
    Commented Sep 16, 2014 at 13:33
  • @mustaccio It is (was) easy to solve, what I consider serious is that Oracle can't support it.
    – peterh
    Commented Aug 25, 2015 at 23:02
  • 2
    @mustaccio Sorry for the late answer. But I consider this as a serious problem, because everybody uses nonstandard ways to emulate boolean with SMALLINT or with CHAR(1). And these solutions are incompatible to eachother, so if you see a big data structure and see a CHAR field, you won't know on the spot if it is really boolean or not. Another problem: comparing different field types would be ((BOOLFIELD1 = 'Y') AND (BOOLFIELD2 = 1)) OR ((BOOLFIELD1 = 'N') AND (BOOLFIELD2 = 0)) which is a terrible mess compared to BOOLFIELD1 = BOOLFIELD2.
    – peterh
    Commented Feb 1, 2016 at 10:36
  • @mustaccio There is another problem with booleans: having 1e+15 booleans would be most easily used in a bitfield and it is a very important thing, how is it actually physicall stored on the disk. If there isn't boolean in a db, then there is surely absolutely no possibility to optimize this out on the db-provided ways (f.e., with bitfields).
    – peterh
    Commented Feb 5, 2016 at 13:26
  • 1
    @RDFozz The posts are for the eternity, more exactly the continuously growing knowledge base is what attracts here more and more visitors. Rejecting an edit only because it happened to an old post, I think it only shows, 1) you simply won't understand how this whole SE works 2) you somehow becomes annoyed seeing that others get +2 reputation. | The grammar fix to my post is fine, and I am thankful to all of the parttakers (MDCCL and Codeflava). Furthermore, I consider it as a big honor as a native speaker fixes what I can't do.
    – peterh
    Commented Aug 28, 2017 at 15:17

2 Answers 2

Reset to default
3

I recommend you use a SMALLINT column with a default value required (1=true, 0=false, default value = 0)

Improve this answer
2
  • According to psoug.org/definition/SMALLINT.htm and stackoverflow.com/questions/24240087/… both SMALLINT and INTEGER would be stored as NUMBER(38) and take 2 bytes of space when holding just zeroes and ones. Same as NUMBER(1), though.
    – Vadzim
    Commented Dec 23, 2016 at 14:40
  • 1
    I am using Oracle 12c standard. SMALLINT become to NUMBER(38,0) . I don't think use SMALLINT for boolean is best practice.
    – Vy Do
    Commented Feb 10, 2018 at 14:22
10

I usually use a number(1) type combined with a check constraint: 

some_flag number(1) not null check (some_flag in (1,0))

To make things crystal clear I also add a comment to that table:

comment on column some_table.some_flag is '0 is false, 1 is true';

so that the explanation on what "true" means can be seen when looking at the definition of the table.

I would avoid a character representation due to localization problems. But if you do, make sure you create the approriate check constraint. Because it's not clear if a char(1) would use T, Y, y, t (or even W or J which I have seen in German).

Improve this answer
3
  • W and J woulnd't make any sense in German. More like W and U
    – sschrass
    Commented May 25, 2020 at 10:46
  • @sschrass: all examples given, are for the "true" value.
    – user1822
    Commented May 25, 2020 at 10:52
  • Oh I see, my bad!
    – sschrass
    Commented May 25, 2020 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.