Scheduling Info In Repository for Monthly Frequency

security Supervisor (4.x - 6.x)
1

The query that is part of this discussion (https://bobj-board.org/t/21519) is showing that a monthly scheduled document is running as:

MONTHLY on 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,LAST

The developer who scheduled the document says it is in fact only running on the 1st of the month. It appears the SQL is reportig the inverse of what has actually been scheduled.

The DAY_TIMING info for that scheduled document is 2147483648

Since I don’t have the specs that were used to develop the query, can someone confirm that it is working correctly for Monthly reports ?

Thanks.

tscoccol :us: (BOB member since 2003-11-15)

2

When I run the following query (Oracle 8.1.7) :

select bitand(power(2,31),2147483648)+0 from dual

I get 2147483647

Shouldn’t I be getting 2147483648 back ?

Power(2,31)is equivalent to 2147483648. A bitwise AND of the same number should always produce that number, right (X AND X = X) ?

I’m still trying to figure out why the query that Digpen posted for us doesn’t parse the Monthly scheduled documents correctly.

tscoccol :us: (BOB member since 2003-11-15)

3

Ummm… poor documentation? :smiley:

I only tested about 30 scenarios since I created this on a test oracle repository while on vacation, not while I was in the office. I was going with what is listed in the 5.x Repository guide, though that information was wrong in many of the cases.

Let me know what’s not working and I’ll look into it.
According to my calculator (and Excel), 2^31 = 2147483648

The Bitand should just return the value of the expression. You will have to DECODE or use a CASE to compare it with the actual value you want to test against. That’s why I use the same “power” statement in the bitand and the comparison.

I tested against Personal Oracle 9, so I don’t know if Oracle 8 returns different values. BTW… My code reads:


decode(bitand(DAY_TIMING,power(2,31)),power(2,31),'1,',null) ||

That means that 2^31 reads that the report is scheduled for the FIRST of the month, not the last. Isn’t that what is happening? As I said, the Repository breakdown is incorrect in this instance. If you are getting everything BUT the first, that means that you are getting 2^30 + 2^29 + 2^ 28 … + 2^1, or all values other than the last BITAND turned on, and the SQL is showing correct. (All those values when added, return: 2147483647… the number you say Oracle is returning). My guess is that this version of Oracle calculates powers incorrectly, or that your code is incorrect.

try:
select power(2,31) from dual
-or-
select decode( 2147483648, power(2,31)), power(2,31), ‘Yep’, ‘Somethings Amiss’) from dual

digpen :us: (BOB member since 2002-08-15)

4

Hey Digpen, I think your SQL is working ok. The probably seems to be that the BITAND function on 8i has a bug where it always returns true for any bit position if bit 31 is set. According to the person who told me, this is bug 1355836. This does not affect Oracle 9i.

I haven’t been able to find this bug on the Oracle website and I don’t have a login to the Metalink website either, but I’m looking into whether there is a workaround.

I thought about shifting DAY_TIME up by a factor of two and then back down by a factor of two to unset bit 31, but since DAY_TIME is not an actual bitfield and is defined as NUMBER(10), that doesn’t work. If I could somehow constrain the DAY_TIME value to a four byte datatype, I think my theory might work.

The other possible solution for us is to not include the 1st when we schedule our monthly documents. The 31st bit would not be set and would therefore not affect the BITAND function.

Sorry for making you doubt your fine work ! :smiley: :smiley:

tscoccol :us: (BOB member since 2003-11-15)

5

Ummm… Well, if it’s just a problem with the single BITAND and 32-bit values, try something like:


decode( sign(DAY_TIMING - 2147483648), -1, null, '1,')

That means, if you subtract out the value, and it returns a negative number, chances are the bit is not set, otherwise it is.

digpen :us: (BOB member since 2002-08-15)

6

Yeah, that looks promising. I think I’ll have to embed that into the CASE statement you already gave us. I’ll post whatever I come up with.

Thanks.

tscoccol :us: (BOB member since 2003-11-15)

7

Digpen, I came up with the following code to parse the MONTHLY scheduled documents which seems to work.

If anyone has a Oracle based repository that is at version 8i, feel free to use.

{moderator edit to disable smilies that were appearing in the code … dwayne}

tscoccol :us: (BOB member since 2003-11-15)