ORA-20000

Sometimes you need to know Java-machine settings for the proper coding.

To do this, use the function dbms_java.get_ojvm_property.

SQL> set pagesize 1000
SQL> 
SQL> with java_properties as
  2    (
  3      select 'java.version' as property, q'~Java Runtime Environment version~' as description from dual union all
  4      select 'java.vendor' as property, q'~Java Runtime Environment vendor~' as description from dual union all
  5      select 'java.vendor.url' as property, q'~Java vendor URL~' as description from dual union all
  6      select 'java.home' as property, q'~Java installation directory~' as description from dual union all
  7      select 'java.vm.specification.version' as property, q'~Java Virtual Machine specification version~' as description from dual union all
  8      select 'java.vm.specification.vendor' as property, q'~Java Virtual Machine specification vendor~' as description from dual union all
  9      select 'java.vm.specification.name' as property, q'~Java Virtual Machine specification name~' as description from dual union all
 10      select 'java.vm.version' as property, q'~Java Virtual Machine implementation version~' as description from dual union all
 11      select 'java.vm.vendor' as property, q'~Java Virtual Machine implementation vendor~' as description from dual union all
 12      select 'java.vm.name' as property, q'~Java Virtual Machine implementation name~' as description from dual union all
 13      select 'java.specification.version' as property, q'~Java Runtime Environment specification version~' as description from dual union all
 14      select 'java.specification.vendor' as property, q'~Java Runtime Environment specification vendor~' as description from dual union all
 15      select 'java.specification.name' as property, q'~Java Runtime Environment specification name~' as description from dual union all
 16      select 'java.class.version' as property, q'~Java class format version number~' as description from dual union all
 17      select 'java.class.path' as property, q'~Java class path~' as description from dual union all
 18      select 'java.library.path' as property, q'~List of paths to search when loading libraries~' as description from dual union all
 19      select 'java.io.tmpdir' as property, q'~Default temp file path~' as description from dual union all
 20      select 'java.compiler' as property, q'~Name of JIT compiler to use~' as description from dual union all
 21      select 'java.ext.dirs' as property, q'~Path of extension directory or directories~' as description from dual union all
 22      select 'os.name' as property, q'~Operating system name~' as description from dual union all
 23      select 'os.arch' as property, q'~Operating system architecture~' as description from dual union all
 24      select 'os.version' as property, q'~Operating system version~' as description from dual union all
 25      select 'file.separator' as property, q'~File separator ("/" on UNIX)~' as description from dual union all
 26      select 'path.separator' as property, q'~Path separator (":" on UNIX)~' as description from dual union all
 27      select 'line.separator' as property, q'~Line separator ("\n" on UNIX)~' as description from dual union all
 28      select 'user.name' as property, q'~User's account name~' as description from dual union all
 29      select 'user.home' as property, q'~User's home directory~' as description from dual union all
 30      select 'user.dir' as property, q'~User's current working directory~' as description from dual
 31    )
 32  select dbms_java.get_ojvm_property(java_properties.property) as value, java_properties.property, java_properties.description
 33  from   java_properties
 34  /
 
VALUE                                                                            PROPERTY                      DESCRIPTION
-------------------------------------------------------------------------------- ----------------------------- ----------------------------------------------
1.5.0_10                                                                         java.version                  Java Runtime Environment version
Oracle Corporation                                                               java.vendor                   Java Runtime Environment vendor
http://www.oracle.com/java/                                                      java.vendor.url               Java vendor URL
D:\ORACLE\DB112\JAVAVM\                                                          java.home                     Java installation directory
1.0                                                                              java.vm.specification.version Java Virtual Machine specification version
Sun Microsystems Inc.                                                            java.vm.specification.vendor  Java Virtual Machine specification vendor
Java Virtual Machine Specification                                               java.vm.specification.name    Java Virtual Machine specification name
1.5.0_01                                                                         java.vm.version               Java Virtual Machine implementation version
Oracle Corporation                                                               java.vm.vendor                Java Virtual Machine implementation vendor
JServer VM                                                                       java.vm.name                  Java Virtual Machine implementation name
1.5                                                                              java.specification.version    Java Runtime Environment specification version
Sun Microsystems Inc.                                                            java.specification.vendor     Java Runtime Environment specification vendor
Java Platform API Specification                                                  java.specification.name       Java Runtime Environment specification name
48.0                                                                             java.class.version            Java class format version number
                                                                                 java.class.path               Java class path
d:\oracle\db112\bin;.;C:\WINDOWS\system32;C:\WINDOWS;D:\oracle\db112\bin;C:\WIND java.library.path             List of paths to search when loading libraries
C:\WINDOWS\TEMP\                                                                 java.io.tmpdir                Default temp file path
                                                                                 java.compiler                 Name of JIT compiler to use
                                                                                 java.ext.dirs                 Path of extension directory or directories
