Как разобрать json с несколькими записями с помощью pljson

Ты можешь использовать:

DECLARE
  obj pljson := pljson(
    '{
    "status": "success",
    "execution_time": "0.0304 seconds",
    "total_records": 2,
    "records_returned": 2,
    "offset": 0,
    "limit": 150,
    "orders": [{
        "id": "305954583",
        "email": "[email protected]",
        "date_added": "2022-03-16 20:42:44",
        "date_updated": "2022-03-16 20:43:12",
        "checkout_data": [],
        "order_metadata": [],
        "discount_list": [],
        "order_notes": [],
        "order_items": [{
            "id": "163220786",
            "name": "099922511015",
            "price": 5,
            "quantity": 3,
            "weight": 0,
            "code": "099922511015",
            "delivery_type": "ship",
            "category_code": "",
            "fulfillment_method": "",
            "variation_list": [],
            "metadata": []
        }],
        "order_shipments": []
    }, {
        "id": "170951391",
        "email": "[email protected]",
        "date_added": "2021-04-27 22:50:11",
        "date_updated": "2022-03-17 02:38:43",
        "checkout_data": [],
        "order_metadata": [],
        "discount_list": [],
        "order_notes": [{
            "date_added": "2022-03-17 02:38:43",
            "username": "username",
            "content": "testing notes"
        }],
        "order_items": [{
            "id": "112184373",
            "name": "COUNTER",
            "price": 1,
            "quantity": 1,
            "weight": 0.25,
            "code": "COUNTER",
            "delivery_type": "ship",
            "category_code": "",
            "fulfillment_method": "",
            "variation_list": [],
            "metadata": []
        }],
        "order_shipments": []
    }]
}'
  );
  v_order_list  pljson_list;
  v_order       pljson;
  v_order_items pljson_list;
  v_item        pljson;
BEGIN
  v_order_list := pljson_ext.get_json_list( obj, 'orders');
  FOR i IN 1 .. v_order_list.COUNT() LOOP
    v_order := TREAT(v_order_list.get(i).get_element() AS pljson);
    DBMS_OUTPUT.PUT_LINE('Order id: ' || v_order.get_string('id'));
    v_order_items := TREAT(v_order.get('order_items').get_element() AS pljson_list);
    FOR j IN 1 .. v_order_items.COUNT() LOOP
      v_item := TREAT(v_order_items.get(j).get_element() AS pljson);
      DBMS_OUTPUT.PUT_LINE('  Order item id: ' || v_item.get_string('id'));
    END LOOP;
  END LOOP;
END;
/

Что выводит:

Order id: 305954583
  Order item id: 163220786
Order id: 170951391
  Order item id: 112184373

дб <> рабочий пример здесь