# PL/SQL function to receive a number and return its binary format

I'm trying to write a function to receive a number and return its binary format. This is what I've developed so far and it's ok for inputs : 4,8,16 but it does not return correct binary format for other numbers. I could not find the problem and I was wondering if you could find the problem?

``````create or replace function Show_Binary(i_Number in Number) Return Varchar2
AS

V_Binary varchar2(50) := '';
i_Number2 Number := i_Number;
Begin
While i_Number2>=2 LOOP

V_Binary := V_Binary || Remainder(i_Number2, 2);
i_Number2 := i_Number2 / 2;

End LOOP;
V_Binary := V_Binary || TO_CHAR(i_Number2);
select reverse (V_Binary) into V_Binary from dual;
return (V_Binary);
End;
``````

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
``````
• Szia @BalazsPapp - I know that you're pretty hot with Oracle - would it not be better to use SQL as per my answer - I seem to have read that performance-wise, it's better to use SQL first, then PL/SQL? Does Oracle do pure binary arithmatic without moving back and forth between strings? Jun 2, 2019 at 9:52
• @Vérace That is typically true when someone does row-by-row processing in PL/SQL instead of pure SQL. But that is not a general truth, see my update. Jun 2, 2019 at 10:46
• @BalazsPapp - thanks for that explanation. Obviously rules of thumb are not universal truths! :-) I'll try and bear that in mind. I'd love to have Tom Kyte's runstats to hand to really drill down on this issue - but, so many intereting questions, so little time! :-) Jun 2, 2019 at 10:58
• Just a note, `MOD()` works properly only up to numbers 2**127. Jun 3, 2019 at 6:29
• When my factory is producting its ( (2**127) + 1 )-th widget, I'll start to worry! :-) Jun 3, 2019 at 10:51

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.