<

Creating JSON from Oracle using SYS_REFCURSOR

Published on
35,354 Points
28,754 Views
6 Endorsements
Last Modified:
Approved
JSON (JAVA Script Object Notation) is a text-based open standard designed for human-readable data interchange.

Information on JSON (JavaScript Object Notation) can be found from below links:

There are various methods of generating JSON from Oracle database, for example PL/JSON and pl/sql library for JSON.

Here are the links for getting more info on available oracle packages to generate JSON string:

But in my case I need to generate the JSON format with the datatype of the underlying columns from the table/cursor/query which can later be useful to identify what kind of data the string holds, and which can later be used to do various kinds of validations from UI including Key Value pair and to handle appropriate formatting/presentation of the data.

The format which we want to generate the JSON for sending to the UI is as below:

{"metadata":[{"name":"<name>","value":"<value>",”type”:”<type>”},{"name":"<name>","value":"<value>"},{"name":"<name>","value":"<value>"},{"name":"<name>","value":"<value>"}],
"data":[<comeplte data JSON.]
}

Open in new window


To achieve this we shall first get the data through a SYS_REFCURSOR, generate XML out of it and then apply XSL to transform the same into JSON.
Let's take an example: If I want to generate all the records from the Employee table i.e. EMP in the similar format, then first of all we need a function which will return a SYS_REFCURSOR.

C:\Users\Sloba>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 11 18:52:07 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter user-name: scott
Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - Production

SQL> select empno,ename,job,deptno from emp;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

Open in new window


Now we need the output of the above mentioned query as a SYS_REFCURSOR.
Let us create the function to return SYS_REFCURSOR:

SQL> CREATE OR REPLACE FUNCTION get_emp_data RETURN SYS_REFCURSOR AS
        l_cursor SYS_REFCURSOR;
BEGIN
        OPEN l_cursor FOR
                SELECT empno,ename,job,deptno FROM emp ;
        RETURN l_cursor;
END;

Function created.

Open in new window


Run the function:

SQL> select get_emp_data()  from dual;

Open in new window


Output of get_emp_data
Here is the function which will generate the JSON after passing the output of the first function:

SQL> CREATE OR REPLACE FUNCTION get_json_fnc(ip_rfc VARCHAR2) RETURN CLOB AS
        /*====================================================================================================+
        FUNCTION:   GET_JSON_FNC

        SUMMARY:   TO generate JSON string dynamically from SYS_REFCURSOR

        Revision History:
        Date          Name                             Revision      Description
        ======================================================================================================
         11-AUG-13     Swadhin Ray(Sloba)                   1.0       First Version
        ======================================================================================================*/
        lhtmloutput   xmltype;
        lxsl          LONG;
        lxmldata      xmltype;
        lcontext      dbms_xmlgen.ctxhandle;
        l_ret_clob    CLOB;
        desc_cur      NUMBER;
        l_descr_tab   dbms_sql.desc_tab2;
        l_num_cols    NUMBER;
        l_header_clob CLOB;
        l_row_data    VARCHAR2(100);
        l_ip_rfc      SYS_REFCURSOR;
        l_exec_comm   VARCHAR2(250);
