Shift Operators in PL / SQL

Question

Is there an alternative to shift statements in PL / SQL? There is a bitand function, but it accepts only arguments of type binary_integer.

What should I do if I need to check the lower / upper bit of a really long number (possibly set in a string)?

C has the operators << and >> . How can I implement them in PL / SQL?

Answer 1

Here is my own LPAD / RPAD solution.

I take the Tom Kyte package as a 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> / 

Answer 2

The following answer is not agnostic, and my wording is based on a small end format ...

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

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

on the contrary, if I want to shift the value 16711680 by 16 bits to the right, I would do the following:

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

Answer 3

Since in Oracle Version 8, you can use Java code in the database. In PL / SQL, you can define a wrapper for java code. eg.

 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 java code you can use shift operator. Although a little awkward, it can work that way.

Unfortunately, this is not possible in Oracle XE, as there is no Java support in this free version.