I'm completely new to Oracle and I want to define a column with a boolean datatype, however I can't find a suitable datatype to represent a boolean.

Which datatype can I use?

thx,
Pieter

The common solution is to use number(1) and put a constraint on the column to only accept zero or one or whatever values would suit you, some people prefer zero and minus one.

--

Joe

Thanks Joe, I'll try it out!

Pieter

Oracle 9i has a boolean data type. You can set it to TRUE FALSE or NULL.
Oracle 8i does not have Boolean.



Sal

Hmm, strange ... I installed Oracle 9i and I can't seem to find a boolean datatype. Are there any installation options you need to check to have this datatype.

Pieter

I think it can only be used with PL/SQL.



Sal

I have a little problem with Booleans, our application has an OLD function that has a boolean as an IN parameter. This function worked in Oracle 7, 8, and 8i. Since upgrading to 9i, it fails.

Actually, I dont know if it really worked in 8/8i. I would always pass a NULL into the function instead of TRUE or FALSE. But now that doesnt work.

My question is, can you really have a boolean used like this? How would I pass a TRUE or FALSE to the function? Strings dont work.

note: this function is used in reports 3.0

Thanks for any help/insight.

I believe that in Oracle booleans, 0 = False, Anything other than Zero = True, Null is null.

It is a Number type, not string.



Sal

here's the spec
function GetPhoneString (OwnerEID IN number, --EID of person or company
        TypeCode IN varchar2, --Address type code
        DisplayType IN boolean := TRUE ) --"true" also returns the description of the phone type


And I would call it like this (in 8i)
standardtasks.GetPhoneString(sometable.owner_eid,' BUSINESS',null)(and it would return the phone number)

And apparently, that 'null' was like just like false because it did not return the type description. Which is what I wanted.


Before now, I never questioned the boolean parameter, because it was before my time, and it worked, until 9i.

Now when I call it with DisplayType = 0,1,NULL, TRUE, or FALSE I get this error:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETPHONESTRING'

Hi,

I am not sure how function calls work in Reports 3.0, but boolean datatype is supported in PL/SQL (atleast in Oracle 8i and 9i). From the Oracle documentation, the restriction is:
<snip>
Oracle SQL does not support calling of functions with
boolean parameters or returns.
</snip>
Also, columns with boolean datatypes are not allowed.

Boolean is different from the numbers 0, 1 etc. Look at the execution sequence below:

----------------------------------------------------------------
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production

SQL> create or replace function GetPhoneString(
  2 OwnerEID IN number,
  3 TypeCode IN varchar2,
  4 DisplayType IN boolean := TRUE )
  5 return varchar2
  6 as
  7 begin
  8 if ( DisplayType is null ) then
  9 return 'DisplayType IS NULL. OwnerEID = '||OwnerEID||' TypeCode = '||TypeCode
 10 elsif ( DisplayType ) then
 11 return 'DisplayType IS TRUE. OwnerEID = '||OwnerEID||' TypeCode = '||TypeCode
 12 else
 13 return 'DisplayType IS FALSE. OwnerEID = '||OwnerEID||' TypeCode = '||TypeCod
 14 end if;
 15 exception
 16 when others then
 17 return 'Some problem occured';
 18 end;
 19 /

Function created.

SQL> var v_phonestring varchar2(100)
SQL>
SQL> exec :v_phonestring := GetPhoneString(100, 'abcd', true);

PL/SQL procedure successfully completed.

SQL> print v_phonestring

V_PHONESTRING
---------------------------------------------------------------
DisplayType IS TRUE. OwnerEID = 100 TypeCode = abcd

SQL> exec :v_phonestring := GetPhoneString(100, 'abcd', false);

PL/SQL procedure successfully completed.

SQL> print v_phonestring

V_PHONESTRING
---------------------------------------------------------------
DisplayType IS FALSE. OwnerEID = 100 TypeCode = abcd

SQL> exec :v_phonestring := GetPhoneString(100, 'abcd', null);

PL/SQL procedure successfully completed.

SQL> print v_phonestring

V_PHONESTRING
---------------------------------------------------------------
DisplayType IS NULL. OwnerEID = 100 TypeCode = abcd

SQL> exec :v_phonestring := GetPhoneString(100, 'abcd');

PL/SQL procedure successfully completed.

SQL> print v_phonestring

V_PHONESTRING
---------------------------------------------------------------
DisplayType IS TRUE. OwnerEID = 100 TypeCode = abcd

SQL>

SQL> exec :v_phonestring := GetPhoneString(100, 'abcd', 1);
BEGIN :v_phonestring := GetPhoneString(100, 'abcd', 1); END;

                        *
ERROR at line 1:
ORA-06550: line 1, column 25:
PLS-00306: wrong number or types of arguments in call to 'GETPHONESTRING'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> exec :v_phonestring := GetPhoneString(100, 'abcd', 0);
BEGIN :v_phonestring := GetPhoneString(100, 'abcd', 0); END;

                        *
ERROR at line 1:
ORA-06550: line 1, column 25:
PLS-00306: wrong number or types of arguments in call to 'GETPHONESTRING'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>
------------------------------------------------------------------

It works as expected, in 8i as well. However, the following function call from an SQL statement won't work either in 8i or 9i.

------------------------------------------------------------------
SQL> select GetPhoneString(100, 'abcd', true) from dual;
select GetPhoneString(100, 'abcd', true) from dual
                                   *
ERROR at line 1:
ORA-00904: invalid column name


SQL>
------------------------------------------------------------------

Hope that helps.

Cheers,
Prat