ORA-40478: output value too large – JSON_SERIALIZE

Posted on by Leave a comment

In my previous posts, while explaining JSON Relational Duality Views, I mentioned that I encountered ORA-40478: output value too large.

So, in this post, let’s look at the available options to by-pass ORA-40478. But first, the basics. Let’s understand what the JSON_SERIALIZE functions does. The function takes JSON data of any SQL data type ( BLOB,CLOB, JSON, VARCHAR2, or VECTOR ) as input and returns a textual representation of it. The basic syntax( from Oracle documentation), for using json_serialize, is as follows:

Now let’s go back to our example. What we did is we created a JSON Relational Duality View which de-normalizes the tables DEPARTMENTS and EMPLOYEES. And used the following statement and landed with ORA-40478: output value too large.

If we narrow down the search, we get the error if we select data from DEPARTMENT_ID = 50.

SQL> Select json_serialize(data pretty) from j_vw_deptemp j WHERE j.data."_id" = 50;

Error starting at line : 1 in command -
Select json_serialize(data pretty
) from j_vw_deptemp j WHERE j.data."_id" = 50
Error at Command Line : 2 Column : 8
Error report -
SQL Error: ORA-40478: output value too large (maximum: 4000)
JZN-00018: Input to serializer is too large
40478. 00000 -  "output value too large (maximum: %s)"
*Cause:    The provided JavaScript Object Notation (JSON) operator generated a
           result which exceeds the maximum length specified in the RETURN
           clause.
*Action:   Increase the maximum size of the data type in the RETURNING clause
           or use a CLOB or BLOB in the RETURNING clause.

More Details :
https://docs.oracle.com/error-help/db/ora-40478/
https://docs.oracle.com/error-help/db/jzn-00018/

So, to overcome ORA-40478: output value too large, what we can use is the TRUNCATE keyword. This will truncate the output to fit into the buffer instead of raising an error.

SQL> Select json_serialize(data pretty TRUNCATE) from j_vw_deptemp j WHERE j.data."_id" = 50;

