APEX

Hello APEX community!  We are experiencing an undesired result while using APEX_JSON.WRITE to output the contents of a SYS_REFCURSOR.

Our environment is as follows:

Scenario 1 - produces desired results.  Notice in the JSON output the "TOCHAR_NUMBER_DATA" field shows as a string

PL/SQL:

DECLARE
     C_TEST_DATA SYS_REFCURSOR;
BEGIN
     OPEN C_TEST_DATA FOR
          SELECT TO_CHAR(1234) AS tochar_number_data
               ,'abcd' AS varchar_data
          FROM DUAL;
     APEX_JSON.OPEN_OBJECT;
     APEX_JSON.WRITE('TEST_DATA', C_TEST_DATA);
     APEX_JSON.CLOSE_OBJECT;
END;

JSON Output:

{
    "TEST_DATA": [
        {
            "TOCHAR_NUMBER_DATA": "1234",
            "VARCHAR_DATA": "abcd"
        }
    ]
}

Scenario 2 - produces an undesired result.  Notice in the JSON output the "TOCHAR_NUMBER_DATA" field now is represented as a number both in the outer object and the inner cursor data.

PL/SQL:

DECLARE
     C_TEST_DATA SYS_REFCURSOR;
BEGIN
     OPEN C_TEST_DATA FOR
          SELECT TO_CHAR(1234) AS tochar_number_data
               ,'abcd' AS varchar_data
               ,CURSOR (
                    SELECT TO_CHAR(5678) AS cursor_tochar_number_data
                         ,'efgh' AS cursor_varchar_data
                    FROM DUAL
               ) AS cursor_data
          FROM DUAL;
     
     APEX_JSON.OPEN_OBJECT;
     APEX_JSON.WRITE('TEST_DATA', C_TEST_DATA);
     APEX_JSON.CLOSE_OBJECT;
END;

JSON Output:

{
    "TEST_DATA": [
        {
            "TOCHAR_NUMBER_DATA": 1234,
            "VARCHAR_DATA": "abcd",
            "CURSOR_DATA": [
                {
                    "CURSOR_TOCHAR_NUMBER_DATA": 5678,
                    "CURSOR_VARCHAR_DATA": "efgh"
                }
            ]
        }
    ]
}

Can this difference in behavior be explained? Or is this a potential bug in APEX_JSON when introducing a more complex data structure in a cursor?  Any help would greatly be appreciated.

Thanks!

Sean

Message was edited by: Sean Akers