Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jeremy.

Asked: November 02, 2002 - 7:42 pm UTC

Last updated: August 08, 2023 - 1:47 pm UTC

Version: 9.2.0.1.0

Viewed 100K+ times! This question is

You Asked

Here's a real short one for you Tom:

Why doesn't Oracle RDBMS have a boolean datatype?

and Tom said...

since
...,
flag char(1) check (flag in ( 'Y', 'N' )),
...,

serves the same purpose, requires the same amount of space and does the same thing - I guess we feel this is a feature we can let them have that we really don't need.

I mean - what do you get back from a column in "access" that is a boolean? TRUE / FALSE. We'll give you Y/N -- if you would like TRUE/FALSE, we can accomplish that easily with DECODE(flag,'Y','TRUE','N','FALSE')


Rating

  (109 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A few reasons why...

Jeremy Smith, November 03, 2002 - 2:13 pm UTC

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.

Tom Kyte
November 03, 2002 - 9:05 pm UTC

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

JBrain, November 04, 2002 - 4:34 am UTC

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...?

Tom Kyte
November 04, 2002 - 8:27 am UTC

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

Tony Andrews, November 04, 2002 - 8:48 am UTC

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.

Chandra S.Reddy, February 05, 2003 - 7:38 am UTC

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.


Tom Kyte
February 05, 2003 - 9:06 am UTC

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

Kurt, June 13, 2003 - 11:02 pm UTC

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

Tom Kyte
June 14, 2003 - 8:39 am UTC

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

Kurt, June 14, 2003 - 10:20 am UTC

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

Tom Kyte
June 14, 2003 - 1:45 pm UTC

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

DH, July 02, 2003 - 8:20 am UTC

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?

Tom Kyte
July 02, 2003 - 8:39 am UTC

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

DH, July 02, 2003 - 9:13 am UTC

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

AS, June 10, 2004 - 6:16 am UTC

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


Tom Kyte
June 10, 2004 - 8:01 am UTC

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

Ditter, September 17, 2004 - 4:56 am UTC

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.

Tom Kyte
September 17, 2004 - 9:16 am UTC

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

Kevin Ryan, September 22, 2004 - 11:16 am UTC

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.

Tom Kyte
September 22, 2004 - 11:49 am UTC

your welcome?

Boolean Array in PLSQL?

Andrew Cohen, November 08, 2004 - 4:59 pm UTC

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!!!




Tom Kyte
November 08, 2004 - 9:13 pm UTC

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?

Andrew Cohen, November 08, 2004 - 10:02 pm UTC

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?

Tom Kyte
November 09, 2004 - 8:31 am UTC

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

Bob B, November 09, 2004 - 12:48 am UTC

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

Tom Kyte
August 23, 2021 - 2:20 am UTC

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

mal, May 10, 2006 - 6:24 pm UTC

  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

Marius, July 18, 2006 - 1:50 pm UTC

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.

Tom Kyte
July 19, 2006 - 8:45 am UTC

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

Booleans.

D, August 29, 2006 - 12:42 pm UTC

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)

awomynda, October 06, 2006 - 8:35 am UTC

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

Earl Lewis, April 26, 2007 - 1:08 pm UTC

Tom,

You said that you like to use a varchar2(1) for boolean datatypes. That's fine. I think that works for all the situations I would need.

But how about a PL/SQL function that returns a boolean and you then find at a later time that it would be very convenient to reuse this function, and all it's complex underlying logic, in your select statements? In other words, I want a true/false from my function so it fits nicely into my PL/SQL. Then I find that that same function (again, returning true/false boolean) would be really useful in my query output? But I can't use it because it returns an incompatible datatype.

And, I can't create another function with the same name/signature (except for the return value datatype). Well, actually, I can create it, but then when I try to use it SQL complains that it finds too many definitions of the function. Now I've got to go back and rework my original function and all the PL/SQL the uses it.

Obviously, some thorough forethought could eliminate this problem. But it's not always clear when/where you might want to use everything you write into an application.

Can you tell that I just ran into this problem? I know, I'm completely transparent. What can I do?

Either way, do you consider this a compelling argument for booleans in SQL?
Tom Kyte
April 27, 2007 - 10:21 am UTC

so, you write a wrapper

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


PLSQL types are not SQL types - that is the mismatch here, you need to have a SQL type (sql being "lower" than plsql) for both to see.


Another winner!

Earl Lewis, April 27, 2007 - 5:35 pm UTC

Of course! Sometimes you can't see the forest fo the trees.

Thanks for the reply and thanks for sharing your vast wealth of Oracle knowledge with us all.

Sincerely,

Earl

Please just add the boolean datatype

Jd, July 17, 2007 - 3:53 pm UTC

Please accept a customer's request to add a boolean datatype; it is tiresome always having to "work around" the lack of a boolean datatype and it's something that's been frustrating for all developers with which I've worked.

I understand that a viable "solution" is to 1) create a field using currently supported types then 2) create a constraint restricting the vales on the new field and 3) create a wrapper for use in SQL or PL/SQL then 4) create a helper method or utility class to convert the type returned in the resultset.

As a customer, and also expressing the frustration of other customers and their developers, I am giving my feedback that, as I customer, I am frustrated with having to "work around" Oracle databases not having native boolean support.


question of bitmaps

Dave, September 18, 2007 - 6:46 pm UTC

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?
Tom Kyte
September 19, 2007 - 11:40 am UTC

the utl_raw package provides a full set of bit twiddle functions

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

Lot's of Bool

Ed Cortes, April 15, 2008 - 1:55 pm UTC

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

Samuel, April 30, 2008 - 2:41 pm UTC

hello, I'm a happy MySQL developer, in my new job they where tempted to use Oracle for their databases.

I evaluated your database solutions and seemed a good replacement, your RDBMS is robust, highly reliable, secure and easy to mantain, except for this, the boolean stuff, we got to use a lot of fields with true/false fields, to use in data mining and logistic regression, the programs we developed worked even three times slower because of the boolean parsing, since we are working with billions of records, speed is critcial, so we decided to stick with mysql.

The moral of this is that you LOST A CUSTOMER because of this. 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....
Tom Kyte
April 30, 2008 - 3:27 pm UTC

... the programs we developed worked even three times slower because of the boolean parsing, ..

umm, you did something seriously whacky then, please consider what you just said there.

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

is just, well, 1 and 0 - a number - nothing special - I don't really see how you could get "three times slower"

please, elaborate on what you did exactly.

Is Oracle ever going to add a boolean datatype?

G Siems, April 30, 2008 - 6:44 pm UTC

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?

samuel, April 30, 2008 - 8:10 pm UTC

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....
Tom Kyte
April 30, 2008 - 9:13 pm UTC

you said the way you implemented it in oracle was "three times slower"

so, tell us, how did you implement the concept of "0 and 1" in Oracle? I'm curious - because - well, 0 and 1 are just 0 and 1 (in mysql as well as oracle).

what was your implementation in Oracle - what did you do to mimic mysql having a #define of TRUE=1 and FALSE=0? (that is their boolean type, a number with 0 or 1 in it)

*chuckle*

Mike Hennessy, May 01, 2008 - 2:38 am UTC

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

response

Samuel, May 02, 2008 - 12:35 am UTC

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)
Tom Kyte
May 02, 2008 - 7:03 am UTC

why would you use "spanish billion" instead of just trillion.

you executed that 15 trillion times - and lived to speak of it.



this has not a thing to do with numbers Samuel, everything to do with writing the most inefficient code humanly possible. Code that is horribly subject to SQL Injection and that has not a chance of scaling up in the real world. Why would you parse over and over and over and over again and again and again. You spent 90% or more of your runtime parsing sql in all probability.

NOTE: in mysql, where my_value = TRUE is precisely identical to where my_value = 1 - boolean my butt, it is a *number*



Antediluvian

DK, May 06, 2008 - 5:51 pm UTC

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

Johnson, May 21, 2008 - 12:50 pm UTC

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.




Tom Kyte
May 21, 2008 - 3:05 pm UTC

well, it could be that


it would really add nothing to the language.

where column = TRUE;

that is mysql. Wow, that is so much different from

where column = 'Y'

or

where column = 1


I'm sorry, but I don't see the issue.

issue for no boolean

Johnson, May 21, 2008 - 3:36 pm UTC

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
Tom Kyte
May 21, 2008 - 3:43 pm UTC

..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.
...

I won't tell you - but it should be obvious that not only can you - but it is done quite a bit. Don't look at my code, you might be disappointed that I've never felt the need to hide or 'wrap' this.

we'll just have to agree to disagree - I don't see it as a big deal at all - in 20 years, I've never "missed" a boolean - many of the languages I've used over the years never had the concept, it wasn't a big deal.


I can definitely say "if not all" is easily disproved. I am a plsql programmer.


and you know, the mysql boolean would not give you, does not give you (eg: the ansi implementation would not give you either) a boolean you would use like this:

if ( column )
then

it would give you

if ( column = TRUE )
then


which means, you'd be right back to where you are. If you want that right now, just create a package with constants true and false defaulted to Y and N and you'd be *done*

Johnson, May 21, 2008 - 3:53 pm UTC

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.





Tom Kyte
May 21, 2008 - 5:19 pm UTC

hate it when people put words in my mouth.

what flow of logic did you see that makes you say that.

if ( column )

versus

if ( column ='Y' )

lazy or no, there ain't much difference there.

and if you got a SQL boolean, you'd be typing

if ( column = TRUE )

which is one more character, so we are saving you a keystroke.


I never said "remove boolean from language X", I'm saying "I don't see the point in a SQL boolean, it doesn't do anything for us"

Language independance

Nick Price, May 21, 2008 - 6:43 pm UTC

I have inherited an application that was developed by a primarily French team, who obviously read your column - however, as "Y" and "N" mean nothing to them, they've chosen to use "O" and "N" (Oui and Non respectively)...

My Maltese colleague started developing a new module, so he chose to set up his boolean columns as "I" (Iva) and "L" (Le) - the module developed by my Swedish colleague used "J" (Ja) and "N" (Nej)...

