Deciphering user bits from the Oracle archive

[aorange]

Hi guys,

We have set up a number of reports using Business Objects to gather data quality information directly from our Oracle archives. Whilst this is working perfectly for values, time stamps, etc, I cannot seem to find the user bits in the archives. The help file states that "Archiving saves the original value, source time and all status bits of a variable".

There is a column in the archive groups created by PVSS named STATUS of datatype NUMBER(20), I suspect that this may hold both the status and user bits but I am not 100% plus I cannot seem to figure out where one begins and the other ends if this is the case.

Can anyone shed some light on this?

PS. I checked the FAQ this time but couldn't find the answer

[vogler]

Hi,
I found an oracle function => VALARCH.getStatusBit, you can find it in your rdb schema:

function getStatusBit(bitvar in binary_integer, pos in binary_integer) return binary_integer is

I guess that you can use this function to decode the value of the status column!
select pvalarch.getStatusBit(status, ) from xxxx

Of course you can take a look inside the function and you can see how it is encoded!
brgds.
Andy

[vogler]

Hi,
maybe this function is not public and/or not available in a standard installation, here is the source:

function getStatusBit(bitvar in binary_integer, pos in binary_integer) return binary_integer is
lbits number(20) := 0;
begin
case pos
when 1 then --_active 0
lbits := bitand(bitvar,power(2,0)); --1
when 2 then --_exp_default 1
lbits := bitand(bitvar,power(2,1)); --2
when 3 then --_aut_default 2
lbits := bitand(bitvar,power(2,2)); --4
when 4 then --_out_prange 3
lbits := bitand(bitvar,power(2,3)); --8
when 5 then --_out_range 4
lbits := bitand(bitvar,power(2,4)); --16
when 6 then --_exp_inv 5
lbits := bitand(bitvar,power(2,5)); --32
when 7 then --_aut_inv 6
lbits := bitand(bitvar,power(2,6)); --64
when 8 then --_default_bad 8
lbits := bitand(bitvar,power(2,8)); --256
when 9 then --_from_GI 9
lbits := bitand(bitvar,power(2,9)); --512
when 10 then --_from_SI 10
lbits := bitand(bitvar,power(2,10)); --1024
when 11 then --_per_active 11
lbits := bitand(bitvar,power(2,11)); --2048
when 12 then --_corr 12
lbits := bitand(bitvar,power(2,12)); --4096
when 13 then --_compr 13
lbits := bitand(bitvar,power(2,13)); --8192
when 14 then --_corr_add 15
lbits := bitand(bitvar,power(2,15)); --32768
when 15 then --_stime_inv 17
lbits := bitand(bitvar,power(2,17)); --131072
when 16 then --_userbit1 24
lbits := bitand(bitvar,power(2,24)); --16777216
when 17 then --_userbit2 25
lbits := bitand(bitvar,power(2,25)); --33554432
when 18 then --_userbit3 26
lbits := bitand(bitvar,power(2,26)); --67108864
when 19 then --_userbit4 27
lbits := bitand(bitvar,power(2,27)); --134217728
when 20 then --_userbit5 28
lbits := bitand(bitvar,power(2,28)); --268435456
when 21 then --_userbit6 29
lbits := bitand(bitvar,power(2,29)); --536870912
when 22 then --_userbit7 30
lbits := bitand(bitvar,power(2,30)); --1073741824
when 23 then --_userbit8 31
-- Left shift for 8 bits
lbits := bitand(greatest(bitvar-power(2,8),0),power(2,31-8)); --8388608
when 24 then --_bad virtuell == _invalid || _out_prange
lbits := bitand(bitvar,power(2,3)+power(2,5)+power(2,6)+power(2,17)); --(8+32+64+131072);
when 25 then --_default virtuell == _exp_default || _aut_default
lbits := bitand(bitvar,power(2,1)+power(2,2)); --(2+4);
when 26 then --_invalid virtuell == _exp_inv || _aut_inv || _stime_inv
lbits := bitand(bitvar,power(2,5)+power(2,6)+power(2,17)); --(32+64+131072);
when 27 then --_offline_bad virtuell == ! (_corr || !_online_bad || (_compr && !_corr)
declare
online_bad number(20) := 0;
begin
if bitand(bitvar, (2+4)) = 6 then
online_bad := bitand(bitvar, power(2,8)); --256
else
online_bad := bitand(bitvar,power(2,3)+power(2,5)+power(2,6)+power(2,17)); --(32+64+131072+8);
end if;
--if ( ((bit32Var & (4096)) == 4096) || (online_bad == 0) || ((bit32Var & (8192+65536)) == 8192) )
if bitand(bitvar,power(2,12)) = power(2,12) or online_bad = 0 or bitand(bitvar,power(2,13)+power(2,16)) = power(2,13) then
lbits := 1;
end if;
end;
when 28 then --_online_bad virtuell == _default ? _default_bad : _bad
if bitand(bitvar, (2+4)) = 6 then
lbits := bitand(bitvar, power(2,8)); --256
else
lbits := bitand(bitvar,power(2,3)+power(2,5)+power(2,6)+power(2,17)); --(8+32+64+131072);
end if;
else
lbits := 0;
end case;
--dbms_output.put_line(lbits);
return sign(lbits);
end;

[aorange]

Great stuff! thank you very much, I shall try that now...

[Gertjan van Schijndel]

The code above does not contain all status bits, so I have added bits: 19, 20 and 21. These bits can be found in the online help under: Reference tables -> Datapoint configs -> _original

Code: Select all

  function getStatusBit(bitvar in binary_integer,
                        pos    in binary_integer)  return binary_integer is
    lbits number(20) := 0;
  begin
  	case pos
      when 1 then  --_active    0
  				lbits := bitand(bitvar,power(2,0)); --1
      when 2 then  --_exp_default 1
  				lbits := bitand(bitvar,power(2,1)); --2
      when 3 then  --_aut_default 2
  				lbits := bitand(bitvar,power(2,2)); --4
      when 4 then  --_out_prange  3
  				lbits := bitand(bitvar,power(2,3)); --8
      when 5 then  --_out_range 4
  				lbits := bitand(bitvar,power(2,4)); --16
      when 6 then  --_exp_inv   5
  				lbits := bitand(bitvar,power(2,5)); --32
      when 7 then  --_aut_inv   6
  				lbits := bitand(bitvar,power(2,6)); --64
      when 8 then  --_default_bad 8
  				lbits := bitand(bitvar,power(2,8)); --256
      when 9 then  --_from_GI   9
  				lbits := bitand(bitvar,power(2,9)); --512
      when 10 then  --_from_SI   10
  				lbits := bitand(bitvar,power(2,10)); --1024
      when 11 then  --_per_active  11
  				lbits := bitand(bitvar,power(2,11)); --2048
      when 12 then  --_corr    12
  				lbits := bitand(bitvar,power(2,12)); --4096
      when 13 then  --_compr   13
  				lbits := bitand(bitvar,power(2,13)); --8192
      when 14 then  --_corr_add  15
  				lbits := bitand(bitvar,power(2,15)); --32768
      when 15 then  --_stime_inv 17
  				lbits := bitand(bitvar,power(2,17)); --131072
      when 16 then  --_last_value_storage_off 19
  				lbits := bitand(bitvar,power(2,19)); --524288
      when 17 then  --_value_changed 20
  				lbits := bitand(bitvar,power(2,20)); --1048576
      when 18 then  --_value_up 21
  				lbits := bitand(bitvar,power(2,21)); --2097152
      when 19 then  --_userbit1  24
  				lbits := bitand(bitvar,power(2,24)); --16777216
      when 20 then  --_userbit2  25
  				lbits := bitand(bitvar,power(2,25)); --33554432
      when 21 then  --_userbit3  26
  				lbits := bitand(bitvar,power(2,26)); --67108864
      when 22 then  --_userbit4  27
  				lbits := bitand(bitvar,power(2,27)); --134217728
      when 23 then  --_userbit5  28
  				lbits := bitand(bitvar,power(2,28)); --268435456
      when 24 then  --_userbit6  29
  				lbits := bitand(bitvar,power(2,29)); --536870912
      when 25 then  --_userbit7  30
  				lbits := bitand(bitvar,power(2,30)); --1073741824
      when 26 then  --_userbit8  31
          -- Left shift for 8 bits
          lbits := bitand(greatest(bitvar-power(2,8),0),power(2,31-8)); --8388608
  		when 27 then  --_bad     virtuell == _invalid || _out_prange
  				lbits := bitand(bitvar,power(2,3)+power(2,5)+power(2,6)+power(2,17)); --(8+32+64+131072);
  		when 28 then  --_default   virtuell == _exp_default || _aut_default
  				lbits := bitand(bitvar,power(2,1)+power(2,2)); --(2+4);
  		when 29 then  --_invalid   virtuell == _exp_inv || _aut_inv || _stime_inv
  				lbits := bitand(bitvar,power(2,5)+power(2,6)+power(2,17)); --(32+64+131072);
  		when 30 then  --_offline_bad virtuell == ! (_corr || !_online_bad || (_compr && !_corr)
        declare
          online_bad number(20) := 0;
        begin
          if bitand(bitvar, (2+4)) = 6 then
            online_bad := bitand(bitvar, power(2,8)); --256
          else
            online_bad := bitand(bitvar,power(2,3)+power(2,5)+power(2,6)+power(2,17)); --(32+64+131072+8);
          end if;
          --if ( ((bit32Var & (4096)) == 4096) || (online_bad == 0) || ((bit32Var & (8192+65536)) == 8192) )
          if bitand(bitvar,power(2,12)) = power(2,12) or online_bad = 0 or bitand(bitvar,power(2,13)+power(2,16)) = power(2,13) then
            lbits := 1;
          end if;
  			end;
      when 31 then  --_online_bad  virtuell == _default ? _default_bad : _bad
        if bitand(bitvar, (2+4)) = 6 then
          lbits := bitand(bitvar, power(2,8)); --256
        else
          lbits := bitand(bitvar,power(2,3)+power(2,5)+power(2,6)+power(2,17)); --(8+32+64+131072);
        end if;
      else
        lbits := 0;
    end case;
    --dbms_output.put_line(lbits);
  	return sign(lbits);
  end;