Re: base64 encoding

From: Kofi <kofi_at_nimoh.com>
Date: 20 Dec 2004 04:34:51 -0800
Message-ID: <1103546091.691622.62390@c13g2000cwb.googlegroups.com>

Hi Jeroen,

I don't know, you may have solved this problem already by now. If not, then I had exactly the same problem as you had, and your pl/sql code gave me a headstart actually.

It turns out that the utl_encode.base64_encode function does not create the base64 string according to the MIME standard (lines no longer than 76 characters). A few more searches led me to this example of sending base64 encoded attachments in email. Now of course, that would have to follow the standard eh? It does:

http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/mailexample8i_sql.txt

If you look at the line where the "demobase64" function is called you'll see how they do it, which is exactly what you should be doing in your method. Unfortunately this means concatenating chunks of only 57 characters at a time - if you have really large blobs this could be a problem, if you don't (we don't, no more than 32K fortunately), then you are fine. Your getBase64String function now becomes:

CREATE OR REPLACE Function getbase64String( lv_blob blob ) Return clob is
Result clob;
resultString VARCHAR2(80);
resultString1 clob;

l_amt number default 57;
l_raw raw(80);
l_offset number default 1;
l_clob clob;

BEGIN
dbms_output.put_line( 'length blob: ' || dbms_lob.getlength( lv_blob ) );
begin

DBMS_LOB.CREATETEMPORARY(resultString1,FALSE,DBMS_LOB.CALL); DBMS_LOB.CREATETEMPORARY(result,FALSE,DBMS_LOB.CALL); loop

dbms_lob.read( lv_blob, l_amt, l_offset, l_raw );

l_offset := l_offset + l_amt;

resultString := utl_raw.cast_to_varchar2( utl_encode.base64_encode( l_raw ) ) || utl_tcp.CRLF;

resultString1:=to_clob(resultString);
dbms_lob.append(result,resultString1);

end loop;

exception
when no_data_found then
null;
end;

RETURN ( result );

END getbase64String;
/

You will find this works perfectly (slowly)!

Jeroen van Sluisdam wrote:
> Hi,
>
> We have to provide a conversion of blobs to base64 in order to
> exchange data with
> a 3th party. I wrote the following function which is used to format
an
> xml-message.
> Problem is the supplier reads this data with a .net decode of base64
> complains it's invalid and it
> sees some lines with more than 76 characters which seems to be not
> correct.
> Any ideas what is wrong are greatly appreciated.
> 9.2.0.4 HPUX11.11
>
> Tnx,
>
> Jeroen
>
> Function getbase64String( lv_blob blob )
> Return clob is
> Result clob;
> resultString VARCHAR2(4096);
> resultString1 clob;
> l_amt number default 2048;
> l_raw raw(4096);
> l_offset number default 1;
> l_clob clob;
> BEGIN
> dbms_output.put_line('length blob: ' || dbms_lob.getlength( lv_blob
> ) );
> begin
> DBMS_LOB.CREATETEMPORARY(resultString1,FALSE,DBMS_LOB.CALL);
> DBMS_LOB.CREATETEMPORARY(result,FALSE,DBMS_LOB.CALL);
> loop
> dbms_lob.read( lv_blob, l_amt, l_offset, l_raw );
> l_offset := l_offset + l_amt;
> -- dbms_output.put_line(' voor resultstring');
> resultString := utl_raw.cast_to_varchar2(
> utl_encode.base64_encode( l_raw ) );
> -- dbms_output.put_line(' na resultsstring');
> resultString1:=to_clob(resultString);
> dbms_lob.append(result,resultString1);
> end loop;
> exception
> when no_data_found then
> null;
> end;
> -- dbms_output.put_line('length:'||dbms_lob.getlength(result));
> RETURN ( result );
> END getbase64String;
Received on Mon Dec 20 2004 - 06:34:51 CST