What is a base64 RAW? How do I use it? 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.
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.