| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Base conversion
Here ya go:
create or replace package radix
is
/*
base code courtesy of Thomas Kyte
*/
function to_base( p_dec in number, p_base in number )
return varchar2;
function to_dec (
p_str in varchar2,
p_from_base in number default 16
) return number;
function to_hex( p_dec in number ) return varchar2;
function to_bin( p_dec in number ) return varchar2;
function to_oct( p_dec in number ) return varchar2;
function to_36( p_dec in number ) return varchar2;
function to_64( p_dec in number ) return varchar2;
pragma restrict_references( to_base, wnds, rnds, wnps, rnps );
pragma restrict_references( to_dec, wnds, rnds, wnps, rnps );
pragma restrict_references( to_hex, wnds, rnds, wnps, rnps );
pragma restrict_references( to_bin, wnds, rnds, wnps, rnps );
pragma restrict_references( to_oct, wnds, rnds, wnps, rnps );
pragma restrict_references( to_36, wnds, rnds, wnps, rnps );
pragma restrict_references( to_64, wnds, rnds, wnps, rnps );
end radix;
/
show errors
create or replace package body radix
is
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(64) :=
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
-- base 64 for Oracle extended rowid format
if p_base = 64 then
l_hex :=
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise INVALID_NUMBER;
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;
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(64) :=
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
-- base 64 for Oracle extended rowid format
if p_from_base = 64 then
l_hex :=
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
end if;
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;
function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
function to_36( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 36 );
end to_36;
function to_64( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 64 );
end to_64;
end radix;
/
show errors
ol object new_value object
col file new_value file
col block new_value block
col row new_value row
select rowid
, substr(rowid,1,6) "OBJECT"
, substr(rowid,7,3) "FILE"
, substr(rowiD,10,6) "BLOCK"
, substr(rowid,16,3) "ROW"
, dbms_rowid.rowid_to_restricted(rowid,0 ) RESTRICTED
, dbms_rowid.rowid_to_absolute_fno(rowid,'SYS','DUAL') FNO
, dbms_rowid.rowid_object(rowid) OBJECT_ID
, dbms_rowid.rowid_block_number(rowid) BLOCK_NUM
, dbms_rowid.rowid_row_number(rowid) ROW_NUMBER
from dual
select
radix.to_dec('&&file',64) FNO
, radix.to_dec('&&object',64) OBJECT_ID
, radix.to_dec('&&block',64) BLOCK_NUM
, radix.to_dec('&&row',64)
Jared
Stephen Lee <Stephen.Lee_at_DTAG.Com>
Sent by: root_at_fatcity.com
01/28/2003 11:25 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: Base conversion
> -----Original Message-----
> Stephen,
>
> The code I posted earlier is easily adapted to do base 64.
>
OK. I figured out that "A" is zero (I think). Now, only 63 more to go!
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: Stephen.Lee_at_DTAG.Com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jan 28 2003 - 14:10:29 CST
 |
 |