Convert from hex-encoded CLOB to BLOB in Oracle

Question

I have some large binary content in hex form stored in a CLOB and want to convert that to a BLOB where the hex code is actual binary byte encoding:

DECLARE

  -- This would be my 8 byte hex-encoded binary content. Real content is much bigger
  c CLOB := 'cafebabe12345678';
  b BLOB;
BEGIN

  -- Need the implementation of this function
  b := hex_to_blob(c);
END;
/

What's the easiest way to do that in Oracle, using PL/SQL?

Drin

Dear @WernfriedDomscheit. This is a Stack Overflow feature, and it is encouraged. It is encouraged, because it will help all future visitors and the platform as a whole. I recommend this interesting read: stackoverflow.blog/2011/07/…. Ask a question and look out for the "answer your own question button" at the bottom. Reputation is a welcome side effect, sure. Cheers, mate. – Drin

@WernfriedDomscheit - Stack Overflow was designed to become a repository of great questions and answers. Self-answers have always been allowed and are welcome. We even let people post the question and answer at the same time (see the ask question page - there's an option to "answer your own question" there for a reason. – Commemorative

Answer 1

The desired function could look like this:

CREATE OR REPLACE
FUNCTION hex_to_blob (hex CLOB) RETURN BLOB IS
  b BLOB                := NULL;
  s VARCHAR2(4000 CHAR) := NULL;
  l NUMBER              := 4000;
BEGIN
  IF hex IS NOT NULL THEN
    dbms_lob.createtemporary(b, FALSE);

    FOR i IN 0 .. LENGTH(hex) / 4000 LOOP
      dbms_lob.read(hex, l, i * 4000 + 1, s);
      dbms_lob.append(b, to_blob(hextoraw(s)));
    END LOOP;
  END IF;

  RETURN b;
END hex_to_blob;

Drin

typo: hex_to_clob -> hex_to_blob. Also, why 4000 is somewhere as constant and elsewhere as literal? – Surefire

Thanks for mentioning the typo. Fixed. The l variable is passed as an IN OUT NOCOPY value to dbms_lob.read, which is why we need that variable. – Drin