APEX_JSON Package

 

A simple How2Do with APEX_JSON Package is given below.

We can Generate  JSON

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  
  OPEN l_cursor FOR
    SELECT e.empno AS "employee_number",
           e.ename AS "employee_name",
           e.deptno AS "department_number"
    FROM   emp e
    WHERE  rownum <= 2;

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;

  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;
/
{
  "employees": [
    {
      "employee_number": 7369,
      "employee_name": "SMITH",
      "department_number": 20
    },
    {
      "employee_number": 7499,
      "employee_name": "ALLEN",
      "department_number": 30
    }
  ]
}

We can Parse JSON

DECLARE
    s varchar2(32767) := '{ "a": 1, "b": ["hello", "world"]}';
BEGIN
    apex_json.parse(s);
    sys.dbms_output.put_line('a is '||apex_json.get_varchar2(p_path => 'a'));
END;