Re: Is there a bitfield type field in oracle?

From: malcolm <>
Date: Wed, 30 Jan 2008 09:51:48 -0800 (PST)
Message-ID: <>

On Jan 30, 3:50 pm, dean <> wrote:
> Hello all,
> Oracle 10g.
> I have a general question here on how best to implement this: We have
> a table called TRAIN that holds around 10K records of railroad data. A
> client of ours wants to be able add a field that specifies which of
> the 365 days of a year the train runs.
> I can think of a few ways of doing this:
> 1) Add 365 fields of varchar2(1), each of which holds a 'Y' or a 'N'
> entry to state whether the train runs that day.
> 2) Add a foreign key to an external table that holds unique date
> ranges.
> 3) Is there a bitmap-kind of field that could hold one entry in a bit
> of an integer type. Such things are available to software developers
> who need to compress the data as much as possible.
> I'd appreciate a pointer to the right area here, if (3) is available.
> Thanks
> Dean

I was going to write how this would not work, because although there are bit manipulation functions in PL/SQL you would need a 365 bit number (366 for leap years), and I thought this would be out of range.

But then I tried it, and it worked:

SQL> select bitand(power(2, 366), 1) from dual;



Well, no exception anyway... But the answer is wrong. It should be 0. Weird. I chalk this up to a floating point inaccuracy.

But anyway, if I were designing this, I would have another table containing a foreign key back to the train table, with a row for each days of the years that the train runs. Received on Wed Jan 30 2008 - 11:51:48 CST

Original text of this message