Now, this is a somewhat exaggerated story - in truth, we only had hard-coded strings in English and French throughout the database.

However, I am very disappointed to discover that you recommend storing true/false flag values as CHAR(1), rather than a language independent solution such as NUMBER(1) - while I can appreciate that perhaps "1" and "0" aren't as intuitive, it certainly makes it easier for a multi-national development team to agree on a standard for the application!


Tom Kyte
May 21, 2008 - 9:54 pm UTC

Ok, you have just convinced me with a compelling reason.

I now like 1 and 0 - I see your point here. It was like a light bulb going on - never hit that (the different languages).

And since 2002 when this was first asked - I've outlawed char(1), it would be varchar2(1) if it were a one character field today....

But 0 and 1 - you just convinced me, in a compelling way.

Joe Bloggs, May 23, 2008 - 10:01 am UTC

I too am an Oracle expert of 20 years, and cannot for the life of me see what your objections to adding BOOLEAN datatypes in the DB are, Tom.

I have many a time desired it myself, only having to resort to the workaround of the VARCHAR2(1) option which confuses a lot of people. Adding a column constraint is mandatory, though I prefer to limit it to 'X' or NULL, and not 'Y' or 'N', or 0 or 1. That way, it is far more of a real representation of a flag, and as such a tad more self-commenting, as well as being language independent too. Also, if it becomes necessary to index the column, it will be done so efficiently since NULLs are never stored in non-unique indexes.

Further, your argument towards not implementing BOOLEAN natively and using VARCHAR2(1) as a workaround to store 'Y' and 'N' or 0 and 1 holds no water. Why not in that case only ever allow a character datatype natively and store all numbers and dates in that character form ?!?!? Not logical !!!

(PS. When is Oracle gonna let us use VARCHAR ? It must be the 15th anniversary of the VARCHAR2 datatype by now).



If it were possible to store Boolean types natively, Oracle could perhaps add some fancy indexing possibilities natively over the top too. Such as the ability to choose whether TRUE or whether FALSE could be represented in the index as NULL.

Example,
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
/

Now, to find all the uncredit-worthy people you would query where the credit-worthy flag is false, and harness the use of an index to do it (because only the FALSE values are stored in the index in this example).
Tom Kyte
May 23, 2008 - 6:03 pm UTC

I don't have "objections", I don't see the incredible usefulness.

ANSI says:

boolean is a byte, it would actually be a number under the covers

you would use the words TRUE and FALSE and optionally UNKNOWN (in place of null)

You would insert:

insert into t (x,y) values (s.nextval,TRUE);

you would query

select * from t where y = TRUE;

I don't see *why this is so important*, what this does that is so much better, how this makes life wonderful. That is all.


Joe Bloggs, May 27, 2008 - 4:29 am UTC

Followup May 23, 2008 - 6pm US/Eastern:

> I don't see *why this is so important*, what this does that is so much better, how this makes life wonderful. That is all.


Well, it would be more descriptive. Increasing readability of code. (Also personally, I would choose IS TRUE over = TRUE).

Bools and language

Per, May 29, 2008 - 8:58 am UTC

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

Vincent Baier, July 21, 2008 - 12:20 pm UTC

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

Steve Appling, August 27, 2008 - 11:25 am UTC

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.

Tom Kyte
August 29, 2008 - 12:00 pm UTC

You would really ultimately have to go back to ANSI and ask them to modify the language syntax and semantics. The language itself has no concept of "boolean", even in the current suggested approach, it would always be:

select * from t where something = TRUE;


if you select true from table, you get a number back - 1 or 0, not a boolean really.


The case statement would not be any less efficient and personally (honestly), the select c1 and c2 from t - I would expect (being a C programmer at heart, from the old days) something totally different from what you expect, I'd be expecting the result of c1 & c2 (bitwise and of two numbers). Being a C programmer that does a lot of database stuff, actually, select bitand(c1,c2) from t would do it.... using 1 and 0 anyway.

but the case statement doesn't seem any less "readable" to me,

select case when a<b then 1 else 0 end, ....




G Siems, August 29, 2008 - 2:11 pm UTC

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.
Tom Kyte
August 30, 2008 - 9:55 am UTC

and the so called boolean datatype is simply a number

and the semantics would be

"where column = TRUE;"


not:

"where column"


it would be

"where column = false"
not
"where NOT column"


it is just a predefined constant. It is not any more boolean than Y/N, 1/0


Compile time errors

Salman Syed, September 02, 2008 - 11:33 am UTC

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"?

Erich, September 09, 2008 - 5:20 am UTC

Should Oracle ever add a boolean datatype to SQL (and I surely hope so, for all the reasons already mentioned), I would strongly expect it not to be just a new name for number(1), but syntactically just as (or even more) powerful as in PL/SQL or other languages that support boolean variables.

i.e.

UPDATE orders SET has_active_items=EXISTS(SELECT x FROM items WHERE orderid=orders.id AND NOT item_cancelled);

SELECT * FROM orders WHERE NOT has_active_items AND NOT order_cancelled;

and even

CREATE FUNCTION xor(BOOLEAN v1, BOOLEAN v2) RETURN BOOLEAN AS
BEGIN
RETURN v1<>v2;
END;
/

SELECT xor(a,b)
FROM foobaar
WHERE xor(c,d)
GROUP BY a,b,c
HAVING xor(b,c);


This obviously requires the TRUE and FALSE literals to become native in SQL, too, as in

SELECT * FROM sometable WHERE FALSE;

Tom Kyte
September 09, 2008 - 8:03 am UTC

tell it to the ANSI sql committee that states what the implementation should be.

enums

rc, September 09, 2008 - 2:37 pm UTC

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

Boolean Types

Shaun, September 10, 2008 - 11:41 am UTC

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

Marc Wilson, September 20, 2008 - 2:47 am UTC

Of course boolean types are just (often) a byte and there are many ways to represent or simulate the concept; that is not the point. The specific concept that something can be TRUE, or it can be FALSE, is the point of the BOOLEAN data type.

The possible values of the BOOLEAN data type are inherently unambiguous. True values are always represented by TRUE and false values are always represented by FALSE. It is this concept that is the whole point behind the BOOLEAN data type in any language. It is not because BOOLEAN data types save space, are easier to implement, easier to read, or are faster.

As touched on by an earlier reviewer, BOOLEAN values leave nothing up to interpretation, reinterpretation, or changing development team standards as an application evolves. The values are interpreted only by the compiler. They enable a consistency that removes the element of interpretation at the data element level altogether. A value of TRUE will always mean that a condition is true and FALSE will always mean that a value represents the concept of false. (I know I said it before)

The scope of a constraint on a single data element is only the single entity that it constrains. The constraint on the column does not ensure that the same type of data (or concept) will be represented the same way in another column in a different table. (I know that most all reviewer and readers here know this)

The scope of the constraint that a data type imposes, is the entire application that is developed with a language. Data types ensure that data of that type are represented consistently.

A BOOLEAN data type removes any chance that one person will decide to use 'Y', another 'y', another '1', another 'T', to represent the *concept* of TRUE. A BOOLEAN data type is a simple and concise way to ensure consistent representation, across an application, of a *very* common concept in computer programming and data representation; whether something is TRUE or not.

It would not be desirable to use a VARCHAR column to hold numbers (efficiency aside) and have one person represent the number 45 (forty-five) as '45', another as 'forty-five', another as 'FORTY FIVE', another as 'VL' (50 minus 5), and so on.

Mechanics are not the reason a BOOLEAN data type should exist; it is to assist in the consistent representation of an important and common concept. The BOOLEAN concept is so rudimentary, that how it is represented, should not be left up to interpretation of the personalities of multiple developers (or the multiple personalities of a single developer for that matter).

I have never heard a programmer tout the amazingly perfect and extra cool way they represented a TRUE value before. (The representation of BOOLEAN entities are just not that interesting)

Tom, I absolutely enjoy the raw representation of the facts that your insightful responses embody. However I feel that you are arguing for the sake of arguing on this subject.

...just my 499 words.

Thank you.

Tom Kyte
September 21, 2008 - 1:42 pm UTC

why is boolean any different from any other domain value.

why is boolean different from the column that may only contain A, B, or C.

or the column that can only have the integers 1-10

All of the arguments have been "well, it would be consistent", but that is true for any domain value constrained type - all of them. They all need to use values consistently - for all domain constrainted types. I don't see anyone saying "we need a COLOR type, else someone might use magenta as a value instead of reddish-purple which we think is right"

I just don't see how TRUE and FALSE lead us into a greater place.


