There are many ways to generate JSON files from Oracle Database (11g, 12c, ...), but when it comes to Oracle DataBase 10g it's a bit of difficult.
PLSQL-utils is a method can convert SQL and REF_CURSOR to JSON.
You can download it from here: https://code.google.com/archive/p/plsql-utils/downloads
To install only the required Package:
PLSQL-utils is a method can convert SQL and REF_CURSOR to JSON.
You can download it from here: https://code.google.com/archive/p/plsql-utils/downloads
To install only the required Package:
create or replace type t_str_array as table of varchar2(4000); / create or replace package json_util_pkg as /* Purpose: JSON utilities for PL/SQL Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 30.01.2010 Created */ -- generate JSON from REF Cursor function ref_cursor_to_json (p_ref_cursor in sys_refcursor, p_max_rows in number := null, p_skip_rows in number := null) return clob; -- generate JSON from SQL statement function sql_to_json (p_sql in varchar2, p_param_names in t_str_array := null, p_param_values in t_str_array := null, p_max_rows in number := null, p_skip_rows in number := null) return clob; end json_util_pkg; / create or replace package body json_util_pkg as /* Purpose: JSON utilities for PL/SQL Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 30.01.2010 Created */ g_json_null_object constant varchar2(20) := '{ }'; function get_xml_to_json_stylesheet return varchar2 as begin /* Purpose: return XSLT stylesheet for XML to JSON transformation Remarks: see http://code.google.com/p/xml2json-xslt/ Who Date Description ------ ---------- ------------------------------------- MBR 30.01.2010 Created MBR 30.01.2010 Added fix for nulls */ return '<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!-- Copyright (c) 2006, Doeke Zanstra All rights reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of the dzLib nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. --> <xsl:output indent="no" omit-xml-declaration="yes" method="text" encoding="UTF-8" media-type="text/x-json"/> <xsl:strip-space elements="*"/> <!--contant--> <xsl:variable name="d">0123456789</xsl:variable> <!-- ignore document text --> <xsl:template match="text()[preceding-sibling::node() or following-sibling::node()]"/> <!-- string --> <xsl:template match="text()"> <xsl:call-template name="escape-string"> <xsl:with-param name="s" select="."/> </xsl:call-template> </xsl:template> <!-- Main template for escaping strings; used by above template and for object-properties Responsibilities: placed quotes around string, and chain up to next filter, escape-bs-string --> <xsl:template name="escape-string"> <xsl:param name="s"/> <xsl:text>"</xsl:text> <xsl:call-template name="escape-bs-string"> <xsl:with-param name="s" select="$s"/> </xsl:call-template> <xsl:text>"</xsl:text> </xsl:template> <!-- Escape the backslash (\) before everything else. --> <xsl:template name="escape-bs-string"> <xsl:param name="s"/> <xsl:choose> <xsl:when test="contains($s,''\'')"> <xsl:call-template name="escape-quot-string"> <xsl:with-param name="s" select="concat(substring-before($s,''\''),''\\'')"/> </xsl:call-template> <xsl:call-template name="escape-bs-string"> <xsl:with-param name="s" select="substring-after($s,''\'')"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="escape-quot-string"> <xsl:with-param name="s" select="$s"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- Escape the double quote ("). --> <xsl:template name="escape-quot-string"> <xsl:param name="s"/> <xsl:choose> <xsl:when test="contains($s,''"'')"> <xsl:call-template name="encode-string"> <xsl:with-param name="s" select="concat(substring-before($s,''"''),''\"'')"/> </xsl:call-template> <xsl:call-template name="escape-quot-string"> <xsl:with-param name="s" select="substring-after($s,''"'')"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="encode-string"> <xsl:with-param name="s" select="$s"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- Replace tab, line feed and/or carriage return by its matching escape code. Can''t escape backslash or double quote here, because they don''t replace characters (� becomes \t), but they prefix characters (\ becomes \\). Besides, backslash should be seperate anyway, because it should be processed first. This function can''t do that. --> <xsl:template name="encode-string"> <xsl:param name="s"/> <xsl:choose> <!-- tab --> <xsl:when test="contains($s,''	'')"> <xsl:call-template name="encode-string"> <xsl:with-param name="s" select="concat(substring-before($s,''	''),''\t'',substring-after($s,''	''))"/> </xsl:call-template> </xsl:when> <!-- line feed --> <xsl:when test="contains($s,''
'')"> <xsl:call-template name="encode-string"> <xsl:with-param name="s" select="concat(substring-before($s,''
''),''\n'',substring-after($s,''
''))"/> </xsl:call-template> </xsl:when> <!-- carriage return --> <xsl:when test="contains($s,''
'')"> <xsl:call-template name="encode-string"> <xsl:with-param name="s" select="concat(substring-before($s,''
''),''\r'',substring-after($s,''
''))"/> </xsl:call-template> </xsl:when> <xsl:otherwise><xsl:value-of select="$s"/></xsl:otherwise> </xsl:choose> </xsl:template> <!-- number (no support for javascript mantise) --> <xsl:template match="text()[not(string(number())=''NaN'')]"> <xsl:value-of select="."/> </xsl:template> <!-- boolean, case-insensitive --> <xsl:template match="text()[translate(.,''TRUE'',''true'')=''true'']">true</xsl:template> <xsl:template match="text()[translate(.,''FALSE'',''false'')=''false'']">false</xsl:template> <!-- item:null --> <xsl:template match="*[count(child::node())=0]"> <xsl:call-template name="escape-string"> <xsl:with-param name="s" select="local-name()"/> </xsl:call-template> <xsl:text>:null</xsl:text> <xsl:if test="following-sibling::*">,</xsl:if> <xsl:if test="not(following-sibling::*)">}</xsl:if> <!-- MBR 30.01.2010: added this line as it appeared to be missing from stylesheet --> </xsl:template> <!-- object --> <xsl:template match="*" name="base"> <xsl:if test="not(preceding-sibling::*)">{</xsl:if> <xsl:call-template name="escape-string"> <xsl:with-param name="s" select="name()"/> </xsl:call-template> <xsl:text>:</xsl:text> <xsl:apply-templates select="child::node()"/> <xsl:if test="following-sibling::*">,</xsl:if> <xsl:if test="not(following-sibling::*)">}</xsl:if> </xsl:template> <!-- array --> <xsl:template match="*[count(../*[name(../*)=name(.)])=count(../*) and count(../*)>1]"> <xsl:if test="not(preceding-sibling::*)">[</xsl:if> <xsl:choose> <xsl:when test="not(child::node())"> <xsl:text>null</xsl:text> </xsl:when> <xsl:otherwise> <xsl:apply-templates select="child::node()"/> </xsl:otherwise> </xsl:choose> <xsl:if test="following-sibling::*">,</xsl:if> <xsl:if test="not(following-sibling::*)">]</xsl:if> </xsl:template> <!-- convert root element to an anonymous container --> <xsl:template match="/"> <xsl:apply-templates select="node()"/> </xsl:template> </xsl:stylesheet>'; end get_xml_to_json_stylesheet; function ref_cursor_to_json (p_ref_cursor in sys_refcursor, p_max_rows in number := null, p_skip_rows in number := null) return clob as l_ctx dbms_xmlgen.ctxhandle; l_num_rows pls_integer; l_xml xmltype; l_json xmltype; l_returnvalue clob; begin /* Purpose: generate JSON from REF Cursor Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 30.01.2010 Created */ l_ctx := dbms_xmlgen.newcontext (p_ref_cursor); dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag); -- for pagination if p_max_rows is not null then dbms_xmlgen.setmaxrows (l_ctx, p_max_rows); end if; if p_skip_rows is not null then dbms_xmlgen.setskiprows (l_ctx, p_skip_rows); end if; -- get the XML content l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none); l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx); dbms_xmlgen.closecontext (l_ctx); close p_ref_cursor; if l_num_rows > 0 then -- perform the XSL transformation l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet)); l_returnvalue := l_json.getclobval(); else l_returnvalue := g_json_null_object; end if; l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode); return l_returnvalue; end ref_cursor_to_json; function sql_to_json (p_sql in varchar2, p_param_names in t_str_array := null, p_param_values in t_str_array := null, p_max_rows in number := null, p_skip_rows in number := null) return clob as l_ctx dbms_xmlgen.ctxhandle; l_num_rows pls_integer; l_xml xmltype; l_json xmltype; l_returnvalue clob; begin /* Purpose: generate JSON from SQL statement Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 30.01.2010 Created MBR 28.07.2010 Handle null value in bind variable value (issue and solution reported by Matt Nolan) */ l_ctx := dbms_xmlgen.newcontext (p_sql); dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag); -- bind variables, if any if p_param_names is not null then for i in 1..p_param_names.count loop dbms_xmlgen.setbindvalue (l_ctx, p_param_names(i), nvl(p_param_values(i), '')); end loop; end if; -- for pagination if p_max_rows is not null then dbms_xmlgen.setmaxrows (l_ctx, p_max_rows); end if; if p_skip_rows is not null then dbms_xmlgen.setskiprows (l_ctx, p_skip_rows); end if; -- get the XML content l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none); l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx); dbms_xmlgen.closecontext (l_ctx); -- perform the XSL transformation if l_num_rows > 0 then l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet)); l_returnvalue := l_json.getclobval(); l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode); else l_returnvalue := g_json_null_object; end if; return l_returnvalue; end sql_to_json; end json_util_pkg; /
another way to generate json from versions before 12.2.
ReplyDelete~~~
SELECT '{"items":['
|| REPLACE (
XMLCAST (
XMLAGG (XMLELEMENT ("e", '{"Value_id":"' || Value_id || '"}')
ORDER BY Value_id) AS CLOB),
'}{',
'},{')
|| ']}'
PAT_EXPENS
FROM TABLEe;
~~~
Link: https://www.orafaq.com/forum/t/204909/