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.

Monday, October 28, 2019

GRANT INHERIT PRIVILEGES

As per Oracle 12.2 SQL Reference:

GRANT INHERIT PRIVILEGES TO user

"Execute invoker's rights procedures or functions owned by the grantee with the privileges of the invoker when the invoker is the user on whom this privilege is granted."


If there is someone who can translate in plain english the sentence above, please contact me ASAP, I read it a dozen times and still can't make a sense out of it.

UPDATED 2020/2/13: in the comments a link to a useful article explaining the issue in detail with examples.

Thursday, October 10, 2019

Pretty print JSON strings in Oracle 12.2 the easy way

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

It turns out that in Oracle 12.2 you can easily get pretty printed large JSON strings using a query like this:

select json_query(large_json_value, '$' returning clob pretty) as json_clob
from some_table;

Note however that the syntax returning clob pretty is not mentioned in the documentation, where returning varchar2(n) pretty (with n <= 4000) seems to be the only valid syntax.


I really appreciated the hidden feature because now I can quickly export a bunch of JSON strings  automatically generated with views using nested JSON_ARRAYAGG and JSON_OBJECT SQL functions.


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