Databases
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