With Oracle 12.1, not on 12.2, I have this error when using JSON_TABLE on large string.
Error report:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 1801
01460. 00000 - "unimplemented or unreasonable conversion requested"
*Cause:
*Action:
The code is:
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 '$'
)
)
);
The expected is this:
This is my workaround: