Friday, December 13, 2019

ORA-40478: output value too large (maximum: )

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If you are hitting the following error while executing a query containing a JSON_TABLE function like this:

select s.code, 
       s.date_ins, 
       to_char(s.date_ins,'HH24:MI') as t, 
       r.report_name,
       fn_tab2str(cast(multiset(select name || ':' || value 
                                  from json_table(
                                          s.params format json, 
                                          '$[*]' error on error
                                          columns(
                                            name   varchar2(2)  path '$.NAME',
                                            value  varchar2(5)  path '$.VALUE'
                                          )
                                       )
                               ) as ora_mining_varchar2_nt
                      )
                 ) as params
  from batch_statistics s,
       batch_reports r
 where s.code = r.report_code;

ORA-40478: output value too large (maximum: )


it's very likely that you might have specified an insufficient size for a column (in red).
In my case the path $.NAME returns strings of up to 255 bytes, so any value larger than 2 bytes would break the query.


See message translations for ORA-40478 and search additional resources.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles