5

Oracle has a function for encoding a binary value, specifically a RAW type, into base64: BASE64_ENCODE. This makes sense. I want to turn binary into something I can more easily port across systems.

However, in addition to accepting a binary RAW for its input, it also returns a binary RAW for its output. I find this extremely confusing, as the purpose of base64 is to encode the bytes in a text format.

So what does this output RAW actually contain? What is the proper way to convert it into an actual text type (e.g., VARCHAR2)?

I additionally assume that BASE64_DECODE expects its input to be in the same format as the output of BASE64_ENCODE, but please note if that's not the case.

I'm specifically using Oracle 11.2, but I doubt this has changed since it was introduced. Feel free to correct my if I'm wrong on that.

  • are you talking about utl_encode.base64_encode? – JSapkota Feb 11 '16 at 1:04
  • @JSapkota Yes. I linked to it in the question. If there's an alternative that actually returns a text type, I'm more than interested. – jpmc26 Feb 11 '16 at 1:09
  • 1
    have you used hextoraw to convert it? select utl_raw.cast_to_varchar2(hextoraw('hex value')) from dual; – JSapkota Feb 11 '16 at 1:25
  • @JSapkota True, I could use that to spit it out in hexadecimal instead. Not an answer, but a decent workaround. Thanks. – jpmc26 Feb 11 '16 at 3:23
  • Well have you tried to use UTL_ENCODE.TEXT_ENCODE?. If you want to encode character set sensitive data i mean text and send to target and decode to original text then this might be the appropriate function. – JSapkota Feb 12 '16 at 3:49
3

It order to understand your problem and to simulate it I have generated one scenario. First of all I would like to tell how the Base64 works as general.
1) It takes the ASCII text.
2) Converts it to Hexadecimal values(Decimal to Hex conversion).
3) Represents the text in bits(groups as byte).
4) Since the Base64 takes three bytes of data. That is, it takes 24 bits of data and returns as 32 bit encoded characters. So bit should be represented as 6 bits string.
5) Then these 6 bits string will be converted to decimal number.
6) Then it selects the ascii characters(A-Z,a-z,0-1,+ and /) according to the decimal number calculated in step 4.

Now i want to show this practically and also want to shows how the Oracle's Base64 encoding returns RAW value.
Lets say we want to encode the text 'ABCD'.
1) Lets convert this to RAW data which is hexadecimal values.

SQL> SELECT UTL_RAW.CAST_TO_RAW('ABCD') FROM dual;
41424344

We have got the hex values of 'ABCD' that is '41424344'.
2) Lets encode this hex using Base64. In Oracle we have got UTL_ENCODE package to encode it, which takes RAW and returns encoded value as RAW.

SQL> SELECT UTL_ENCODE.BASE64_ENCODE('41424344') FROM dual;
51554A4452413D3D

Let me explain how we have got this values according the encoding steps that i have mentioned above.
1) Our text is 'ABCD'.
2) Hex representation is '41424344'.
3) Binary representation of text grouped by bytes.

41->01000001 42->01000010 43->01000011 44->01000100

4) Bit representation of text grouped by 6 bits.

010000 010100  001001  000011  010001  000000

(Adds '=' sign to output text as we added 0s to make it 6bits)
5) Decimal representation of 6 bits representation of text.

010000->16  010100->20  001001->9  000011->3  010001->17  000000->0

6) Now lets select from base64 characters(A-Z,a-z,0-9,+,/ altogether 64 characters:0-63) according to the decimal values.

16->Q, 20->U, 9->J,3->D,17->R,0->A

Output:QUJDRA==
But Oracle Base64 encoding returns this text as RAW(Hex Values). In order to get encoded string as text instead of RAW we can cast it to VARCHAR2.

SQL>SELECT UTL_RAW.CAST_TO_VARCHAR2('51554A4452413D3D') FROM dual;
QUJDRA==

If we convert the ASCII value of these text into hex we get the encoded string returned by Base64 encoding.

Character  ASCII  Hex
Q          81       51
U          85       55
J          74       4A
D          68       44
R          82       52
A          65       41
=          61       3D
=          61       3D

Now lets convert this text to hex.

SQL>SELECT UTL_RAW.CAST_TO_RAW('QUJDRA==') FROM dual;
51554A4452413D3D

We get the original encoded string

51554A4452413D3D


Now in order to get my text back to original text 'ABCD', lets decode it.

SQL>SELECT UTL_ENCODE.BASE64_DECODE('51554A4452413D3D') FROM dual;
41424344
SQL>SELECT UTL_RAW.CAST_TO_VARCHAR2('41424344') FROM dual;
ABCD

Conclusion: So if we want to get encoded value as text instead of Hex we can use UTL_RAW.CAST_TO_VARCHAR2 which simply converts Hex to varchar2.

  • Ok, but why is it doing this? – eckes Nov 28 '17 at 4:53
  • you will fail on real binary data (like images) for example as you will hit Oracle buffer size on character data types. But good review on what is possible. – kensai Mar 6 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.