I changed this line:
l_ret_clob := dbms_xmlgen.convert(lhtmloutput.getclobval(),dbms_xmlgen.ENTITY_DECODE );
SQL> create or replace function foo
2 return sys_refcursor as
3 c sys_refcursor;
4 begin
5 open c for select empno,ename,hiredate
6 from scott.emp
7 where deptno = 10;
8 return c;
9 end;
10 /
Function created.
SQL>
SQL> CREATE OR REPLACE FUNCTION get_json_fnc(ip_rfc VARCHAR2) RETURN CLOB AS
2 lhtmloutput xmltype;
3 lxsl LONG;
4 lxmldata xmltype;
5 lcontext dbms_xmlgen.ctxhandle;
6 l_ret_clob CLOB;
7 desc_cur NUMBER;
8 l_descr_tab dbms_sql.desc_tab2;
9 l_num_cols NUMBER;
10 l_header_clob CLOB;
11 l_row_data VARCHAR2(100);
12 l_ip_rfc SYS_REFCURSOR;
13 l_exec_comm VARCHAR2(250);
14 BEGIN
15 l_exec_comm := 'SELECT ' || ip_rfc || ' from dual';
16
17 EXECUTE IMMEDIATE l_exec_comm
18 INTO l_ip_rfc;
19
20 l_header_clob := '{"metadata":[';
21 desc_cur := dbms_sql.to_cursor_number(l_ip_rfc);
22
23 dbms_sql.describe_columns2(desc_cur
24 ,l_num_cols
25 ,l_descr_tab);
26
27 FOR i IN 1 .. l_num_cols
28 LOOP
29 CASE
30 WHEN l_descr_tab(i).col_type IN (2
31 ,8) THEN
32 l_row_data := '{"name":"' || l_descr_tab(i)
33 .col_name || '","type":"number"},';
34 WHEN l_descr_tab(i).col_type = 12 THEN
35 l_row_data := '{"name":"' || l_descr_tab(i)
36 .col_name || '","type":"date"},';
37 ELSE
38 l_row_data := '{"name":"' || l_descr_tab(i)
39 .col_name || '","type":"text"},';
40 END CASE;
41 dbms_lob.writeappend(l_header_clob
42 ,length(l_row_data)
43 ,l_row_data);
44 END LOOP;
45 l_header_clob := rtrim(l_header_clob
46 ,',') || '],"data":';
47
48 EXECUTE IMMEDIATE l_exec_comm
49 INTO l_ip_rfc;
50 lcontext := dbms_xmlgen.newcontext(l_ip_rfc);
51 dbms_xmlgen.setnullhandling(lcontext
52 ,1);
53 lxmldata := dbms_xmlgen.getxmltype(lcontext,dbms_xmlgen.none);
54
55 -- this is a XSL for JSON
56 lxsl := '<?xml version="1.0" encoding="ISO-8859-1"?>
57 <xsl:stylesheet version="1.0"
58 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
59 <xsl:output method="html"/>
60 <xsl:template match="/">[<xsl:for-each select="/ROWSET/*">
61 {<xsl:for-each select="./*">
62 "<xsl:value-of select="name()"/>":"<xsl:value-of select="text()"/>"<xsl:choose>
63 <xsl:when test="position()!= last()">,</xsl:when>
64 </xsl:choose>
65 </xsl:for-each>
66 }<xsl:choose>
67 <xsl:when test="position() != last()">,</xsl:when>
68 </xsl:choose>
69 </xsl:for-each>
70 ]}]}</xsl:template></xsl:stylesheet>';
71
72 lhtmloutput := lxmldata.transform(xmltype(lxsl));
73 l_ret_clob := dbms_xmlgen.convert(lhtmloutput.getclobval(),dbms_xmlgen.ENTITY_DECODE );
74 l_ret_clob := REPLACE(l_ret_clob
75 ,'_x0020_'
76 ,' ');
77 dbms_lob.writeappend(l_header_clob
78 ,length(l_ret_clob)
79 ,l_ret_clob);
80 RETURN l_header_clob;
81 EXCEPTION
82 WHEN OTHERS THEN
83 dbms_output.put_line(SQLERRM);
84 dbms_output.put_line(dbms_utility.format_error_backtrace);
85 RETURN NULL;
86 END get_json_fnc;
87 /
Function created.
SQL> sho err
No errors.
SQL>
SQL> set long 50000
SQL> select get_json_fnc('foo') from dual;
GET_JSON_FNC('FOO')
--------------------------------------------------------------------------------
{"metadata":[{"name":"EMPNO","type":"number"},{"name":"ENAME","type":"text"},{"n
ame":"HIREDATE","type":"date"}],"data":[
{
"EMPNO":"7782",
"ENAME":"CLARK",
"HIREDATE":"09-JUN-81"
},
{
"EMPNO":"7839",
"ENAME":"KING",
"HIREDATE":"17-NOV-81"
},
{
"EMPNO":"7934",
"ENAME":"MILLER",
"HIREDATE":"23-JAN-82"
}
]}]}
1 row selected.
SQL>
SQL>