Jeremy -- Thanks for the question regarding "Boolean Datatype", version 9.2.0.1.0

A few reasons why...   November 3, 2002 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jeremy Smith from Salt Lake City, UT

Clarity: if the value I'm trying to store really is a boolean, then when I'm glancing at my model, 
it's clear that that's what the column is.  Sure, if it's a char(1) that's a good indication, but 
not a sure-fire one.

Compatibility:  I know you're suspicious of products developed for multiple platforms, but there 
are a lot of them out there.  It would be nice to not have to make that conversion every time.



Consistency: Y/N values with a check constraint are one way to represent a boolean. When I needed 
my first one, I used T and F.  Another person I know used 0 and 1.  Someone who spoke another 
language might choose two different characters.   

PL/SQL compatibility:  It would be nice to be able to declare booleans using "var_name%TYPE".

and just to clarify on performance issues:

Speed: doesn't that check constraint take a tiny amount more  time than validating against 
conformance to a particular datatypes?

Space: if I'm using a fixed width multi-byte character set, doesn't a char(1) boolean take up more 
space than a universal boolean would?

Sorry if this sounds whiney, but it's something I find myself explaining to nearly every new Oracle 
developer, and I've never heard a really good reason for why not.  Best I've been able to come up 
with is "Because they don't _need_ it, and because they don't feel like having it."  You have to 
admit, that's a pretty rare answer to give w/r/t an Oracle feature. 

For clarity -- i would hope the name (which could even incorporate "boolean") or the check 
constraint would tell you that.

For compatibility -- you better stick with SQL89 datatypes (basically strings and numbers) --  not 
really appealing when you get down to it.

Consistency -- well, the same thing could be said about zip codes, addresses, storing names, (and 
the list goes on).  Sounds like an implementation detail

Speed -- no, there wouldn't be.

space -- use a number with 1 and 0 then.  Or using utf-8 or any variable length encoding scheme....


It just isn't a type we have -- I can say no more and no less.  ANSI doesn't have it -- many 
databases don't have it (we are certainly not alone).  In the grand scheme of things -- I would say 
the priotization of this is pretty "low" (thats my opinion there) 

PL/SQL functions   November 4, 2002 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: JBrain from England

Point taken, but I find the following annoying...
(a) Someone (or even me) writes a PL/SQL function returning a BOOLEAN.
(b) I later find a use for it in a SQL WHERE clause.

True I might be able to code the condition in SQL, but if I want to try to maintain some semblance 
of code reuse I either have to provide a Y/N function wrapper or go back to the author...

Would be nice just to be able to slip the original function into the WHERE clause and forget about 
it.

Are there people out there who have coding standards banning functions which return a BOOLEAN...? 

Are there people out there banning functions such as those returning a boolean -- I can almost 
assure you there are.  I've seen EVERY construct "banned" out of FUD at one point or another. 

Boolean   November 4, 2002 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: Tony Andrews from UK

