Oracle Tip: Choosing an efficient design for Boolean column values
Takeaway: When designing a database table structure, it's important to choose an efficient strategy for storing a logical Boolean that you can use in many programming environments. Find out how from this Oracle expert.
When designing a database table structure, it's important to choose an efficient strategy for storing a logical Boolean that you can use in many programming environments. (Although Oracle doesn't come with a Boolean datatype for database columns, it does have a Boolean datatype in PL/SQL.)Any Boolean-defined column should also be properly constrained by checks to make sure that only valid values are entered at insert/update time.
create table tbool (bool char check (bool in
('N','Y'));
insert into tbool values ('N');
insert into tbool values ('Y');
The most commonly seen design is to imitate the many Boolean-like flags that Oracle's data dictionary views use, selecting 'Y' for true and 'N' for false. However, to interact correctly with host environments, such as JDBC, OCCI, and other programming environments, it's better to select 0 for false and 1 for true so it can work correctly with the getBoolean and setBoolean functions.
We could define a Boolean as NUMBER(1); however, in Oracle's internal number format, 0 takes 1 byte and 1 takes 2 bytes after the length byte (so it's more efficient to store it as CHAR). Even though the character is defined as CHAR, SQL can convert and verify against actual numbers.
create table tbool (bool char check (bool in
(0,1));
insert into tbool values(0);
insert into tbool values(1);
Here is a Java example:
import java.sql.*;
public class bool
{
public static void main(String[] args)
throws SQLException
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch
(ClassNotFoundException e)
{
System.out.println("error:
driver not in CLASSPATH");
return;
}
Connection conn =
DriverManager.getConnection(
"jdbc:oracle:oci8:@","scott","tiger");
Statement stmt =
conn.createStatement();
ResultSet rset =
stmt.executeQuery("select bool from tbool");
Boolean
bool;
while
(rset.next())
{
if
(rset.getBoolean(1))
System.out.println("bool is true");
else
System.out.println("bool is false");
}
rset.close();
stmt.close();
conn.close();
}
}
Also, in OCI, OCCI, and PRO/C, if the selected value is requested as an integer (SQLT_INT or OCCIINT), it will automatically convert into binary 0 or 1 by the client-side libraries, which can be used as native Boolean values.
Here is the same sample in OCCI:
#include <string>
#include <iostream>
#include <occi.h>
using namespace oracle::occi;
using namespace std;
int main(int argc,char* argv[])
{
bool b;
Environment* env =
Environment::createEnvironment();
try
{
Connection* conn =
env->createConnection("scott","tiger");
Statement* stmt =
conn->createStatement("select bool from tbool");
ResultSet* rset =
stmt->executeQuery();
rset->setDataBuffer(1,&b,OCCIINT,sizeof(bool));
while
(rset->next())
{
if
(b) cout << "bool was true" << endl;
else
cout << "bool was false" << endl;
}
stmt->closeResultSet(rset);
conn->terminateStatement(stmt);
env->terminateConnection(conn);
}
catch (SQLException e)
{
cout <<
e.what() << endl;
}
Environment::terminateEnvironment(env);
return 0;
}
By using setDataBuffer with a C++ bool value, the correct integer value gets bound to a C++ bool. Unfortunately, there's no getBoolean in OCCI. Therefore, it may be more portable to use an int or char, or use rset->getInt(1) instead of binding. (Note: In my tests, there is apparently a bug in OCCI where using getInt(1) on a CHAR column failed unless I used to_number(bool) or bool+0.)
When creating a Boolean data column, you should be careful to make sure that the column is properly "nullable." If a column with two possible values isn't constrained with NOT NULL, then you're allowing three possible values: true, false, and unknown. This is often not what is intended and host languages environments must deal with the possibility that NULL is returned. Either 2-value or 3-value may be acceptable in certain circumstances. This SQL restricts a BOOLEAN value to 2-values only:
create table tbool (bool char not null check (bool in (0,1));
However, Oracle SQL still requires a condition operator, so there's no way to get around testing for the actual value being 1 or 0, although you can hide these values in a standardization package. For instance, see how I can reuse/expose the keywords true and false through a PL/SQL package:
create or replace package bool
as
subtype bool is char;
function false return bool;
function true return bool;
function val(b bool) return
varchar2;
end bool;
/
show error
create or replace package body bool
as
function false return bool
is
begin
return 0;
end false;
--
function true return bool
is
begin
return 1;
end true;
--
function val(b bool) return varchar2
is
begin
if b = true
then
return
'true';
end if;
return
'false';
end val;
end bool;
/
show error
insert into tbool values(bool.false);
insert into tbool values(bool.true);
select bool.val(bool) from tbool where bool = bool.true;
TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!
White Papers, Webcasts, and Downloads
- Five Steps to Determine When to Virtualize YourServers VMware Thinking of virtualizing the servers at your company? Use this step-by-step guide to determine when's the best time to make your big move. Download Now
- Why Isn't Server Virtualization Saving Us More? A Few Small Changes May Dramatically Increase Your Efficiency VMware Ever wonder why your company isn't saving more from its server virtualization? Making a few small changes could dramatically increase your efficiency. Download Now
- The Impact of Virtualization Software on Operating Environments VMware Today's use of virtualization technology allows IT professionals to ... Download Now
- Building the Virtualized Enterprise with VMware Iinfrastructure VMware VMware virtualization software has been adopted by over 120,000 enterprise ... Download Now
- Building the Virtualized Enterprise with VMware Infrastructure VMware This paper explains how adopting a virtual infrastructure -- comprised of server, storage, and networking virtualization technologies -- can help your organization build a sustainable competitive ... Download Now
Article Categories
- Security
- Security Solutions, IT Locksmith
- Networking and Communications
- E-mail Administration NetNote, Cisco Routers and Switches
- CIO and IT Management
- Project Management, CIO Issues, Strategies that Scale
- Desktops, Laptops & OS
- Windows 2000 Professional, Microsoft Word, Microsoft Excel, Microsoft Access, Windows XP,
- Data Management
- Oracle, SQL Server
- Servers
- Windows NT, Linux NetNote, Windows Server 2003
- Career Development
- Geek Trivia
- Software/Web Development
- Web Development Zone, Visual Basic, .NET
