json - PL/SQL json_table 函数为大型 JSON 文档提供错误

Question

使用 Oracle 12.1，而不是 12.2，在大字符串上使用 JSON_TABLE 时出现此错误。

Error report:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 1801
01460. 00000 -  "unimplemented or unreasonable conversion requested"
*Cause:    
*Action:

代码是：

VAR jsonstr CLOB;
EXEC :jsonstr := '{"jevents":[{"header":{"hdClassType":3,"hdTypeIdentifier":{"type":"A"},"hdEventInstance":"6D71E8C4-F37F-A359-E053-6701100AB504.2021-06-17T17:28:39.710","hdDetectionTime":"2021-06-17T17:28:39.710","hdSource":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdAnnotation":"Annotation Alarm","hdOffsetTime":"1","hdIdentity":"1cbe6731-36d6-4587-8ad9-482e50f4cfc9","hdMaterialized":false,"hdToSibling":null,"hdCertainty":1,"hdGranularity":"m","hdToParent":null,"hdTTL":"0","hdToChild":null,"hdScenario":"1","hdInternalEventIdentifier":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdOccurrenceTime":"2021-06-17T17:28:39.710","hdFacts":null},"payLoadType":{"alarm":{"almNormalTime":null,"almOII":"","almDescription":null,"almCode":"ADWFAILURE","almOIC":{"documentCode":"DocumentCode","oicString":"TSAO_STCN_AIDWHARM1EIIA_001_AIDWHETHSEIIA_002_ADWFAILURE","annotation":"Annotation","oic":{"spn":"AIDWHETHSEIIA","area":"TSAO","oicIdentity":"6D71E8C4-F37F-A359-E053-6701100AB504","serial":null,"mpnp":"001","mpn":"AIDWHARM1EIIA","spnp":"002","location":"STCN","signal":"ADWFAILURE","spare":null},"version":"1"},"almOperator":"DWH","almAutomatic":"1","almPriority":"4","almState":"3","almRole":"DWH","almAckTime":"2021-06-17T17:28:39.710","almConsole":"","almOICExt":{"des01":"TID","des02":null,"des03":null,"val03":null,"val01":"","val02":null,"spare":null},"almInitTime":"2021-06-17T17:28:39.710"},"label":null,"time":null,"measurement":null,"command":null,"status":null}},{"header":{"hdClassType":3,"hdTypeIdentifier":{"type":"M"},"hdEventInstance":"6D71E8C4-C880-A359-E053-6701100AB504.2021-06-17T17:28:18.562","hdDetectionTime":"2021-06-17T17:28:17.682","hdSource":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdAnnotation":"Annotation Measure","hdOffsetTime":"1","hdIdentity":"2cedefff-d4e7-45b9-a4a1-252a20bfa46c","hdMaterialized":false,"hdToSibling":null,"hdCertainty":1,"hdGranularity":"m","hdToParent":null,"hdTTL":"0","hdToChild":null,"hdScenario":"1","hdInternalEventIdentifier":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdOccurrenceTime":"2021-06-17T17:28:18.562","hdFacts":null},"payLoadType":{"alarm":null,"label":null,"time":null,"measurement":{"msFactor":8,"msOICExt":{"des01":"TID","des02":"test","des03":"test","val03":"1","val01":"","val02":"1","spare":null},"msSampling":null,"msRelative":false,"msValue":27,"msOIC":{"documentCode":"DocumentCode","oicString":"TSAO_STCN_AIDWHARM1EIIA_001_AIDWHAPPSEIIA_001_MDWAMBIENT","annotation":"Annotation","oic":{"spn":"AIDWHAPPSEIIA","area":"TSAO","oicIdentity":"6D71E8C4-C880-A359-E053-6701100AB504","serial":null,"mpnp":"001","mpn":"AIDWHARM1EIIA","spnp":"001","location":"STCN","signal":"MDWAMBIENT","spare":null},"version":"1"},"msCode":null,"msQuality":"8","msOII":null,"msUM":"null"},"command":null,"status":null}},{"header":{"hdClassType":3,"hdTypeIdentifier":{"type":"M"},"hdEventInstance":"6D71E8C4-D580-A359-E053-6701100AB504.2021-06-17T17:28:25.472","hdDetectionTime":"2021-06-17T17:28:24.689","hdSource":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdAnnotation":"Annotation Measure","hdOffsetTime":"1","hdIdentity":"e2b662ca-e5fc-4da5-874f-79e3ecb51be0","hdMaterialized":false,"hdToSibling":null,"hdCertainty":1,"hdGranularity":"m","hdToParent":null,"hdTTL":"0","hdToChild":null,"hdScenario":"1","hdInternalEventIdentifier":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdOccurrenceTime":"2021-06-17T17:28:25.472","hdFacts":null},"payLoadType":{"alarm":null,"label":null,"time":null,"measurement":{"msFactor":17,"msOICExt":{"des01":"TID","des02":"test","des03":"test","val03":"1","val01":"","val02":"1","spare":null},"msSampling":null,"msRelative":false,"msValue":45.5,"msOIC":{"documentCode":"DocumentCode","oicString":"TSAO_STCN_AIDWHARM1EIIA_001_AIDWHETHSEIIA_001_MDWTEMP","annotation":"Annotation","oic":{"spn":"AIDWHETHSEIIA","area":"TSAO","oicIdentity":"6D71E8C4-D580-A359-E053-6701100AB504","serial":null,"mpnp":"001","mpn":"AIDWHARM1EIIA","spnp":"001","location":"STCN","signal":"MDWTEMP","spare":null},"version":"1"},"msCode":null,"msQuality":"83","msOII":null,"msUM":"null"},"command":null,"status":null}}]}'

