Number to binary conversion Oracle

In some cases when developing PL/SQL code within an Oracle database (and within a lot of other programming languages) you might want to refer to a programming style in which you use the binary number format (base-2).

In mathematics and computer science, the binary numeral system, or base-2 numeral system, represents numeric values using two symbols: 0 and 1. More specifically, the usual base-2 system is a positional notation with a radix of 2. Numbers represented in this system are commonly called binary numbers. Because of its straightforward implementation in digital electronic circuitry using logic gates, the binary system is used internally by almost all modern computers and computer-based devices such as mobile phones.

When working this way it can be handy to have a small function ready which converts a number to the binary numeral system. Some years ago Tom Kyte has developed this small function that can come in handy.

create or replace
FUNCTION num_to_bin( i_num IN PLS_INTEGER )
    RETURN VARCHAR2 IS
       l_Num      PLS_INTEGER;
       l_bit      PLS_INTEGER;
       l_binary   VARCHAR2(128);
    BEGIN
       --
       l_num := i_num;
      --
      WHILE l_num > 1 LOOP
         l_bit := MOD(l_num,2);
         l_binary := TO_CHAR(l_bit)||l_binary;
         l_num := FLOOR(l_num / 2);
      END LOOP;
      --
      IF l_num = 1 THEN
          l_binary := '1'||l_binary;
      END IF;
      --
      RETURN l_binary;
      --
   END num_to_bin;