Oracle中属性大于4000的JSON解析

Question

我试图解析值大于4000个字符的JSON属性。这是我的示例JSON：

[{
        "id": "268edbb5d111",
        "name": "Sample Product",
        "status": "created",
        "description": "this is sample product",
        "productCharacteristic": [{
                "name": "property_1",
                "value": "abc",
                "valueType": "String"
            }, {
                "name": "property_2",
                "value": 123,
                "valueType": "Number"
            }, {
                "name": "property_3",
                "value": "2020-05-01T04:56:07.000+00:00",
                "valueType": "Date"
            }
        ]
    }
]

这是PL/SQL代码：

FOR x IN (SELECT *
            FROM json_table(l_response, '$[*]'
                 COLUMNS
                 id                        varchar2(255)   PATH '$.id',
                 name                      varchar2(255)   PATH '$.name',
                 description               varchar2(255)   PATH '$.description',
                 status                    varchar2(255)   PATH '$.status',
                 productCharacteristic     varchar2(4000)  FORMAT JSON PATH '$.productCharacteristic'
                 )
            ) LOOP
  -- do something
END LOOP;

ProductFeature属性可能长于4000，但将其datatype更改为VARCHAR2(32000)将使用ORA-00910:指定的长度对其datatype太长来编译包，并更改为CLOB将返回ORA-40484:JSON_TABLE列的数据类型无效。是否有一种方法可以将整个ProductFeature数组作为JSON传递，并在循环中解析它？

谢了。

Answer 1

是否有一种方法可以将整个ProductFeature数组作为JSON传递，并在循环中解析它？

这可能不是您想要的，但您可以使用嵌套路径将所有数组元素展平：

FOR x IN (SELECT *
            FROM json_table(l_response, '$[*]'
                 COLUMNS
                 id                        varchar2(255)   PATH '$.id',
                 name                      varchar2(255)   PATH '$.name',
                 description               varchar2(255)   PATH '$.description',
                 status                    varchar2(255)   PATH '$.status',
                 nested path '$.productCharacteristic[*]'
                   COLUMNS  (
                     product_name          varchar2(255)   PATH '$.name',
                     product_value         varchar2(4000)  PATH '$.value',
                     product_value_type    varchar2(255)   PATH '$.valueType'
                   )
                 )
            ) LOOP
  -- do something
  dbms_output.put_line(x.id || ' ' || x.product_name || ' ' || x.product_value);
END LOOP;

DB<>小提琴