Write a Get_bit Function

user8101600 · Oct 29, 2014 1:19AM

Hi,

I have a get_bit function in Postgres database. In Oracle, there is no built-in function to handle the get_bit. I need to write my custom Oracle PLSQL function to mimic this get_bit fucntion's behavior. Can anyone have any suggestion on how to do that? Thanks!

The definition of get_bit is as the following:

Function	Return Type	Description	Example	Result
get_bit(string,offset)	int	Extract bit from string	get_bit(E'Th\\000omas'::bytea, 45)	1

get_bit(string, offset)	(returns int)
	Extract bit from string
	get_bit(E'Th\\000omas'::bytea, 45) => 1

Note that the bits within a byte are numbered with the least significant bit being the zero bit. In the above example, the 'a' is the

byte being referenced and it contains x'61' => 0110 0001 and the third bit from the left (bit 5 starting with zero on the right) is returned.

odie_63 · Oct 29, 2014 4:26AM

I'm not sure if you need a VARCHAR2 string as input or directly a byte sequence so here's an example that takes a VARCHAR2 input and an optional charset argument to convert the string to byte (default is the database character set).

SQL> create or replace function get_bit (
  2    p_str     in varchar2
  3  , p_bit_num in integer
  4  , p_cs      in varchar2 default null
  5  )
  6  return integer deterministic
  7  is
  8    input        raw(32767) := utl_i18n.string_to_raw(p_str, p_cs);
  9    target_byte  raw(1);
 10    output       integer;
 11  begin
 12    target_byte := utl_raw.substr(input, trunc(p_bit_num/8)+1, 1);
 13    output := bitand(utl_raw.cast_to_binary_integer(target_byte) / power(2, mod(p_bit_num, 8)), 1);
 14    return output;
 15  end;
 16  /

Function created

SQL> 
SQL> select get_bit('Th'||chr(0)||'omas', 45)
  2  from dual;

GET_BIT('TH'||CHR(0)||'OMAS',4
------------------------------
                             1

Manik · Oct 29, 2014 3:07AM

Can you add few examples please.

Cheers,

Manik.

odie_63 · Oct 29, 2014 4:26AM

I'm not sure if you need a VARCHAR2 string as input or directly a byte sequence so here's an example that takes a VARCHAR2 input and an optional charset argument to convert the string to byte (default is the database character set).

SQL> create or replace function get_bit (
  2    p_str     in varchar2
  3  , p_bit_num in integer
  4  , p_cs      in varchar2 default null
  5  )
  6  return integer deterministic
  7  is
  8    input        raw(32767) := utl_i18n.string_to_raw(p_str, p_cs);
  9    target_byte  raw(1);
 10    output       integer;
 11  begin
 12    target_byte := utl_raw.substr(input, trunc(p_bit_num/8)+1, 1);
 13    output := bitand(utl_raw.cast_to_binary_integer(target_byte) / power(2, mod(p_bit_num, 8)), 1);
 14    return output;
 15  end;
 16  /

Function created

SQL> 
SQL> select get_bit('Th'||chr(0)||'omas', 45)
  2  from dual;

GET_BIT('TH'||CHR(0)||'OMAS',4
------------------------------
                             1

RajenB · Oct 29, 2014 4:47AM

Hi,

One way of doing it is:

1) Convert character to decimal using ASCII, for example:

 with t as
 (select 'Th'||chr(0)||'omas' str, 45 offset from dual)
  select ascii(substr(str,trunc(offset/8)+1,1))  l_num from t;

2) Write a function which converts Dec to Binary, for example something like:

while (l_num > 0 )
loop
  l_bin := mod(l_num, 2) || l_bin;
  l_num     := trunc( l_num / 2 );
end loop;
substr ('0110 0001', 8-mod(45,8), 1)

3) Then:

 substr (l_bin, 8-mod(offset,8), 1)

BluShadow · Oct 29, 2014 5:12AM

No need for PL/SQL code for this...

SQL> ed
Wrote file afiedt.buf
  1  with t as (select 'Th'||chr(0)||'omas' as txt from dual)
  2      ,r as (select 45 as req_bit from dual)
  3  --
  4  -- end of example string
  5  --
  6  select txt
  7        ,req_bit
  8        ,substr(txt,ceil(req_bit/8),1) as byte_chr
  9        ,mod(req_bit,8) as req_chr_bit
 10        ,sign(bitand(ascii(substr(txt,ceil(req_bit/8),1)), power(2,mod(req_bit,8)))) as get_bit
 11* from   t cross join r
SQL> /
TXT        REQ_BIT B REQ_CHR_BIT    GET_BIT
------- ---------- - ----------- ----------
Th omas         45 a           5          1

odie_63 · Oct 29, 2014 6:25AM

No need for PL/SQL code for this...

If OP is looking for something reusable, we do have to make it a function, don't we?

Your query doesn't work when the searched bit is the LSB of a byte (beyond the first one).

For example, it returns 1 instead of 0 when asking for bit 8 in 'AB'.

ceil(req_bit/8) should be trunc(req_bit/8)+1

BluShadow · Oct 29, 2014 6:34AM