In fact, I see all kinds of issues where binds would never be used again :( Because how to you "bind" true or false. They become keywords all of a sudden. What does a client bind for trueness? (and this is especially true of enumerations - something which I hope sql never even remotely considers)

And what of the obvious ambiguity all of a sudden in plsql where true and false are already defined and are not zero and one.


It sounds like such an "easy quick change", but I see more questions, ambiguities, issues than "easy-ness" - truly.

I also think that the "concept is the point", here.

Juan Calero, December 11, 2008 - 1:12 pm UTC

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

Javier Castañón, March 21, 2009 - 2:00 pm UTC

I was irritated with the lack of boolean type in several databases and thought Mr. Kyte was very stubborn in his views about this "issue". Now that I'm assembling an SQL and PL/SQL coding conventions manual for a customer, did some homework and I have to accept that I've changed my mind and now I side with Mr. Kyte. Furthermore, I plan to discourage the use of booleans in the data model. My rationale is as follows:

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

"The BOOLEAN type is optional (has feature ID T031), which is a bit surprising for such a basic type. However, it seems that endless discussions of how NULL is to be interpreted for a boolean value is holding BOOLEAN from becoming a core type."

"The standard says that a BOOLEAN may be one of the following literals:

* TRUE
* FALSE
* UNKNOWN or NULL (unless prohibited by a NOT NULL constraint)"

Citing from Joe Celko's "Thinking in Sets" 1st Ed., page 228:

"The BIT and BIT VARYING data type were deprecated in the SQL:2003 Standards, ... While BOOLEAN had not yet been deprecated as of this writing, it also has problems with the rules about NULLs and the three-valued logic of SQL"

"The NULL cannot be treated as an UNKNOWN because one of the basic rules of NULLs is that they propagate. The resulting four-valued logic is inconsistent:

UNKNOWN AND TRUE = UNKNOWN
UNKNOWN AND FALSE = FALSE
NULL AND FALSE = NULL
NULL AND TRUE = NULL"

And from page 229, same book:

"There are two situations in practice. Either the bits are individual attributes, or they are used as a vector to represent a single attribute"

One example: "Is this garment domestic or imported?" the answer could be encoded with at least two values but some people think there is a problem because they would prefer to use Is_Domestic = True. Same case with answers like yes/no, according to Celko those eventually could expand to "Not Answered", "Not Applicable", etc. In the former example, the garment origin could be expanded to ISO country codes. Another example: "Is this item public?" could be expanded from yes/no or public/private to "public only for friends".

With a better data model, cases like:

WHERE Is_Invoiced = 'Y' AND Is_Paid = 'N'

would be:

WHERE Invoice_Number IS NOT NULL AND Payment_Date IS NOT NULL

Things like CREDIT_WORTHY_FLAG BOOLEAN perhaps should be computed values, not attributes to be stored. And what about columns used as boolean flags that are not consistent with data in other columns? In those cases either there are not constraints or triggers that check the values or there are so many that maintenance is a nightmare and not all checks are performed.

Last but no least, there are people that need boolean because either their user interface use them as in checkboxes or their web services transfer them through the wire. I could not comment on those cases, my data model usually come from requirements in the business domain, not from constraints in user interface technologies or infrastructure.

I believe Tom Kyte when he says "in 20 years, I've never 'missed' a boolean"

If boolean is not a useful data type, then what is?

Jeff V., August 14, 2009 - 10:28 am UTC

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

Karsten, September 15, 2009 - 6:52 pm UTC

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?

Patil, November 03, 2009 - 4:14 am UTC

Tom,

For one of the queries above, you've mentioned the following workaround,

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.


I've got the similar problem now, but i'm not sure how to register the outparameters for statement which is prepared with Preparestatement call.

I believe PreparedStatement class only provides the Set**** methods and doesn't have the method "registerOutParameter".

Please help me?
Tom Kyte
November 09, 2009 - 2:20 pm UTC

you need to use a callable statement.

there are "statements", they should never be used except perhaps for DDL, but then again, most things should never do DDL either.

there are preparedstatements - send inputs to the statement. used for most all DML

there are callablestatements - send inputs and retrieve outputs from the statement. used for some dml (returning clause for example) and often for stored procedures.

Why does Oracle use BOOLEAN parameters in dbms_* and utl_* then?

Petr Novotny, February 04, 2010 - 4:47 am UTC

All these theoretical discussions about whether boolean is useful or not...

The fact is, Oracle itself apparently believes that boolean is useful. Otherwise, APIs of useful DBMS_* packages and UTL_* packages wouldn't be full of them.

I don't care about boolean database columns. But the fact that I cannot write
select ' http://...?param1='||utl_url.escape(path,TRUE )||'&...' URL from sometable;
is very annoying. The workaround to create my own PL/SQL function that encapsulates this utl_url is not always useful, sometimes you have no privileges on the connect account...

Why can't Oracle just keep it consistent? Either support boolean in SELECTs, or not put boolean parameters to the public functions...
Tom Kyte
February 04, 2010 - 1:57 pm UTC

because it is plsql and not sql?



Oleksandr Alesinskyy, February 05, 2010 - 2:32 am UTC

Let put it this way - "Absence of the Boolean is no more than nuisance but it is a pretty irritating nuisance"

A boolean datatype is necessary

Julius, March 12, 2010 - 4:40 am UTC

I totally agree, that the absence of a boolean datatype is a nuisance. One can use boolean in PLSQL, but not in SQL - that's inconsistent. Using 0 and 1 or 'y' and 'n' is just a hack. I only want two conditions - true and false. If I create a function returning a number (i.e. 1 = true, 0 = false) one can write:
SELECT * FROM dual WHERE my_function(x) = 2
, and this is what I don't want to be possible. The one and only query should be:
SELECT * FROM my_table WHERE my_function(x)
OR
SELECT * FROM my_table WHERE not my_function(x)
. All other popular databases can handle this, even MySQL.

A reader, April 23, 2010 - 8:50 am UTC

Booleans are everywhere in nearly every programming language. Oh Great Oracle, please give me a place to put them in my database tables without having to settle for something else that is "good enough"...

A reader, April 23, 2010 - 9:02 am UTC

Sorry for the double post:

This IS more than a little annoying. Even if booleans and numbers are stored the same way in the DB, the way us programmers have been, well... programmed... for years is that booleans use different syntax/operators that make logical syntactical sense due to the nature of Booleans to "exist" or "not-exist". There is no way to store "exists" or "not-exists" with varchar2 or number that makes logical sense in all the ways (and all the languages) that you want to say it in. Y/N fails, 0/1 fails. Even "T"/"F" fails operationally.

Even SQL understands the concept of BOOLEAN, but for some reason we can't store it?

Eg.: SELECT * FROM emps WHERE employee_name = 'Fred';

Somewhere in the SQL engine, a little flag is going "True" or "False" on every row of the select.

Honestly, this is almost as annoying as not having packages/global variables in PostgreSQL.

Well, now that's out of my system... back to development ;).

About time...

Aron Medders, September 05, 2010 - 6:29 am UTC

I'm not going to go through the arguments since they've been outlined very well here. My comment is much more simple.

Seeing as how this thread has been going on for over 8 years and there's obviously a lot of interest, why not just relent to the demands of the people and just give us a boolean data type???

The discussion is spinning

Tegiri Nenashi, September 30, 2010 - 9:58 am UTC

Not sure if Nick Price's argument is so compelling

Mark Brady, October 18, 2010 - 11:19 am UTC

While it may be true that Y and N seem remarkably similar to the English words Yes and No, but that fact alone does make those two values a "Language Dependent Solution". They are EXACTLY as abstract as 1 and 0. At some point a developer will have to introduced to that concept in his own language and given the decoder ring. 1 = Oui or Y = Oui. To me or any coder, the only thing that I request is consistency.

Should the Keywords in a language also be language independent? What about variables? Should every variable be A1, A2, A3, A4?

INSERT makes sense to me, a native English speaker but once you learn that those 6 letters form the command to add a new row, the semantic meaning is irrelevant. Think about your Assembly language days. What did mov mean anyways?

Fortunately for Americans, English has become the lingua franca of technology in general and computing/internet specifically.

If the database in question is actually being developed for the Maltese, then using their mnemonics would be fine. So long as it's consistent and documented, no one should care what two characters are chosen.

Oracle Boolean solution is an Anti-pattern

Joe, December 16, 2010 - 3:18 pm UTC

For Oracle to refuse to simplify their boolean approach results more complexity than necessary.

Which is quicker/easier to understand exactly what is going on?:

flag char(1) check (flag in ( 'Y', 'N' )),
flag2 char(1) check (flag2 in ( 'Y', 'N' )),
flag3 char(1) check (flag3 in ( 'Y', 'N' )),

or

flag boolean,
flag2 boolean,
flag3 boolean,

Failure to simplify just results in developers googling until they come across blogs such as this and roll their eyes at the "real" solution

Architect View

RB, January 04, 2011 - 9:45 am UTC

I am a system architect with financial responsability. We desing with logical models not physical models. My db group supports both Oracle and SQL Server (MS). Logically a Boolean type is required as T/F 1/0 are not lgical concepts, but implementation constructs. In my experience the simple fact is that going from a Logical model to physical model in Oracle is much more expensive when using Oracle/PLSQL than SQL Server/T-SQL. The many reasons cited above drive this additional cost. From my job perspective the db is just another component in my overall system. I really don't care about how a db or any other component is implemented. I only care about performance and the total cost of ownership. For the same "bang" Oracle's lack of a Boolean construct relates to significantly bigger "Bucks".
Tom Kyte
January 04, 2011 - 10:51 am UTC

I told myself I'd stop replying to the boolean thread - just keeps going around and around. But - I could not resist. I'm not going to debate booleans - but other things mentioned here...


If you design "logically" without a care in the world for the "physical implementation", then you better have the best developers in the world.

Because you have just made their jobs a whole lot harder than if you didn't exist at all. They'll receive pie in the sky, wouldn't it be nice, it should work OK on everything 'designs'.

If you are telling me that a boolean or lack thereof is costing you "big bucks" - besides not believing you - you don't have too much credibility.

... I really don't care about how a db or any other
component is implemented. ...

This is why I really don't appreciate the "architects" I meet sometimes (well, ok, many times). That statement sums it all up.

If you don't care about how the components that make the foundation of your system work function - you are not an architect in any sense I know. Do you think that real architects - the ones from which we borrowed the name - have the same attitude? Or do you think they really strive to *fully* understand what they are working with so as to be able to build something that a) works, b) works well, c) keeps working for a really long time, d) in a safe manner.


If you really have financial responsibility - you would change that "care" factor really fast.

Once more on boolean

Oleksandr Alesinskyy, January 04, 2011 - 1:06 pm UTC

I guess that Oracle was able to implement boolean with less efforts than spent already on defense of its needlessness.
Tom Kyte
January 04, 2011 - 1:31 pm UTC

I am not Oracle really, I just answer questions here. I have stated my opinion and I'm not defending anyone. I'm defending my opinion. I am and have been of the opinion that a boolean type would not be worth anything.

And I just stopped on that entire track - not commenting on boolean anymore, it seems to strike deep emotions on either side.

Comment to the architect...

TA, January 04, 2011 - 1:29 pm UTC

Logically, a boolean is not necessary. As C. J. Date has pointed out (see for instance his "Database in Depth" from O'Reilly); a relation, i.e. a "table" without nulls, represents a predicate in first-order predicate logic and each tuple, i.e. "row", represents a true proposition for that predicate. To represent a boolean you set up a new relation representing the "boolean" state, either true or false but not both, and associate that with the original relation using a foreign key. For example, let's say that we want to model cars and whether they are in traffic or not. The later could be a "boolean" in a "car" relation but the way to model it logically is to have one "car" relation and one "in traffic" relation. The (primary) key of the "car" relation go into the "in traffic" relation if the car is in traffic and is removed when not. Its presense in the "in traffic" relation represents boolean true and its absense represents boolean false. That is what goes into the logical , relational, model. How that is then realised in physical model is another matter (esp. as SQL leaves a bit to be desired in terms of being relational)...

Enhancement Request - Bug 8518898 on My Oracle Support

Philip Moore, January 04, 2011 - 2:05 pm UTC

Due to my personal preference for adding a BOOLEAN datatype to make SQL and PL/SQL more tightly integrated - I filed an enhancement request with "My Oracle Support":

Bug 8518898: ADD THE BOOLEAN DATA TYPE TO THE SQL LANGUAGE IN ORACLE

I did this back in 2009 - and it hasn't been updated since then:
*** 11/11/09 02:05 pm *** (CHG: Asg->NEW OWNER OWNER)

I think the new owner is someone called "/dev/null"...

I guess they talked with Tom :)

Happy New Year!
Tom Kyte
January 04, 2011 - 2:22 pm UTC

It is assigned to a human - I can see their name. It is in status "Internal Review"

System Architect Clarfication

RB, January 04, 2011 - 5:37 pm UTC

Don't want to swell this thread but as a system architect I am concerned with the high level/business view of the system in a multi-platform shop. I have database architects and software architects working for me, and database designers and software designers working with them (and I have some great ones), leading the developers. These guys absolutly care about implementation details and I trust their decisions. If you do not have tiered teams I can understand your frustration with architects.
I (from my job position and level) really don't need and shouldn't care about the technical implementation unless it impacts functionality/performance/cost - I care about the bottom line. My guys tell me if I need to care about anything lower and when doing a detailed cost analysis Bool's were one of the items that came up when comparing implementation code size and complexity. Seems a trivial thing but it propogates through the entire system. Implementing true Booleans in any database that does not natively support them takes more lines of code/characters in both the client and database logic (i.e. Procedures and triggers); I don't feel that this has been disputed here. More lines of code (forced by technology, developer skills being equal and conforming to good practices - no "artists") usually means more points of failure and higher related overall costs. Tri-states are another story and the costs are comparable IMHO.

As for logical models, in a past job I had to support five databases. I was implementing for county government across the country. I had to present the same user interface using whatever database the county had. I can imagine the issues I would have had going from requirements to a consistent implementation without a logical design somewhere in between. Logical being "Pie in the sky" makes some pretty rash assumptions about the skills of these people. (credibility?... kidding) All databases have features that make the other guys jealous. Booleans and DateTimes are usually the most painful and contentious when implimenting on multiple platforms. In a single platform shop it all (Bool's and Logical) seems academic to me - you use what you have and move on with solving the business need.

I do need to apologize for the use of the term "significantly" (it just slipped out..I know to be more careful) as the difference is not all that severe; but I stand by my statement (generalized) that native Booleans in a database reduces total cost of ownership of the overall system.

To TA, I agree conceptually and this level of detail is a little over my head, but again comming from my somewhat technically removed cost of ownership perspective, booleans and denormalization even at the logical level (used smartly) can reduce costs.

Maybe I shouldn't have jumped in here as this is a technical thread and not a business thread. Sorry if I stired a cold pot.

BTW - for what it is worth I've found numeric implementations of Bool's more cost effective than character implementations in multi-platform shops.
Tom Kyte
January 05, 2011 - 7:18 am UTC

... I (from my job position and level) really don't need and shouldn't care about
the technical implementation unless it impacts functionality/performance/cost - ...

I respectfully disagree. Entirely and completely.

...I can imagine the
issues I would have had going from requirements to a consistent implementation
without a logical design somewhere in between. ..


I never said "logical design bad". I said "not understanding and not caring to understand and purposely stating 'I don't want to know' about the implementation of the tools you are using and trying to create a logical design is a bad idea. I've seen it over and over and over again, I've seen it turn out not so well over and over and over again"

If you truly cared about performance and cost of ownership - you would take care to understand the implementations so as to come up with "logical things" that make sense given what can actually be done and how it is done. You cannot build a performant system any other way.


... BTW - for what it is worth I've found numeric implementations of Bool's more
cost effective than character implementations in multi-platform shops. ..

that is a very strange sounding statement. Care to explain what you mean by that. I don't see any possible connection between the choice of a number/varchar2(1) as a datatype and "cost effectiveness".


Architect Response

A reader, January 05, 2011 - 3:00 pm UTC

Hi Tom,

My "Don't care" response relates to this topic of Bool's or really for me data types of the implementation. The higher the level of implementation/abstraction the more I care.

This comes from a few areas
First is time management. I spend most of my day with sales/marketing, and legal. My work runs in cycles. I take the business requirements and develop the top level black box component design. This is reviewed by my domain architects and tweaked based on their expertice. From then on I work with sales/marketing to help them sell the product and with legal to help them understand things like liability issues. Sometimes I support identifying, verifying, and reviewing patents. I work at block box level - I write a spec, define parameters (budget, performance, etc) and let the experts do their job. In this area if I cared about data types that aren't brought to my attention I wouldn't get my job done.

Second is a chain of command factor. I am a couple of layers removed (on a day-to-day basis) from the people doing the "real"work". I don't think I should jump over my guys and meddle in the work. I believe this is the responsible approach. I don't think you would like it if your V.P. of Development came directly to you and dictated data types. Also my guys would question if I trusted thier skills or integrity. I put the appropriate decisions in the proper hands. I asked my guys why there were differences in cost and they said impeadence mismatches between platforms was one factor, and Booleans and Dates were part of this. Going from the on/off conceptual to chars and back to on/off on the hardware was a longer path than "all numeric all the time" in our case - made sense to me and explained some of the cost differences.

Finally is skill. I'm not that technical anymore. Things have changed alot in technology since I was hands on. We've come a long way from the 8 bit processors I used. Also, being an EE Booleans were simple - see if a point was +5V or 0V. From this perspective I could (poorly) argue that to support Bool's in this domain they should be implemented as varchar(3).

On logical models I think here we may be disagreeing on caring based on reading of my original post where I said "We" and not "I". I don't develop logical models myself and again the further from my responsability the less I care about the details unless I'm asked to get involved, which is pretty rare. If it wasn't I would need to care about this level of detail very much. Sorry if I'm wrong here.

Re: T/F 0/1 There are two reasons. First is consistancy across platforms, mindsets, etc. When we hit the hardware we are at (TTL) bit level and all Bool's are really just wrappers for this. Numbers are closer to this than characters and more simply constrained at a higer level. (e.g., no 'T' 't') It's kind of a least common factor thing. When the hardware, drivers, and some of the databases are using bits for Bool's chars become outliers. Having to think in both numeric and char to implement the same function costs a little more brain power.

Second is Boolean algebra. It's eaiser to process logic functions with and's, or's, not's or to store a Karnaugh map in the database with numerics.

SQL Server doesn't have Bool's but the standard is 'bit(1)'. The Oracle standard is char(1). Since this was brought to my attention I do have to make a judgement and have decided we will be using numeric(1) for Bool's everywhere (TCO analysis based). I guess all my database guys can be mad at me together!


I've enjoyed this discussion and have two final comments (three if I say I'm sorry for moving this off the main topic)

1)Give the people Bool's in all DB's and everywhere for that matter. People think natively in a Boolean manner and voice that with T/F/t/f/Y/N/0/1/0/>0/+5V ad finitum. Bool's are a primitive data type in the real world, why not in technology? Remove the implementation from the logic (a basic OO concept) and become more platform independent. If so the (ongoing everywhere it seems) discussion of Bool or not and char vs. numeric will never come up when implementing true Bool's (manditory and constrained to affirmative/negitive) - just use Boolean. For those that need increased functionality such as tri-states (or think that Bool's are evil... smile), they are free to implement as they choose - numeric, char, even image...

2) I guess I do care more than I admit or I wouldn't have had this discussion. I ran across this thread doing my own research on the topic which was brought to my attention as a cost factor. From what I've seen here and other places, the lack of Bool's drives many discussions like this which (going on years) eats into productivity time across the industry. I must stand by my statements that lack of Bool's does increase overall costs (it's costing my company right now) and numerics (at least when your cross-platform system drives hardware) are most cost effective.

SQL and DBMS native languages never mature..

A reader, February 17, 2011 - 2:52 pm UTC

PL-SQL is just ugly and inefficient. Esp. when you programmed in languages like Scala, Ruby...
SQL seems so ancient. Just look at the immature responses for Why not Boolean support question. Use CHAR(1)?? Are you kidding me? We expect to use just 1 bit of memory space and not 8 or 16 bits when we say BOOLEAN. That's just about the space. There are other type safety advantages as discussed in this thread.

Tom Kyte
February 17, 2011 - 3:10 pm UTC

what ever.

PL-SQL ugly? I don't think so. When I look at Ruby - that is when I see "ugly". I guess beauty is in the eye of the beholder.

When I want to process data in the database - PL/SQL is simply the most efficient way to do it. I'll program circles around you - if you want to process data. If I want to do something else - C, C++, Java - something like that would make sense.

SQL is about data - and if you took the time to actually learn it thoroughly, you'd find yourself programming circles around others too.


"Never Mature" - that made me laugh out loud. They are actually fairly mature languages. They've been used to build some pretty large applications - probably longer than you've been programming and probably longer than you've been alive.


and if you think Ruby, Scala, etc use a bit for anything - I've got a bridge I'd like to sell you....

and I'd like you to show me the computer that would support a 1 bit representation in memory - the typical word size is at least 32 if not 64 bits - if you have a boolean in your language - it is going to be stored in a word, not in a single bit.

output boolean values

Gabriel, February 21, 2011 - 11:08 am UTC

Hello Tom,

How can I output what is in some boolean variables? If boolean then output is obvious but is there anything else? decode (lv_bolean, TRUE, 'TRUE') doesn't work because of type mismatch. Anything else out there that would do this job?

Thank you,
Tom Kyte
February 22, 2011 - 9:58 pm UTC

create or replace function prt_bool( x in boolean ) return varchar2
......


on why PLSQL is not so beautiful!

A reader, February 22, 2011 - 6:21 pm UTC

What's new in PL-SQL (in 11g)?
CONTINUE

a := a + 1; -- so short and succinct!

wow!!
Good for only ancient Oracle Business folks!!

Tom Kyte
February 23, 2011 - 8:40 pm UTC

hmmm, how is it that you can write text but not read it??? It is perplexing to me.

There is a plsql guide.
It actually has a chapter entitled "whats new"
that chapter actually lists what's new.

It is an amazing concept - I'll give you that - you might not be expecting that from documentation.

tell me - can your whiz bang language - on the same app server or whatever you use - run version 1 and version 2 of your application simultaneously without impacting the application, without any special setup? PLSQL can. It is called edition based redefinition

http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o10asktom-172777.html
http://www.oracle.com/technetwork/issue-archive/2010/10-mar/o20asktom-098897.html
http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30asktom-082672.html

but beyond that - there is just the plsql guide
http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e17126/whatsnew.htm#CJAEGHHH

that sort of lists some of the major things, then there is everything that is added to SQL that is part of plsql as well - but you consider sql to be a dead language (but you probably don't even know 10% of it)....

You have formed an opinion, not based on facts, have fun with that.


and did you get me that name of the computer that would really use 1 bit to store a boolean? I didn't see that in a followup as yet???? I'm waiting....

A different reader, February 24, 2011 - 8:55 am UTC

http://www.old-computers.com/museum/computer.asp?st=1&c=834

<sarcasm>
But i think it can only run Oracle v1.0 ...
</sarcasm>
Tom Kyte
February 24, 2011 - 12:48 pm UTC

laughed out loud at that ;)

there was no version 1.0 of Oracle - the numbering started at version 2 in 1979.

PLSQL ugly..

A reader, February 24, 2011 - 6:59 pm UTC

and one can write stored proc in Java now..why?

CONTINUE is really a brand New feature introduced so so early!!


anyway.. it's no surprise to defend something useless which helps pay your bills%%
Tom Kyte
February 24, 2011 - 7:14 pm UTC

One could have written a stored procedure in Java since oracle 8.1.5. One could have written a stored procedure in C since oracle 8.0

It is because - wait for it - there are uses for many languages, there is no one single "best language", they all excel in some way for some things. I've used so many different languages in my career - and they all did something well, all were suited for what they were designed to do - and they all stank when you tried to do something in them they were not well suited for - ok, well, except for C, it can do anything). Until you learn that, you'll not be very useful.

And frankly, if your goal is to process data, you would be stupid to ignore PL/SQL as the language. continue not withstanding. (In my personal and professional experience, the need to use continue in general means you have spaghetti code anyway - you have not been very modular in your coding - it ranks right up there with "goto").

If your only argument is "continue", stop it already, it is embarrassing - if you can put together a real argument - go for it. I'll gladly address your points - but you haven't really provided any.

It not only helps me pay the bills - it helps me do my job (and others) - which is processing data - strange as that is.

I'll still program circles around you when processing data - you never seemed to argue that. And that my friend - is what counts.


If you can do it 100% in SQL (which is probably older than you are - but that is not a bad thing) - do it.

If you can't - do it in as little plsql as possible (which is probably not older than you are since it dates from version 6 of Oracle - late 1980's - and has been greatly enhanced with truly useful things over the years - not this "continue" thing).

