Oracle Apex - Assign JSON object to variable for returning-CodePudding

I have to generate a JOSN object trough a SQL query. Finally i have to save this JSON object to a variable to return it later. So I came up with this code snippet for an ajax callback:

DECLARE
    json_result JSON_OBJECT_T;
BEGIN
    SELECT JSON_OBJECT('if'          VALUE ID,
                       'data'        VALUE DATA_BLOB,
                       'upload_date' VALUE UPLOAD_DATE FORMAT JSON) INTO json_result FROM TEST_TABLE WHERE ID = 0;
    apex_json.open_object;
    apex_json.write('success', true);
    apex_json.write('result', json_result);
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', sqlerrm);
        apex_json.close_object;
END;

But unlikely there is a error:

PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR

I can not find out what is wrong here.

Help is highly appreciated.

Best regards,
Filip.

CodePudding user response：

You shouldn't need apex_json, this can be done directly in sql. Here an example on the EMP table.

SELECT JSON_OBJECT (
         KEY 'success' VALUE 'true',
         KEY 'employees' VALUE (
           SELECT JSON_ARRAYAGG(
                    JSON_OBJECT (
                      KEY 'ename' VALUE e.ename,
                      KEY 'empno' VALUE e.empno,
                      KEY 'job' VALUE e.job
                    )
                  )
           FROM   emp e
         )
       ) AS employees
FROM   dual;

So to assign this value to a page item, create a computation of type "SQL Query (returns single value)" with the code above on any process point before rendering.