Convert fractional number to Hexadecimal in Oracle

Question

I have a table in database with fractional value- value 14.65 45 7458.34 34.69 4.7 34

I want to convert this fractional value to hexadecimal.

But to_hex function only converts decimal value(14) and not (14.65).

Tried with to_char also- SELECT TO_CHAR(15.33, 'X') FROM DUAL; --->output: F

I don't want to ceil or floor the value and print Hexadecimal number. I want 15.33 --->F.547AE147

Answer 1

Split the values into the integer and the decimal parts and use TO_CHAR on them separately:

WITH table_name (value) AS (
  SELECT  15.33 FROM DUAL UNION ALL
  SELECT -15.33 FROM DUAL
)
SELECT value,
       CASE WHEN value < 0 THEN '-' END
       || TO_CHAR(TRUNC(ABS(value)), 'fmXXXXXXXXXX')
       || '.'
       || TO_CHAR(
            ABS(MOD(value, 1))*TO_NUMBER('FFFFFFFF', 'XXXXXXXX'),
            'fm0XXXXXXX'
          ) AS hex
FROM   table_name;

Which outputs:

VALUE	HEX
15.33	F.547AE147
-15.33	-F.547AE147

db<>fiddle here