CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
Snippet Name: Convert between Decimal, Binary, Octal and Hex
Description: This one will solve a lot of your problems. :)
Examples:
select dbms_numsystem.dec2bin(22) from dual;
select dbms_numsystem.bin2dec('10110') from dual;
select dbms_numsystem.dec2oct(44978) from dual;
select dbms_numsystem.oct2dec(127662) from dual;
select dbms_numsystem.dec2hex(44978) from dual;
select dbms_numsystem.hex2dec('AFB2') from dual;
Also see: » Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» UNISTR
» TRANSLATE
» TO_YMINTERVAL
» TO_TIMESTAMP_TZ
» TO_TIMESTAMP
» TO_SINGLE_BYTE
» TO_NUMBER
» TO_NCLOB
» TO_NCHAR
» TO_MULTI_BYTE
» TO_LOB
» TO_DSINTERVAL
» TO_DATE
» TO_CLOB
» TO_CHAR
» TO_BINARY_FLOAT
» TO_BINARY_DOUBLE
» TIMESTAMP_TO_SCN
» SCN_TO_TIMESTAMP
» ROWIDTONCHAR
» ROWIDTOCHAR
» REFTOHEX
» RAWTONHEX
» RAWTOHEX
» NUMTOYMINTERVAL
» NUMTODSINTERVAL
» HEXTORAW
» DECOMPOSE
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 04th, 2009
SET serveroutput ON
CREATE OR REPLACE PACKAGE dbms_numsystem AS
FUNCTION bin2dec ( binval IN CHAR ) RETURN NUMBER ;
FUNCTION dec2bin ( N IN NUMBER ) RETURN VARCHAR2 ;
FUNCTION oct2dec ( octval IN CHAR ) RETURN NUMBER ;
FUNCTION dec2oct ( N IN NUMBER ) RETURN VARCHAR2 ;
FUNCTION hex2dec ( hexval IN CHAR ) RETURN NUMBER ;
FUNCTION dec2hex ( N IN NUMBER ) RETURN VARCHAR2 ;
END dbms_numsystem;
/
show errors
CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS
FUNCTION bin2dec ( binval IN CHAR ) RETURN NUMBER IS
i NUMBER ;
digits NUMBER ;
result NUMBER := 0 ;
current_digit CHAR ( 1 ) ;
current_digit_dec NUMBER ;
BEGIN
digits := LENGTH ( binval) ;
FOR i IN 1.. digits LOOP
current_digit := SUBSTR ( binval, i, 1 ) ;
current_digit_dec := TO_NUMBER ( current_digit) ;
result := ( result * 2 ) + current_digit_dec;
END LOOP ;
RETURN result;
END bin2dec;
FUNCTION dec2bin ( N IN NUMBER ) RETURN VARCHAR2 IS
binval VARCHAR2 ( 64 ) ;
N2 NUMBER := N;
BEGIN
WHILE ( N2 > 0 ) LOOP
binval := MOD ( N2, 2 ) || binval;
N2 := TRUNC ( N2 / 2 ) ;
END LOOP ;
RETURN binval;
END dec2bin;
FUNCTION oct2dec ( octval IN CHAR ) RETURN NUMBER IS
i NUMBER ;
digits NUMBER ;
result NUMBER := 0 ;
current_digit CHAR ( 1 ) ;
current_digit_dec NUMBER ;
BEGIN
digits := LENGTH ( octval) ;
FOR i IN 1.. digits LOOP
current_digit := SUBSTR ( octval, i, 1 ) ;
current_digit_dec := TO_NUMBER ( current_digit) ;
result := ( result * 8 ) + current_digit_dec;
END LOOP ;
RETURN result;
END oct2dec;
FUNCTION dec2oct ( N IN NUMBER ) RETURN VARCHAR2 IS
octval VARCHAR2 ( 64 ) ;
N2 NUMBER := N;
BEGIN
WHILE ( N2 > 0 ) LOOP
octval := MOD ( N2, 8 ) || octval;
N2 := TRUNC ( N2 / 8 ) ;
END LOOP ;
RETURN octval;
END dec2oct;
FUNCTION hex2dec ( hexval IN CHAR ) RETURN NUMBER IS
i NUMBER ;
digits NUMBER ;
result NUMBER := 0 ;
current_digit CHAR ( 1 ) ;
current_digit_dec NUMBER ;
BEGIN
digits := LENGTH ( hexval) ;
FOR i IN 1.. digits LOOP
current_digit := SUBSTR ( hexval, i, 1 ) ;
IF current_digit IN ( 'A' , 'B' , 'C' , 'D' , 'E' , 'F' ) THEN
current_digit_dec := ASCII ( current_digit) - ASCII ( 'A' ) + 10 ;
ELSE
current_digit_dec := TO_NUMBER ( current_digit) ;
END IF ;
result := ( result * 16 ) + current_digit_dec;
END LOOP ;
RETURN result;
END hex2dec;
FUNCTION dec2hex ( N IN NUMBER ) RETURN VARCHAR2 IS
hexval VARCHAR2 ( 64 ) ;
N2 NUMBER := N;
digit NUMBER ;
hexdigit CHAR ;
BEGIN
WHILE ( N2 > 0 ) LOOP
digit := MOD ( N2, 16 ) ;
IF digit > 9 THEN
hexdigit := CHR ( ASCII ( 'A' ) + digit - 10 ) ;
ELSE
hexdigit := TO_CHAR ( digit) ;
END IF ;
hexval := hexdigit || hexval;
N2 := TRUNC ( N2 / 16 ) ;
END LOOP ;
RETURN hexval;
END dec2hex;
END dbms_numsystem;
/
show errors
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.
Click here to find out more
31 users online
© 2009 psoug.org