PL/SQL function to receive a number and return its binary format
This is not an Oracle or PL/SQL issue, but a matter of implementing the proper algorithm.
Here is an example:
https://www.orafaq.com/wiki/Binary
CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS
binval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
binval := mod(N2, 2) || binval;
N2 := trunc( N2 / 2 );
end loop;
return binval;
END dec2bin;
/
SQL> SELECT dec2bin(22) FROM dual;
DEC2BIN(22)
----------------
10110
The overhead of LISTAGG + hierarchical query + other SQL functions is bigger than the overhead of a simple PL/SQL function.
SQL> with g as (select * from dual connect by level <= 1000) select count(distinct dec2bin(rownum)) as bincd from g,g;
BINCD
----------
1000000
Elapsed: 00:00:13.75
with g as (select * from dual connect by level <= 1000),
g2 as (select rownum as r from g, g)
select count(distinct bin) as bincd from (
select (SELECT LISTAGG(SIGN(BITAND(r, POWER(2,LEVEL-1))),'')
WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=r
) as bin
from g2
);
BINCD
----------
1000000
Elapsed: 00:00:35.53
And this is without the UDF Pragma. On 12c and above the usage of the function gets slightly faster with the addition of PRAGMA_UDF
.
CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS
PRAGMA UDF; -- <==================================================== MAGIC
binval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
binval := mod(N2, 2) || binval;
N2 := trunc( N2 / 2 );
end loop;
return binval;
END dec2bin;
/
SQL> with g as (select * from dual connect by level <= 1000) select count(distinct dec2bin(rownum)) as bincd from g,g;
BINCD
----------
1000000
Elapsed: 00:00:12.01
No need to use a function - you can do it in (Oracle's) SQL.
SELECT LISTAGG(SIGN(BITAND(43, POWER(2,LEVEL-1))),'')
WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=43;
Result:
BIN
101011
I found this deadly snippet here and the fiddle is here. For the number 43, just substitute your column of choice. It's probably possible to do this using recursive CTE
s, but that's a bit above my pay grade :-).
And to reverse the process, you can use this snippet
WITH INPUT AS
(SELECT REVERSE('1000') AS X FROM DUAL)
SELECT SUM(TO_NUMBER(SUBSTR(X,LEVEL,1)*POWER(2,LEVEL-1))) AS OUTPUT
FROM INPUT CONNECT BY LEVEL<=LENGTH(X);
Result:
OUTPUT
8
From here - dbfiddle here. Again, a recursive CTE
might do the trick. Again, for '1000', just substitute your column.
Just for kicks, I found another function which will work (with tweaks) on older versions of databases that don't have recursive CTEs. From the excellent Orafaq site here.
SELECT
DECODE(BITAND(VALUE, 128), 128, '1', '0') ||
DECODE(BITAND(VALUE, 64), 64, '1', '0') ||
DECODE(BITAND(VALUE, 32), 32, '1', '0') ||
DECODE(BITAND(VALUE, 16), 16, '1', '0') ||
DECODE(BITAND(VALUE, 8), 8, '1', '0') ||
DECODE(BITAND(VALUE, 4), 4, '1', '0') ||
DECODE(BITAND(VALUE, 2), 2, '1', '0') ||
DECODE(BITAND(VALUE, 1), 1, '1', '0') as bin_number from
(select 8 as value from dual) A;
Result:
MY_BIN
1000
Fiddle here. The more observant among you will notice that that code does not remove leading zeros - the code above has to be encase (entombed?) in this snippet
select replace(ltrim(replace(ColumnName,'0',' ')),' ','0')
which can be found here.