SELECT * FROM JSON_TABLE(:jsonstr,
                       '$' COLUMNS(NESTED PATH '$.jevents[*]'
                                    COLUMNS(  
                                            jsonobjstr  VARCHAR2 format json path '$'                                            
                                            )
                                  )
          );

预期是这样的：

标签： jsonoracleoracle12.1

Answer 1

这是我的解决方法：

set serverout on
declare
    l_data           clob;
    l_objdata        dbms_sql.varchar2_table;
    begin
    l_data := '{"jevents":[{"header":{"hdClassType":3,"hdTypeIdentifier":{"type":"A"},"hdEventInstance":"6D71E8C4-F37F-A359-E053-6701100AB504.2021-06-17T17:28:39.710","hdDetectionTime":"2021-06-17T17:28:39.710","hdSource":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdAnnotation":"Annotation Alarm","hdOffsetTime":"1","hdIdentity":"1cbe6731-36d6-4587-8ad9-482e50f4cfc9","hdMaterialized":false,"hdToSibling":null,"hdCertainty":1,"hdGranularity":"m","hdToParent":null,"hdTTL":"0","hdToChild":null,"hdScenario":"1","hdInternalEventIdentifier":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdOccurrenceTime":"2021-06-17T17:28:39.710","hdFacts":null},"payLoadType":{"alarm":{"almNormalTime":null,"almOII":"","almDescription":null,"almCode":"ADWFAILURE","almOIC":{"documentCode":"DocumentCode","oicString":"TSAO_STCN_AIDWHARM1EIIA_001_AIDWHETHSEIIA_002_ADWFAILURE","annotation":"Annotation","oic":{"spn":"AIDWHETHSEIIA","area":"TSAO","oicIdentity":"6D71E8C4-F37F-A359-E053-6701100AB504","serial":null,"mpnp":"001","mpn":"AIDWHARM1EIIA","spnp":"002","location":"STCN","signal":"ADWFAILURE","spare":null},"version":"1"},"almOperator":"DWH","almAutomatic":"1","almPriority":"4","almState":"3","almRole":"DWH","almAckTime":"2021-06-17T17:28:39.710","almConsole":"","almOICExt":{"des01":"TID","des02":null,"des03":null,"val03":null,"val01":"","val02":null,"spare":null},"almInitTime":"2021-06-17T17:28:39.710"},"label":null,"time":null,"measurement":null,"command":null,"status":null}},{"header":{"hdClassType":3,"hdTypeIdentifier":{"type":"M"},"hdEventInstance":"6D71E8C4-C880-A359-E053-6701100AB504.2021-06-17T17:28:18.562","hdDetectionTime":"2021-06-17T17:28:17.682","hdSource":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdAnnotation":"Annotation Measure","hdOffsetTime":"1","hdIdentity":"2cedefff-d4e7-45b9-a4a1-252a20bfa46c","hdMaterialized":false,"hdToSibling":null,"hdCertainty":1,"hdGranularity":"m","hdToParent":null,"hdTTL":"0","hdToChild":null,"hdScenario":"1","hdInternalEventIdentifier":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdOccurrenceTime":"2021-06-17T17:28:18.562","hdFacts":null},"payLoadType":{"alarm":null,"label":null,"time":null,"measurement":{"msFactor":8,"msOICExt":{"des01":"TID","des02":"test","des03":"test","val03":"1","val01":"","val02":"1","spare":null},"msSampling":null,"msRelative":false,"msValue":27,"msOIC":{"documentCode":"DocumentCode","oicString":"TSAO_STCN_AIDWHARM1EIIA_001_AIDWHAPPSEIIA_001_MDWAMBIENT","annotation":"Annotation","oic":{"spn":"AIDWHAPPSEIIA","area":"TSAO","oicIdentity":"6D71E8C4-C880-A359-E053-6701100AB504","serial":null,"mpnp":"001","mpn":"AIDWHARM1EIIA","spnp":"001","location":"STCN","signal":"MDWAMBIENT","spare":null},"version":"1"},"msCode":null,"msQuality":"8","msOII":null,"msUM":"null"},"command":null,"status":null}},{"header":{"hdClassType":3,"hdTypeIdentifier":{"type":"M"},"hdEventInstance":"6D71E8C4-D580-A359-E053-6701100AB504.2021-06-17T17:28:25.472","hdDetectionTime":"2021-06-17T17:28:24.689","hdSource":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdAnnotation":"Annotation Measure","hdOffsetTime":"1","hdIdentity":"e2b662ca-e5fc-4da5-874f-79e3ecb51be0","hdMaterialized":false,"hdToSibling":null,"hdCertainty":1,"hdGranularity":"m","hdToParent":null,"hdTTL":"0","hdToChild":null,"hdScenario":"1","hdInternalEventIdentifier":"E8AC92BC-9C07-EB74-E053-0100007F6A02","hdOccurrenceTime":"2021-06-17T17:28:25.472","hdFacts":null},"payLoadType":{"alarm":null,"label":null,"time":null,"measurement":{"msFactor":17,"msOICExt":{"des01":"TID","des02":"test","des03":"test","val03":"1","val01":"","val02":"1","spare":null},"msSampling":null,"msRelative":false,"msValue":45.5,"msOIC":{"documentCode":"DocumentCode","oicString":"TSAO_STCN_AIDWHARM1EIIA_001_AIDWHETHSEIIA_001_MDWTEMP","annotation":"Annotation","oic":{"spn":"AIDWHETHSEIIA","area":"TSAO","oicIdentity":"6D71E8C4-D580-A359-E053-6701100AB504","serial":null,"mpnp":"001","mpn":"AIDWHARM1EIIA","spnp":"001","location":"STCN","signal":"MDWTEMP","spare":null},"version":"1"},"msCode":null,"msQuality":"83","msOII":null,"msUM":"null"},"command":null,"status":null}}]}';      
    SELECT jsonobjstr BULK COLLECT INTO l_objdata FROM JSON_TABLE(l_data,
                       '$' COLUMNS(NESTED PATH '$.jevents[*]'
                                    COLUMNS(  
                                            jsonobjstr  varchar2 format json path '$'                                            
                                            )
                                  )
          );
     -- show
    FOR i IN 1..l_objdata.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(l_objdata(i));
    END LOOP;
   end;