Saturday, July 23, 2005

Oracle BOOLEAN

There is no BOOLEAN datatype in Oracle, as far as tables are concerned.

CREATE TABLE BooleanTable (MyBool BOOLEAN);

ORA-00902: invalid datatype

But there is a BOOLEAN datatype in PL/SQL.

CREATE OR REPLACE PROCEDURE BoolProc (in_bool IN BOOLEAN)
AS
my_bool BOOLEAN := TRUE;
BEGIN
IF (in_bool = my_bool) THEN
DBMS_OUTPUT.PUT_LINE('True');
ELSE
DBMS_OUTPUT.PUT_LINE('False or NULL');
END IF;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END BoolProc;

Why is there no Boolean in Oracle for tables? What should we do instead? This:

CREATE TABLE BoolTable (MyBool CHAR(1) CHECK (MyBool IN ( 'Y', 'N' )));

As for the first question, as usual, let's Ask Tom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:2109566621053828525::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:6263249199595

Comments:
thanks for your explanation about boolean datatype in oracle that was useful for me
 
Yeah definitely Useful for me too
 
ya really
 
it is realy good, but how will we call it
 
Gubble-do-gock.

Furthermore the asktom link is invalid.
 
Link still works for me.

And if you think it's gobbledy-gook, I'd suggest just running the provided examples. That's what they're for. That last one essentially creates a table with a single value that is constrained to being either 'Y' or 'N' - functionally equivalent to a Boolean.
 
I see your "work around".
Nice, but does it support
all of the logical function
Y + Y = Y;
Y + N = N;
etc.

I don't know how to give feedback
to Oracle. But if I could, I
would tell them it is about time
they put aside the "purest attitude" or whatever reason they have for not implementing boolean data type in there data base, and just do it (I mean that it should come working streight out of the box not as a package add-on). It may bring "closure" to an annoyance, that should have been "fixed", and not just circumvented, alone time ago.

Don't get me wrong, I like oracle. I work with it every day. I have not found a better DBMS. But it is fustrating in this area of consistent data types (db and pl/sql).

Thanks for the opportunity to let it out!!!
 
Good stuff!
 
thank you.. i appreciate that..
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?