how can i convert decimal values to its binary form in pl/sql? 1

[beksalur]

how can i convert decimal values to its binary form in pl/sql?i used select to_bin(5) binary from dual; but it say no sucah a to_bin function.
Exp:
Decimal Binary
-------- -------
5 0101

Thanks in advance.
Serdar Can

 

[SantaMufasa]

Beksalur,

Whenever converting from one base to another, I like to use my BASECONV function. Using your sample values:

col a heading "Base|Conversion" format a10
select baseconv(5,10,2) a from dual;

Base
Conversion
----------
101

The code for my BASECONV function is:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor "Dasages, LLC" makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM
REM Please contact the author via email (dave@dasages.com) when
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM NAME: BASECONV.SQL - PL/SQL code to translate positive or
REM       negative integer values from any base (radix)
REM       (base 36 max.) to its counterpart value in any other
REM       base (base 36 max.).
REM
REM AUTHOR: Dave Hunt
REM         Co-principal, Dasages, LLC
REM         1-801-733-5333
REM
REM **************************************************************
REM Usage:
REM       BASECONV (<value>,<source-base>,<target-base>)
REM
REM   ...where <value> is some "number" using valid "numerals" in
REM   the <source-base>. For example, in base 2, valid numerals are
REM   "0" and "1"; in base 36, valid numerals are "0" thru "z".
REM   When using bases 11 thru 36, you specify alpha characters for
REM   numerals beyond decimal '9'. Alpha characters may be in either
REM   upper or lower case.
REM
REM   ...where <source-base> is any base (radix) between 2 and 36.
REM   ...where <target-base> is any base (radix) between 2 and 36.
REM   Be sure to specify both source and target bases in base 10.
REM
REM Sample invocations:
REM
REM   col x heading "Conversion" format a20
REM   select baseconv(255,10,2) x from dual;
REM   Conversion
REM   ----------
REM   11111111
REM
REM   select baseconv('Zebra',36,10) x from dual;
REM   Conversion
REM   ----------
REM   59454982
REM
REM   select baseconv('aaaaaaaaaaaaaaaa',16,2) x from dual;
REM   Conversion
REM   ----------------------------------------------------------------
REM   1010101010101010101010101010101010101010101010101010101010101010
REM **************************************************************
Create or replace function BaseConv
   ( ValueIn       in varchar2     -- incoming value to convert
   , RadFrom       in number       -- source base
   , RadOut        in number       -- target base
   )
   return          varchar2        -- outgoing value in target base
is
   ValIn           varchar2(1000);
   Sign            char;
   LenIn           number;
   Base10Value     number;
   DigitPool       varchar2(50)    := '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   DigitHold       varchar(1);
   HighPower       number;
   CurrValue       number;
   CurrDigit       number;
   ResultingValue  varchar(2000);
   function GetDigit10 (InDigit in varchar2, RadIn in number) return number
   is
           bad_digit       exception;
           pragma exception_init(bad_digit,-6502);
   begin
           if InDigit = '0' then
                   return 0;
           end if;
           DigitHold := upper(InDigit);
           for i in 1..RadIn-1 loop
                   if DigitHold = substr(DigitPool,i,1) then
                           return i;
                   end if;
           end loop;
           raise_application_error(-20000,'Illegal digit, "'||
                   InDigit||'" for base "'||RadIn||'"');
   end;
begin
   ValIn   :=      ValueIn;
   if substr(ValIn,1,1) = '-' then
           Sign    := '-';
           ValIn   := substr(ValIn,2);
   else
           Sign    := null;
   end if;
   LenIn := length(nvl(ValIn,'0'));
   Base10Value     := 0;
   for i in 1..LenIn loop
           Base10Value     := Base10Value +
                   GetDigit10(substr(ValIn,i,1),RadFrom) * power(RadFrom,LenIn-i);
   end loop;
   for i in 1..1000 loop
           if power(RadOut,i) > Base10Value then
                   HighPower := i-1;
                   exit;
           end if;
   end loop;
   CurrValue       := Base10Value;
   ResultingValue  := null;
   for i in 0..HighPower loop
           CurrDigit := floor(Currvalue / power(RadOut,HighPower-i));
           CurrValue := Currvalue - (CurrDigit * power(RadOut,HighPower-i));
           if CurrDigit = 0 then
                   ResultingValue := ResultingValue||'0';
           else
                   ResultingValue := ResultingValue||substr(DigitPool,CurrDigit,1);
           end if;
   end loop;
   return sign||ResultingValue;
end;

As the description indicates, you can do any value conversion where the bases fall between base 2 and base 36. For example, here is a base 36-to-base 16 conversion:

select baseconv('1234567890abcdefghijklmnopqrstuvwxyz',36,16) from dual;

BASECONV('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ',36,16)
------------------------------------------------------
20A45C4A82A3EB406AF773B5C3F6BBB6ABA606A213EA58

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:19 (24Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:19 (24Nov04) Mountain Time

 

[carp]

Rack up another star, Dave!
It doesn't solve any of my problems right now, but I'm sure it will some day!
You are STILL the wind beneath my wings!

 

[SantaMufasa]

Thanks, Carp. I'm glad you like it. Unfortunately, I get complaints from people about the air beneath my wings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:33 (24Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:33 (24Nov04) Mountain Time