SQL & PL/SQL

Hello,

I know Oracle SQL has no real BOOLEAN type. So many revert to a CHAR(1) with a 'F' | 'T' or 'N' | 'Y' check. I think using a one-byte INT is better, so the check would be for 0 | 1. That's the DDL side.

Now I want to know, since I don't use Oracle actively: Is it possible to write

SELECT ... FROM ... WHERE emulated_boolean_column ...

without error?

In Java an if in the form

if ( isValid ) //boolean type

or in C/C++

if ( intvar ) //int type

checks for true | false (Java) or zero | non-zero (C/C++) respectively, so these would technically be equivalent (other than the NULL in DBs).

Having to write

SELECT ... FROM ... WHERE char1_boolean_column = 'F'
SELECT ... FROM ... WHERE char1_boolean_column = 'T'

or

SELECT ... FROM ... WHERE int_boolean_column = 0
SELECT ... FROM ... WHERE int_boolean_column = 1

is redundant and actually pretty stupid. For the latter, checking

SELECT ... FROM ... WHERE int_boolean_column = 0
SELECT ... FROM ... WHERE int_boolean_column () 0

[NOTE: I used () as the inequality operator, as that gets eaten here]

would be more C/C++ like (no specific values, but zero and non-zero)

In Java, for example, you don't write things like

if ( isValid == false ) //bad code
if ( isValid == true ) //bad code

, too. Same with C/C++:

if ( intvar == 0 ) //bad code
if ( intvar == 1 ) //bad code

or

if ( intvar == 0 ) //bad code
if ( intvar != 0 ) //bad code

...

I hope I got the things laid out without errors. I haven't used C/C++ for years, so correct me if I'm wrong.

I think using CHAR(1) as booleans is rather like using a two-fold enumeration type. To me, an integer appears much more natural.

Comments?

Karsten

PS: Please forget about the NULL for this discussion.