oracle数据库之base64编码

  这几天在做一个数据库业务时，需要用到oracle数据库的系统函数utl_encode.base64_encode()。由于之前没用过，就从网上找了一些相关的资料以作参考，网上大部分没有过多地介绍，基本只给出以下用法：
1.base64 的解码函数
select utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(‘YWJjZA==’))) from dual

2.base64 的编码函数
select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(‘abcd’))) from dual
  上述用法没有问题，出现问题的是utl_encode.base64_encode()这个函数。这个函数经过试验发现，当其输入参数的字符个数(这里只考虑英文字符，中文字符的话应该考虑其字节数)大于等于48时，编码生成的字符串以64个字符为一组在其后添加回车换行字符。正是这个坑搞得我半天找不出是什么原因导致从指定表查询出的字符串莫名其妙地被换行了，以致后续业务数据不对。大家可以参照下面的示例验证一下：

这是我的环境：

SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64biPL/SQL Release 10.2.0.1.0 - ProductionCORE    10.2.0.1.0  ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production

这是验证方式（直接复制到sqlplus上运行）：
三个星号(*)是为了直观地显示出有无回车换行符而加在字符串末尾。

declare    strBefore1 varchar2(100) := '123456789012345678901234567890123456789012345678';    strAfter1 varchar2(100);    strBefore2 varchar2(100) := '12345678901234567890123456789012345678901234567';    strAfter2 varchar2(100);    strBefore3 varchar2(100) := '1234567890123456789012345678901234567890123456789';    strAfter3 varchar2(100);begin    strAfter1 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(strBefore1)));    strAfter2 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(strBefore2)));    strAfter3 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(strBefore3)));    dbms_output.put_line('length of strBefore1:' || length(strBefore1));    dbms_output.put_line('length of strAfter1:' || length(strAfter1));    dbms_output.put_line('strBefore1:');    dbms_output.put_line(strBefore1 || '***');    dbms_output.put_line('strAfter1:');    dbms_output.put_line(strAfter1 || '***');    dbms_output.put_line('length of strBefore2:' || length(strBefore2));    dbms_output.put_line('length of strAfter2:' || length(strAfter2));    dbms_output.put_line('strBefore2:');    dbms_output.put_line(strBefore2 || '***');    dbms_output.put_line('strAfter2:');    dbms_output.put_line(strAfter2 || '***');    dbms_output.put_line('length of strBefore3:' || length(strBefore3));    dbms_output.put_line('length of strAfter3:' || length(strAfter3));    dbms_output.put_line('strBefore3:');    dbms_output.put_line(strBefore3 || '***');    dbms_output.put_line('strAfter3:');    dbms_output.put_line(strAfter3 || '***');end;/

这是实验结果：
  从结果中可以看出strAfter31和strAfter3字符串中插有回车换行符，要证明是这两个字符，各位可自行通过substr()函数(获取子串)和ascii()函数获取多出的两个字符的ascii值。另外提一点，正常的base64编码后的字符串的长度是4的倍数。
  对此问题我没有找到相关资料，oracle官方文档也没有说明。
文档地址：http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_encode.htm#CACECFHF

length of strBefore1:48length of strAfter1:66strBefore1:123456789012345678901234567890123456789012345678***strAfter1:MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc4***length of strBefore2:47length of strAfter2:64strBefore2:12345678901234567890123456789012345678901234567***strAfter2:MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc=***length of strBefore3:49length of strAfter3:70strBefore3:1234567890123456789012345678901234567890123456789***strAfter3:MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc4OQ==***PL/SQL procedure successfully completed.

解决办法：
要解决这个问题其实很简单(当时想复杂了，本想分组逐个去掉回车换行符)，就是使用替换函数

1. regexp_replace(source_string, expression [, replace_string [, position [, occurrence [, match_parameter]]]])这是一个正则表达式替换函数。source_stirng 待搜索的字符串。expression 一个用来描述要寻找文本的模式的正则表达式。replace_string 查找替换中要替换成的文本。position 源字符串source_string中搜索开始的字符位置。默认是1。occurrence 要定位的模式出现的次数。默认是1，也就是第一次匹配。match_parameter 这个字符串指定选项，改变正则表达式匹配引擎的行为。针对当前问题的用法：regexp_replace(strAfter, '\s', '')'\s'会匹配所有空白字符，包括回车符、换行符、制表符、垂直制表符、跳页符、空格等。2. replace(string1, match_string, replace_string)这是一个普通的替换函数。把字符串string1中的match_string替换为replace_string，并返回替换后的字符串。这一函数同时实现了查找替换功能。针对当前问题的用法：replace(strAfter, chr(10) || chr(13), '')chr(10)表示换行符；chr(13)表示回车符。3. replace(string1, match_string)这是一个普通的替换函数。把string1中的所有match_string都删除掉并返回删除后的字符串。针对当前问题的用法：replace(strAfter, chr(10) || chr(13))注：这三个函数的解释摘抄自《oracle PL/SQL程序设计 第五版》上册216页，下册1097页。

这里只用了正则表达式替换函数，好处是所有空白都能替换为空字符，而不管是否是回车换行字符。

declare    strBefore1 varchar2(100) := '123456789012345678901234567890123456789012345678';    strAfter1 varchar2(100);    strBefore2 varchar2(100) := '12345678901234567890123456789012345678901234567';    strAfter2 varchar2(100);    strBefore3 varchar2(100) := '1234567890123456789012345678901234567890123456789';    strAfter3 varchar2(100);begin    strAfter1 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(strBefore1)));    strAfter2 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(strBefore2)));    strAfter3 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(strBefore3)));    strAfter1 := regexp_replace(strAfter1, '\s', '');    strAfter2 := regexp_replace(strAfter2, '\s', '');    strAfter3 := regexp_replace(strAfter3, '\s', '');    dbms_output.put_line('length of strBefore1:' || length(strBefore1));    dbms_output.put_line('length of strAfter1:' || length(strAfter1));    dbms_output.put_line('strBefore1:');    dbms_output.put_line(strBefore1 || '***');    dbms_output.put_line('strAfter1:');    dbms_output.put_line(strAfter1 || '***');    dbms_output.put_line('length of strBefore2:' || length(strBefore2));    dbms_output.put_line('length of strAfter2:' || length(strAfter2));    dbms_output.put_line('strBefore2:');    dbms_output.put_line(strBefore2 || '***');    dbms_output.put_line('strAfter2:');    dbms_output.put_line(strAfter2 || '***');    dbms_output.put_line('length of strBefore3:' || length(strBefore3));    dbms_output.put_line('length of strAfter3:' || length(strAfter3));    dbms_output.put_line('strBefore3:');    dbms_output.put_line(strBefore3 || '***');    dbms_output.put_line('strAfter3:');    dbms_output.put_line(strAfter3 || '***');end;/

这是上述解决办法的实验结果：

length of strBefore1:48length of strAfter1:64strBefore1:123456789012345678901234567890123456789012345678***strAfter1:MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc4***length of strBefore2:47length of strAfter2:64strBefore2:12345678901234567890123456789012345678901234567***strAfter2:MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc=***length of strBefore3:49length of strAfter3:68strBefore3:1234567890123456789012345678901234567890123456789***strAfter3:MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc4OQ==***PL/SQL procedure successfully completed.

  上述说明如有问题，欢迎批评指正。