BITAND with large integers

I have a problem relating to BITAND in Oracle Server 8.7.1. The context of the problem is that I use the BITAND function in SQL for filtering data in queries of large tables in a data warehouse environment. The performance of the queries is high priority.

I have written a PL/SQL module that relies on the ability of BITAND to accept arguments as described in the Oracle 8.7.1 SQL Reference. In the section on BITAND the reference manual states that the arguments must resolve to non-negative integers. The arguments I pass are non-negative integers of the PL/SQL data type INTEGER, INT or SMALLINT and range in size up to a maximum precision of 38 decimal places.

However, BITAND appears to return incorrect results when for arguments are greater than 2^31. No error condition occurs. Just wrong results. I assume this is because BITAND is limited to accept arguments of the type BINARY_INTEGER, although the Oracle SQL Reference document does not appear to state this limitation.

I am aware that I could write my own version of BITAND as a stored function to handle larger integers. However, because the performance of the SQL is critical I am asking if anyone can suggest an efficient way to do this, or a better solution.

Any advice would be appreciated.

Matt Received on Sun Dec 14 2003 - 19:37:05 CST