continue is the same as goto

Sokrates, February 25, 2011 - 2:05 am UTC


plsql and its beauty!

A reader, February 25, 2011 - 7:42 pm UTC

Java would have been obsolete long time ago had it adapted pl-sql type weirdo lingo ( =>, :=, BEGIN, END,....).

Any new language that doesn't have C type lingo will face challenges. Hence is the C and JAva support to code stored procs.

That said, one can't compare C/Java with PL-SQL. They are in totally different league. One generic and the other just to complement SQL. Yet, generic triumphs when it comes to succinctness.

One only wish the beautiful SQL been well complemented with a C type lang from the beginning than the wierdo plsql.
Tom Kyte
February 28, 2011 - 8:18 am UTC

Java would have been obsolete long time ago had it adapted pl-sql type weirdo
lingo ( =>, :=, BEGIN, END,....).


How so? Many languages have that "weirdo" syntax. And the fact that your only attack on the language is from "weirdo" perspectives just makes you look silly. Come on - give us something real and tangible other than "weirdo".


How's this: I think you are a weirdo - so therefore you must be. Hence, you are not relevant. If the weirdo factor works for you, it works for me.


I don't think you even know the origins of PLSQL. I don't think you even know what you can do with PLSQL. (PLSQL comes from another weirdo language - Ada)

