5

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;
12

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
  • 1
    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? – Vérace Jun 2 at 9:52
  • 1
    @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. – Balazs Papp Jun 2 at 10:46
  • 1
    @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! :-) – Vérace Jun 2 at 10:58
  • 1
    Just a note, MOD() works properly only up to numbers 2**127. – Wernfried Domscheit Jun 3 at 6:29
  • 1
    When my factory is producting its ( (2**127) + 1 )-th widget, I'll start to worry! :-) – Vérace Jun 3 at 10:51
5

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 CTEs, 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.