ORA-20000

Sometimes the built-in transformer is not working very well, so you have to use their own transformer.

Will create its own transformer.

create or replace and compile java source named ora_20000_xmlutility_jaxp as
package ru.ora_20000.xml;

import java.io.IOException;
import java.io.StringWriter;
import java.io.Reader;
import java.io.StringReader;
import java.io.Writer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import oracle.sql.CLOB;

import org.w3c.dom.Document;

import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

public class xmlutility_jaxp {

  private static Document parseDocument(String xmlText) throws Exception
  {
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    factory.setNamespaceAware(true);
    DocumentBuilder builder;
    try {
      builder = factory.newDocumentBuilder();
      return builder.parse(new InputSource(new StringReader(xmlText)));
    } catch (ParserConfigurationException e) {
      throw new Exception("parseDocument ERROR-ParserConfigurationException: " + e.getMessage());
    } catch (IOException e) {
      throw new Exception("parseDocument ERROR-IOException: " + e.getMessage());
    } catch (SAXException e) {
      throw new Exception("parseDocument ERROR-SAXException: " + e.getMessage());
    }
  }

  private static String processXSL(Document xmlDocument, Document xsltDocument) throws Exception
  {
    TransformerFactory tFactory = TransformerFactory.newInstance();
    Transformer transformer;
    StringWriter strWriter = new StringWriter();
    try {
      transformer = tFactory.newTransformer(new DOMSource(xsltDocument));
      transformer.setOutputProperty(OutputKeys.INDENT, "yes");
      transformer.transform(new DOMSource(xmlDocument), new StreamResult(strWriter));
      return strWriter.getBuffer().toString();
    } catch (TransformerConfigurationException e) {
      throw new Exception("processXSL ERROR-TransformerConfigurationException: " + e.getMessage());
    } catch (TransformerException e) {
      throw new Exception("processXSL ERROR-TransformerException: " + e.getMessage());
    }
  }

  public static String getStringFromClob(CLOB clob) throws Exception
  {
    Reader reader;
    StringBuffer stringBuffer;

    try
    {
      reader = clob.getCharacterStream();
      stringBuffer = new StringBuffer();

      int numchars;
      char[] buffer = new char[clob.getChunkSize()];
      while ((numchars = reader.read(buffer, 0, clob.getChunkSize())) != -1)
      {
        stringBuffer.append(buffer, 0, numchars);
      }
      reader.close();
    } catch (SQLException e) {
      throw new Exception("getStringFromClob ERROR-SQLException: " + e.getMessage());
    } catch (IOException e) {
      throw new Exception("getStringFromClob ERROR-IOException: " + e.getMessage());
    }
    return stringBuffer.toString();
  }

  public static CLOB getClobFromString(String string) throws Exception
  {
    CLOB clob = null;
    Writer writer = null;
    try {
      Connection connection = DriverManager.getConnection("jdbc:default:connection:");

      clob = CLOB.createTemporary(connection, true, CLOB.DURATION_SESSION);
      writer = clob.setCharacterStream(1L);
      if (string != null) { writer.write(string); }
      writer.flush();
      writer.close();
    } catch (SQLException e) {
      throw new Exception("getClobFromString ERROR-SQLException: " + e.getMessage());
    } catch (IOException e) {
      throw new Exception("getClobFromString ERROR-IOException: " + e.getMessage());
    } finally {
      if (writer != null) { writer.close(); }
    }
    return clob;
  }

  public static String transformXmlString(String xmlText, String xslText) throws Exception
  {
    if (xmlText == null || xslText == null)
    {
      return null;
    }

    Document xmlDoc = parseDocument(xmlText);
    Document xslDoc = parseDocument(xslText);

    return processXSL(xmlDoc, xslDoc);
  }

  public static CLOB transformXmlClob(CLOB xmlClob, CLOB xslClob) throws Exception
  {
    if (xmlClob == null || xslClob == null)
    {
      return null;
    }

    String xmlText = getStringFromClob(xmlClob);
    String xslText = getStringFromClob(xslClob);
    String xmlResult  = transformXmlString(xmlText, xslText);
    return getClobFromString(xmlResult);
  }

}

Let's create pl\sql wrapper for java methods.

