<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Creating JSON from Oracle using SYS_REFCURSOR

Published on
33,117 Points
26,517 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
  • 6
  • 2
  • 2
  • +5
16 Comments

Expert Comment

by:mickey_egp73
Did not work returned the following error
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "GET_JSON_FNC", line 87
0
LVL 17

Author Comment

by:Swadhin Ray
Not sure why are you getting this error I tried with other instance and used the same function what was mentioned in the article. And works for me.

Here is the example :

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


The Function
My select query :

The Select query for testing
The wrapper to use the select query.

Wrapper Function
The final output after calling the wrapper .

Final Output
{<FIRST_NAME=IS_Analyst_1,LAST_NAME=IS_Analyst_1,USER_NAME=IS_Analyst_1>,<FIRST_NAME=IS_Analyst_2,LAST_NAME=IS_Analyst_2,USER_NAME=IS_Analyst_2>,<FIRST_NAME=IS_Manager_1,LAST_NAME=IS_Manager_1,USER_NAME=IS_Manager_1>,<FIRST_NAME=IT_Manager_1,LAST_NAME=IT_Manager_1,USER_NAME=IT_Manager_1>,<FIRST_NAME=IT_Manager_2,LAST_NAME=IT_Manager_2,USER_NAME=IT_Manager_2>,}
0

Expert Comment

by:murthy avr
dbms_lob.writeappend(l_header_clob
                            ,length(l_ret_clob)
                            ,l_ret_clob);

Getting Error while crossing more than 32767 char in result set.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "GET_JSON_FNC", line 53

Please help
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LVL 17

Author Comment

by:Swadhin Ray
>> Getting Error while crossing more than 32767 char in result set.

The input is a blob for the package , but what is the max you are looking for.
0

Expert Comment

by:Shine G
How to remove double quotes for number data type?
Now I am getting like,
"QTY":"1"
It should be like this,
"QTY":1
0
LVL 15

Expert Comment

by:Shaju Kumbalath
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00118: Warning: undefined entity "nbsp"

How to handle this
0

Expert Comment

by:Matt Davis
Hi. This works well!
Do you think it's possible to generate a json file from two tables? What i mean is, the header of the output json is from TABLE A and the datas are on TABLE B.
0
LVL 17

Author Comment

by:Swadhin Ray
Yes you can if you want to join the table and show the out put or generate the two output and concatenate it.
2

Expert Comment

by:rila kumako
how ? Can you give us a sample ?
0

Expert Comment

by:rila kumako
thanks Matt , Got it :)
0
LVL 17

Author Comment

by:Swadhin Ray
@Matt : It would be helpful for other too to get the sample. @ rila kumako : If you can share your version others will also get some inputs too.
1

Expert Comment

by:Matt Davis
ok, so the solution if you want to generate a json file from two tables and combine it together, the sql statement would be like this:

select concat(get_json_fnc('fnc_1()'), get_json_fnc('fnc_2()')) as json_file from dual;

just use the same json function and concatenate it. works also with morethan two tables.
1

Expert Comment

by:KIRITHIKA THANGAVEL
I have to fetch 2,00,000 rows of data from table and convert to Json. Is it possible?.. Also, the function you have mentioned can handle large volume of data ?
0
LVL 17

Author Comment

by:Swadhin Ray
@KIRITHIKA: You can try out the function, I have not tested this for 2,00,000 rows
0

Expert Comment

by:KIRITHIKA THANGAVEL
Tried it. Its taking 22 minutes for completing the execution but could not see the result in CLOB object result. Is there a way to reduce the time?.
I am using 11.2 version
0
LVL 17

Author Comment

by:Swadhin Ray
you can post as a question on the performance issue . I believe other experts can also try to provide more information and believe me that helps.
0

Featured Post

CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month