PLSQL was used to write advanced replication in its entirety in 1994. Portions of it have since been internalized in the database kernel (which is written in C) - but the API to it is still that weirdo language.

PLSQL was used to write the core of Oracle applications - it still is there. There is probably as much PLSQL as Java and C and other languages put together for Oracle apps.

PLSQL is used to write in their entirety many websites. If you want to buy Oracle on the web - guess what language you'll be using to complete that purchase.


You are right in that you cannot compare C and Java with PLSQL - PLSQL is by far superior as far as dealing with data and processing data than either of those languages. And PLSQL is safe in the hands of many a programmer as it doesn't make it easy to make boneheaded mistakes like "not using bind variables" and the other first year programmer mistakes the "professional" java programmers make (I didn't put C there because in general, C programmers typically go out of their way to learn the tools they are using - must be something in the mindset of a C programmer I guess - to learn what the tools they are using can and cannot do and how best to use them).



Bottom line though - if you have to attack something via the "weirdo" principle - you are not going to get very far. It doesn't make you look smart.

I'm also an architect

Art Vandelay, February 26, 2011 - 9:31 pm UTC


To "A reader":

Java would have been obsolete long time ago had it adapted pl-sql type weirdo lingo ( =>, :=, BEGIN, END,....).
Any new language that doesn't have C type lingo will face challenges.


Don’t waste that crystal ball on Java, C and PL/SQL! Future lottery results, perhaps?

Hence is the C and JAva support to code stored procs.

Hence nothing. VW Group makes Lamborghini Gallardo and VW Polo. Hence what?

That said, one can't compare C/Java with PL-SQL. They are in totally different league. One generic and the other just to complement SQL.

Yes, one can! You did!
Next time you go for an interview tell them they should move to C or Java stored procedure because you can write a=a+1 rather than the a:=a+1 in PL/SQL.

Yet, generic triumphs when it comes to succinctness.

Yes. And pink triumphs when it comes to size.

But if you like succinct computer languages, here is one for your next "Hello World": http://tinyurl.com/4f6mt
One only wish the beautiful SQL been well complemented with a C type lang from the beginning than the wierdo plsql.

Pro*C, OCI perhaps? Knock yourself out.

Art.

"PLSQL was used to write advanced replication in its entirety in 1994"

Sokrates, February 28, 2011 - 8:55 am UTC

Forms 3.0 used PL/SQL already in 1992, as far as I can remember ?
Tom Kyte
February 28, 2011 - 11:17 am UTC

Forms 3.0 was written in C

Inside of forms - you could code using PLSQL as a language.

You could (and we did, Oracle apps) write applications using forms in PLSQL, but PLSQL was not used to write forms itself.

PLSQL debuted in Oracle Version 6.0. You could code anonymous blocks back then (no stored procedures yet, just anonymous blocks that would be sent to the server and compiled/executed there). Forms had a client side implementation of PLSQL as well as the ability to invoke the server side PLSQL.


Just a comment.

Shannon Severance, February 28, 2011 - 9:16 am UTC

"Next time you go for an interview tell them they should move to C or Java stored procedure because you can write a=a+1 rather than the a:=a+1 in PL/SQL."

I think what the troll is alluding to is using a++ instead of a := a + 1. Not that a++ would make up for all the additional work required to perform data heavy tasks in Java versus PL/SQL.


Tom Kyte
February 28, 2011 - 11:18 am UTC

And how many bugs have I seen in my lifetime by a Java programmer getting cute with the use of ++ or -- in an assignment ;)


A reader, February 28, 2011 - 9:33 am UTC

<quote>

I didn't put C there because in general, C programmers typically go out of their way to learn the tools they are using - must be something in the mindset of a C programmer I guess - to learn what the tools they are using can and cannot do and how best to use them

</quote>


Yes , Indeed .

I see the concept of "Database Independence " & "Old Vs New Programming languages " only from Java programmers .

Not so much from any other area ... May be there are in "Write once , Run Anywhere" fantasy land still .



Tom Kyte
February 28, 2011 - 11:22 am UTC

I live in the Write once, Run anywhere real world.


I write in PL/SQL. Available on pretty much every commercially available server out there - callable by pretty much every single language of any relevance.


You want portable?

Write in a stored procedure language (any stored procedure language for the database you use).

You'll not only be portable across operating systems (assuming you pick a database that runs on more than one platform)..

You'll be portable across all languages...

For a really long time, probably longer than the language that calls you is around....


You want reusable?

Write in a stored procedure language

You'll be reused forever across all operating systems

And middle tier "approaches" as they come (and go, and come and go and come and go)...

And reused across many different languages.





Write One, Run Anywhere, Run FROM Anywhere is possible. If you take the right aproach.

rubbish plsql

A reader, March 01, 2011 - 4:02 pm UTC

These rubbish and immature responses are expected if one has done only pl-sql whole lifetime and wrote some "Hello World" programs in other languages and claim (fake) expertise...

Just dream in your own little dirty world.. I don't think any current generation programmers would even smell here. Just wait for around 3 more years and bookmark this conversation for your own good to look back.
Tom Kyte
March 01, 2011 - 5:10 pm UTC

