Re: How do you display hex via SQL

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Thu, 15 Jul 2004 23:41:44 -0400
Message-ID: <20040716034144.GA3828@medo.noip.com>

On 07/15/2004 10:26:44 PM, Warren Homer wrote:
> Hi ,
>
> is there a way via Oracle SQL to display the hex representation of a number.
>
> e.g select hex(57) from dual;
>

This is a simple programming excercise. Here is how it goes:

PROMPT CREATE OR REPLACE PACKAGE myutil

CREATE OR REPLACE PACKAGE myutil
as
function num2hex(num number) return varchar2 deterministic; end;
/

PROMPT CREATE OR REPLACE PACKAGE BODY myutil

CREATE OR REPLACE PACKAGE BODY myutil as type arr is table of char(1);
digits arr;
function num2hex(num number) return varchar2 is
quot number;
rem number;
dig char(1);
begin

   rem:=mod(num,16);
   dig:=digits(rem+1);
   quot:=floor(num/16);
   if (num>0) then

      return(num2hex(quot)||dig);
   else

      return(NULL);
   end if;
end;
begin
digits:=arr('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'); end;
/

Here comes the demonstration:
SQL> select myutil.num2hex(&num) from dual; Enter value for num: 57
old 1: select myutil.num2hex(&num) from dual new 1: select myutil.num2hex(57) from dual  

MYUTIL.NUM2HEX(57)

---

39  

SQL> /
Enter value for num: 64
old 1: select myutil.num2hex(&num) from dual new 1: select myutil.num2hex(64) from dual  

MYUTIL.NUM2HEX(64)

---

40  

SQL> /
Enter value for num: 256
old 1: select myutil.num2hex(&num) from dual new 1: select myutil.num2hex(256) from dual  

MYUTIL.NUM2HEX(256)

---

100  

SQL> /
Enter value for num: 65535
old 1: select myutil.num2hex(&num) from dual new 1: select myutil.num2hex(65535) from dual  

MYUTIL.NUM2HEX(65535)

---

FFFF   SQL> /
Enter value for num: 1048576
old 1: select myutil.num2hex(&num) from dual new 1: select myutil.num2hex(1048576) from dual  

MYUTIL.NUM2HEX(1048576)

---

100000  

SQL>

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jul 15 2004 - 22:38:20 CDT