Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Using bits to store information in the database.

Re: Using bits to store information in the database.

From: Jared Still <>
Date: Wed, 07 May 2003 10:37:30 -0800
Message-ID: <>

>From a database maintenance and code maintenance perspective,
this is a bad idea.

If comes under the heading of 'clever code' that a developer can look at and say "Ain't I clever". I've done it, we've probably all done it at some time.

It was a bad idea when I did it, and it remains a bad idea. Do it and you will regret it as long as you have to work with the system.

Do yourself a favor and properly model this, then build a database that gives you flexibility and no limitations.


On Wednesday 07 May 2003 09:32, laura pena wrote:
> The reason to use bit flags suggested by developers ... are that we can
> represent multiple check options.In my example verified and confirmed
> could be check.So if verified was 1 and confirmed was 01 then the following
> bits would be set 11. I see both advantages and disadvantages to this. The
> alternative approach would be to use varchar2(1) and have columns represent
> each.I heard 32 entries would be max with this approach. If we used a long
> 64 bits but that seems like a lot of wasted space. Fom the developers view
> point they can reference a database column once and perfom the bit
> translation on the client side. I am wondering if alternatively I could
> create a stored procedure to do the samething. Seems like an interesting
> design decision. -Lizz
> Jonathan Gennick <> wrote:Wednesday, May 7, 2003,
> 10:22:06 AM, you wrote: lp> Looking for advise on storing bits in the
> Oracle
> lp> database column.The scenario goes like this: define
> lp> column statusFlag NUMBER(x)statusFlag can represent the
> lp> following: verified set to 1nonverified set to
> lp> 10confirmed set to 100non confirmed set to 1000audited
> lp> set to 10000 I believe this gets my point accross.... So
> lp> here are my questions: What is the largest amount of bit
> lp> I can set for a NUMBER(x)?
> When you go from 1 to 10 to 100 to 1000, you aren't working
> your way up in terms of bits, but rather in terms of decimal
> digits. NUMBER columns are stored in some sort of decimal
> format. I believe the largest possible NUMBER is NUMBER(31),
> but check the manuals to be sure. That would give you 31
> positions: 1, 10, 100, 1000, etc.
> What is the reason for going down this path? Have you
> considered alternatives? You might find it easier to us a
> CHAR(31) column, in which case you could use SUBSTR to
> address each, individual flag. For example:
> FROM my_table
> WHERE SUBSTR(x,23)='T';
> I suppose you could even create function-based indexes to
> help such queries.
> If you really want to work in terms of bits, then you need
> to think in terms of 1, 2, 4, 8, etc.
> Best regards,
> Jonathan Gennick --- Brighten the corner where you are
> * 906.387.1698 *
> Join the Oracle-article list and receive one
> article on Oracle technologies per month by
> email. To join, visit
>, or send email to
> and
> include the word "subscribe" in either the subject or body.
> How can one set and retrive the information in Oracle based on bits being
> set? Are there any draw backs from using too large of a lp> NUMBER? I can
> guess one.. wasted space. Thanks in advanced,-Lizz
> lp> ---------------------------------
> lp> Do you Yahoo!?
> lp> The New Yahoo! Search - Faster. Easier. Bingo.

Content-Type: text/html; charset="us-ascii"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Please see the official ORACLE-L FAQ:
Author: Jared Still

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed May 07 2003 - 13:37:30 CDT

Original text of this message