Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 30th, 2003, 09:12 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Oracle boolean datatype??

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
Reply With Quote
  #2 (permalink)  
Old October 30th, 2003, 10:09 AM
joefawcett's Avatar
Wrox Author
Points: 9,716, Level: 42
Points: 9,716, Level: 42 Points: 9,716, Level: 42 Points: 9,716, Level: 42
Activity: 9%
Activity: 9% Activity: 9% Activity: 9%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,067
Thanks: 1
Thanked 37 Times in 36 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old October 30th, 2003, 10:59 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Joe, I'll try it out!

Pieter
Reply With Quote
  #4 (permalink)  
Old October 30th, 2003, 11:09 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
Reply With Quote
  #5 (permalink)  
Old October 30th, 2003, 11:29 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old October 30th, 2003, 11:38 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
Reply With Quote
  #7 (permalink)  
Old November 17th, 2003, 12:05 PM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sal
 Oracle 9i has a boolean data type. You can set it to TRUE FALSE or NULL.
Oracle 8i does not have Boolean.

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.

Reply With Quote
  #8 (permalink)  
Old November 18th, 2003, 02:03 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

It is a Number type, not string.



Sal
Reply With Quote
  #9 (permalink)  
Old November 19th, 2003, 10:07 AM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sal
 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'








Reply With Quote
  #10 (permalink)  
Old November 19th, 2003, 09:22 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Oracle to SQL Server DataType Mapping maulik33 SQL Language 0 November 1st, 2007 02:48 PM
help me with boolean array Un33k Pro Visual Basic 2005 1 June 12th, 2007 11:43 AM
boolean starlightt Java Basics 2 April 6th, 2006 05:34 PM
Oracle Boolean Datatype Fireball Oracle 1 April 26th, 2004 05:46 PM



All times are GMT -4. The time now is 05:18 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.