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.CAL L);
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;

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/sam...mple8i_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.CAL L);
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.CAL L);
> 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;