I don't think you read anything above.

But anyway, have a nice life. Enjoy your myopic view of the world.

Says a guy that was called the SAS Master in a previous life.
And lived on C coding for many years.
And was a pretty darn good PL/I (that is not plsql, that is PL/I - an IBM language)
And could do exec2 better than most
And really dug REXX
And wasn't too bad at Ada
And did this JCL thing inside out
And really dug prolog for a while
And got into this thing call SQL big time

(never liked perl, really didn't get into C++, ...)

All that was before getting really into PL/SQL - that didn't happen until halfway through my career.


If you point out rubbish or immature responses on my side, I'll be glad to address them more professionally.

You have yet to point out anything of any technical merit, not a single thing.


And as an Oracle employee or contractor yourself, you are really embarrassing - to yourself. If you don't like your current job - get another one. Don't sit there and complain ad nauseum. You must be really frustrated - so do something about it.


The demise of PL/SQL has been predicted for many years by various people. They for the most part aren't around anymore. And PL/SQL continues on. And probably will be for long after you are writing code. It is already 23 years old and shows no signs of disappearing.


And you'd be wrong about any "current generation". I meet them all of the time at conferences and seminars. There are still some current generation developers looking for how to do things the right away - believe it, or not.

Alexander, March 02, 2011 - 12:05 pm UTC

The plsql basher works for Oracle is that what you said? Well it's a big company I suppose :)

mycelo, March 03, 2011 - 9:10 am UTC

About 9 years ago, someone asked the wrong question, which generated this torrent of harsh emotions depicited in this very thread that really didn't add much to enlighten our lives as Oracle developers.

I would simply change the very first question:

Tom, why does Oracle PL/SQL have a boolean datatype?

I understand that the PL/SQL compiler/interpreter has to deal with boolean things when evaluating/parsing conditions and all (probably just numbers), but I guess giving the programmers the power of declaring their own fancy BOOL variables wasn't a smart move (unless there is a good technical reason for that - which I myself in all my humbleness just can't see).

My particular grip in this issue is:

SQL+>VAR bool_var BOOLEAN;
SQL+>EXEC some_proc(bool_out_param => :bool_var);

But I myself can live with that (as I can live with thousands illogical things logically implemented in our computers everyday).

Nevertheless if Oracle do allow BOOL columns then people will start demanding for big VARCHAR and PLS_INTEGER columns too (but in these cases I can see clearly why these datatypes exist in PL/SQL but not in SQL).

SQL

GOWRI, April 12, 2011 - 2:39 am UTC

WHAT IS A USE OF ECHO OFF IN ORACLE
Tom Kyte
April 13, 2011 - 9:11 am UTC

NOTHING - THE USE OF THE CAPSLOCK KEY HOWEVER....

In sqlplus, "set echo off" has meaning:

http://docs.oracle.com/docs/cd/E11882_01/server.112/e16604/ch_twelve040.htm#SQPUG075

Why I would find boolean useful

Galen Boyer, April 13, 2011 - 10:22 am UTC

Hi Tom,

You have asked for opinions of "What would a boolean do for
me". Let me give you mine.

When I want to mimic a boolean datatype I do exactly as you
say, varchar2(1) NOT NULL IN ('Y','N'). But, then, when I
want a colleague to also mimic a boolean and he is designing
his tables, he may not do exactly as I do, especially he
might not add the NOT NULL or the check constraint, or both.
So, then, we start having mismatches of what the full model
views as a "boolean". And, invariably, someone else codes
in (0,1). When months have gone by, the model now has
multiple ideas on what a "boolean" is. If there is one
single data-modeler for the life of the model, then, maybe,
the "boolean of varchar2(1) might be abided by.

It would have been nice if the database supported a
"boolean" datatype to begin with, and then, the suggestion
to others would have been very easily accepted as well as,
where they not to follow it, very easily rejected by the
team. Or, put another way, with a boolean datatype
available, the model would have a single way to represent a
boolean when it was modeled. All the 1000s of lines of code
that have those CASE and DECODES would be consistent. You
wouldn't always be checking if the "boolean" where NULL or
not. You wouldn't constantly be visiting a desc of the
table to see if the 'Y','N' constraint is on, or if the NOT
NULL constraint is on. You would know by the datatype that
TRUE/FALSE are the only things available.

Maybe an analogy would be, why does Oracle have an "integer"
datatype? Your argument against booleans translated on that
would sound like, "Why not just use number(38)? I don't see
the big win here". But, the integer "constraint" makes the
model more understandable when you see it in the model,
correct? Same argument for booleans.

On the PLSQL side, Just as you like to code "WHERE exists
(select NULL ...)" I think many like to code IF (some_bool).
Just says exactly what is meant to convey. IF (some_bool =
'Y') takes more mental parses. You have to say, oh, the
datatype is a boolean and 'Y' is TRUE. The previous one is,
by its construct, a boolean question. I find myself liking
the boolean enough that I will sometimes set booleans in my
PLSQL so that my IF blocks are driven by boolean logic.

Tom Kyte
April 13, 2011 - 11:03 am UTC

ut, then, when I
want a colleague to also mimic a boolean and he is designing
his tables, he may not do exactly as I do, especially he
might not add the NOT NULL or the check constraint, or both.


Missing not null - so what, that would happen with any SQL datatype. So we are down to "not doing it the same way" or "not having the check constraint".

That is a matter of standards and the same could happen anywhere. Suppose there was a boolean datatype, would this prevent the colleague from using char(1) not null in ('0','1') anymore or less?

So, then, we start having mismatches of what the full model
views as a "boolean".


And you would have the same issue with what the full models views as "X", you have this domain issue everywhere in your model, not just with a boolean.

When months have gone by, the model now has
multiple ideas on what a "boolean" is.


and what eye color is, and what zip code is, and what phone number is, and so on. Be complete :)


I myself would rather see DOMAINS implemented properly ;) that would solve the general problem you describe above (that is not limited to the concept of true/false)


ou
wouldn't always be checking if the "boolean" where NULL or
not.


really ??? why not? If it were nullable you best be looking about for it.

integer is there for ansi compliance basically, just like the DB2 datatypes and float and the like...


ops$tkyte%ORA11GR2> create or replace type bool as object
  2  ( val varchar2(1),
  3    static function true return bool,
  4    static function false return bool,
  5    constructor function bool( self in out nocopy bool ) return self as result,
  6    constructor function bool( self in out nocopy bool, val in varchar2 ) return self as result
  7  )
  8  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace type body bool
  2  as
  3          constructor function bool( self in out nocopy bool ) return self as result
  4          is
  5          begin
  6                  raise_application_error(-20001, 'should be Y or N' );
  7                  return;
  8          end;
  9  
 10          constructor function bool( self in out nocopy bool, val in varchar2 ) return self as result
 11          is
 12          begin
 13                  if upper(val) not in ('Y','N')
 14                  then
 15                          raise_application_error(-20001, 'should be Y or N' );
 16                  end if;
 17                  self.val := upper(val);
 18                  return;
 19          end;
 20  
 21          static function true return bool
 22          as
 23          begin
 24                  return bool('Y');
 25          end;
 26  
 27          static function false return bool
 28          as
 29          begin
 30                  return bool('N');
 31          end;
 32  end;
 33  /

Type body created.

ops$tkyte%ORA11GR2> create table t
  2  ( x int,
  3    y bool not null,
  4    z bool
  5  );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 1, bool.true(), bool.false() );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 1, bool('N'), bool.false() );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 1, bool('N'), bool('Y') );

1 row created.

ops$tkyte%ORA11GR2> desc t;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 Y                                        NOT NULL BOOL
 Z                                                 BOOL

ops$tkyte%ORA11GR2> select * from t where y = bool.true();

         X
----------
Y(VAL)
-------------------------------------------------------------------------------
Z(VAL)
-------------------------------------------------------------------------------
         1
BOOL('Y')
BOOL('N')


that example does suffer from some defects - in that VAL cannot be protected fully, it is exposed and could be modified directly

REGEXP_LIKE seems to benefit from an SQL boolean datatype

Jens Krogsbøll, May 13, 2011 - 4:44 am UTC

Hi Tom,

It seems to me that Oracle themselves found a need for the SQL boolean when they introduced the REGEXP_LIKE feature.

Otherwise we would have to write:

WHERE REGEXP_LIKE(sometext, somepattern) = 'Y'

Why not let the rest of us create our own boolean type SQL-able functions like the REGEXP_LIKE feature? I know REGEXP_LIKE is probably not really a function but rather an integrated part of the the SQL syntax - but still - it would be nice.

Br
Jens

George, June 29, 2011 - 1:03 am UTC

What really amazes me is appeal to ANSI SQL-89 standard as a reason of not implementing Boolean datatype. Especially with Oracle which has plenty of TIMESTAMPLTZ, INTERVALDS, NVARCHAR2, REF, RAW, CURSOR and other non-ANSI non-standard and of course non-SQL-89 standard datatypes.

I don't expect this comment to be published.
Tom Kyte
June 29, 2011 - 12:05 pm UTC

everything is published, why wouldn't it be? You see many comments that do not agree - that are critical. You don't need to try and bait me into publishing anything.

and if you read what I wrote, you would see that I wasn't using sql89 as a means of saying 'do not do boolean', I referenced sql89 in the context of someone saying 'compatibility'. My reference was "if you want to be cross database compatible, you best stick with sql89 datatypes - anything else - risky"

Add this one to the list

Kamal, August 24, 2011 - 3:46 pm UTC

Hi Tom,
I'm not sure if someone pointed this out already.
An obvious advantage of having a standardized BOOLEAN datatype would be when merging companies and their applications / databases, if the companies adopted two different "standards", e.g. one has 1/0 and the other has Y/N.
I'm sure it's not the biggest of their problems but it's very annoying having to use a different standard every time, depending on which table you're hitting...

Thanks!

--
Kamal

Rui, August 27, 2011 - 3:00 pm UTC

