| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Using bits to store information in the database.
Lizz,
makes sense except for one thing. how are you going to produce reports? most 3rd party reporting tools do not deal with "bit logic" when it comes to generating reports.
I think you are making a mistake here. developers love to work the magic and store things in secret code. but then they forget about the poor end-user who has to generate a report of everyone who was "verified".
I think this is wrong.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, May 07, 2003 12:32 PM
To: Multiple recipients of list ORACLE-L
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 <jonathan_at_gennick.com> 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 rea! son 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:
SELECT *
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 http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit
http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to Oracle-article-request_at_gennick.com and
include the word "subscribe" in either the subject or body.
How can one set and retrive the information in Oracle based on b! its 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> ---------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Gennick
INET: jonathan_at_gennick.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
Do you Yahoo!?
The New <http://us.rd.yahoo.com/search/mailsig/*http://search.yahoo.com>
Yahoo! Search - Faster. Easier. Bingo.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (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 - 12:47:32 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |