Package body: xlsx_builder_pkg / ORA-06502: number or value error: text buffer too small

Question

I use package: xlsx_builder_pkg (export data to excel file from some statement). I added an attachments:

• xlsx_builder_pkg.pkb
• xlsx_builder_pkg.pks

I try to export all the data from institution table to Excel file with one sheet named country.

I receive follwing errors:

ORA-06502: PL/SQL: number or value error: text buffer too small
ORA-06512: "SYS.XLSX_BUILDER_PKG", line 1646
ORA-06512: "SYS.XLSX_BUILDER_PKG", line 1870

The statement I run:

BEGIN
xlsx_builder_Pkg.clear_workbook;
xlsx_builder_pkg.new_sheet ('country');
xlsx_builder_pkg.query2sheet (p_sql => '**SELECT distinct name FROM system.institution where rownum < 500**', p_sheet => 1);    
xlsx_builder_pkg.save ('country', 'country.xlsx');
END;

But if I use statement '**SELECT distinct name FROM system.institution where rownum < 400**' it works perfect. I think there is limit of file size. But in what way I could change it.

According to errors I think the problem is somewhere in function finish

ORA-06512: "SYS.XLSX_BUILDER_PKG", line 1646
ORA-06512: "SYS.XLSX_BUILDER_PKG", line 1870

The line 1646 tells the problem with t_tmp := t_tmp || t_str;

I use package and package body from this: https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/xlsx_builder_pkg.pkb

and

https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/xlsx_builder_pkg.pks

Answer 1

Maybe you are using 2-byte encoding in the database? Please change all places in package from > 32000 to > 16000.

For example line 1638 
     old: if t_len > 32000 
     new: if t_len > 16000

For me helped for error

ORA-06502: PL/SQL: number or value error: text buffer too small
error ORA-06512: on  "SIEBEL_CHECK.XLSX_BUILDER_PKG", line 1853

in line 1848 I changed
     old: if length( t_tmp ) + length( t_str ) > 32000
     new: if length( t_tmp ) + length( t_str ) > 16000