Search This Blog

Monday, July 16, 2018

PL/SQL To JSON Conversion





SET SERVEROUTPUT ON
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;

OUTPUT WILL LIKE :

{"employees":[{"employee_number":4,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":2,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":3,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":1,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":6,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":7,"employee_name":"aa","department_number":30}
,{"employee_number":5,"employee_name":"jhjkhkj"}]}


No comments:

Post a Comment

Split A String By Coma (,)

Step 1.  create or replace type split_tbl as table of varchar2(32767); Step 2. create or replace function split (     p_list varchar2,...