utl_encode包中的下面两个函数是用来进行Base64编码和解码的
/*----------------------------------------------------------------*/
/* BASE64_ENCODE */
/*----------------------------------------------------------------*/
function base64_encode(r in raw) return raw;
/* BASE64_ENCODE */
/*----------------------------------------------------------------*/
function base64_encode(r in raw) return raw;
/*----------------------------------------------------------------*/
/* BASE64_DECODE */
/*----------------------------------------------------------------*/
function base64_decode(r in raw) return raw;
/* BASE64_DECODE */
/*----------------------------------------------------------------*/
function base64_decode(r in raw) return raw;
下面看看使用方法:
SQL> select utl_encode.BASE64_ENCODE('denglt2') from dual;
select utl_encode.BASE64_ENCODE('denglt2') from dual
ORA-01465: 无效的十六进制数字
SQL> select utl_encode.BASE64_ENCODE(utl_raw.cast_to_raw('denglt2')) from dual;
UTL_ENCODE.BASE64_ENCODE(UTL_R
--------------------------------------------------------------------------------
5A4756755A3278304D673D3D
select utl_encode.BASE64_ENCODE('denglt2') from dual
ORA-01465: 无效的十六进制数字
SQL> select utl_encode.BASE64_ENCODE(utl_raw.cast_to_raw('denglt2')) from dual;
UTL_ENCODE.BASE64_ENCODE(UTL_R
--------------------------------------------------------------------------------
5A4756755A3278304D673D3D
如果对Base64有点了解的话,这儿的结果肯定不是Base64的编码('denglt2'对3求余数后为1,按照Base64的编码规范出来的结果最后两个字符应该为==).我再进行如下的转换
SQL> select utl_raw.cast_to_varchar2('5A4756755A3278304D673D3D') from dual;
UTL_RAW.CAST_TO_VARCHAR2('5A47
--------------------------------------------------------------------------------
ZGVuZ2x0Mg==
UTL_RAW.CAST_TO_VARCHAR2('5A47
--------------------------------------------------------------------------------
ZGVuZ2x0Mg==
这儿就是base64的编码了。
现在我们再进行解码:
SQL> select utl_raw.cast_to_varchar2(utl_encode. base64_decode(utl_raw.cast_to_raw('ZGVuZ2x0Mg=='))) from dual;
UTL_RAW.CAST_TO_VARCHAR2(UTL_E
--------------------------------------------------------------------------------
denglt2
UTL_RAW.CAST_TO_VARCHAR2(UTL_E
--------------------------------------------------------------------------------
denglt2
这儿有一点可能有点迷糊 "function base64_encode(r in raw) return raw"的返回类型是raw.实际上反回的是编号后字符串的二进制raw数据,同时在Oracle里raw与十六进制的字符串是可以相互自动转换的,所以看到的结果是5A4756755A3278304D673D3D。
raw与十六进制的字符串是可以相互自动转换的例子(但BLOB类型不能够):
SQL> create table t_blob (b blob);
Table created
SQL> insert into t_blob (b) values (utl_raw.cast_to_raw('denglt'));
1 row inserted
SQL> commit;
Commit complete
Table created
SQL> insert into t_blob (b) values (utl_raw.cast_to_raw('denglt'));
1 row inserted
SQL> commit;
Commit complete
SQL> set serveroutput on ;
SQL> declare
2 vB long raw;
3 vStr varchar2(2000);
4 begin
5 select b into vB from t_blob where rownum=1;
6 dbms_output.put_line(utl_raw.cast_to_varchar2(vB));
7 vStr := vB;
8 dbms_output.put_line(vStr);
9 vB := vStr;
10 dbms_output.put_line(utl_raw.cast_to_varchar2(vB));
11 end;
12 /
denglt
64656E676C74
denglt
PL/SQL procedure successfully completed
2 vB long raw;
3 vStr varchar2(2000);
4 begin
5 select b into vB from t_blob where rownum=1;
6 dbms_output.put_line(utl_raw.cast_to_varchar2(vB));
7 vStr := vB;
8 dbms_output.put_line(vStr);
9 vB := vStr;
10 dbms_output.put_line(utl_raw.cast_to_varchar2(vB));
11 end;
12 /
denglt
64656E676C74
denglt
PL/SQL procedure successfully completed
declare
vB blob; --换成blob
vStr varchar2(2000);
begin
select b into vB from t_blob where rownum=1;
dbms_output.put_line(utl_raw.cast_to_varchar2(vB));
vStr := vB;
dbms_output.put_line(vStr);
vB := vStr;
dbms_output.put_line(utl_raw.cast_to_varchar2(vB));
end;
ORA-06550: 第 8 行, 第 10 列:
PLS-00382: 表达式类型错误
ORA-06550: 第 8 行, 第 2 列:
PL/SQL: Statement ignored
ORA-06550: 第 10 行, 第 8 列:
PLS-00382: 表达式类型错误
ORA-06550: 第 10 行, 第 2 列:
PL/SQL: Statement ignored
vB blob; --换成blob
vStr varchar2(2000);
begin
select b into vB from t_blob where rownum=1;
dbms_output.put_line(utl_raw.cast_to_varchar2(vB));
vStr := vB;
dbms_output.put_line(vStr);
vB := vStr;
dbms_output.put_line(utl_raw.cast_to_varchar2(vB));
end;
ORA-06550: 第 8 行, 第 10 列:
PLS-00382: 表达式类型错误
ORA-06550: 第 8 行, 第 2 列:
PL/SQL: Statement ignored
ORA-06550: 第 10 行, 第 8 列:
PLS-00382: 表达式类型错误
ORA-06550: 第 10 行, 第 2 列:
PL/SQL: Statement ignored
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/195110/viewspace-677977/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/195110/viewspace-677977/