Re: Q: How to compare hexa to decimal in PL/SQL?
Date: 1999/06/17
Message-ID: <37695611.1386944_at_newshost.us.oracle.com>#1/1
A copy of this was sent to ManniP_at_Hotmail.com (if that email address didn't require changing) On Thu, 17 Jun 1999 18:48:54 +0200, you wrote:
>Hi,
>
>Sorry for the novice question, but I have a column created as raw data
>type and I'd like to compare that with a varchar(2) column from another
>table.
>
>All I need is converting that hex to decimal in PL/SQL. For example:
>function(0hFF) should give me '0255'. I can do that in SQL+ with the
>dump function but I can't find that in PL/SQL.
>
>Could someone please give me a little help?
>
>Thanks in advance,
>Paolo
Here is a set of conversion functions to convert between any base (16 or less). Once you install, you can:
SQL> select to_dec( 'ff' ) from dual;
TO_DEC('FF')
255
or
begin
x := to_dec( 'ff' );
end;
in plsql....
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL; l_num number default p_dec; l_hex varchar2(16) default '0123456789ABCDEF'; begin if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then raise PROGRAM_ERROR; end if; loop l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str; l_num := trunc( l_num/p_base ); exit when ( l_num = 0 ); end loop; return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0; l_hex varchar2(16) default '0123456789ABCDEF'; begin for i in 1 .. length(p_str) loop l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1; end loop; return l_num;
end to_dec;
/
show errors
create or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
/
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
-- Opinions are mine and do not necessarily reflect those of Oracle CorporationReceived on Thu Jun 17 1999 - 00:00:00 CEST