Oracle Apex - Assign JSON object to variable for returning

Question

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.

try removing the FORMAT JSON

sinclair
– sinclair

2022-10-11 11:36:14 +00:00
Commented Oct 11, 2022 at 11:36 — sinclair
– sinclair, Commented Oct 11, 2022 at 11:36
@sinclair Does not work - already tried.

Filip Degenhart
– Filip Degenhart

2022-10-11 11:38:02 +00:00
Commented Oct 11, 2022 at 11:38 — Filip Degenhart
– Filip Degenhart, Commented Oct 11, 2022 at 11:38
community.oracle.com/tech/developers/discussion/4484819/…

sinclair
– sinclair

2022-10-11 12:05:51 +00:00
Commented Oct 11, 2022 at 12:05 — sinclair
– sinclair, Commented Oct 11, 2022 at 12:05

Koen Lostrie · Accepted Answer · 2022-10-11 12:12:52Z

1

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.

answered Oct 11, 2022 at 12:12

Koen Lostrie

19.5k2 gold badges16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Apex - Assign JSON object to variable for returning

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related