SQL decode base64 in sql developer

Question

I got the below sql base64 decoder from stackoverflow (forgot the link). I have slightly modified to my requirement. When I try to execute, the decoding is only partially working. I have max of 10MB worth of base64 encoded clob data per row in my DB.

Not sure what is that I am missing.

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

I did some debugging and found out that the issue is with the substring. The substring works fine for the firstloop of (2000) char. but its not able to move the the next 2000 char. Not sure what is the problem. Can some one advise.

Answer 1

You've changed the call to dbms_lob.substr() and in doing so you've removed the third argument, which is the offset. Since you aren't providing that it defaults to 1, so every time around the loop you're getting the same value - the first 2000 characters from your encoded string.

Well, you'll get 2000 for lots of calls, then once n gets big enough you'll get null, with the code you've posted. Since you said it goes into a 'hung' state that suggests what you're running is slightly different.

With some slight modifications this seemed to work:

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;
/

I changed the CLOB handling slightly; concatenation is fine but this is a little more explicit. I'm not sure I'd bother with the nested function though, personally. And I'm not sure the least() calculation is really adding anything.

But this can error with either ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 or ORA-14553: cannot perform a lob write operation inside a query if the base64-encoded value is line-wrapped, which is usually the case. The first chunk is decoded OK, but the next chunk starts with an unconsumed newline which throws everything off and produces garbage; eventually that garbage is null or some other value that causes one of those exceptions.

So you need to track the position as you move through the CLOB, in multiples of the line-wrap length; and adjust the tracked position to take extra newlines into account:

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;
/

Or strip out any potential newline/carriage return characters first:

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;
/

This works with wrapped and unwrapped base64 values.

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

Also tested with encoded values that are larger than the substring_length value.