Windows XP                                                                       os.name                       Operating system name
x86                                                                              os.arch                       Operating system architecture
5.1                                                                              os.version                    Operating system version
\                                                                                file.separator                File separator ("/" on UNIX)
;                                                                                path.separator                Path separator (":" on UNIX)
                                                                                 line.separator                Line separator ("\n" on UNIX)
                                                                                 user.name                     User's account name
                                                                                 user.home                     User's home directory
D:\ORACLE\DB112                                                                  user.dir                      User's current working directory
 
28 rows selected
 
SQL>

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>

Add namespace, method 1:

select insertchildxml(xmltype('<a id="a1"><b id="b1">bbb_value<!-- dummy --></b></a>'),
                      '/a',
                      '@xmlns',
                      'http://www.my.com/scheme')
from   dual;

Result:

<a id="a1" xmlns="http://www.my.com/scheme"><b id="b1">bbb_value<!-- dummy --></b></a>

Add namespace, method 2:

select xmltransform(
        xmltype('<a id="a1"><b id="b1">bbb_value<!-- dummy --></b></a>'),
        xmltype(
'<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns="http://www.my.com/scheme">
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="node()|@*" />
    </xsl:element>
  </xsl:template>
  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>
  </xsl:template>
  <xsl:template match="processing-instruction()|comment()">
    <xsl:copy>
      <xsl:apply-templates select="node()"/>
    </xsl:copy>
  </xsl:template>   
</xsl:stylesheet>
')
       ).getclobval()
from   dual;

Result:

<?xml version="1.0" encoding="utf-8"?>
<a xmlns="http://www.my.com/scheme" id="a1">
 <b id="b1">bbb_value<!-- dummy --></b>
</a>

Update namespace:

select xmltransform(
        xmltype('<a xmlns="http://www.my.com/scheme_old" id="a1"><b id="b1">bbb_value<!-- dummy --></b></a>'),
        xmltype(
'<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns="http://www.my.com/scheme">
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="node()|@*" />
    </xsl:element>
  </xsl:template>
  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>
  </xsl:template>
  <xsl:template match="processing-instruction()|comment()">
    <xsl:copy>
      <xsl:apply-templates select="node()"/>
    </xsl:copy>
  </xsl:template>   
</xsl:stylesheet>
')
       )
from   dual;

Result:

<?xml version="1.0" encoding="utf-8"?>
<a xmlns="http://www.my.com/scheme" id="a1">
 <b id="b1">bbb_value<!-- dummy --></b>
</a>

Delete namespace:

select xmltransform(
        xmltype('<a xmlns="http://www.my.com/scheme_old" id="a1"><b id="b1">bbb_value<!-- dummy --></b></a>'),
        xmltype(
'<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  >
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="node()|@*" />
    </xsl:element>
  </xsl:template>
  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>
  </xsl:template>
  <xsl:template match="processing-instruction()|comment()">
    <xsl:copy>
      <xsl:apply-templates select="node()"/>
    </xsl:copy>
  </xsl:template>   
</xsl:stylesheet>
')
       )
from   dual;

Result:

<?xml version="1.0" encoding="utf-8"?>
<a id="a1">
 <b id="b1">bbb_value<!-- dummy --></b>
</a>

Oracle 10g introduced a new mechanism to write in quotes. In previous versions it was necessary to use double quotes. You can now use the syntax q'quote_delimiter ... quote_delimiter'.see more...

SQL> set serveroutput on
SQL>
SQL> declare
 2    vs varchar2(100);
 3    vcur sys_refcursor;
 4 
 5    vSQL varchar2(2000) :=
 6  q'@
 7  select 'MARY' as nm from dual union all
 8  select q'BBEN'S BOOKB' as nm from dual union all
 9  select q'XMARK'S CATX' as nm from dual union all
10  select q'[TED'S DOG]' as nm from dual
11  @';
12  begin
13 
14    open vcur for vSQL;
15 
16    dbms_output.put_line(vSQL);
17    loop
18      fetch vcur into vs;
19      exit when vcur%notfound;
20      dbms_output.put_line(vs);
21    end loop;
22    close vcur;
23  end;
24  /


select 'MARY' as nm from dual union all
select q'BBEN'S BOOKB' as nm from dual union all
select q'XMARK'S CATX' as nm from dual union all
select q'[TED'S DOG]' as nm from dual

MARY
BEN'S BOOK
MARK'S CAT
TED'S DOG

PL/SQL procedure successfully completed