Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for
Did you mean:

## Decimal to hexadecimal conversion vith Oracle

Respected Contributor

## Decimal to hexadecimal conversion vith Oracle

Hi!

Is there a way to convert decimal value to hexadecimal within an Oracle SQL request?
6 REPLIES
Esteemed Contributor

## Re: Decimal to hexadecimal conversion vith Oracle

Bonjour Sylvain,

If think there is no direct SQL function to do this.

Take a look at :

http://www.revealnet.com/pipelines/plsql/archives.htm#code27

Cheers

Nicolas
All different, all Unix
Honored Contributor

## Re: Decimal to hexadecimal conversion vith Oracle

Hi,

You can get the hexadecimal value for a number by using the function TO_CHAR in SQLPLUS.

To convert a decimal number to hexadecimal For example, the number "123" will be converted to "7B".

SQL> select to_char(123,'XXX') from dual

output : 7B

You must ensure that your format string is as large as the biggest value you will need to convert, but this is a built-in feature.

Or you can write a PLSQL package to define your own function for the conversion. Eg:- below.

FUNCTION dec_to_hex (decin IN NUMBER) RETURN VARCHAR2 IS
v_decin NUMBER;
v_next_digit NUMBER;
v_result varchar(2000);
BEGIN
v_decin := decin;
WHILE v_decin > 0 LOOP
v_next_digit := mod(v_decin,16);
IF v_next_digit > 9 THEN
IF v_next_digit = 10 THEN v_result := 'A' || v_result;
ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
ELSE raise_application_error(-20600,'Untrapped exception');
END IF;
ELSE
v_result := to_char(v_next_digit) || v_result;
END IF;
v_decin := floor(v_decin / 16);
END LOOP;
RETURN v_result;
END dec_to_hex;

v_return := v_return + TO_NUMBER(v_charval) * POWER(2,v_power);

I hope this helps.

Indira A

Never give up, Keep Trying
Honored Contributor

## Re: Decimal to hexadecimal conversion vith Oracle

Oracle 9i has a function rawtohex() which returns the raw string x converted to hexadecimal as the following example shows:

SQL> select rawtohex(job_id) from jobs;

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Honored Contributor

## Re: Decimal to hexadecimal conversion vith Oracle

hi,

In 8i (8.1) and up, it is simply:
select to_char( number, 'xxxxxxx' ) from t;

hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Respected Contributor

## Re: Decimal to hexadecimal conversion vith Oracle

Well, this Oracle function seem not to work in my case:

---------- ------
957054740 ######

---------- --------------------------------------------
957054740 C50A3A063029

Esteemed Contributor

## Re: Decimal to hexadecimal conversion vith Oracle

Does it work with on X for every figure ?

select to_char(957054740,'XXXXXXXXX') from dual;

TO_CHAR(95
----------
390B7F14

Cheers

Nicolas
All different, all Unix