×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Jon Nickerson
Added: Apr 2, 2018 7:35 PM
Views: 1
Tags: no tags
  1. CREATE OR REPLACE FUNCTION get_tab_data RETURN SYS_REFCURSOR AS
  2.         l_cursor SYS_REFCURSOR;
  3. BEGIN
  4.         OPEN l_cursor FOR
  5.                 SELECT * FROM WHERE rownum = 1;
  6.         RETURN l_cursor;
  7. END;
  8.  
  9. SELECT get_json_fnc('get_tab_data()') AS contact_json FROM dual;
  10.  
  11.  
  12. CREATE OR REPLACE FUNCTION get_json_fnc(ip_rfc VARCHAR2) RETURN CLOB AS
  13.         /*====================================================================================================+
  14.         FUNCTION:   GET_JSON_FNC
  15.  
  16.         SUMMARY:   TO generate JSON string dynamically from SYS_REFCURSOR
  17.  
  18.         Revision History:
  19.         Date          Name                             Revision      Description
  20.         ======================================================================================================
  21.          11-AUG-13     Swadhin Ray(Sloba)                   1.0       First Version
  22.         ======================================================================================================*/
  23.         lhtmloutput   xmltype;
  24.         lxsl          LONG;
  25.         lxmldata      xmltype;
  26.         lcontext      dbms_xmlgen.ctxhandle;
  27.         l_ret_clob    CLOB;
  28.         desc_cur      NUMBER;
  29.         l_descr_tab   dbms_sql.desc_tab2;
  30.         l_num_cols    NUMBER;
  31.         l_header_clob CLOB;
  32.         l_row_data    VARCHAR2(100);
  33.         l_ip_rfc      SYS_REFCURSOR;
  34.         l_exec_comm   VARCHAR2(250);
  35. BEGIN
  36.         l_exec_comm := 'SELECT ' || ip_rfc || ' from dual';
  37.  
  38.         EXECUTE IMMEDIATE l_exec_comm
  39.                 INTO l_ip_rfc;
  40.  
  41.         l_header_clob := '{"metadata":[';
  42.         desc_cur      := dbms_sql.to_cursor_number(l_ip_rfc);
  43.  
  44.         dbms_sql.describe_columns2(desc_cur
  45.                                   ,l_num_cols
  46.                                   ,l_descr_tab);
  47.  
  48.         FOR i IN 1 .. l_num_cols
  49.         LOOP
  50.                 CASE
  51.                         WHEN l_descr_tab(i).col_type IN (2
  52.                                          ,8) THEN
  53.                                 l_row_data := '{"name":"' || l_descr_tab(i)
  54.                                              .col_name || '","type":"number"},';
  55.                         WHEN l_descr_tab(i).col_type = 12 THEN
  56.                                 l_row_data := '{"name":"' || l_descr_tab(i)
  57.                                              .col_name || '","type":"date"},';
  58.                         ELSE
  59.                                 l_row_data := '{"name":"' || l_descr_tab(i)
  60.                                              .col_name || '","type":"text"},';
  61.                 END CASE;
  62.                 dbms_lob.writeappend(l_header_clob
  63.                                     ,LENGTH(l_row_data)
  64.                                     ,l_row_data);
  65.         END LOOP;
  66.         l_header_clob := rtrim(l_header_clob
  67.                               ,',') || '],"data":';
  68.  
  69.         EXECUTE IMMEDIATE l_exec_comm
  70.                 INTO l_ip_rfc;
  71.         lcontext := dbms_xmlgen.newcontext(l_ip_rfc);
  72.         dbms_xmlgen.setnullhandling(lcontext
  73.                                    ,1);
  74.         lxmldata := dbms_xmlgen.getxmltype(lcontext
  75.                                           ,dbms_xmlgen.none);
  76.         -- this is a XSL for JSON
  77.         lxsl := '<?xml version="1.0" encoding="ISO-8859-1"?>
  78. <xsl:stylesheet version="1.0"
  79.  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  80. <xsl:output method="html"/>
  81. <xsl:template match="/">[<xsl:for-each select="/ROWSET/*">
  82.  {<xsl:for-each select="./*">
  83.    "<xsl:value-of select="name()"/>":"<xsl:value-of select="text()"/>"<xsl:choose>
  84.      <xsl:when test="position()!= last()">,</xsl:when>
  85.    </xsl:choose>
  86.   </xsl:for-each>
  87.  }<xsl:choose>
  88.      <xsl:when test="position() != last()">,</xsl:when>
  89.    </xsl:choose>
  90.   </xsl:for-each>
  91. ]}]}</xsl:template></xsl:stylesheet>';
  92.  
  93.         lhtmloutput := lxmldata.transform(xmltype(lxsl));
  94.         l_ret_clob  := lhtmloutput.getclobval();
  95.         l_ret_clob  := REPLACE(l_ret_clob
  96.                               ,'_x0020_'
  97.                               ,' ');
  98.         dbms_lob.writeappend(l_header_clob
  99.                             ,LENGTH(l_ret_clob)
  100.                             ,l_ret_clob);
  101.         RETURN l_header_clob;
  102. EXCEPTION
  103.         WHEN OTHERS THEN
  104.                 dbms_output.put_line(SQLERRM);
  105.                 dbms_output.put_line(dbms_utility.format_error_backtrace);
  106.                 RETURN NULL;
  107. END get_json_fnc;
  108.