CODEWITHSUNDEEP
sqlplsqlbitbitwise-operators
Ajay
Ajay

Reputation: 3086

What is a good way to do bitor in pl/sql?

What is a good way to do bitor operation in pl/sql ?

Currently we are using

bitor(x,y) = x + y - bitand(x,y)

Thanks in advance.

Upvotes: 3

Views: 5125

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

Have a look at UTL_RAW package. There you can find BIT_AND, BIT_COMPLEMENT, BIT_OR and BIT_XOR Function

However, first you have to convert numeric values to RAW, e.g. CAST_FROM_BINARY_INTEGER

WITH t AS (
    SELECT 6 AS x, 8 AS y FROM dual),
r AS (
    SELECT
        x, y,
        UTL_RAW.CAST_FROM_BINARY_INTEGER(x) AS raw_x,
        UTL_RAW.CAST_FROM_BINARY_INTEGER(y) AS raw_y
    FROM t),
op AS (
    SELECT
        x, y,
        raw_x, raw_y,
        UTL_RAW.BIT_AND(raw_x, raw_y) AS BIT_AND,
        UTL_RAW.BIT_OR(raw_x, raw_y) AS BIT_OR,
        UTL_RAW.BIT_XOR(raw_x, raw_y) AS BIT_XOR
    FROM r)
SELECT 
    UTL_RAW.CAST_TO_BINARY_INTEGER(BIT_AND),
    UTL_RAW.CAST_TO_BINARY_INTEGER(BIT_OR),
    UTL_RAW.CAST_TO_BINARY_INTEGER(BIT_XOR)
FROM op;

Upvotes: 1

MT0
MT0

Reputation: 167932

From Oracle 21, there are built-in BITOR and BITXOR functions; however, they are undocumented.

SELECT a,
       b,
       BITAND(a, b),
       BITOR(a, b),
       BITXOR(a, b)
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (a, b) AS
  SELECT 0, 0 FROM DUAL UNION ALL
  SELECT 0, 1 FROM DUAL UNION ALL
  SELECT 1, 0 FROM DUAL UNION ALL
  SELECT 1, 1 FROM DUAL;

Outputs:

A B BITAND(A,B) BITOR(A,B) BITXOR(A,B)
0 0 0 0 0
0 1 0 1 1
1 0 0 1 1
1 1 1 1 0

Note: There are also new (and documented) aggregation functions BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG.

db<>fiddle here

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 220787

I've been happy with this emulation in the past

CREATE OR REPLACE FUNCTION bitor(x NUMBER, y NUMBER) RETURN NUMBER DETERMINISTIC
IS
BEGIN
    RETURN x - bitand(x, y) + y;
END;

It's the same as yours. An explanation can be found here

Upvotes: 8

Related Questions