4

I have created the following function in Oracle to convert my string to BASE64 and I am getting a new line after 64 characters. The function is as below

function to_base64(t in varchar2) return varchar2 is
begin
  return utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
end to_base64;

My input is tobase64('ACae23b41949dbcfa8cd2c8b114bb81af4:909f2edb3e9b8c7e9c545d2942aa8576')

and output I am getting is as follows where new line is added after 64 characters. QUNhZTIzYjQxOTQ5ZGJjZmE4Y2QyYzhiMTE0YmI4MWFmNDo5MDlmMmVkYjNlOWI4 YzdlOWM1NDVkMjk0MmFhODU3Ng==

Can someone suggest how can I remove the new line or any change required in the function?

Kinjan Bhavsar
  • 1,439
  • 28
  • 58

1 Answers1

8

You can use replace() to remove the two characters individually:

function to_base64(t in varchar2) return varchar2 is
begin
  return replace(
    replace(
      utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t))),
      chr(10)),
    chr(13)
  );
end to_base64;

or as a pair:

function to_base64(t in varchar2) return varchar2 is
begin
  return replace(
    utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t))),
    chr(13)||chr(10)
  );
end to_base64;

and then either way

select to_base64('ACae23b41949dbcfa8cd2c8b114bb81af4:909f2edb3e9b8c7e9c545d2942aa8576')
from dual;

gives the result on a single line:

QUNhZTIzYjQxOTQ5ZGJjZmE4Y2QyYzhiMTE0YmI4MWFmNDo5MDlmMmVkYjNlOWI4YzdlOWM1NDVkMjk0MmFhODU3Ng==

But it's common for base64 values to include them, and decoding should ignore them anyway.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks. It worked like a charm!. But only this is the solution just replacing? – Kinjan Bhavsar Nov 15 '18 at 12:29
  • You could use translate instead of replace. But there doesn't seem to be any way to prevent the newlines being added in the first place, if that's what you're really after. – Alex Poole Nov 15 '18 at 12:31
  • Why is it common for them to include them? Can you point to a doc which explains it? – Jimenemex Dec 07 '18 at 22:00
  • 1
    Well, [the MIME encoding](https://tools.ietf.org/html/rfc2045#section-6.8) says it "must be represented in lines of no more than 76 characters each". SSL certificates etc. tend to be generated with line breaks too. Not sure why, other than for readability. – Alex Poole Dec 07 '18 at 22:27