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?

Answer 1

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)

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

Answer 2

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.

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

Answer 3

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> /