Browse All Articles > Creating JSON from Oracle using SYS_REFCURSOR
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:
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.
Now we need the output of the above mentioned query as a SYS_REFCURSOR.
Let us create the function to return SYS_REFCURSOR:
Run the function:
![Output of get_emp_data]()
Here is the function which will generate the JSON after passing the output of the first function:
Now call this function by passing the first function which we have created earlier as like below:
Output will be as per the required JSON format mentioned earlier, after running the function we will get the result as below:
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
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.]
}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.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.Run the function:
SQL> select get_emp_data() from dual;
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;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;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"
}
]}]}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
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (16)
Commented:
select concat(get_json_fnc('fnc_1
just use the same json function and concatenate it. works also with morethan two tables.
Commented:
Author
Commented:Commented:
I am using 11.2 version
Author
Commented:View More