0

Oracle RAW format when read and converted to base64 works fine:

UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob, len, 1)))

The above function requires looping though. But, I am trying to achieve this with mix-match of Oracle and Java:

DBMS_LOB.substr(column, 2000) AS col_1,
DBMS_LOB.substr(column, 2000, 2001) AS col_2,
DBMS_LOB.substr(column, 2000, 4001) AS col_3

I know the length of the column to be fixed (5000 or so). When I do the above I get string in the HEX format (probably ?) - sample below

C7A4B364A2C1FC7CFCCD35D0559BD70CA9E1FA09237E099A7B6BC1FFC106AD2BFE9599B

Now, I can read it as string in Java, but doing base64 encoding after reading it as string does not yield the same result as the Oracle equivalent (above first one). So, Oracle blob how to convert to string after getting string from dbms_lob.substr? Also, the above looks hex but hex decode also fails.

To be precise, what is the equivalent of UTL_RAW.cast_to_varchar2 in Java?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
user3499836
  • 87
  • 1
  • 9
  • Got to answer my own question. The data from dbms_lob.substr is hexadecimal data. Doing the following in java one can convert to base64 encoded string - ```new String (Base64.getEncoder().encode(Hex.decodeHex(str)))``` where str -> is the concatenated strings from all 2000 strings above - col_1, col_2, col_3 etc. – user3499836 Feb 10 '22 at 04:46
  • If you managed to solve your problem, post the solution as an *answer* not a *comment*, and accept the answer after the timeout. As an aside, your solution contradicts the claim in your question that _"hex decode also fails"_. – Mark Rotteveel Feb 12 '22 at 11:23

0 Answers0