Since ANSI don't specify a BOOLEAN datatype, I guess our issue is with ANSI rather than Oracle.  
But I don't buy the argument that BOOLEANs are redundant because you can use CHAR(1) with 'Y' or 
'N' (what I'd call a "work around").  In that case, why have a PL/SQL BOOLEAN type either?  BOOLEAN 
is a fundamental and natural datatype, and most databases I have known would use it liberally if it 
existed. 

What is the Boolean mapping in Java.   February 5, 2003 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: Chandra S.Reddy from Hyderabad India

Hi Tom,
I have a question regarding boolean datatype.

Below is the SP, written by our client and we need to integrate this with our application.

PROCEDURE sp_xxxx_map (
                ic_opt_number       IN  VARCHAR2,
                in_cast_number    IN  NUMBER,
                in_tyre_amount          IN  NUMBER,
                ob_is_valid             OUT BOOLEAN,
                ob_is_obgr_obgt_valid   OUT BOOLEAN,

--------------- few more parameters....

While integrating the above, everything id fine except boolean OUT parameter.
I think(correct me if am wrong) there is no SQL datatype in java(JDBC classes) to catch the  
"ob_is_valid", "ob_is_obgr_obgt_valid" parameters.
How do can go about this issue.
One idea is:
Can I write another SP(say SP1), which will call the above SP and will convert the boolean types to 
CHARs like 'Y' for true and 'N' for false.
Finally I let the application to call the newly created SP1.
 

just prepare and execute:

declare
   b1 boolean;
   b2 boolean;
   n1 number := 0;
   n2 number := 0; 
begin
   sp_xxxx_map( ?, ?, ?, b1, b2 );
   if (b1) then n1 := 1; end if;
   if (b2) then n2 := 1; end if;
   ? := n1;
   ? := n2;
end;

from the jdbc app. 

pseudo-boolean datatype best practices   June 13, 2003 - 11pm Central time zone

Bookmark | Bottom | Top

Reviewer: Kurt from Springfield, VA USA

Tom,
Hi. This is a follow-on to the current thread.

I'm involved in a large database development project and I'd like to find out your opinion on best 
practices for what I'm referring to as "pseudo-boolean datatypes".

As mentioned in this thread, Oracle does not natively support an SQL boolean datatype, and you've 
suggested an alternative implementation using a char(1) datatype with a check constraint. I think 
that's a very good idea, and I've been using a similar approach for years. So, unless you've 
revised your original suggestion, let's assume the datatype and length issues have been settled - 
char(1).

You've also suggested that there should be a check constraint in place that ensures that the values 
are members of an explicit domain - 'Y' or 'N'. This implies that the column can be defined as "Not 
Null", and I think that's a good idea, too. Although I think ('Y','N') is the correct domain, I've 
seen other implementations where ('T','F') are used, and that's fine - any 2 values are fine as 
long as they're used consistently across the project.

Since joining my current project, I've heard of 2 more approaches from well-respected Oracle 
developers. One developer suggests that one single value ('Y' for example) means True, and any 
other value (including null) means False. For example...

Y = True
y = False
3 = False
null = False
F = False
etc.

The other developer suggests that null means False, and any non-null value means True (a C-like 
paradigm). For example...

Y = True
y = True
3 = True
null = False
F = True
etc.

My implementations have always been more rigid - the column is defined as "Not Null", 'Y' means 
True and 'N' means False. Anything else is an error. To help out a bit, I use a pre-insert/update 
trigger which converts null values to 'N', and uppercases all values. Then the trigger raises an 
exception if the value is not in ('Y','N'), although this functionality could be accomplished using 
the check constraint that you mentioned.

I try to avoid the use of "null" to indicate an explicit value. To me, it's like making decisions 
based upon the absence of a value rather than making decisions based upon the presence of a value, 
and that seems counter-intuitive. I understand that there are other places where this approach is 
used successfully - such as performing an outer join and using the null values in the "right-hand 
side" of the join to detect missing records - but that seems more palatable to me. I consider a 
"Not Null" column to be "stronger" than a nullable column, unless you're forced to pack in 
arbitrary data simply to satisfy the constraint.

Also, I believe there are legitimate reasons within Oracle to favor "Not Null" columns. For 
example, null values are not stored in an index, and this could impact the resources needed to 
satisfy a query where that column appears in a predicate. And, the optimizer may choose a different 
(possibly inferior) execution plan based upon whether the column is nullable or not, even if the 
column does not appear in an index.

Can you provide a critique of the implementations mentioned here, and make a suggestion as to the 
best approach in general? The target application is a web-based OLTP application (JSP, WebSphere), 
with the usual data entry screens, searches, and reports.

Thanks, Kurt 

if a column has a domain of three values

true
false
unknow

then a constant value for TRUE is called for, a constant value for FALSE and NULL for unknown.

If it has two -- then two constants are called for.

It seems to make more sense to me anyway to consistently code:

select * from t where ..... and shipment_has_shipped = 'Y';

.... and payment_made = 'N';


rather then, 

....  and shipment_has_shipped is not null;
....  and payment_made is null;


the first two just seem to convery "more details" about what they are doing.

so, I've never used anything other then Y/N/null




 

representing tri-state in a 2-state client   June 14, 2003 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Kurt from Springfield, VA USA

Tom,
Thanks for the response.

If the sole application sitting on top of the database only has 2 representations for the value of 
a boolean field, then doesn't that imply that the database should support only 2 values? For 
example, a checkbox control on an HTML form can only represent checked and unchecked, for True and 
False. It's clear that when the HTML form displays a pre-populated record, it will take the field 
value 'Y' and render the checkbox in a checked state, and will take the field value 'N' and render 
the checkbox in an unchecked state. But what should it do with a null value? I'm sure the usual 
approach is to render the checkbox unchecked. But since that's the same as 'N', then why allow null 
values in the field at all?

In a similar way, what does it mean when the user submits the HTML form with an unchecked checkbox? 
To the data handler (whether that's the client JSP or the database PL/SQL), that doesn't mean null, 
it means 'N' even if the user is "unsure" of what the correct value should be. HTML forms simply 
don't support tri-state boolean values.

Thanks, Kurt 

if it only has two values - Y or N, it should be defined:

flag char(1) NOT NULL check (flag in ('Y','N'))

there are no nulls to deal with then 

Which would you rather see   July 2, 2003 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: DH from USA

Not sure about the purpose for this thread, because it seems to me that Oracle does have a boolean 
datatype, but I'm curious at the present time, which you would rather see in an application that 
uses these constructs a great deal:

if( flag != 1 ) then

or 

if( <boolean variable> ) then

I gather from the thread that they're certainly functionally the same, but I've seen the latter 
more often in your examples.  Is either one more efficient than the other? 

i disagree, I mostly use the first case in my stuff.

I would not:

  bool_val := flag != 1;
  if ( bool_val )
  then 
    ....


I would

   if ( flag != 1 )
   then
      ....


 

funny   July 2, 2003 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: DH from USA

how perception sometimes deviates from reality.  Looking back, I do see the boolean construct in 
your examples, but only when the reader provided such a construct in their question.  Ironically, I 
started using boolean variables only because it appeared to me that it was your preference. 

Use of Boolean in Query   June 10, 2004 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: AS from INDIA

I wanted to use function in query which returns boolean. Kindly can you please demonstrate : 

I tried : select IS_NUMBER ('AAA') from dual but it failed.

Regards,
AS
 

it returns boolean.

sql knows nothing about boolean.

you'll have to make it return something like "1" and "0" or "yes" and "no" - a number or string, 
instead of a boolean. 

Parameter boolean   September 17, 2004 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: Ditter from PHIL.

Hi Tom,

One of my colleague is suggesting to convert all the input/out parameters having a output field of 
'Y/N' to boolean. According to him, "bugs caused by alpha-literals are avoided"

example :

PROCEDURE VALID_CR
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_PR                            VARCHAR2                IN
 P_DR                            DATE                    IN
 P_CHK                           VARCHAR2                IN/OUT
 
should be :

PROCEDURE VALID_CR
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_PR                            VARCHAR2                IN
 P_DR                            DATE                    IN
 P_CHK                           BOOLEAN                 IN/OUT

Is there any gain if we modify the existing codes. And I told him that it would be just a waste of 
time converting this.... Any comments. 

alpha-literals -- is that anything like "the typical alpha male"?

umm, no clue what he is talking about at all.

there is about 0% chance I would go back and change Y/N logic to True/False boolean's


utter waste of time. 

Boolean Datatype   September 22, 2004 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Kevin Ryan 

I am an ASP.net developer and all I want is for a oracleadpter to populate a simple dataset to bind 
to a datagrid.
I want the dataset to populate checkboxes and it cant because you guys dont want to use booleans.

Thanks a bunch. 

your welcome? 

Boolean Array in PLSQL?   November 8, 2004 - 4pm Central time zone

Bookmark | Bottom | Top

Reviewer: Andrew Cohen from Wellington, NZ

Hi, just curious if I declare an array of bools in PLSQL, will it be compressed into a bitarray, 
i.e. one bit per bool, or 8 bools per byte? i.e.:

type bitarray is table of boolean index by binary_integer;
b bitarray;
for in in 1..8000 loop
  b(i) := true;
end loop;

will this take up 8000 bytes or 1000 bytes, or possibly more for some PLSQL overhead?  Would an 
array of varchar2(1) or number(1) be smaller?  how can i "trace" or "profile" the memory usage?

thanks!!!


 

you can use v$sesstat to track your pga/uga memory usage.



ops$tkyte@ORA9IR2> create or replace procedure p( p_n in number )
  2  as
  3          type array is table of boolean index by binary_integer;
  4          b array;
  5  begin
  6          for i in 1 .. p_n
  7          loop
  8                  b(i) := true;
  9          end loop;
 10  end;
 11  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> set echo off
 
PL/SQL procedure successfully completed.
 
 
NAME                                VALUE
------------------------------ ----------
session pga memory max             630760
 
ops$tkyte@ORA9IR2> exec p(1);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
 
NAME                                    V       DIFF
------------------------------ ---------- ----------
session pga memory max             630760          0
 
ops$tkyte@ORA9IR2> @mystat "pga%max"
ops$tkyte@ORA9IR2> set echo off
 
NAME                                VALUE
------------------------------ ----------
session pga memory max             630760
 
ops$tkyte@ORA9IR2> exec p(100000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
 
NAME                                    V       DIFF
------------------------------ ---------- ----------
session pga memory max            1855824    1225064
 
ops$tkyte@ORA9IR2> @mystat "pga%max"
ops$tkyte@ORA9IR2> set echo off
 
NAME                                VALUE
------------------------------ ----------
session pga memory max            1855824
 
ops$tkyte@ORA9IR2> exec p(200000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
 
NAME                                    V       DIFF
------------------------------ ---------- ----------
session pga memory max            3400056    1544232
 
ops$tkyte@ORA9IR2> @mystat "pga%max"
ops$tkyte@ORA9IR2> set echo off
 
NAME                                VALUE
------------------------------ ----------
session pga memory max            3400056
 
ops$tkyte@ORA9IR2> exec p(500000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
 
NAME                                    V       DIFF
------------------------------ ---------- ----------
session pga memory max            8032752    4632696
 


so, about a byte, plus a bit of "management overhead" 

Re: Boolean Array in PLSQL?   November 8, 2004 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: Andrew Cohen from Wellington, NZ

Wow, thanks for the quick response!!

Is it my math or does it seem like your results suggest about 10 bytes per item in the array.  Ah, 
because it is a sparse array it has to store the index, a binary integer, plus the boolean.

Also, any chance of seeing mystat2.sql? 

mystat2.sql:

set echo off
set verify off
select a.name, b.value V, b.value-&V diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on


mystat.sql:



set echo off
set verify off
column value new_val V
define S="&1"
                                                                                                    
   
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
 

PL/SQL vs SQL   November 9, 2004 - 12am Central time zone

Bookmark | Bottom | Top

Reviewer: Bob B from Albany, NY

Oracle decided that a boolean variable type would be a useful construct at some point.  I'm very 
glad they've done this as it allows complex logic to be precalculated and can thus increase 
performance.  Consider:

Case 1 with boolean:
my_boolean := <complex evaluation>;
WHILE loop_condition
LOOP
  <common logic>
  IF my_boolean THEN
    do_action_1;
  ELSE
    do_action_2;
  END IF;
  <more common logic>
END LOOP;

Case 2 without boolean worst case:
WHILE loop_condidition
LOOP
  <common logic>
  IF <complex evaluation> THEN
    do_action_1;
  ELSE
    do_action_2;
  END IF;
  <more common logic>
END LOOP;

Case 3 without boolean, better case:
IF <complex evaluation> THEN
  WHILE loop_condition
  LOOP
    <common logic>
    do_action_1;
    <more common logic>
  END LOOP;
ELSE
  WHILE loop_condition
  LOOP
    <common logic>
    do_action_2;
    <more common logic>
  END LOOP;
END IF;

I would argue that Case 1 is the most efficient, easiest to maintain, and easiest to understand.  
Case 2 is equally as easy to maintain, might be a little more difficult to understand, but will 
likely be less efficient.  Case 3 is as efficient as Case 1, but can lose some maintainability 
and/or readability (<common logic> and <more common logic> would be procedures/functions).

Oracle is pushing the forefront of SQL and will likely/hopefully add additional features to the 
ANSI standard such as analytics and/or the modeling clause.  A boolean datatype can offer several 
optimization opportunities for Oracle.

I'm assuming a Boolean datatype would have to be tri-valued to fully support integration with 
PL/SQL and to be consistent with ANSI SQL.  This means each boolean column would require 3 bits.  
Many of the tables I've seen will have several booleans to represent several T/F situations.  These 
currently take up a minimum of 1 byte.  With one time coding, up to 2^8/3 = 85 boolean columns 
could fit in 1 byte (though likely 2 bits per column for a total of 4 booleans would be closer to 
reality or 8 1 bit columns if NOT NULL is enforced).

- my 2 cents 

analytics are part of the SQL drafts already....

CASE (variable) 
  when value1
  when value2


or 


decode( variable, value1, <A>, value2, <B>, ... )

already do "boolean" work in SQL.

I'm not aware of any work on making a "bit field" like that in place. 

boolean   May 10, 2006 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: mal 

  1  CREATE TABLE posts (
  2     id               INTEGER,
  3     hidden           BOOLEAN,
  4     subject          VARCHAR(255) NOT NULL,
  5*    text             VARCHAR(10) NOT NULL)
SQL> /
   hidden           BOOLEAN,
                    *
ERROR at line 3:
ORA-00902: invalid datatype

 

The underlying data type   July 18, 2006 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Marius 

To be honest, a bool data type would be nice to have but not at all necessary. Actually I believe I 
wouldn't even consider using it should it pop out the next version, there are just too many 
projects already done the old/current way. Besides, I don't believe they could design it to be 
faster or take less space(1 bit? forget it).

My question tho is: what would it be better to use, numeric(1) or char(1)?
Both take the same amount of space, 1 byte(ok, there might be exceptions with char(1), but that's 
beyond the scope of this question).

From a performance point of view they are similar, the numeric approach being just slightly faster 
in my insert tests, but being slightly slower in my select tests.

So what would you pick and why?
I'd rather choose the numeric approach, at least I'll get rid of '' in the ad-hoc queries. 

i myself use varchar2(1) with Y/N in it. 

Booleans.   August 29, 2006 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: D from UK

i find the way that booleans are implemented in say mySQL to be..a bit odd. 

look at this example:

mysql> create table test (a varchar(10));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test values ('abcd');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('Y');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('y');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ('0');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test where a = 0;
+------+
| a    |
+------+
| abcd |
| Y    |
| y    |
| 0    |
+------+
4 rows in set (0.00 sec)

mysql> 


assume that you had a code bug and that was an update on a big table..i meant to type '0' and not 
0. (no rollback available in standard mysql "myisam" tables!). 

:-s 

numeric(1) or char(1)   October 6, 2006 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: awomynda from Belgium

We too have recently discussed whether our standard should be 0/1 or Y/N. 

Working on DataWarehouse, I proposed 0/1 for easier-to-read queries. 

Example:

  total_sales * isDiscounted  -- for discounted_sales 
, total_sales * isHoliday     -- for holiday_sales

rather than

  decode(isDiscounted, 'Y', total_sales, 0) -- discounted
, decode(isHoliday, 'Y', total_sales, 0) -- holiday_sales 
 

More on booleans   April 26, 2007 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Earl Lewis from Salt Lake City, UT, USA

create or replace function my_wrapper( .... ) return varchar2
as
begin
   if (original_function(.....)) 
   then
       return 'y';
   else
       return 'n';
   end if;
end;
/

Another winner!   April 27, 2007 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Earl Lewis from Salt Lake City

Please just add the boolean datatype   July 17, 2007 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jd from Milwaukee, WI

question of bitmaps   September 18, 2007 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: Dave from New York, NY

so while it is not necessary to have a "boolean" type (and i agree, i usually just use 0 and 1), 
does oracle support bitmaps - ie an array of booleans?  can you do bit tests like if you were to 
use a raw data type?  or generally do we use a 0/1 or Y/N column for each flag we're looking at 
here?  i know oracle can use bitmaps for indexes, can it use them for data types?

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_raw.htm#sthref15234

Lot's of Bool   April 15, 2008 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Ed Cortes from Puerto Rico

Definitely Oracle has a way of making everything more complex, this shown with this discussion on 
Boolean data type. It's like the OraGuys are stuck in the 1973...

I'm also a .Net developer, and it's a pain in the *** to be converting everytime to 0/1...

you should consider your customers requests   April 30, 2008 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Samuel from Spain

http://dev.mysql.com/doc/refman/5.1/en/boolean-values.html

Is Oracle ever going to add a boolean datatype?   April 30, 2008 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: G Siems from St Paul, MN

SQL2003 has a boolean datatype. Don't you think that maybe it's time for Oracle to finally add 
support for it?

how can I know that?   April 30, 2008 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: samuel from Spain

how can I know why the sollutions worked faster with mysql? they just did worked faster, with only 
the same stuff, we where in a hurry since they were billions of records. and actually my strongest 
point is:

People are paying money (AND A LOT OF MONEY) for your solutions, don't you think you should give to 
your customers what they want....

*chuckle*   May 1, 2008 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Mike Hennessy from Brisbane, Australia

On a sidenote, "your welcome", tsk tsk, next thing it'll be "ur welcome". A slippery slope indeed ! 
:-)

response   May 2, 2008 - 12am Central time zone

Bookmark | Bottom | Top

Reviewer: Samuel 

this part was is executed 15 times in billions (spanish billions, not english billions of 
operations):

mysql:
boolean my_varb1, my_varb2;
//----retrieved somewhere from a control, checking file existances ()
//like a ficticious example 
varb1 = file.exists(my_file.psp);
query = "SELECT * FROM some_table WHERE some_value=" + my_varb.toString();
//note mysql supports WHERE my_value = TRUE, my_value='TRUE'
result = db_exec(query);

with oracle:
boolean my_varb1, my_varb2;
varb1 = file.exists(my_file.psp);
string d2varb1;
if(varb1)
   d2varb1 = "1";
else
   d2varb1 = "0";
query = "SELECT * FROM some_table WHERE some_value=" + d2varb1;
result = db_exec(query);

it's worth to mention that boolean ands and ors are used as well. if you think there are better 
ways, I'm open to use them, thanks (this is becoming a forum, sorry)

Antediluvian   May 6, 2008 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: DK from UK

I think that for me there are two frustrating parts to not having booleans in SQL.

First, while it's possible to constrain the column to Y/N the same doesn't apply to queries.

i.e. WHERE Paid is fairly foolproof 
while
     WHERE Is_Paid = 'y'
     WHERE Is_Paid = ' Y'
are typing mistakes that need tracking down

The second is in that it's not possible to use boolean logically.

For instance,
     WHERE Invoiced AND NOT Paid
is unambiguous

whilst
     WHERE Is_Invoiced = 'Y' AND Is_Paid = 'N'
is not always equivalent to 
     WHERE Is_Invoiced = 'Y AND Is_Paid <> 'Y'

Subsequent rationalisation of code often falls foul of these things.  

There was a trend in the 70's to reduce silly coding errors.  For that reason Oracle insists on 
predeclaring variables.  

Why not move with the times?

my two cents why Oracle not supports boolean   May 21, 2008 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: Johnson from USA

my personel opinion why Oracle does not have Boolean support in Sql:

1. the oracle database designer is not a PL/SQL programmer, he is not using Toad to make lines of 
lines of plsql code everyday and calls his BOOL2YN YN2BOOL wrapper functions  all the time. 
Obviously, Oracle's PL/SQL team definitely needs Boolean while its Database engine team is thinking 
differently.

2. Supporting boolean in database level(SQL) is a loud voice from the user, however, taking actions 
on this is not going to increase their revenue and only to increase your developing cost.

Sorry Tom, I can not accept your explanation. 

issue for no boolean   May 21, 2008 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Johnson from USA

Tom:
the issue(inconvenient, to be exact) for not supporting boolean in DB level is , for database 
centered application, when alot of logic is put inside PL/SQL , users have to use wrapper functions 
like I do (BOOL2YN YN2BOOL) to remap the YN to boolean or vice versa. 
Please do not tell me I can use "if OldEnough='Y' then ..." to do flow control, this is just not 
the decent flow control way in any programming language. Google "oracle boolean" you will find tons 
of sighs about this type of wrapper solution, Steven Feuerstein is also a strong supporter to use 
boolean instead of YN inside PLSQL, and he also provides the similar wrapper functions.
To conclude: As a daily plsql programmer, I am not saying I can not live without boolean, but make 
it available in database level will make my life way much easier.  I bet most PL/SQL programmers ( 
if not all) will  agree with me. And I will be much more looking forward to having this included in 
Oracle Version XX than any other fancy features oracle has added to its new version . 


Thanks 

  May 21, 2008 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Johnson from USA

well,
according to your logic, all computer languages should take out boolean as their data type, because 
they can simply use Y and N to do it.
I am a lazy programmer, if I can use one line of code to do the job, I won't use two. 
Some people can live without car for their life and have nothing to complain. 
this is their life style.

Language independance   May 21, 2008 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: Nick Price from Coventry UK

  May 23, 2008 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Joe Bloggs 

CREATE TABLE PERSON (NAME                VARCHAR2(30),
                     SEX                 VARCHAR2(1) CHECK (SEX IN ('M','F')),
                     CREDIT_WORTHY_FLAG  BOOLEAN)
/
CREATE INDEX PERSON_CREDIT_WORTHY_FLAG_IDX ON PERSON (CREDIT_WORTHY_FLAG) REPRESENTING TRUE AS NULL
/

  May 27, 2008 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: Joe Bloggs 

Bools and language   May 29, 2008 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: Per from Bremen, Germany

I for one think that a bool SQL Type would make table data more readable for the developer, and 
reduce the amount of errors made while coding. In our project, we have -1 = True and 0 = False (or 
was it the other way around). 

Y/N is more descriptive, but it still has the problem of querying for T/F,y/n, j/n or other 
variations of it. Best workaround I have found for now is to declare a constant in my code c_true 
and c_false with the respective values. That way I can be sure, that any query comparing it to any 
number/string is obviously wrong.... not pretty, but there is no better way atm.

Bool is not nessesary, bool is JUST beautifull. As a coder I perefer beautifull code, because I 
think it is good code.

Readability in general   July 21, 2008 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: Vincent Baier 

I certainly have no problem using that implementation if there is no alternative; however, I am 
forced to agree with many of the reviewers. While whatever implementation is required for a boolean 
could certainly cause losses in performance (at least the first few releases it is present, these 
things tend to clean up over time), the increased readability is a very important factor.

I have recently gotten into working with databases and as somebody who has always been for well 
documented, easily readable code all SQL implementations have been a problem for me. In general 
large SQL scripts and database types seem to be largely undecipherable compared to the usual code 
of myself and my colleagues. This is further complicated by the fact that I am not a programmer by 
trade and find that undocumented or unclear code is a lot harder for me to understand.

Furthermore, as an Electrical Engineer who often deals with digital applications, the convenience 
of a BOOLEAN data type is particular obvious for small scale applications. When interfacing with a 
wide scope of different programming languages including some assembly languages it does wonders for 
readability of abstraction layers that are just a single step above hardware.

I've never been one to use just one technology or have a narrow sight when programming so for me 
legibility is key. I need to know what things do when they talk to different languages, 
implementations (high level or embedded), application types, etc. I see that it does not 
necessarily add to the language from a technical level; however, its addition does not have any 
obvious drawbacks. Certainly the previous implementation would still work, so why not add it for 
the select few of us who might use it to some understandable end? 

Thank you for any response.

Real need for boolean   August 27, 2008 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Steve Appling from Atlanta, GA USA

My concern over the lack of a real Boolean type support isn't related to the size or format of the 
data.  Lack of a Boolean type limits the range of expressions that may be used in a select - in 
other words, you must use only expressions and not boolean valued conditions.

Example:

With boolean valued columns C1 and C2:

In MySQL or PostgreSQL I can write:
SELECT C1 AND C2 FROM MYTABLE

In Oracle (assuming use of 0,1 for booleans) I would have to write:
SELECT CASE WHEN (C1=1) AND (C2=1) THEN 1 ELSE 0 FROM MYTABLE

This is much harder to read and I would expect much less efficient.

I think it is this lack of boolean results that prohibits use of all boolean valued conditions like 
"SELECT A<B" when other numeric expressions like "SELECT A+B" work fine.

  August 29, 2008 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: G Siems from St. Paul, MN USA

With respect to ANSI, which "language syntax and semantics" are you referring to? 

If you are referring to the SQL standard then I should point out that the boolean data type was 
added to the standard starting with SQL99.

Compile time errors   September 2, 2008 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Salman Syed from Pittsburgh, PA

Tom,

Your points are valid. However, having a Boolean datatype would definitely be beneficial.

Here is a reason: We initially used Y/N with a check constraint. That caused the data in the 
database to be okay. However, there was no way to ensure that all PL/SQL followed that convention. 
We ran into issues with case etc. 

Then we reverted to 0,1. However, even over there we ran into issues aswe could not enforce 0 and 1 
to be the only values to be compared against in PL/SQL.

Having a boolean data type will allow to compiler to make, what are now runtime errors, into 
compile time errors. That will be a huge plus.

We spent a lot of time hunting down where the issues might be and would have never had to do that.

why "=TRUE"?   September 9, 2008 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: Erich from Vienna, Austria

enums   September 9, 2008 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: rc from The Netherlands

Maybe not just booleans but also enumerations in SQL and PL/SQL?

Boolean Types   September 10, 2008 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Shaun from UK

Hi Tom,

Like you, I have been and Oracle developer for many years and haven't felt any real loss for the 
lack of a boolean type.  In the new world of SOAP, however, it would really help!!!  The proble 
we're having is that we are using JDevleoper to expose a PL/SQL package as a web service and have 
complex types coming in and going out, a few of the outgoing columns need to be classified as 
BOOLEANS.  The only way we are able to deal with complex record types is to define them on the 
database and then reference them in the package.  If we define the types locally in the package, 
JDeveloper no longer allows us to expose that procedure as a web service.  I am very vocal in 
trying to ensure as much of the development here is done in Oracle, explaining that we too can 
provide and call web services, but this is becoming a real limiting factor for us at the moment.

The concept is the point   September 20, 2008 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Marc Wilson from Centennial, CO USA

I also think that the "concept is the point", here.   December 11, 2008 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Juan Calero from Madrid, Spain

I also think that the "concept is the point", here.
It's nice to have an abstraction of what means "Yes" and what means "No". We do not use everyday 
"all the range of colors" or some other domain value constrained type or collection, but "Yes" or 
"No" is used globally, by everybody, everyday. 

Besides, you say that you always use the varchar 'Y', 'N'. It's important to you, then, to keep 
that consistency by always using the same rule. 
¿Why then, translate this effort to all us? Isn't it better , if we are using the same concept, to 
use the same rules?

I'm sure every single Oracle database out there have implemented an "ad hoc" boolean data type and 
the administrators and developers have to deal with it, spending time in the definition of how to 
store Yes and No and how to check for it.

Yes and No it's pure logic and mathematics and, the same as NUMBER(9), it would be nice to have and 
easy, intuitive and universal way of defining it in Oracle.

Maybe BOOLEAN it's not ANSI, but, hey, Oracle is not precisely standard SQL, anyway.

Boolean type is a conflictive one   March 21, 2009 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Javier Castañón from Mexico City

http://troels.arvin.dk/db/rdbms/#data_types-boolean:

If boolean is not a useful data type, then what is?   August 14, 2009 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Jeff V. from Cleveland, OH USA

First off, I appreciate this long standing discussion on the boolean data type in Oracle. I just 
wanted to comment on the issue.

In my opinion, the comments of "boolean is a useless type because you can use a char(1) and a check 
for Y/N" is not valid.  By this same logic, we should then ditch the DATE type, because we can 
represent date/time stamps with a NUMBER (ticks since epoch), and while we're at it, why keep a 
NUMBER at all, since it can be a VARCHAR with a check for 0-9?

My point being, "we don't need a boolean because we can represent it with something else" is a bad 
argument because everything can be represented by something else. But that doesn't mean we should 
ditch every type and store everything as bits.

Oracle missing basic types   September 15, 2009 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: Karsten from Germany

Of course, no one really needs a BOOLEAN type to achieve what s/he has in mind.

Of course, we don't need wireless broadband Internet, yes, and we don't need 2 TV's per household. 
If that's your understanding of the word "progress", we should all crawl back into caves and wait 
until the sun rises...

It's conceptual! Oracle builds every fancy thing into their products and doesn't manage to provide 
a BOOLEAN type? That's ridiculous - given that Oracle is the market leader.

For booleans, the true/false check shouldn't involve any operator. In Java, one writes

if ( boolvar ) ...

In C/C++ you write

if ( intvar ) ...

In SQL you should be able to write

WHERE bool_col ...

...which you can't in Oracle (PostgreSQL can). MySQL offers an extension, you can at least do (with 
any int):

WHERE int_col ...

Considering the BOOLEAN issue, Oracle is the worst DBMS I know of. Oracle doesn't even support 
domains, so one might have thought: "Well, I'll emulate booleans via an integer" - bogus. You have 
to repeat and can't reuse the boolean type declaration.

No one needs string BOOLEANs. They are just a kind of enumerated type, with their localization, 
capitalization, typing, and mapping issues all having been addressed in this thread. Not to mention 
the metadata issue... and the actual data:

The natural way to store BOOLEANs in computers has always been to use integers with 0 meaning false 
and 1 (or non-zero) meaning true. It goes all the way down to processor instructions. Why should 
anyone care about strings booleans then? I read all the above, it's rethoric...

Can JDBC prepare statement handle out parameters?   November 3, 2009 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: Patil from London

declare
   b1 boolean;
   b2 boolean;
   n1 number := 0;
   n2 number := 0; 
begin
   sp_xxxx_map( ?, ?, ?, b1, b2 );
   if (b1) then n1 := 1; end if;
   if (b2) then n2 := 1; end if;
   ? := n1;
   ? := n2;
end;