Generating JSON with multiple functions using Oracle APEX_JSON

Question

Currently I am generating some jsons with data with oracle for backend purposes and I'm struggling with complex and repetetive structions that I have to process manually.

For example I have this array of objects:

{
  "infoColumnsWidgets": [
    {
      "widgetNamespace": "mot",
      "widgetName": "info_column",
      "orderNumber": 1,
      "navigateToPage": null,
      "widgetData": {
        "title": "Fact",
        "textPattern": "$v0",
        "values": [
          {
            "id": "v0",
            "type": "int",
            "value": "200000"
          }
        ]
      }
    },
    {
      "widgetNamespace": "mot",
      "widgetName": "info_column",
      "orderNumber": 2,
      "navigateToPage": null,
      "widgetData": {
        "title": "Plan",
        "textPattern": "$v0",
        "values": [
          {
            "id": "v0",
            "type": "int",
            "value": "200000"
          }
        ]
      }
    },
    {
      "widgetNamespace": "mot",
      "widgetName": "info_column",
      "orderNumber": 3,
      "navigateToPage": null,
      "widgetData": {
        "title": "Prognosis",
        "textPattern": "$v0",
        "values": [
          {
            "id": "v0",
            "type": "int",
            "value": "100"
          }
        ]
      }
    }
  ]
}

Certainly I generate it in a loop but this structure occurs often and I'd prefer to put it into some function to do the following:

function f_getTest return clob as
  v_res clob;
begin
  apex_json.initialize_clob_output;
  apex_json.open_object;
    apex_json.open_object('infoColumnsWidgets');
    for rec in (select * from some_table_data)
    loop
      apex_json.write_raw(f_getWidgetJson(rec.param));
    end loop;
    apex_json.close_object;
  apex_json.close_all;
  v_res := apex_json.get_clob_output;
  apex_json.free_output;
  return v_res;
end;

But as far as I know there is no option to put one json into another using apex_json. I can try with some weird workarounds with putting some placeholders and replacing them in final clob but no, I don't want, please, don't make me do that.

Any ideas are super welcome

What version of oracle are you using ? It is not so clear what you want to do exactly - do you want to generate json from relational tables or do you have json stored that you need to modify ? — Koen Lostrie
– Koen Lostrie, Commented Dec 23, 2021 at 15:24
Oracle 12c, apex - 5, as far as I remember. I successfully generate the json I showed above with apex_json functions: open array, add object, put some values, close object and array, you know it. The thing I am trying to do is to take repetitive parts and turn them into functions; calling them in the loop while generating. — John Doe
– John Doe, Commented Dec 23, 2021 at 15:31
I've made separate function to generate an object which I want to put into the "main" json function, but I didn't find any way to properly add it into structure. — John Doe
– John Doe, Commented Dec 23, 2021 at 15:34
from 12c release 2 onward you can generate json with sql natively in the database - that is a lot easier than using apex_json — Koen Lostrie
– Koen Lostrie, Commented Dec 23, 2021 at 19:53
alternatively you could just write the sql statements and expose them as json using ORDS (as a restful service) - you'll just have to check what is possible in your version of ords — Koen Lostrie
– Koen Lostrie, Commented Dec 23, 2021 at 20:14

Koen Lostrie · Accepted Answer · 2021-12-27 16:34:01Z

2

Does this help ? I took the example from oracle-base and moved the body code into a separate procedure. In the example below it is an inline procedure but nothing stops you from putting into a standalone procedure or a package.

DECLARE
  PROCEDURE dept_object 
  IS
    l_cursor SYS_REFCURSOR;
  BEGIN
    OPEN l_cursor FOR
      SELECT d.dname AS "department_name",
             d.deptno AS "department_number",
             CURSOR(SELECT e.empno AS "employee_number",
                           e.ename AS "employee_name"
                    FROM   emp e
                    WHERE  e.deptno = d.deptno
                    ORDER BY e.empno) AS "employees"
      FROM   dept d
      ORDER BY d.dname;
    APEX_JSON.open_object;
    APEX_JSON.write('departments', l_cursor);   
    APEX_JSON.close_object;         
  END;
BEGIN
  APEX_JSON.initialize_clob_output;  
  dept_object;
  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;
/

answered Dec 27, 2021 at 16:34

Koen Lostrie

19.5k2 gold badges16 silver badges25 bronze badges

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

2 Comments

John Doe Over a year ago

Thanks a lot, that helped. My mistake was initializing second clob output in the inner function.

Koen Lostrie Over a year ago

Glad to hear it helped :)

Collectives™ on Stack Overflow

Generating JSON with multiple functions using Oracle APEX_JSON

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related