我从Stackoverflow(忘记链接)获得了以下SQL Base64解码器。我对自己的要求有所修改。当我尝试执行时,解码只是部分工作。我的最大值为 base64编码每行的最大值。
不确定我缺少什么。
create or replace FUNCTION Decode64_CLOB3(IN_CLOB CLOB) RETURN CLOB IS
clobOriginal clob;
clobInBase64 clob;
substring varchar2(2000);
n pls_integer := 0;
substring_length pls_integer := 2000;
function from_base64(t in varchar2) return varchar2 is
begin
return
utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
end from_base64;
begin
select myCLobData into clobInBase64 from myClobTable where ID = 3;
clobInBase64 :=IN_CLOB;
n := 0;
clobOriginal := null;
while true loop
substring := dbms_lob.substr(clobInBase64,
least(substring_length, length(clobInBase64) - (substring_length * n + 1)));
if substring is null then
DBMS_OUTPUT.put_line('this is me 2');
exit;
end if;
clobOriginal := clobOriginal || from_base64(substring);
n := n + 1;
end loop;
return clobOriginal;
end;
update2
我进行了一些调试,发现问题与底条有关。该子字符串适用于(2000年)char的第一层。但是它无法移动下一个2000个字符。不确定是什么问题。有人可以建议吗?
您已将呼叫更改为 dbms_lob.substr()
,然后您删除了第三个参数,这是偏移量。由于您没有提供默认为1的默认值,因此每次循环围绕您获得相同的值 - 您编码的字符串中的前2000个字符。
好吧,您将获得2000次通话,然后n
足够大后,您将在您发布的代码中获得无效。因为您说它进入了一个"悬挂"状态,暗示您正在运行的内容略有不同。
经过一些轻微的修改,这似乎有效:
create or replace function decode64_clob3(p_clob_encoded clob)
return clob
is
l_clob_decoded clob;
substring varchar2(2048);
substring_length pls_integer := 2048;
n pls_integer := 0;
function from_base64(t in varchar2) return varchar2
is
begin
return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
end from_base64;
begin
dbms_lob.createtemporary(l_clob_decoded, false);
while true
loop
substring := dbms_lob.substr(p_clob_encoded, substring_length,
(substring_length * n) + 1);
if substring is null then
exit;
end if;
dbms_lob.append(l_clob_decoded, from_base64(substring));
n := n + 1;
/* protective check for infinite loop while debugging
if n > 10000 then
dbms_output.put_line('n is ' || n);
exit;
end if;
*/
end loop;
return l_clob_decoded;
end;
/
我稍微改变了障碍的处理;串联很好,但这更明确。我不确定我个人会打扰嵌套功能。而且我不确定least()
计算是否真的在添加任何内容。
但是,如果基本64编码的值是线条包装的,则可以使用ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
或ORA-14553: cannot perform a lob write operation inside a query
错误,通常是这种情况。第一个块是可以解码的,但是下一个块开始于无需耗时的新线,它丢掉了一切并产生垃圾。最终,该垃圾是无效的或导致其中一个例外之一的其他值。
因此,您需要在线圈长度的倍数中穿过clob时跟踪位置;并调整跟踪的位置以考虑额外的新线:
create or replace function decode64_clob3(p_clob_encoded clob)
return clob
is
l_clob_decoded clob;
l_substring varchar2(2048);
l_substring_length pls_integer := 2048;
l_pos pls_integer := 1;
begin
dbms_lob.createtemporary(l_clob_decoded, false);
while l_pos <= dbms_lob.getlength(p_clob_encoded)
loop
l_substring := dbms_lob.substr(p_clob_encoded, l_substring_length, l_pos);
if l_substring is null then
exit;
end if;
dbms_lob.append(l_clob_decoded,
utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(l_substring))));
/* Adjust l_pos to skip over CR/LF chars if base64 is line-wrapped */
l_pos := l_pos + length(l_substring);
while dbms_lob.substr(p_clob_encoded, 1, l_pos) in (chr(10), chr(13)) loop
l_pos := l_pos + 1;
end loop;
end loop;
return l_clob_decoded;
end;
/
或首先剥离任何潜在的newline/运输返回字符:
create or replace function decode64_clob3(p_clob_encoded clob)
return clob
is
l_clob_encoded clob;
l_clob_decoded clob;
l_substring varchar2(2048);
l_substring_length pls_integer := 2048;
l_pos pls_integer := 1;
begin
l_clob_encoded := replace(replace(p_clob_encoded, chr(10), null), chr(13), null);
dbms_lob.createtemporary(l_clob_decoded, false);
while l_pos <= dbms_lob.getlength(l_clob_encoded)
loop
l_substring := dbms_lob.substr(l_clob_encoded, l_substring_length, l_pos);
if l_substring is null then
exit;
end if;
dbms_lob.append(l_clob_decoded,
utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(l_substring))));
l_pos := l_pos + length(l_substring);
end loop;
return l_clob_decoded;
end;
/
这与包装和未包装的base64值一起使用。
set serveroutput on
with t (c) as (
select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('Hello world'))) from dual
)
select c, decode64_clob3(c)
from t;
C
--------------------------------------------------------------------------------
DECODE64_CLOB3(C)
--------------------------------------------------------------------------------
SGVsbG8gd29ybGQ=
Hello world
还用大于substring_length
值大的编码值进行了测试。