Well, yes, if it needs to be reusable then it can be incorporated in a function. My point was that it doesn't require any complex PL code to loop through things, convert things to binary strings etc. etc. as it can be done in a single statement.

Good spot on the LSB issue.

BluShadow · Oct 29, 2014 6:59AM

Another answer, just for the silliness of it...

SQL> ed
Wrote file afiedt.buf
  1  with t as (select 'Th'||chr(0)||'omas' as txt from dual)
  2      ,r as (select 45 as req_bit from dual)
  3      ,hex2bin as (select '0' as h,'0000' as b from dual union all
  4                   select '1', '0001' from dual union all
  5                   select '2', '0010' from dual union all
  6                   select '3', '0011' from dual union all
  7                   select '4', '0100' from dual union all
  8                   select '5', '0101' from dual union all
  9                   select '6', '0110' from dual union all
 10                   select '7', '0111' from dual union all
 11                   select '8', '1000' from dual union all
 12                   select '9', '1001' from dual union all
 13                   select 'A', '1010' from dual union all
 14                   select 'B', '1011' from dual union all
 15                   select 'C', '1100' from dual union all
 16                   select 'D', '1101' from dual union all
 17                   select 'E', '1110' from dual union all
 18                   select 'F', '1111' from dual
 19                  )
 20  --
 21  -- end of example string
 22  --
 23  select txt
 24        ,substr(listagg(h.b) within group (order by l),-(r.req_bit+1),1)
 25  from (select txt, d, level l
 26              ,substr(d,level,1) as b
 27        from (select txt
 28                    ,upper(replace(regexp_replace(regexp_replace(dump(txt,16),'^.*: '),',(.),',',0\1,'),',')) as d
 29              from   t
 30             )
 31        connect by level <= length(d)
 32       ) x
 33       join hex2bin h on (h.h = x.b)
 34       cross join r
 35* group by txt, r.req_bit
SQL> /

TXT     S
------- -
Th omas 1

odie_63 · Oct 29, 2014 9:33AM

XQuery rulez

SQL> select bit
  2  from xmltable(
  3    'declare function local:shr ($n as xs:integer, $p as xs:integer) as xs:integer {
  4       if ($p = 0) then $n else local:shr($n idiv 2, $p - 1)
  5     };
  6     local:shr( string-to-codepoints($str)[$bitnum idiv 8 + 1]
  7              , xs:integer($bitnum mod 8) ) mod 2'
  8        passing 'ABC' as "str"
  9              , 17 as "bitnum"
 10        columns bit number path '.'
 11       )
 12  ;

       BIT
----------
         1

BluShadow · Oct 29, 2014 9:49AM

I think some people are just wrong in the head.

Unknown · Oct 29, 2014 1:15PM

I have a get_bit function in Postgres database.  In Oracle, there is no built-in function to handle the get_bit.    I need to write my custom Oracle PLSQL function to mimic this get_bit fucntion's behavior.  Can anyone have any suggestion on how to do that?

What PROBLEM are you trying to solve?

Why do you need bits and bit fields to solve it?

The biggest oddity in that example is that bits are strictly byte based while strings are character based. A character can have 1, 2 or more bytes depending on the actual character set. In some character sets (e.g. UTF8) different characters will have a DIFFERENT number of bytes.

Even the same character can use a different number of bytes in a different character set. An ASCII character will have only one byte in some character sets, two bytes in others and four bytes in a 32 bit character set.

Reliable 'bit fiddling' is generally performed on either integers (when a small number of possible bits is needed) or RAW data.

The point being that if you are using bit manipulation in Postgres and are converting to Oracle you likely need to convert more than just that one function.

So tell us what the actual scope of the requirement is for the functionality that includes the bit manipulation.

See the discussion in this thread for using the bitand function for certain types of bit manipulation

https://forums.oracle.com/thread/2601491?start=0&tstart=0

For bit operations on large bit strings you would use the UTL_RAW package

UTL_RAW

That package has functions for performing AND, OR, XOR and COMPLEMENT operation on RAW operands. An example query from my own development:

CREATE TABLE EMP_LANGUAGE (EMPID NUMBER, LANGUAGES RAW (2000))
 
-- set language 15000 for empids in the 100 range
update emp_language
set languages = UTL_RAW.BIT_OR(languages, bit_pk.bit_to_raw(15000))
where empid > 99 and empid < 200

-- now query the data for language 15000 
 select empid from emp_language
where UTL_RAW.BIT_AND(languages, bit_pk.bit_to_raw(15000))

That query select all EMPID values rows where bit 15,000 is set in the RAW column named 'languages'.

It uses a custom function named 'BIT_TO_RAW' in my custom package 'BIT_PK' to convert the integer 15000 to a RAW byte string that has at least 15000 bits (just short of 2000 bytes) and sets ONLY bit 15000 to a '1'. Then the BIT_AND function does the actual comparison.

That custom code was written long before bit map indexes were available and hasn't been used in years. That is why I am curious as to whether you really have a legitimate use case for that now.

Forum Stats

Quick Links

Categories

Write a Get_bit Function

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Write a Get_bit Function

Best Answer

Answers