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: Jonathan Gennick <>
Date: Wed, 07 May 2003 07:26:57 -0800
Message-ID: <>

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

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.


Please see the official ORACLE-L FAQ:

Author: Jonathan Gennick

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 - 10:26:57 CDT

Original text of this message