BIN_TO_NUM - Binary to Number

Function BIN_TO_NUM

The PL/SQL example below converts a number (45 in this example) into binary value (101101). Using PL/SQL Reverse Loop, the binary value is used as input to the function to get the numeric value. The binary values have to be passed as comma seperated value to the function BIN_TO_NUM(1,0,1,1,0,1). The reverse computation to get the numeric value is done by calling the function BIN_TO_NUM() using dynamic sql and passing the binary values as a numeric array.

Oracle Function BIN_TO_NUM(1,0,1,1,0,1)

DECLARE
 TYPE typ_num IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 v_num   typ_num;

 v_bin   VARCHAR2(50);
 v_fn    VARCHAR2(50);
 s_sql   VARCHAR2(60) := 'SELECT BIN_TO_NUM(';

 v_val   PLS_INTEGER;
 v_idx   PLS_INTEGER := &i_idx;
 idx     PLS_INTEGER :=0;

BEGIN
 WHILE v_idx > 1 LOOP
  idx := idx +1;
  v_bin := MOD(v_idx,2)||v_bin;
  v_num(idx) := MOD(v_idx,2);
  v_idx := FLOOR(v_idx/2);
 END LOOP;

 v_num(idx+1) :=1; 

 FOR i IN REVERSE v_num.FIRST .. v_num.LAST LOOP
   IF i > v_num.FIRST THEN
      s_sql := s_sql||v_num(i)||',';
      v_fn := v_fn||v_num(i)||',';
   ELSE
      s_sql := s_sql||v_num(i)||') FROM DUAL';
      v_fn := v_fn||v_num(i);
   END IF;
 END LOOP;
  
 EXECUTE IMMEDIATE s_sql INTO v_val;

 v_bin := 1||v_bin;
 DBMS_OUTPUT.PUT_LINE(' Binary ['||v_val||'] -> '||v_bin||
   ' BIN_TO_NUM('||v_fn||') -> '||v_val);
END;
/
-- Above PL/SQL Code (I/O) -> Bin_To_Num(1,0,1,1,0,1)

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012

72835