Re: get integers in hex format

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 06 Dec 1999 07:08:18 -0500
Message-ID: <jj9n4s0q5aebs4asebj9l8emgu5drg8efo@4ax.com>

A copy of this was sent to Thomas Beetz <t.beetz_at_fnt.de> (if that email address didn't require changing) On Mon, 06 Dec 1999 08:10:32 +0100, you wrote:

>Hello
>
>A simple problem:
>if i use
> select rownum from foo;
>i get

tkyte_at_8i> select to_char(rownum,'xx' ) from all_users where rownum < 15;

TO_
---
  1
[snip]
  9
  a
  b
  c
  d

that works in Oracle8i, release 8.1 and up.

In Oracle8.0 and before:

tkyte_at_8.0> create or replace function to_base( p_dec in number, p_base in number )
  2 return varchar2
  3 is

  4          l_str   varchar2(255) default NULL;
  5          l_num   number  default p_dec;
  6          l_hex   varchar2(16) default '0123456789ABCDEF';
  7  begin
  8          if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
  9                  raise PROGRAM_ERROR;
 10          end if;
 11          loop
 12                  l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
 13                  l_num := trunc( l_num/p_base );
 14                  exit when ( l_num = 0 );
 15          end loop;
 16          return l_str;

 17 end to_base;
 18 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace function to_dec

  2 ( p_str in varchar2,
  3 p_from_base in number default 16 ) return number   4 is

  5          l_num   number default 0;
  6          l_hex   varchar2(16) default '0123456789ABCDEF';
  7  begin
  8          for i in 1 .. length(p_str) loop
  9                  l_num := l_num * p_from_base +
instr(l_hex,upper(substr(p_str,i,1)))-1;
 10          end loop;
 11          return l_num;

 12 end to_dec;
 13 /

Function created.

tkyte_at_8.0> show errors
No errors.
tkyte_at_8.0>
tkyte_at_8.0> create or replace function to_hex( p_dec in number ) return varchar2   2 is
  3 begin
  4 return to_base( p_dec, 16 );   5 end to_hex;
  6 /

Function created.

and then:

tkyte_at_8.0> select to_hex( rownum ) from all_users where rownum < 15;

TO_HEX(ROWNUM)

---

1
2
[snip]
9
A
B
C
D
E

14 rows selected.

[snip]

>is there an easy way or must i write a simple function for this.
>
>Thanks thomas
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 06 1999 - 06:08:18 CST