Error on large tables (ORA-06502: PL/SQL: numeric or value error) #7
Description
Getting the following error with large (more than 32K is my guess) nested tables.
We're Sorry. An unexpected error has occurred. Please note the following information and contact the help desk:
Application ID: 4000250
Page ID: 300
APEX ERROR CODE: WWV_FLOW_PLUGIN.RUN_PLSQL_ERR
ora_sqlcode: -6502
ora_sqlerrm: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 504
ORA-06502: PL/SQL: numeric or value error
The culprit line 494 in the code:
htp.p( apex_json.get_clob_output );
I believe htp.p cannot handle such large values without them being split.
However, the fix I recommend is simply to let APEX_JSON output the to the stream like this:
-- apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write( 'data', l_sys_cursor );
apex_json.open_array('headers');
for i in 1..l_desc_col_no loop
apex_json.open_object;
apex_json.write('COLUMN_NAME', l_desc_col_info(i).col_name);
apex_json.write('COLUMN_TYPE', getColumnTypeString( l_desc_col_info(i).col_type ) );
apex_json.close_object;
end loop;
apex_json.close_array;
apex_json.write( 'x01', l_ajax_column_name, true );
apex_json.write( 'x02', l_ajax_column_values, true );
apex_json.close_object;
-- htp.p( apex_json.get_clob_output );
So, remove apex_json.initialize_clob_output and the htp.p call.