I have a problem with Oracle JSON_VALUE in subquery/CTE. For testing I have this query:
WITH test_CTE AS (
SELECT
LENGTH(val) AS len,
JSON_VALUE(val, '$.a2' RETURNING VARCHAR2) AS res
FROM
(
SELECT TO_CLOB('{
a:100,
x: {
obj: {
valS: "String dummy value"
},
valI: 1000000000
},
y: [
{
t: "test string property"
},
{
t: "test2 string property"
}
],
z: [
{
str: "My Test String with special characters $#@!"
}
],
a2:200,
b:100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,
c:100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,
d:100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,
e:10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,
f:10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
}') AS val
FROM DUAL
)
)
SELECT
test_CTE.LEN,
test_CTE.RES
FROM
test_CTE;
And the result of this query is:
1025 | NULL
If I run just the part of the CTE the result will be correct
1025 | 200
If the length of the JSON object will be <=1024 it works even with the CTE/subquery. Did anyone notice the same limitation? Why it works in a query but not in subquery/CTE? Is this some kind of setting on Oracle? I didn't find any limitation in the documentation that will be related to this scenario.
Database version:
Oracle 18c, Full version: 18.12.0.0.0