I'm not sure having boolean makes anything easier. My current project is on SQL Server which has booleans and I'm finding them to be a pain both in use and understanding the data model.

1. For reporting purposes we use the flags in calculations thus all of our views have to recast the boolean to an integer anyway {0|1}.

2. Are boolean types correctly being modeled? I'm working with a very poor design to begin with, thus our booleans fields are actually tristate as they accept nulls. So, not only are we converting booleans to int in our views, but we're also in most cases defining a default value for null. I find most booleans are really summary information about an entity that has or should have other attributes thus one should question if the field is correctly modeled? Should it be a type instead, etc? I can understand the use of boolean in some cases, i.e. don't care about details or in modeling a light switch. Thus, that begs the question does having a boolean in your schema mean that the entity is not fully modeled?

Andy, September 12, 2011 - 8:40 pm UTC

When dealing with SQL Server there only is a bit datatype. No boolean. Reporting services has a boolean which reads a bit 1/0 as Y/N. That lack of consistency between products causes confusion.

The nice thing about a bit type is the DBMS would store up to 8 of these in a single byte. When talking about tens of millions of rows, this can save a bit of space (now that space is expensive anymore).

Now with oracle and the suggestion to use:
flag char(1) check (flag in ( '1', '0' ))
**(in my project I'm porting a sql server warehouse to oracle so I'll be using the 1/0 instead of Y/N))
When applying this in a warehousing database, isn't adding all these checks putting overhead in where it would not be needed if there was a bit datatype?


Why no bit datatype ?

Kryptus, November 22, 2011 - 4:46 am UTC

Why should we use char(1) whith a check constraint. Why is there no "BIT" datatype which stores basically 0 or 1... because then i need to parse manually Y to True and N to False when I load my business objects which is annoying. It would be much better/easier to be able to cast or parse in a more straightforward manner.

Space for boolean

Allen, January 04, 2012 - 7:52 am UTC

Tom,
I'd like to take a different approach to boolean - space.
We have large tables where we just want to know if somehting was sold or used. The goal is to look at patterns.
The kicker is keeping the database fast and small. Small datafiles to reduce I/O & keep cache hits high.
SQL server 2008, R2 and Denali have this but a 11 meg table, data type bit either NULL or NOT NULL is stored as 31 meg. Bummer. PK index space of 4 meg included.
As one would think, table compression increased, doubled, the storage.
BTW both storage allotments confirmed by checking system table entries, size of the database and backup file.