JSON_SERIALIZE(DATAPRETTYTRUNCATE)
________________________________________________________________________________________________________
{
  "_id" : 50,
  "_metadata" :
  {
    "etag" : "2E7325D8ED85CD186EF8513986DC7CDD",
    "asof" : "00000000008B51E7"
  },
  "deptName" : "Shipping",
  "MgrID" : 121,
  "LocID" : 1500,
  "employees" :
  [
    {
      "empId" : 120,
      "empFirstName" : "Matthew",
      "empLastName" : "Weiss",
      "email" : "MWEISS",
      "phone" : "650.123.1234",
      "hireDate" : "2004-07-18T00:00:00",
      "job" : "ST_MAN",
      "salary" : 8000,
      "commission" : null,
      "managerId" : 100
    },
    {
      "empId" : 121,
      "empFirstName" : "Adam",
      "empLastName" : "Fripp",
      "email" : "AFRIPP",
      "phone" : "650.123.2234",
      "hireDate" : "2005-04-10T00:00:00",
      "job" : "ST_MAN",
      "salary" : 8200,
      "commission" : null,
      "managerId" : 100
    },
    {
      "empId" : 122,
      "empFirstName" : "Payam",
      "empLastName" : "Kaufling",
      "email" : "PKAUFLIN",
      "phone" : "650.123.3234",
      "hireDate" : "2003-05-01T00:00:00",
      "job" : "ST_MAN",
      "salary" : 7900,
      "commission" : null,
      "managerId" : 100
    },
    {
      "empId" : 123,
      "empFirstName" : "Shanta",
      "empLastName" : "Vollman",
      "email" : "SVOLLMAN",
      "phone" : "650.123.4234",
      "hireDate" : "2005-10-10T00:00:00",
      "job" : "ST_MAN",
      "salary" : 6500,
      "commission" : null,
      "managerId" : 100
    },
    {
      "empId" : 124,
      "empFirstName" : "Kevin",
      "empLastName" : "Mourgos",
      "email" : "KMOURGOS",
      "phone" : "650.123.5234",
      "hireDate" : "2007-11-16T00:00:00",
      "job" : "ST_MAN",
      "salary" : 5800,
      "commission" : null,
      "managerId" : 100
    },
    {
      "empId" : 125,
      "empFirstName" : "Julia",
      "empLastName" : "Nayer",
      "email" : "JNAYER",
      "phone" : "650.124.1214",
      "hireDate" : "2005-07-16T00:00:00",
      "job" : "ST_CLERK",
      "salary" : 3200,
      "commission" : null,
      "managerId" : 120
    },
    {
      "empId" : 126,
      "empFirstName" : "Irene",
      "empLastName" : "Mikkilineni",
      "email" : "IMIKKILI",
      "phone" : "650.124.1224",
      "hireDate" : "2006-09-28T00:00:00",
      "job" : "ST_CLERK",
      "salary" : 2700,
      "commission" : null,
      "managerId" : 120
    },
    {
      "empId" : 127,
      "empFirstName" : "James",
      "empLastName" : "Landry",
      "email" : "JLANDRY",
      "phone" : "650.124.1334",
      "hireDate" : "2007-01-14T00:00:00",
      "job" : "ST_CLERK",
      "salary" : 2400,
      "commission" : null,
      "managerId" : 120
    },
    {
      "empId" : 128,
      "empFirstName" : "Steven",
      "empLastName" : "Markle",
      "email" : "SMARKLE",
      "phone" : "650.124.1434",
      "hireDate" : "2008-03-08T00:00:00",
      "job" : "ST_CLERK",
      "salary" : 2200,
      "commission" : null,
      "managerId" : 120
    },
    {
      "empId" : 129,
      "empFirstName" : "Laura",
      "empLastName" : "Bissot",
      "email" : "LBISSOT",
      "phone" : "650.124.5234",
      "hireDate" : "2005-08-20T00:00:00",
      "job" : "ST_CLERK",
      "salary" : 3300,
      "commission" : null,
      "managerId" : 121
    },
    {
      "empId" : 130,
      "empFirstName" : "Mozhe",
      "empLastName" : "Atkinson",
      "email" : "MATKINSO",
      "phone" : "650.124.6234",
      "hireDate" : "2005-10-30T00:00:00",
      "job" : "ST_CLERK",
      "salary" : 2800,
      "commission" : null,
      "managerId" : 121
    },
    {
      "empId" : 131,
      "empFirstName" : "James",
      "empLastName" : "Marlow",
      "email" : "JAMRLOW",
      "phone" : "650.124.7234",
      "hireDate" : "2005-02-16T00:00:00",
      "job" : "ST_CLERK",
      "salary" : 2500,
      "commission" : null,
      "managerId" : 121
    },
    {
      "empId" : 132,
      "empFirstName" : "TJ",
      "empLastName" : "Olson",
      "email" : "TJOLSON",
      "phone" : "650.124.8234",
      "hireDate" : "2007-04-10T00:00:00",
      "job" : "ST

In terms of error handling, you have the following options.

  • ERROR ON ERROR : Throw error if an error is encountered. This is the default behaviour. 
SQL> Select json_serialize(data pretty ERROR ON ERROR) from j_vw_deptemp j WHERE j.data."_id" = 50;

Error starting at line : 1 in command -
Select json_serialize(data pretty ERROR ON ERROR) from j_vw_deptemp j WHERE j.data."_id" = 50
Error at Command Line : 1 Column : 56
Error report -
SQL Error: ORA-40478: output value too large (maximum: 4000)
JZN-00018: Input to serializer is too large
40478. 00000 -  "output value too large (maximum: %s)"
*Cause:    The provided JavaScript Object Notation (JSON) operator generated a
           result which exceeds the maximum length specified in the RETURN
           clause.
*Action:   Increase the maximum size of the data type in the RETURNING clause
           or use a CLOB or BLOB in the RETURNING clause.

More Details :
https://docs.oracle.com/error-help/db/ora-40478/
https://docs.oracle.com/error-help/db/jzn-00018/
https://docs.oracle.com/error-help/db/jzn-00018/
  • NULL ON ERROR : Returns NULL if there is an error ( couldn’t find anything in the Oracle document)
SQL> Select json_serialize(data pretty NULL ON ERROR) from j_vw_deptemp j WHERE j.data."_id" = 50;

JSON_SERIALIZE(DATAPRETTYNULLONERROR)
________________________________________

  • EMPTY ON ERROR : ( not supported). Returns an empty array.
SQL> Select json_serialize(data pretty EMPTY  ON ERROR) from j_vw_deptemp j WHERE j.data."_id" = 50;

JSON_SERIALIZE(DATAPRETTYEMPTYONERROR)
_________________________________________
[
]

References

tagged with , , , , , , , , ,

Leave a comment