CODEWITHSUNDEEP
sqloracle-database
NiiL
NiiL

Reputation: 2827

Oracle - JSON_OBJECT - ORA-40478: output value too large (maximum: 4000)

I am using Oracle 18c database.

For one of my Query, I am trying generate JSon from 3 level of tables.

pages_tbl 
    page_regions_tbl 
        region_items_tbl

For that I have prepared below query. But it is giving me error ORA-40478: output value too large (maximum: 4000)

SELECT 
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'page' VALUE p.name, 
      'regions' VALUE(
        SELECT 
          JSON_ARRAYAGG(
            JSON_OBJECT(
              'region' VALUE r.name, 
              'items' VALUE(
                SELECT 
                  JSON_ARRAYAGG(
                    JSON_OBJECT(
                      'item_name' VALUE i.item_name, 'item_value' VALUE i.item_value
                    ) RETURNING CLOB
                  ) 
                FROM region_items_tbl i
               WHERE i.region_id = r.region_id
                 AND i.enabled = 1
              )
            ) RETURNING CLOB
          ) 
        FROM page_regions_tbl r 
       WHERE r.page_id = p.page_id
         AND r.enabled = 1
      ) 
    ) RETURNING CLOB
  ) 
 FROM pages_tbl p 
WHERE p.category_id = 10150
  AND p.enabled = 1

I have already written RETURNING CLOB so I was expecting smooth result but getting error. Can any one point me out what I am doing wrong or how I can fix this query

Upvotes: 3

Views: 10027

Answers (2)

SteveUK9799
SteveUK9799

Reputation: 103

I had the same issue and the same result (still failing, even with RETURNING CLOB added to every JSON_OBJECT), however I've found that you also need to add RETURNING CLOB to your JSON_ARRAYAGG functions (but not your JSON_ARRAY functions, if you had any). This fixed the issue for me and it displays the returned data as "(CLOB)", but expands to the real data when you click into it.

Upvotes: 5

foo
foo

Reputation: 21

add RETURNING CLOB before all JSON_OBJECT's closing brackets

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_OBJECT.html

JSON_returning_clause

Use this clause to specify the type of return value. One of :

VARCHAR2 specifying the size as a number of bytes or characters. The default is bytes. If you omit this clause, or specify the clause without specifying the size value, then JSON_ARRAY returns a character string of type VARCHAR2(4000). Refer to VARCHAR2 Data Type for more information. Note that when specifying the VARCHAR2 data type elsewhere in SQL, you are required to specify a size. However, in the JSON_returning_clause you can omit the size.

CLOB to return a character large object containing single-byte or multi-byte characters.

Upvotes: 1

Related Questions