BEGIN
        l_exec_comm := 'SELECT ' || ip_rfc || ' from dual';

        EXECUTE IMMEDIATE l_exec_comm
                INTO l_ip_rfc;

        l_header_clob := '{"metadata":[';
        desc_cur      := dbms_sql.to_cursor_number(l_ip_rfc);

        dbms_sql.describe_columns2(desc_cur
                                  ,l_num_cols
                                  ,l_descr_tab);

        FOR i IN 1 .. l_num_cols
        LOOP
                CASE
                        WHEN l_descr_tab(i).col_type IN (2
                                         ,8) THEN
                                l_row_data := '{"name":"' || l_descr_tab(i)
                                             .col_name || '","type":"number"},';
                        WHEN l_descr_tab(i).col_type = 12 THEN
                                l_row_data := '{"name":"' || l_descr_tab(i)
                                             .col_name || '","type":"date"},';
                        ELSE
                                l_row_data := '{"name":"' || l_descr_tab(i)
                                             .col_name || '","type":"text"},';
                END CASE;
                dbms_lob.writeappend(l_header_clob
                                    ,length(l_row_data)
                                    ,l_row_data);
        END LOOP;
        l_header_clob := rtrim(l_header_clob
                              ,',') || '],"data":';

        EXECUTE IMMEDIATE l_exec_comm
                INTO l_ip_rfc;
        lcontext := dbms_xmlgen.newcontext(l_ip_rfc);
        dbms_xmlgen.setnullhandling(lcontext
                                   ,1);
        lxmldata := dbms_xmlgen.getxmltype(lcontext
                                          ,dbms_xmlgen.none);
        -- this is a XSL for JSON
        lxsl := '<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">[<xsl:for-each select="/ROWSET/*">
  {<xsl:for-each select="./*">
    "<xsl:value-of select="name()"/>":"<xsl:value-of select="text()"/>"<xsl:choose>
      <xsl:when test="position()!= last()">,</xsl:when>
    </xsl:choose>
   </xsl:for-each>
  }<xsl:choose>
      <xsl:when test="position() != last()">,</xsl:when>
    </xsl:choose>
   </xsl:for-each>
]}]}</xsl:template></xsl:stylesheet>';

        lhtmloutput := lxmldata.transform(xmltype(lxsl));
        l_ret_clob  := lhtmloutput.getclobval();
        l_ret_clob  := REPLACE(l_ret_clob
                              ,'_x0020_'
                              ,' ');
        dbms_lob.writeappend(l_header_clob
                            ,length(l_ret_clob)
                            ,l_ret_clob);
        RETURN l_header_clob;
EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
                dbms_output.put_line(dbms_utility.format_error_backtrace);
                RETURN NULL;
END get_json_fnc;

Open in new window


Now call this function by passing the first function which we have created earlier as like below:
SQL> SELECT get_json_fnc('get_emp_data()') AS contact_json FROM dual;

Open in new window


Output will be as per the required JSON format mentioned earlier, after running the function we will get the result as below:

{"metadata":[{"name":"EMPNO","type":"number"},{"name":"ENAME","type":"text"},{"name":"JOB","type":"text"},{"name":"DEPTNO","type":"number"}],"data":[
  {
    "EMPNO":"7369",
    "ENAME":"SMITH",
    "JOB":"CLERK",
    "DEPTNO":"20"
  },
  {
    "EMPNO":"7499",
    "ENAME":"ALLEN",
    "JOB":"SALESMAN",
    "DEPTNO":"30"
  },
  {
    "EMPNO":"7521",
    "ENAME":"WARD",
    "JOB":"SALESMAN",
    "DEPTNO":"30"
  },
  {
    "EMPNO":"7566",
    "ENAME":"JONES",
    "JOB":"MANAGER",
    "DEPTNO":"20"
  },
  {
    "EMPNO":"7654",
    "ENAME":"MARTIN",
    "JOB":"SALESMAN",
    "DEPTNO":"30"
  },
  {
    "EMPNO":"7698",
    "ENAME":"BLAKE",
    "JOB":"MANAGER",
    "DEPTNO":"30"
  },
  {
    "EMPNO":"7782",
    "ENAME":"CLARK",
    "JOB":"MANAGER",
    "DEPTNO":"10"
  },
  {
    "EMPNO":"7788",
    "ENAME":"SCOTT",
    "JOB":"ANALYST",
    "DEPTNO":"20"
  },
  {
    "EMPNO":"7839",
    "ENAME":"KING",
    "JOB":"PRESIDENT",
    "DEPTNO":"10"
  },
  {
    "EMPNO":"7844",
    "ENAME":"TURNER",
    "JOB":"SALESMAN",
    "DEPTNO":"30"
  },
  {
    "EMPNO":"7876",
    "ENAME":"ADAMS",
    "JOB":"CLERK",
    "DEPTNO":"20"
  },
  {
    "EMPNO":"7900",
    "ENAME":"JAMES",
    "JOB":"CLERK",
    "DEPTNO":"30"
  },
  {
    "EMPNO":"7902",
    "ENAME":"FORD",
    "JOB":"ANALYST",
    "DEPTNO":"20"
  },
  {
    "EMPNO":"7934",
    "ENAME":"MILLER",
    "JOB":"CLERK",
    "DEPTNO":"10"
  }
]}]}

Open in new window


You can generate other text formats by changing the XSL transformation definition.

The idea to achieve this was taken from generating HTML format from SYS_REFCURSOR. And here is the link to the original article by THOMAS KYTE  that inspired this one:
http://tkyte.blogspot.in/2006/01/i-like-online-communities.html
6
Author:Swadhin Ray
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free