Decimal to hexadecimal conversion vith Oracle

[Sylvain CROUET]

Hi!

Is there a way to convert decimal value to hexadecimal within an Oracle SQL request?

[Nicolas Dumeige]

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

[Indira Aramandla]

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

[Sanjay Kumar Suri]

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

[Yogeeraj_1]

hi,

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


hope this helps too!

regards
Yogeeraj

[Sylvain CROUET]

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

SQL> select ip_address, to_char(ip_address, 'XXXXX') from opc_node_names where node_name like 'cexi01%';

IP_ADDRESS TO_CHA
---------- ------
957054740 ######


SQL> select ip_address, rawtohex(ip_address) from opc_node_names where node_name like 'cexi01%';

IP_ADDRESS RAWTOHEX(IP_ADDRESS)
---------- --------------------------------------------
957054740 C50A3A063029


IP_ADDRESS's type is NUMBER(12)

[Nicolas Dumeige]

Does it work with on X for every figure ?

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

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

Cheers

Nicolas