As of 12cR1 (121.0.2) oracle doesn’t have any native API for converting relation data to JSON format.
But with SQLCL in place, that got easy with SQLFORMAT command or annotated JSON hint.
C:\Users\179818>sql /nolog
SQLcl: Release 4.2.0 Production on Wed Dec 14 16:55:56 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
New version: 4.2.0.1 available to download
idle> conn demo/demo@ora12c
Connected.
demo@ORA12C> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
demo@ORA12C> help set sqlformat
SET SQLFORMAT
SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}
demo@ORA12C> set sqlformat json
demo@ORA12C> select * from dept;
{"results":[{"columns":[{"name":"DEPTNO","type":"NUMBER"},{"name":"DNAME","type":"NUMBER"},{"name":"LOC","type":"NUMBER"}],"items":
[
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK"},{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"},{"deptno":30,"dname":"SALES","loc":"CHICAGO"},{"deptno":40,"dname":"OPERATIONS","lo
c":"BOSTON"}]}]}
4 rows selected.
demo@ORA12C> set sqlformat
SQL Format Cleared
demo@ORA12C> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
demo@ORA12C> select /*json*/ * from dept;
{"results":[{"columns":[{"name":"DEPTNO","type":"NUMBER"},{"name":"DNAME","type":"NUMBER"},{"name":"LOC","type":"NUMBER"}],"items":
[
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK"},{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"},{"deptno":30,"dname":"SALES","loc":"CHICAGO"},{"deptno":40,"dname":"OPERATIONS","lo
c":"BOSTON"}]}]}
4 rows selected.
demo@ORA12C>
However this option SET SQLFORMAT or JSON annotated hint is not available on sql*plus or JDBC/ODBC drivers, and we don’t have native API for converting relation data to JSON format yet.
How to server the application that requires JSON as output from the database? The workaround would be to convert the relation data to XML and then convert it to JSON using style sheet transformation.
demo@ORA12C> CREATE OR REPLACE FUNCTION refcursor_to_json(x VARCHAR2)
2 RETURN CLOB AS
3 lhtmloutput xmltype;
4 lxsl LONG;
5 lxmldata xmltype;
6 lcontext dbms_xmlgen.ctxhandle;
7 l_ret_clob CLOB;
8 desc_cur NUMBER;
9 l_descr_tab dbms_sql.desc_tab2;
10 l_num_cols NUMBER;
11 l_header_clob CLOB;
12 l_row_data VARCHAR2(100);
13 l_ip_rfc SYS_REFCURSOR;
14 l_exec_comm VARCHAR2(250);
15 n number ;
16 BEGIN
17 l_exec_comm := 'SELECT ' ||
18 dbms_assert.simple_sql_name(x)
19 || ' from dual';
20
21 EXECUTE IMMEDIATE l_exec_comm
22 INTO l_ip_rfc;
23
24 l_header_clob := '{"metadata":[';
25 desc_cur := dbms_sql.to_cursor_number(l_ip_rfc);
26
27 dbms_sql.describe_columns2(desc_cur
28 ,l_num_cols
29 ,l_descr_tab);
30
31 FOR i IN 1 .. l_num_cols
32 LOOP
33 CASE
34 WHEN l_descr_tab(i).col_type IN (2,8) THEN
35 l_row_data := '{"name":"' || l_descr_tab(i)
36 .col_name || '","type":"number"},';
37 WHEN l_descr_tab(i).col_type = 12 THEN
38 l_row_data := '{"name":"' || l_descr_tab(i)
39 .col_name || '","type":"date"},';
40 ELSE
41 l_row_data := '{"name":"' || l_descr_tab(i)
42 .col_name || '","type":"text"},';
43 END CASE;
44 dbms_lob.writeappend(l_header_clob
45 ,length(l_row_data)
46 ,l_row_data);
47 END LOOP;
48 l_header_clob := rtrim(l_header_clob
49 ,',') || '],"data":';
50
51 EXECUTE IMMEDIATE l_exec_comm
52 INTO l_ip_rfc;
53 lcontext := dbms_xmlgen.newcontext(l_ip_rfc);
54 dbms_xmlgen.setnullhandling(lcontext
55 ,1);
56 lxmldata := dbms_xmlgen.getxmltype(lcontext,dbms_xmlgen.none);
57
58 -- this is a XSL for JSON
59 lxsl := '<?xml version="1.0" encoding="ISO-8859-1"?>
60 <xsl:stylesheet version="1.0"
61 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
62 <xsl:output method="html"/>
63 <xsl:template match="/">[<xsl:for-each select="/ROWSET/*">
64 {<xsl:for-each select="./*">
65 "<xsl:value-of select="name()"/>":"<xsl:value-of select="text()"/>"<xsl:choose>
66 <xsl:when test="position()!= last()">,</xsl:when>
67 </xsl:choose>
68 </xsl:for-each>
69 }<xsl:choose>
70 <xsl:when test="position() != last()">,</xsl:when>
71 </xsl:choose>
72 </xsl:for-each>
73 ]}]}</xsl:template></xsl:stylesheet>';
74
75 lhtmloutput := lxmldata.transform(xmltype(lxsl));
76 l_ret_clob := dbms_xmlgen.convert(lhtmloutput.getclobval(),dbms_xmlgen.ENTITY_DECODE );
77 l_ret_clob := REPLACE(l_ret_clob
78 ,'_x0020_'
79 ,' ');
80 dbms_lob.writeappend(l_header_clob
81 ,length(l_ret_clob)
82 ,l_ret_clob);
83 RETURN l_header_clob;
84 EXCEPTION
85 WHEN OTHERS THEN
86 if dbms_sql.is_open(desc_cur) then
87 dbms_sql.close_cursor(desc_cur);
88 end if;
89 n := dbms_sql.to_cursor_number(l_ip_rfc);
90 if dbms_sql.is_open(n) then
91 dbms_sql.close_cursor(n);
92 end if;
93 raise;
94 END refcursor_to_json;
95 /
Function created.
demo@ORA12C>
demo@ORA12C> create or replace function foo
2 return sys_refcursor is
3 c sys_refcursor;
4 begin
5 open c for select *
6 from dept;
7 return c;
8 end;
9 /
Function created.
demo@ORA12C>
demo@ORA12C> variable x clob;
demo@ORA12C> exec :x := refcursor_to_json('foo');
PL/SQL procedure successfully completed.
demo@ORA12C> print x
X
--------------------------------------------------------------------------------
{"metadata":[{"name":"DEPTNO","type":"number"},{"name":"DNAME","type":"text"},{"
name":"LOC","type":"text"}],"data":[
{
"DEPTNO":"10",
"DNAME":"ACCOUNTING",
"LOC":"NEW YORK"
},
{
"DEPTNO":"20",
"DNAME":"RESEARCH",
"LOC":"DALLAS"
},
{
"DEPTNO":"30",
"DNAME":"SALES",
"LOC":"CHICAGO"
},
{
"DEPTNO":"40",
"DNAME":"OPERATIONS",
"LOC":"BOSTON"
}
]}]}
demo@ORA12C>
No comments:
Post a Comment