Conversion from hex to char

From: Marc Billiet <Marc.Billiet_at_hae.hydro.com>
Date: Thu, 17 Sep 1998 08:02:14 +0200
Message-ID: <3600A5E6.6DBB@hae.hydro.com>

Hello,

I'm looking for a function which does a conversion of some hexadecimal numbers to a varchar2. I have written such a function myself, but I don't know why there is no built-in function allowing this (which should be, I assume, much faster, because hextoraw is also rather fast).

The opposite is very easy:
  SELECT rawtohex('Oracle')
  INTO hexstr
  FROM dual;

hexstr := rawtohex('Oracle') doesn't work !

Hextoraw only accepts a raw to store the result. Any attempt to store the result in a varchar2 implicitly executes rawtohex again to store it in a hexadecimal format.
Does anyone know a quick way to do this ? I already tried it with scanning the hex string byte by byte, I tried it even by parsing the result of the dump-function, but it all seems to be rather slow.

My fastest function up to now is (I have tested a lot of ways to do it):

CREATE OR REPLACE FUNCTION HEXTOCHAR(P_HEXSTR VARCHAR2) RETURN VARCHAR2   IS
    l_rc VARCHAR2(2000);
    l_len NUMBER;
    cnt NUMBER;
    prevcnt NUMBER;
    l_dump VARCHAR2(2000);
    l_raw RAW(2000);
  BEGIN
    l_raw := hextoraw(p_hexstr);

unfortunately, l_dump := dump(l_raw,10) doesn't work SELECT dump(l_raw,10) INTO l_dump FROM dual; l_rc := NULL; prevcnt := instr(l_dump,':') + 2; cnt := instr(l_dump, ',', prevcnt); WHILE cnt <> 0 LOOP l_rc := l_rc || chr(to_number(substr(l_dump, prevcnt, cnt - prevcnt))); prevcnt := cnt + 1; cnt := instr(l_dump,',',prevcnt); END LOOP; l_rc := l_rc || chr(to_number(substr(l_dump, prevcnt))); RETURN l_rc; END hextochar; /

Thanks for any help,

Marc Billiet Received on Thu Sep 17 1998 - 01:02:14 CDT