JL Computer Consultancy

Somewhere in the $ORACLE_HOME/rdbms/admin set of scripts there is, I am sure, a little procedure of function to convert a decimal representation of a number into a Hexadecimal string. I haven't yet got around to looking for it, and over the years I've managed to do with by using a little PL/SQL routine.

I have just rewritten the routine as a packaged PL/SQl function, which can be called from SQL, although it is not intended to be terribly efficient (more a cute little example of recursion in PL/SQL) so should not be used for heavy-duty, high-speed processing.

rem

rem     Script:        jpl_utils.sql

rem     Author:        Jonathan Lewis

rem     Dated:         

rem     Purpose:       Set of odds and ends.

rem

rem     Notes:

rem     This version holds only a tested decimal to hex conversion function

rem     The function is declared as pure, and can be called from SQL.

rem

rem     The return is a string representing a HEX number up to 12 digits long

rem

rem     Sample of use:

rem            select jpl_utils.decimal_to_hex(99);

rem            execute dbms_output.put_line(jpl_utils.decimal_to_hex(127));

rem

create or replace package jpl_utils as

        function decimal_to_hex(i_decimal in integer) return varchar2;

        pragma restrict_references (decimal_to_hex, wnds, wnps, rnps);

end;

.

/

rem

rem     This isn't supposed to be an efficient high-volume

rem     function, just a cutesy little demo of recursive PL/SQL

rem     There is NO eror trapping

rem

create or replace package body jpl_utils as

function decimal_to_hex (i_decimal in integer)

return varchar2 is

        v_result       varchar2(12);

        v_hex_digit    varchar2(1);

        v_quotient     integer;

        v_remainder    integer;

begin

        if (i_decimal < 10) then

               v_result := to_char(i_decimal);

        elsif (i_decimal < 16) then

               v_result := chr(65+(i_decimal-10));   -- or 55 + idec

        else

               v_remainder := mod(i_decimal,16);

               v_quotient  := round((i_decimal - v_remainder) /16);

               v_result := 

                       jpl_utils.decimal_to_hex(v_quotient) ||

                       jpl_utils.decimal_to_hex(v_remainder);

        end if;

        return v_result;       

end  decimal_to_hex;

end jpl_utils;

.

/