I do not need to store 'Y','N' or True, False. My quest is putting 12 Tb of data in 1.5 Tb of space with 2x or 4x increase in speed.
(Note actual quest is 12 Tb in 20 meg of space for quick load high speed IMDB, but the boos would think I still have a New Year's hangover or need drug testing :)

I'm just looking for help on storing data. Any ideas or suggestions appreciated.

Allen

Boolean is SQL standard

Rafael DBA, January 23, 2012 - 8:26 am UTC

Hello.

Everybody here knows that SQL standard has been evolving since 89 and boolean datatype is covered since 99 and later for 03, 06, and 08 revision (three times confirmed)

i wonder How is possible that Oracle still refuses implement it?!
It is not only a question about if it is useful or not ... this means Oracle is only SQL89 compliant!! ... this is a 20 year old standard, three revisions old.

Does not seems very professional to me.

Regards,
Tom Kyte
January 23, 2012 - 11:24 am UTC

well, we were the last to test for sql compliance - no one has ever tested after us - and that was for sql 92 (and we passed). There is *no testing* for sql compliance anymore.

fact:

No one is fully sql compliant

opinion:

No one will probably ever be sql compliant

NIST doesn't check compliance anymore - stopped doing it years ago.

So, we are as compliant, if not more so, than everyone else out there - no worries there.

So, if I find something in the sql standard that a database you use doesn't do - it means they are unprofessional? interesting, that means there are no professional databases out there!

A defense of marketing FUD

jbo5112, April 22, 2013 - 5:57 pm UTC

Reads like marketing FUD that got caught and is making a lame defense of itself.

Comparison operators coming for free? Nope! The cost might be infinitesimal, but it does add overhead. Saying that having a check doesn't add overhead is as stupid as using a CHAR for a boolean. NUMBER(1) is much closer, can probably be used without a check constraint (0 is FALSE, everything else is TRUE), and is similar to many other computer language constructs.

"So, we are as compliant, if not more so, than everyone else out there."

While maintaining the recommendation of using VARCHAR2? No! Using VARCHAR2 over VARCHAR automatically disqualifies you from running the compliance race. I also kick you out again for not having INFORMATION_SCHEMA (for cross-platform data-dictionary based programming). The basics are most important, and your claim sounds to me like boasting of the greatness of a football player, when he has no feet. If I keep going, I may decide your goal is vendor lock-in, not standards compliance.

I hear your marketing FUD a lot, but never with anything to back it up. What is there that disqualifies the others, because with no Boolean support, that makes 3 VERY glaring problems?

My vote for compliance probably goes to PostgreSQL, which I have used, but is recommended by others for compliance (company statement below). Currently, I'm switching to MySQL, and so far, it's like going on a tropical vacation from the job of being a "helmet test dummy."

PostgreSQL: PostgreSQL prides itself in standards compliance. Its SQL implementation strongly conforms to the ANSI-SQL:2008 standard

P.S.
I'd probably have more to complain about if I could stand to read more of what you wrote. This defense of not supporting the SQL standard is terrible.
Tom Kyte
April 22, 2013 - 8:31 pm UTC

huh?

"I hear your marketing FUD a lot, but never with anything to back it up. "

where is the marketing fud? I just say what is, well, fact.


have you actually read the sql standard? have you? I mean - actually read it?

you do understand that NO DATABASE on the planet comes close to supporting anything near "it" right?


people come here to ask "how can I do something".
I tell them how to accomplish it.

please - tell me - where is marketing fud????


Booleans in relational algebra

Perry, June 30, 2013 - 6:19 am UTC

Oracle RDBMS/database is an implementation of relational algebra. A database table is a fair approximation of a relation.

A relation ("table") represents a "subject matter", and it is a collection of TRUE propositions ("rows") about that "subject matter". To indicate that a proposition is FALSE, you simply do not "insert" that proposition into the relation.

Therefore, Boolean values are REDUNDANT. It's because there are already Boolean propositions, and they are represented as rows in a table.

If you are thinking of, say, a table USER with the following fields:

USER (UserID, FirstName, LastName, IsEmployed);

then you should normalize your table as follows:

USER (UserID, FirstName, LastName);

-and-

EMPLOYED (UserID);

Boolean datatype IS needed

SQB, September 10, 2013 - 9:58 am UTC

> Boolean values are REDUNDANT. It's because there are already Boolean propositions, and they are represented as rows in a table.
>
> If you are thinking of, say, a table USER with the following fields:
>
> USER (UserID, FirstName, LastName, IsEmployed);
>
> then you should normalize your table as follows:
>
> USER (UserID, FirstName, LastName);
>
> -and-
>
> EMPLOYED (UserID);

I disagree. How would you represent someone who is unemployed? (As opposed to someone about whom we don't their employment status). In the first form, we can use T, F, or NULL. In your form, I don't see how we can show the distinction between those two.

Also, by the same logic, we should have

USER (UserID)

and

TOE (UserID)
with ten entries (for most users), instead of

USER (UserID, NumberOfToes)

---

But regarding the original question and Tom's answers, Tom, I disagree with you. The facts that different solutions ((1,0), ('T', 'F'), ('Y', 'N')) are equally valid and that wrapper functions are needed to translate from and to real booleans, all show that there is a need for a boolean datatype.

Of course, we can make do, but for that matter, we could make do with just an integer and nothing more.
Need a string? Store the ASCII or the UTF codes.
Need a decimal? Store the mantissa and the exponent. It would work, but it would be cumbersome.

The same goes for the boolean datatype. Sure, we can make do without it, but it would be easier and reduce errors to have it.
Tom Kyte
September 10, 2013 - 10:21 am UTC

... How would you represent someone who is unemployed? ....

using this model there would be another table - to represent that. T/F/Null would not be a correct way to represent this. You would be assigning meaning to a Null value. You might as well make this be a three valued column - EMPLOYED with Y=yes, employed, N=no, not employed, D=does not apply, doesn't want to be employed. Null would be wrong. Null would mean you don't know if they are employed, not employed or not wanting to be employed.





Employment

SQB, September 10, 2013 - 10:58 am UTC

> using this model there would be another table - to represent that. T/F/Null would not be a correct way to represent this. You would be assigning meaning to a Null value. You might as well make this be a three valued column - EMPLOYED with Y=yes, employed, N=no, not employed, D=does not apply, doesn't want to be employed. Null would be wrong. Null would mean you don't know if they are employed, not employed or not wanting to be employed.

I'm sorry, perhaps I didn't make myself clear, but I meant
'T'/1/'Y' (or actually TRUE, if we had a boolean datatype): employed
'F'/0/'N' (or actually FALSE, if we had a boolean datatype): unemployed
NULL (either case): unknown

Two tables (I assume you mean tables EMPLOYED(UserID) and UNEMPLOYED(UserID)) would be awful. Since a user cannot be both employed and unemployed, we'd need some sort of multi-table constraint to ensure a user has an entry in one of those tables at the most. I'm surprised you would suggest a solution like this.
Tom Kyte
September 10, 2013 - 11:15 am UTC

then you'd have a table employment status, with "employed" or "unemployed" as values. A lack of a row means "I don't know"

Thanks

SQB, September 10, 2013 - 11:32 am UTC

I still disagree, but thank you for your swift replies.

Lack of assertions influence designs...

Toon Koppelaars, September 11, 2013 - 8:07 am UTC

Not an awfull design imho. It's just that the concept of assertions isn't offered that makes the design somewhat labor-intensive, since you're forced to design and implement triggers to enforce the multi table constraint.

It would be far easier if we could just declare that constraint as follows:

create assertion not_in_both as check
(not exists
(select 'an element'
from (select userid from employed
intersect
select userid from unemployed)));

And have the Oracle kernel be responsible for enforcing it in an efficient manner.

David Aldridge, September 17, 2013 - 8:40 am UTC

This employment analogy is awful. In practice employment is a transient state that depends on the current time, so it's more meaningful to store adequate information on the periods of employment and infer the employment condition from them.

Anyway, tt does illustrate the point that Booleans tend to offer a very limited amount of information, and as a designer I generally try to look beyond the need for a boolean to find some meaningful piece of information from which the boolean condition can be inferred. For example a Boolean "HAS_COMPLETED_TASK" might be replaced with "DATE_OF_TASK_COMPLETION".

Very often that information needs to be added in anyway, and you can end up with redundant information.

I work with PostgreSQL at the moment, which does support the boolean data type. Its value is really as a convention so that there is no debate over whether to use 0/1, t/f, y/n.


I don't like date to represent a condition

Galen Boyer, September 17, 2013 - 5:23 pm UTC

Hi David,

What if one knows that the task is completed but is unsure of the
date? That would be a pretty common occurrence. Many times the Y/N
is the most important thing. When is not. And, that is sort of the
crux. I'd rather not overload an attribute. One is it has happened.
Another is when it happened.

Employment, booleans and Toon's enforcement

John, September 17, 2013 - 8:53 pm UTC

@Toon,

I surely agree with you and wish that by declarative means we could enforce cross-table data integrity.

Regarding the booleans, in general I stick to 0 and 1, because numbers are language independent. Y/N works in English, but fails in German (Ja und Nein) and differs again in Russian (Da). Booleans have nothing to do with language, so why inflict language on them?

For the employment issue, have two fields, constrained to have values of 0 or 1. One field is EMPLOYMENT_STATUS_UNKNOWN and the other is IS_EMPLOYED. Have a constraint which enforces the rule that both cannot be populated. If you know the employment status, then IS_EMPLOYED is either true or false, otherwise it's null and EMPLOYMENT_STATUS_UNKNOWN is true.

This sadly demonstrates the hoops one has to jump through to implement booleans. For Christmas I'd like Oracle to deliver booleans in SQL and not just PL/SQL.

David Aldridge, September 18, 2013 - 6:24 am UTC

Well, that's tricky of course. If that was a possibility then I'd think of adding an alternative -- a "consider_as_completed" boolean, preferably with some reason for not knowing the data.

Examples always raise awkward questions.

A different "boolean"

A reader, September 19, 2013 - 8:44 pm UTC

I too miss the boolean datatype in Oracle, and have struggled how to represent it. I don't particularly care for 0 and 1 particularly, and have used Y/N, T/F, even "-"/"+" but recently discovered a new tactic: Space (" ") and Splat ("*"). I really like this for a couple different reasons. Typically when we mean false, we think of a negative or absence and when we think of true, we think of a filled value. The space/splat works nicely here. Then, when looking at booleans in a table using this convention, it's very easy to spot true values * very quickly.

I stole this idea from the days of dBase, where deleted records were marked with a splat and normal records were marked with a space.

That said, I'd still like a real boolean represented in the database like PostgreSQL has.

Epicycles

SQB, December 02, 2013 - 2:34 pm UTC

> It would be far easier if we could just declare that constraint as follows:
>
> create assertion not_in_both as check
> (not exists
> (select 'an element'
> from (select userid from employed
> intersect
> select userid from unemployed)));
>
> And have the Oracle kernel be responsible for enforcing it in an efficient manner.

Which would be a nice feature, but still a bit more complicated than a boolean datatype.


Boolean data exists in the real world. We model the real world in the database. Therefore, the database should be able to model boolean data, and so it needs a boolean datatype.

I'm also for boolean type in Oracle

Hilarion, April 16, 2014 - 1:37 am UTC

My own opinion is that we do need the boolean data type. The fact is that I want the boolean data type. It seems that there are many others that share my opinion and want the type in Oracle DB and in my opinion they do give valid reasons. It'd be nice if Oracle really did consider implementing it and if decided not to, state it's reasons for such decision.

Yes, we can (and do) live without it, using other data types to represent boolean values, but we need it just as we need numerical data types to represent numerical values, date/time data types to represent date/time values, etc. As I remember, Tom, you were always advocating use of proper data type to represent values of specific types (eg. use "date" and not "varchar2" to store dates). I can't understand such aversion to introducing and using boolean data type for the very same reason (and all other valid reasons others gave here).

Yes, the SQL standards/specifications do not enforce this type implementation and their definition of boolean data type is probably shallow, but still it's defined there for a reason. If those standards are a problem, then Oracle corp. should do as it did before - ignore the standards (as - for example - with "varchar" data type) and/or do it better (as long as they document it well). They can even call it "boolean2" if they want.

Oracle could do it better by (beside introducing boolean "true" and "false" literals and a type limited to those true, false and NULL values) allowing use of boolean data type as logical expressions in SQL with all the same rules that do apply to the SQL logical expressions (and vice versa - allow logical expressions being used as boolean data type values). There should be no implicit conversions between boolean data type and other data types (but allow "decode" to be used with boolean type, which would allow easy conversion in both ways). Everything working more or less (preferably more) like the boolean variable type already works in PL/SQL (including NULL value being a representation of UNKNOWN boolean value).

The main problem I see with implementing boolean data type is that it does not present any value for Oracle corporation, as it values revenue over customer satisfaction. (This is my opinion.)


For all the others, that are forced to use other data types to represent boolean values, I recommend using "number(1)" with "0" representing "false" and "1" representing "true" (and a check constraint in place, that would enforce that rule). Why? Because using those you can quite easily represent some simple two-valued logic (does not work for three-valued logic) boolean operators in SQL search conditions with arithmetic operators like this:
Instead boolean "X and Y" you'd write "X * Y > 0".
Instead boolean "X or Y" you'd write "X + Y > 0".
For more complex cases you can either "translate" parts or - using some thinking (especially for complex cases) - use arithmetic for whole expressions. E.g.:
Instead boolean "X and Y or Z and Q" you'd write "X * Y > 0 or Z * Q > 0" or "X * Y + Z * Q > 0".
Instead boolean "(X or Y) and (Z or Q)" you'd write "X + Y > 0 and Z + Q > 0" or "(X + Y) * (Z + Q) > 0".
The "not" operator is a bit harder to do. You can use "complement one" technique which would translate "not X" to "1 - X" but it fails for negating more complex expressions (involving "or" operator). E.g. "not (X or Y) or Z" can't be simply translated to "1 - (X + Y) + Z > 0" as it should be true for X, Y and Z being true and it isn't (as "1 - (1 + 1) + 1 = 1 - 2 + 1 = -1 + 1 = 0", which is not greater than zero). It has to stay as more complex "1 - (X + Y) > 0 or Z > 0" or (using logical transformations to change "or" to "and") as "(1 - X) * (1 - Y) + Z > 0".

Data vs. Data Interpretation

Dumitru UNGUREANU, May 27, 2014 - 8:20 pm UTC

Boolean is no use in SQL, table should contain data, not data interpretation.

You don't store 'emp is terminated': TRUE or FALSE, you store 'emp termination date': DATE.


Boolean is useful in PL/SQL, it's data interpretation.

You interpret 'if em termination date is not null then emp is terminated'.


I guess Oracle got it right and maybe you're all trying to push a little too much business logic into your models?

And most of all, I could be completely wrong about what you're trying to do :)

NULLs in Boolean columns

Kurt Schmidt, November 18, 2014 - 9:47 pm UTC

I firmly believe that "NULL" means "NO DATA." Since tables store facts, we need NULL to express we do not know what is the fact. Thus I believe that true and false equivalents in a column accepting NULLs do not make the column trivariate. I believe that is a binary column that may have cases where we do not know the value.
As to 1/0, 0/1, Y/N, T/F, I am concerned that in English we commonly say "yes"/"no" and "one/zero." This atends to assign value of "yes" to "1" and value of "0" to "false."Yet in a number of COMPUTER languages "0" meant "no error value" and any other VALUE meant "error occur and here is the error code." Thus I believe that somewhere in the database there should be a reference table that intentionally and explicitly equates 0 or 1 to Y, Yes, T and True, and the other number to the opposite. Relational and done. Clear. Supports foreign languages, too.
My 3 cents.
As halfway tongue-in-cheek opinion, the only good reason I ever heard for using numeric 0 and 1 was because it saved typing the two quotes and prevented errors like

If @N = ' T'

It actually sounds right to me. ;-)

Relate!
Kurt

BOOLEAN should be available in Oracle SQL

Sabine Heimsath, March 27, 2016 - 1:46 pm UTC

Of course we can "work around" the missing datatype, but there are too many different workarounds which cleary show there is a need for ONE implementation.

If you agree that Oracle should offer a Boolean Datatype in SQL you can vote for it here:

https://community.oracle.com/ideas/2633
(The only thing you need is a free OTN account)

Developer's perspective

Cam Hyde, August 21, 2021 - 7:17 am UTC

Probably said already; being able to evaluate a column value as true or false is useful. without using DECODE i mean

23c

A reader, November 13, 2022 - 7:36 am UTC

Coming in 23c

20 years later...

DJ, July 25, 2023 - 4:30 pm UTC

So now that this debate has gone on for 20 years, and we now actually have the boolean datatype in 23c, I would love to hear ...
1) Tom's thoughts about this
2) Any details about how it has actually been implemented (e.g. is it just a syntax sugar, or some other wrapper, versus a native/first-class construct?)
Connor McDonald
August 04, 2023 - 5:40 am UTC

(1) Tom's retired so that wont happen :-)

(2) Its a complete implementation. Some details here



Internally a single byte (containing 1 or 0)

Chuck Jolley, August 04, 2023 - 8:06 pm UTC

Is there a to_char() or to_number() mask for booleans?
( I don't have a 19c to try it on)
Chris Saxon
August 08, 2023 - 1:47 pm UTC

SQL Boolean is in 23c ;) Get it at https://www.oracle.com/23cfree/

There are no format masks for it; TO_CHAR returns TRUE/FALSE, TO_NUMBER returns 1/0.