0

I have data present in an oracle table in base 64 encrypted data format. This data is present in a clob field. 

create table tableA
(
id number, 
encoded_description clob);

insert into tableA 
(id, encoded_description) 
values
(1, 'Zm9sbG93IHVw');

insert into tableA 
(id, encoded_description) 
values
(2, 'dG8gbWFueSByZWQgZmxhZ3M=');

commit;

Table A output which contains base64 encoded data in encoded_description field: 

Table A: 

    ID, encoded_description 
    1   Zm9sbG93IHVw
    2   dG8gbWFueSByZWQgZmxhZ3M=



 create table tableB
    (
    id number, 
    decoded_description clob);

Table B: output after conversion 

    ID, Decoded_description
    1   <<Original Text>>
    2   <<Original Text>>

I want to load the data from tableA into tableB by decoding the base64 data in clob field to its original text form. How can i achieve this? Can i make use of any oracle functions to perform this conversion. Please help

user1751356
  • 565
  • 4
  • 14
  • 33
  • 3
    (pedantic comment): Base64 is not "encryption", but "encoding". – The Impaler May 21 '18 at 18:52
  • There's something missing here: Step #1, when you decode Base64, you get binary data (not chars). Then, Step #2, you need to convert binary to varchar by using... which enconding? UTF-8, ASCII, ISO-8859-1, other? – The Impaler May 21 '18 at 18:57
  • There is in fact a built-in, utl_encode.base64_decode, but it decodes to bytes, not characters. You can use utl_raw.cast_to_raw and utl_raw.cast_to_varchar2 to go back and forth, but you are likely to get garbage unless it really is plain text that has been encoded which would be pointless. – ewramner May 21 '18 at 19:10
  • 1
    Possible duplicate of [Base64 encoding and decoding in oracle](https://stackoverflow.com/questions/3804279/base64-encoding-and-decoding-in-oracle) – sticky bit May 21 '18 at 19:12
  • @TheImpaler convert to UTF-8 – user1751356 May 21 '18 at 19:19
  • @ewramner..Thank you. its a email body(mostly plain text) that has been encoded and saved in a oracle table. How do i do the conversion back? – user1751356 May 21 '18 at 19:20

1 Answers1

0

Assuming your data is large enough to actually need to be stored in a CLOB (i.e. it's several thousand characters), you'll need to do the following:

  1. Retrieve your CLOB from the table.
  2. Convert to a BLOB using utl_lob.converttoblob.
  3. Break the BLOB into parts using utl_lob.substr, which returns a RAW. Each part must be less than the RAW size limit of 2000 characters and a multiple of 4 bytes (why 4? see: Is it possible to base64-encode a file in chunks?)
  4. Use utl_encode.base64_decode to base64-decode the chunk.
  5. Use utl_raw.cast_to_varchar2 to convert the decoded chunk back to a varchar2.
  6. Concatenate all the decoded chunks into a CLOB.

Step #5 may lead to some character set problems if your encoded data is not in the expected character set, but I'm not entirely clear how that conversion works.

Obviously, you want to write a function for this so you never have to do it more than once.

eaolson
  • 14,717
  • 7
  • 43
  • 58
  • You can use `UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encoded_description)))` directly - provided the string is not longer than 32k characters. Otherwise you have to use CLOB/BLOB's in chunks, see https://stackoverflow.com/questions/3804279/base64-encoding-and-decoding-in-oracle/3806265#40852152 – Wernfried Domscheit May 22 '18 at 06:06
  • @eaolson. could u explain to me the purpose of step 2. Is it mandatory to convert to blob? – user1751356 May 22 '18 at 13:54
  • Not really. And I forgot the RAW limit is 32K not 2K, so WernfriedDomscheit's suggestion may be a much easier one. – eaolson May 22 '18 at 23:42