create or replace package ora_20000_xmlutility_jaxp is

  function XMLTransformClob( xmlText clob
                            ,xslText clob
                           ) return clob;

  function XMLTransform( xmlText varchar2
                        ,xslText varchar2
                       ) return varchar2;


end ora_20000_xmlutility_jaxp;
/
create or replace package body ora_20000_xmlutility_jaxp is

  function XMLTransformClob( xmlText clob
                            ,xslText clob
                           ) return clob
  is
  language java name 'ru.ora_20000.xml.transformXmlClob(oracle.sql.CLOB, oracle.sql.CLOB) return oracle.sql.CLOB';

  function XMLTransform( xmlText varchar2
                        ,xslText varchar2
                       ) return varchar2
  is
  language java name 'ru.ora_20000.xml.transformXmlString(java.lang.String, java.lang.String) return java.lang.String';

end ora_20000_xmlutility_jaxp;
/

Run the sample for testing

SQL> 
SQL> -- create xml_sample_table
SQL> create table XML_SAMPLE
  2  (
  3    XMLTEXT clob not null
  4  )
  5  ;
 
Table created
 
SQL>

SQL> 
SQL> -- insert sample xml
SQL> insert into XML_SAMPLE
  2  values(q'~<?xml version="1.0"?>
  3  <catalog>
  4   <book id="bk101">
  5    <author>Gambardella, Matthew</author>
  6    <title>XML Developer's Guide</title>
  7    <genre>Computer</genre>
  8    <price>44.95</price>
  9    <publish_date>2000-10-01</publish_date>
 10    <description>An in-depth look at creating applications
 11    with XML.</description>
 12   </book>
 13   <book id="bk102">
 14    <author>Ralls, Kim</author>
 15    <title>Midnight Rain</title>
 16    <genre>Fantasy</genre>
 17    <price>5.95</price>
 18    <publish_date>2000-12-16</publish_date>
 19    <description>A former architect battles corporate zombies,
 20    an evil sorceress, and her own childhood to become queen
 21    of the world.</description>
 22   </book>
 23   <book id="bk103">
 24    <author>Corets, Eva</author>
 25    <title>Maeve Ascendant</title>
 26    <genre>Fantasy</genre>
 27    <price>5.95</price>
 28    <publish_date>2000-11-17</publish_date>
 29    <description>After the collapse of a nanotechnology
 30    society in England, the young survivors lay the
 31    foundation for a new society.</description>
 32   </book>
 33   <book id="bk104">
 34    <author>Corets, Eva</author>
 35    <title>Oberon's Legacy</title>
 36    <genre>Fantasy</genre>
 37    <price>5.95</price>
 38    <publish_date>2001-03-10</publish_date>
 39    <description>In post-apocalypse England, the mysterious
 40    agent known only as Oberon helps to create a new life
 41    for the inhabitants of London. Sequel to Maeve
 42    Ascendant.</description>
 43   </book>
 44   <book id="bk105">
 45    <author>Corets, Eva</author>
 46    <title>The Sundered Grail</title>
 47    <genre>Fantasy</genre>
 48    <price>5.95</price>
 49    <publish_date>2001-09-10</publish_date>
 50    <description>The two daughters of Maeve, half-sisters,
 51    battle one another for control of England. Sequel to
 52    Oberon's Legacy.</description>
 53   </book>
 54   <book id="bk106">
 55    <author>Randall, Cynthia</author>
 56    <title>Lover Birds</title>
 57    <genre>Romance</genre>
 58    <price>4.95</price>
 59    <publish_date>2000-09-02</publish_date>
 60    <description>When Carla meets Paul at an ornithology
 61    conference, tempers fly as feathers get ruffled.</description>
 62   </book>
 63   <book id="bk107">
 64    <author>Thurman, Paula</author>
 65    <title>Splish Splash</title>
 66    <genre>Romance</genre>
 67    <price>4.95</price>
 68    <publish_date>2000-11-02</publish_date>
 69    <description>A deep sea diver finds true love twenty
 70    thousand leagues beneath the sea.</description>
 71   </book>
 72   <book id="bk108">
 73    <author>Knorr, Stefan</author>
 74    <title>Creepy Crawlies</title>
 75    <genre>Horror</genre>
 76    <price>4.95</price>
 77    <publish_date>2000-12-06</publish_date>
 78    <description>An anthology of horror stories about roaches,
 79    centipedes, scorpions  and other insects.</description>
 80   </book>
 81   <book id="bk109">
 82    <author>Kress, Peter</author>
 83    <title>Paradox Lost</title>
 84    <genre>Science Fiction</genre>
 85    <price>6.95</price>
 86    <publish_date>2000-11-02</publish_date>
 87    <description>After an inadvertant trip through a Heisenberg
 88    Uncertainty Device, James Salway discovers the problems
 89    of being quantum.</description>
 90   </book>
 91   <book id="bk110">
 92    <author>O'Brien, Tim</author>
 93    <title>Microsoft .NET: The Programming Bible</title>
 94    <genre>Computer</genre>
 95    <price>36.95</price>
 96    <publish_date>2000-12-09</publish_date>
 97    <description>Microsoft's .NET initiative is explored in
 98    detail in this deep programmer's reference.</description>
 99   </book>
100   <book id="bk111">
101    <author>O'Brien, Tim</author>
102    <title>MSXML3: A Comprehensive Guide</title>
103    <genre>Computer</genre>
104    <price>36.95</price>
105    <publish_date>2000-12-01</publish_date>
106    <description>The Microsoft MSXML3 parser is covered in
107    detail, with attention to XML DOM interfaces, XSLT processing,
108    SAX and more.</description>
109   </book>
110   <book id="bk112">
111    <author>Galos, Mike</author>
112    <title>Visual Studio 7: A Comprehensive Guide</title>
113    <genre>Computer</genre>
114    <price>49.95</price>
115    <publish_date>2001-04-16</publish_date>
116    <description>Microsoft Visual Studio 7 is explored in depth,
117    looking at how Visual Basic, Visual C++, C#, and ASP+ are
118    integrated into a comprehensive development
119    environment.</description>
120   </book>
121  </catalog>
122  ~')
123  
124  /
 
1 row inserted
 
SQL> 

Xml example is taken from here "Sample XML File (books.xml)".

Simple xlst creates html.

select ora_20000_xmlutility_jaxp.xmltransformclob
        (xml_sample.xmltext,
q'~<?xml version="1.0" encoding="Windows-1251"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:sdt="http://localhost/schemas/data" version="1.0">
<xsl:output method="html"/>
<xsl:template match="/">
<html>
<head>
  <title>Books</title>
</head>
<body>
  <table border='1'>
    <tr><th>title</th><th>author</th></tr>
    <xsl:for-each select="/catalog/book">
    <tr>
      <td><xsl:value-of select="title"/></td>
      <td><xsl:value-of select="author"/></td>
    </tr>
    </xsl:for-each>
  </table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>~'                                                        
        )
from   xml_sample

And the result

<html xmlns:sdt="http://localhost/schemas/data">
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Books</title>
</head>
<body>
<table border="1">
<tr>
<th>title</th><th>author</th>
</tr>
<tr>
<td>XML Developer's Guide</td><td>Gambardella, Matthew</td>
</tr>
<tr>
<td>Midnight Rain</td><td>Ralls, Kim</td>
</tr>
<tr>
<td>Maeve Ascendant</td><td>Corets, Eva</td>
</tr>
<tr>
<td>Oberon's Legacy</td><td>Corets, Eva</td>
</tr>
<tr>
<td>The Sundered Grail</td><td>Corets, Eva</td>
</tr>
<tr>
<td>Lover Birds</td><td>Randall, Cynthia</td>
</tr>
<tr>
<td>Splish Splash</td><td>Thurman, Paula</td>
</tr>
<tr>
<td>Creepy Crawlies</td><td>Knorr, Stefan</td>
</tr>
<tr>
<td>Paradox Lost</td><td>Kress, Peter</td>
</tr>
<tr>
<td>Microsoft .NET: The Programming Bible</td><td>O'Brien, Tim</td>
</tr>
<tr>
<td>MSXML3: A Comprehensive Guide</td><td>O'Brien, Tim</td>
</tr>
<tr>
<td>Visual Studio 7: A Comprehensive Guide</td><td>Galos, Mike</td>
</tr>
</table>
</body>
</html>