Shift operators in PL/SQL

Question

Whether there is an alternative of shift operators in PL/SQL? There is bitand function, but it accepts only binary_integer-type arguments.

What should I do if I need check up lower/higher bit of really long number (probably set in the line)?

In C there are << and >> operators. How I can realise them in PL/SQL?

Solution 1

Here is my own LPAD/RPAD solution.

I take Tom Kyte package as base and expand it.

create or replace function bin_shift_right
(  p_bin in varchar2,
   p_shift in number default null) return varchar2
is
    l_len number;
    l_shift number;
begin
    l_shift := nvl(p_shift, 1);
    l_len := length(p_bin);
    if (l_len <= 0) then
        return null;
    end if; 
    if (l_shift > l_len) then
        l_shift := l_len;
    end if;

    return lpad(substr(p_bin, 1, l_len - l_shift), l_len, '0'); 
end bin_shift_right;

create or replace function shright
(  p_num in number,
   p_shift in number default null) return number
is
begin
    if (trunc(p_num) <> p_num OR p_num < 0) then
        raise PROGRAM_ERROR;
    end if;
    return nvl(to_dec(bin_shift_right(to_bin(p_num), p_shift), 2), 0);
end shright;
/

---

And tests

SQL>
SQL> select shright(123) from dual;

SHRIGHT(123)
------------
          61

SQL>
SQL> select shright(123, 2) from dual;

SHRIGHT(123,2)
--------------
            30

SQL>
SQL> select shright(123, 10) from dual;

SHRIGHT(123,10)
---------------


SQL> /

Solution 2

The following answer is not endianness agnostic and my wording is based on little endian format...

You can shift bits simply multiplying (shift left) or dividing (shift right) the argument by 2 to the power of x where x is the number of bits to shift. for example, if I need to shift the low-order byte of a number (255:11111111) 16 bits to the left I would perform the following operation:

select 255 * power(2,16) from dual;  
-- the result will be (16711680:111111110000000000000000)

conversely, if I want to shift the value 16711680 16 bits to the right I would perform the following:

select 16711680 / power(2,16) from dual;
-- the result will be (255:11111111)

Solution 3

Since Oracle Version 8 it's possible you use java code in the database. In PL/SQL you can define a wrapper for the java code. e.g.

PACKAGE BODY JAVA_CODE
IS
  function bitshift_left(x in number,
                         n in number) return number
  is language java name 'com.foo.Bitshift(java.lang.Integer, 
                                          java.lang.Integer) return java.lang.Integer';
END JAVA_CODE;

In the java code you can then use the shift operator. Although a bit clumsy, but it can work this way.

Sadly, this is not possible with Oracle XE, as there is no support for Java in that 'free' edition.

Comments

[drnk]

nice solution, thanks. i find pl/sql way, that i shall post within few minutes

[Pluto]

Using floor(...) on the result when dividing/shifting right would be advisable.