Oracle subquery with JSON_VALUE limited to 1024 characters

303 Views Asked by At

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

0

There are 0 best solutions below