I have an xxmf_json_feed table with the following JSON clob in the json_data column:
{
"P_INVOICE_MASTER_TBL_ITEM": [
{
"P_INVOICE_NUM": "INV20250224-1",
"FILE_NAME": "INV20250224-1.pdf",
"FILE_CONTENT": "65k_char_blob_content"
},
{
"P_INVOICE_NUM": "INV20250224-2",
"FILE_NAME": "INV20250224-2.pdf",
"FILE_CONTENT": "65k_char_blob_content"
}
]
}
The "65k_char_blob_content" is actually a value of around 65k characters. In order to process the JSON, I am trying to put the "P_INVOICE_MASTER_TBL_ITEM" array into a JSON_ARRAY_T variable. This is causing a "value too large" error. Is there a way I can just remove the "FILE_CONTENT" element before attempting to parse it into the JSON_ARRAY_T?
DECLARE
jo JSON_OBJECT_T;
je JSON_ELEMENT_T;
ja JSON_ARRAY_T;
i PLS_INTEGER := 0;
BEGIN
FOR r IN (
SELECT json_data
FROM xxmf_json_feed)
LOOP
jo := JSON_OBJECT_T.parse(r.json_data);
je := jo.get('P_INVOICE_MASTER_TBL_ITEM');
ja := JSON_ARRAY_T.parse(je.to_string); -- line 13 assignment error
LOOP
je := ja.GET(i);
EXIT WHEN je IS NULL;
-- process JSON array here
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM: '||SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
The error I get is:
SQLERRM: ORA-40478: output value too large (maximum: ) ORA-06512: at "SYS.JDOM_T", line 43 ORA-06512: at "SYS.JSON_ELEMENT_T", line 69 ORA-06512: at line 13 ORA-06512: at line 13
This can be done using JSON_TRANSFORM
, check the docs.
DECLARE
jo JSON_OBJECT_T;
je JSON_ELEMENT_T;
ja JSON_ARRAY_T;
i PLS_INTEGER := 0;
BEGIN
FOR r IN (
SELECT json_transform(json_data, REMOVE '$.P_INVOICE_MASTER_TBL_ITEM[*].FILE_CONTENT'
RETURNING CLOB) as json_data
FROM xxmf_json_feed)
LOOP
jo := JSON_OBJECT_T.parse(r.json_data);
je := jo.get('P_INVOICE_MASTER_TBL_ITEM');
ja := JSON_ARRAY_T.parse(je.to_string); -- line 13 assignment error
-- commented out because the statement below goes in endless loop...
-- LOOP
-- je := ja.GET(i);
-- EXIT WHEN je IS NULL;
--
-- -- process JSON array here
-- END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM: '||SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
/