jsonoracle-databaseplsqloracle19c

How to Remove a Large Element From a JSON Object in Oracle


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

Solution

  • 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;
    /