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
Leave a Reply