xlsx_builder_pkg: Comments in a large spreadsheet raise ORA-06502

[eaolson]

In a XLSX workbook large enough that one of the XML files is over the 32767 byte limit, adding a comment will cause ORA-06502, PL/SQL: numeric or value error to be raised when finish() is called.

To replicate:

declare
    b blob;
begin
    xlsx_builder_pkg.clear_workbook;
    xlsx_builder_pkg.new_sheet( 'Sheet1' );
    for i in 1..10000 loop
        xlsx_builder_pkg.cell( 1, i, 'foo' );
    end loop;
    xlsx_builder_pkg.comment( p_row => 1, p_col => 1, 
        p_text => 'This is my note', p_author => 'me' );
    b := xlsx_builder_pkg.finish;
end;

[eaolson]

I think this is happening on line 1358 in xlsx_builder_pkg.pkb: t_xxx := t_xxx || '<legacyDrawing r:id="rId' || ( workbook.sheets( s ).hyperlinks.count() + 1 ) || '"/>';

I don't quite understand why, but when t_xxx is large, concatenating a number raises this error. Adding TO_CHAR prevents this.

Oh, and this line only happens when workbook.sheets( s ).comments.count() > 0, but is adding the hyperlinks count. Is that intentional?

[eaolson]

Ah, this is a known Oracle bug happening in 10.1 and above. See Doc ID 746261.1. Solution is to use dbms_lob.writeappend or to_char.

[mortenbra]

Thanks for reporting this, I'm marking it as a bug. Feel free to submit a pull request to fix it.

As can be seen in the package header, this package is based on Anton Scheffer's (@antonscheffer) "as_xlsx" package, so I can't comment on whether the mismatch between the hyperlinks/comments arrays is intentional. It does look weird, though. I've had a look at the latest version of Anton's package (https://technology.amis.nl/wp-content/uploads/2011/02/as_xlsx18.txt) and the discrepancy is still there.

[antonscheffer]

The " discrepancy" is intentional.