Forum Stats

  • 3,815,817 Users
  • 2,259,093 Discussions
  • 7,893,255 Comments

Discussions

Write a Get_bit Function

user8101600
user8101600 Member Posts: 23
edited Oct 31, 2014 4:02PM in SQL & PL/SQL

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.

Tagged:
Anton SchefferBluShadowuser8101600

Best Answer

  • odie_63
    odie_63 Member Posts: 8,461 Silver Trophy
    Answer ✓

    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
    
    Anton Schefferuser8101600
«1

Answers

  • Manik
    Manik Member Posts: 2,908 Gold Trophy

    Can you add few examples please.

    Cheers,

    Manik.

  • odie_63
    odie_63 Member Posts: 8,461 Silver Trophy
    Answer ✓

    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
    
    Anton Schefferuser8101600
  • RajenB
    RajenB Member Posts: 345 Gold Badge

    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)
    
    user8101600
  • BluShadow
    BluShadow Member, Moderator Posts: 41,875 Red Diamond

    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
    user8101600
  • odie_63
    odie_63 Member Posts: 8,461 Silver Trophy
    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


    user8101600
  • BluShadow
    BluShadow Member, Moderator Posts: 41,875 Red Diamond

    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.

    user8101600
  • BluShadow
    BluShadow Member, Moderator Posts: 41,875 Red Diamond

    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
    user8101600
  • odie_63
    odie_63 Member Posts: 8,461 Silver Trophy

    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
    
    BluShadowuser8101600
  • BluShadow
    BluShadow Member, Moderator Posts: 41,875 Red Diamond

    I think some people are just wrong in the head.

  • Unknown
    edited Oct 29, 2014 1:26PM
    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.

This discussion has been closed.