Convert Decimal to hexadecimal on Oracle

Way 01

SQL> create or replace package number_utils as
 2          function d_to_hex(decimal_num in integer) return varchar2;
 3          pragma restrict_references (d_to_hex, wnds, wnps, rnps);
 4  end;
 5  /
Package created.

SQL> create or replace package body number_utils as
 2  function d_to_hex (decimal_num in integer)
 3  return varchar2 is
 4          v_result       varchar2(12);
 5          v_hex_digit    varchar2(1);
 6          v_quotient     pls_integer;
 7          v_remainder    pls_integer;
 8  begin
 9          if (decimal_num < 10) then
10                 v_result := to_char(decimal_num);
11          elsif (decimal_num < 16) then
12                 v_result := chr(65+(decimal_num-10));
13          else
14                 v_remainder := mod(decimal_num,16);
15                 v_quotient  := round((decimal_num - v_remainder) /16);
16                 v_result :=number_utils.d_to_hex(v_quotient) || number_utils.d_to_hex(v_remainder);
17          end if;
18          return v_result;
19  end  d_to_hex;
20  end number_utils;
21  /
Package body created.

SQL> select number_utils.d_to_hex(7685) from dual;
NUMBER_UTILS.D_TO_HEX(7685)
--------------------------------------------------------------------------------
1E05

Way 02:

SQL> CREATE OR REPLACE FUNCTION dec_to_hex (decimal_val in number) RETURN varchar2 IS
 2    hex_num varchar2(64);
 3    digit  pls_integer;
 4    decimal_num pls_integer:=decimal_val;
 5    hexdigit  char;
 6  BEGIN
 7    while ( decimal_num > 0 ) loop
 8       digit := mod(decimal_num, 16);
 9       if digit > 9 then
10          hexdigit := chr(ascii('A') + digit - 10);
11       else
12          hexdigit := to_char(digit);
13       end if;
14       hex_num := hexdigit || hex_num;
15       decimal_num := trunc( decimal_num / 16 );
16    end loop;
17    return hex_num;
18  END dec_to_hex;
19  /
Function created.

SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 03:

SQL> CREATE OR REPLACE FUNCTION dec_to_hex (dec_num IN NUMBER) RETURN VARCHAR2 IS
 2   v_decin NUMBER;
 3   v_next_digit NUMBER;
 4   v_result varchar(2000);
 5  BEGIN
 6    v_decin := dec_num;
 7    WHILE v_decin > 0 LOOP
 8    v_next_digit := mod(v_decin,16);
 9  IF v_next_digit > 9 THEN
10   IF v_next_digit = 10 THEN v_result := 'A' || v_result;
11    ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
12    ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
13    ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
14    ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
15    ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
16    ELSE raise_application_error(-20600,'Untrapped exception');
17   END IF;
18  ELSE
19   v_result := to_char(v_next_digit) || v_result;
20  END IF;
21  v_decin := floor(v_decin / 16);
22  END LOOP;
23  RETURN v_result;
24  END dec_to_hex;
25  /

Function created.

SQL> select dec_to_hex(17) from dual;

DEC_TO_HEX(17)
--------------------------------------------------------------------------------
11

Way 04:

SQL> create or replace function dec_to_hex(dec_num in number )
 2  return varchar2
 3  is
 4     l_str   varchar2(255) default NULL;
 5     l_num   number  default dec_num;
 6     l_hex   varchar2(16) default '0123456789ABCDEF';
 7          p_base   number:=16;
 8  begin
 9     if ( trunc(dec_num) <> dec_num OR dec_num < 0 ) then
10             raise PROGRAM_ERROR;
11     end if;
12     loop
13             l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
14             l_num := trunc( l_num/p_base );
15             exit when ( l_num = 0 );
16     end loop;
17     return l_str;
18  end dec_to_hex;
19  /

Function created.

SQL> select dec_to_hex(120) from dual;

DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.

Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78

SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;