ORA-20000

Automatic statistics gathering is enabled by default.
To check whether the statistics are collected automatically have to look at dba_scheduler_jobs.
To enable/disable statistics collection is necessary to use package dbms_scheduler.

select * 
from   dba_scheduler_jobs 
where  job_name = 'GATHER_STATS_JOB';

-- disable automatic statistics gathering
begin
  dbms_scheduler.disable('GATHER_STATS_JOB');
end;
/

-- enable automatic statistics gathering
begin
  dbms_scheduler.enable('GATHER_STATS_JOB');
end;
/

Statistics collected automatically if the property statistics_level is set to TYPICAL or ALL.

select *
from   v$parameter
where  v$parameter.name = 'statistics_level';

Job collects change all the tables, which included monitoring.

select *
from   user_tables
where  user_tables.monitoring = 'YES';

Job detects changes in the table (changing rows, adding and deleting). If the number of lines exceeds 10%, then the collection of statistics.

The information about changes of tables can be viewed in the user_tab_modifications view.

10% is not constant and can be changed using dbms_stats.set_global_prefs.

The values of all parameters of the collection of statistics can be found in the table sys.optstat_hist_control$.

begin
  dbms_stats.set_global_prefs(pname => 'STALE_PERCENT',
                              pvalue => 12);
end;
/

select *
from   sys.optstat_hist_control$;

More Automatic Statistics Gathering Читать полностью

Accidentally discovered an unexpected result occurs when an error occurs too_many_rows.
I tested two versions of the DB 10.2.0.3.0 and 11.2.0.1.0. I wrote a few tests.

• Example 1.
  before=#
  after=#
  It's ok. Variable is not changed.
• Example 2.
  before=#
  after=EMP
  I think that this result is failed. If an error occurs, in this case too_many_rows, the variable should not be changed.
• Example 3
  before=#
  after=EXAMPLE
  As in the previous example, the result of an error.
• Example 4.
  before=#
  after=#
  All is well. The result is correct. In contrast to Example 3, there is an implicit conversion from number to string, ie function is applied to_char, which may lead to a correct result.
• Example 5.
  before=#
  after=#
  It's ok. In this example, we explicitly apply the function, leading to a correct result.
• Example 6.
  before=#
  after=too_many_rows
  The last example demonstrates how to write code.
  When error handling is necessary to assign a variable the value that is needed.

SQL> set serveroutput on
SQL> 
SQL> --Example 1. no_data_found
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select user_objects.object_name
  7      into   vdummy
  8      from   user_objects
  9      where  1 = 2;
 10    exception
 11      when no_data_found then
 12        null;
 13      when too_many_rows then
 14        null;
 15    end;
 16    dbms_output.put_line('after=' || vdummy);
 17  end;
 18  /
 
before=#
after=#
 
PL/SQL procedure successfully completed
SQL> --Example 2. to_many_rows and column of table
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select user_objects.object_name
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11        null;
 12      when too_many_rows then
 13        null;
 14    end;
 15    dbms_output.put_line('after=' || vdummy);
 16  end;
 17  /
 
before=#
after=EMP
 
PL/SQL procedure successfully completed
SQL> --Example 3. to_many_rows and custom string without cast
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select 'EXAMPLE'
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11        null;
 12      when too_many_rows then
 13        null;
 14    end;
 15    dbms_output.put_line('after=' || vdummy);
 16  end;
 17  /
 
before=#
after=EXAMPLE
 
PL/SQL procedure successfully completed
SQL> --Example 4. to_many_rows and custom number with cast to varchar2
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select 1
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11        null;
 12      when too_many_rows then
 13        null;
 14    end;
 15    dbms_output.put_line('after=' || vdummy);
 16  end;
 17  /
 
before=#
after=#
 
PL/SQL procedure successfully completed
SQL> --Example 5. to_many_rows and function
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select lpad(user_objects.object_name, 30, '@')
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11        null;
 12      when too_many_rows then
 13        null;
 14    end;
 15    dbms_output.put_line('after=' || vdummy);
 16  end;
 17  /
 
before=#
after=#
 
PL/SQL procedure successfully completed
SQL> --Example 6. right example
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select user_objects.object_name
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11       -- assign value
 12        vdummy := 'no_data_found';
 13      when too_many_rows then
 14        vdummy := 'too_many_rows';
 15    end;
 16    dbms_output.put_line('after=' || vdummy);
 17  end;
 18  /
 
before=#
after=too_many_rows
 
PL/SQL procedure successfully completed
 
SQL> 

Читать полностью

Continue to consider new features xsl-transformer in Oracle XDK.
Now let's talk about the Standard functions.

Standard functions appeared in 2 versions XSLT, so the version = "2.0" is necessary to specify the header of XSLT.

select ora_20000_xmlutility.XMLTransformClob
        (xml_sample.xmltext,
q'~<xsl:stylesheet version="2.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:fn="http://www.w3.org/2005/xpath-functions">  
  
  <xsl:template match="/"> 
    <xsl:value-of select="fn:upper-case('Function plig-in.')"/>  
  </xsl:template> 
</xsl:stylesheet>
~'      )
from   xml_sample;

Result:

<?xml version = '1.0' encoding = 'UTF-8'?>
FUNCTION PLIG-IN.

Additional links:

• Custom XSLT with Oracle XDK library in ORACLE DB
• Custom XSLT with JAPX library in ORACLE DB
• Using Temporary Trees in XSLT Oracle XDK
• XML Transformation with XSLT 1.0 and 2.0
• Using Grouping in XSLT Oracle XDK
• Using java method in XSLT Oracle XDK
• Using User-defined functions in XSLT Oracle XDK

Читать полностью

Continue to consider new features xsl-transformer  in Oracle XDK.
Now let's talk about the User-defined functions.

Custom functions appeared in 2 versions XSLT, so the version = "2.0" is necessary to specify the header of XSLT.

select ora_20000_xmlutility.XMLTransformClob
        (xml_sample.xmltext,
q'~<xsl:stylesheet version="2.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:foo="http://foo" 
  exclude-result-prefix="foo">  
  
  <xsl:function name="foo:twice">
    <xsl:param name="p-string" /> 
    <xsl:value-of select="concat($p-string, $p-string)" /> 
  </xsl:function> 

  <xsl:template match="/"> 
    <xsl:value-of select="foo:twice('Function plig-in. ')"/>  
  </xsl:template> 
</xsl:stylesheet>
~'      )
from   xml_sample;

Result:

<?xml version = '1.0' encoding = 'UTF-8'?>
Function plig-in. Function plig-in. 

Additional links:

• Custom XSLT with Oracle XDK library in ORACLE DB
• Custom XSLT with JAPX library in ORACLE DB
• Using Temporary Trees in XSLT Oracle XDK
• XML Transformation with XSLT 1.0 and 2.0
• Using Grouping in XSLT Oracle XDK
• Using java method in XSLT Oracle XDK

Читать полностью

Continue to consider new features xsl-transformer in Oracle XDK.
Now let's talk about the Java method.

First of all, the database user must be given the right to download the java classes.

begin
  dbms_java.grant_permission( 'SCOTT', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
end;

Now you can try an example.

select ora_20000_xmlutility.XMLTransformClob
        (xml_sample.xmltext,
q'~<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:jstring="http://www.oracle.com/XSL/Transform/java/java.lang.String"> 
  <xsl:template match="/"> 
    <xsl:variable name="str1" select="jstring:new('HeLlO wOrLd')"/> 
    <xsl:value-of select="jstring:toUpperCase($str1)"/> 
  </xsl:template> 
</xsl:stylesheet>
~'                                                        
        )
from   xml_sample;

Result:

<?xml version = '1.0' encoding = 'UTF-8'?>
HELLO WORLD

Additional links:

• Custom XSLT with Oracle XDK library in ORACLE DB
• Custom XSLT with JAPX library in ORACLE DB
• Using Temporary Trees in XSLT Oracle XDK
• XML Transformation with XSLT 1.0 and 2.0
• Using Grouping in XSLT Oracle XDK

Читать полностью

Continue to consider new features trasformera xslt in Oracle XDK.
Now let's talk about the "Grouping".
You can use the <xsl:for-each-group> element, current-group() function, and current-grouping-key() function.

select ora_20000_xmlutility.XMLTransformClob
        (xml_sample.xmltext,
q'~<?xml version="1.0" encoding="Windows-1251"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="2.0">

  <xsl:output method="xml" indent="yes"/>
    
  <xsl:template match="/"> 
    <catalog>
       <xsl:for-each-group select="catalog/book" group-by="genre">
         <genre>
          <xsl:attribute name="name"><xsl:value-of select="current-grouping-key()"/></xsl:attribute>
                  
          <xsl:for-each select="current-group()">
            <xsl:copy-of select="." />
          </xsl:for-each>
         </genre>
       </xsl:for-each-group>
    </catalog>
  </xsl:template>  
</xsl:stylesheet>~'                                                        
        )
from   xml_sample;

Result:

<?xml version="1.0" encoding="UTF-8"?>
<catalog>
 <genre name="Computer">
  <book id="bk101">
   <author>Gambardella, Matthew</author>
   <title>XML Developer's Guide</title>
   <genre>Computer</genre>
   <price>44.95</price>
   <publish_date>2000-10-01</publish_date>
   <description>An in-depth look at creating applications
   with XML.</description>
  </book>
  <book id="bk110">
   <author>O'Brien, Tim</author>
   <title>Microsoft .NET: The Programming Bible</title>
   <genre>Computer</genre>
   <price>36.95</price>
   <publish_date>2000-12-09</publish_date>
   <description>Microsoft's .NET initiative is explored in
   detail in this deep programmer's reference.</description>
  </book>
  <book id="bk111">
   <author>O'Brien, Tim</author>
   <title>MSXML3: A Comprehensive Guide</title>
   <genre>Computer</genre>
   <price>36.95</price>
   <publish_date>2000-12-01</publish_date>
   <description>The Microsoft MSXML3 parser is covered in
   detail, with attention to XML DOM interfaces, XSLT processing,
   SAX and more.</description>
  </book>
  <book id="bk112">
   <author>Galos, Mike</author>
   <title>Visual Studio 7: A Comprehensive Guide</title>
   <genre>Computer</genre>
   <price>49.95</price>
   <publish_date>2001-04-16</publish_date>
   <description>Microsoft Visual Studio 7 is explored in depth,
   looking at how Visual Basic, Visual C++, C#, and ASP+ are
   integrated into a comprehensive development
   environment.</description>
  </book>
 </genre>
 <genre name="Fantasy">
  <book id="bk102">
   <author>Ralls, Kim</author>
   <title>Midnight Rain</title>
   <genre>Fantasy</genre>
   <price>5.95</price>
   <publish_date>2000-12-16</publish_date>
   <description>A former architect battles corporate zombies,
   an evil sorceress, and her own childhood to become queen
   of the world.</description>
  </book>
  <book id="bk103">
   <author>Corets, Eva</author>
   <title>Maeve Ascendant</title>
   <genre>Fantasy</genre>
   <price>5.95</price>
   <publish_date>2000-11-17</publish_date>
   <description>After the collapse of a nanotechnology
   society in England, the young survivors lay the
   foundation for a new society.</description>
  </book>
  <book id="bk104">
   <author>Corets, Eva</author>
   <title>Oberon's Legacy</title>
   <genre>Fantasy</genre>
   <price>5.95</price>
   <publish_date>2001-03-10</publish_date>
   <description>In post-apocalypse England, the mysterious
   agent known only as Oberon helps to create a new life
   for the inhabitants of London. Sequel to Maeve
   Ascendant.</description>
  </book>
  <book id="bk105">
   <author>Corets, Eva</author>
   <title>The Sundered Grail</title>
   <genre>Fantasy</genre>
   <price>5.95</price>
   <publish_date>2001-09-10</publish_date>
   <description>The two daughters of Maeve, half-sisters,
   battle one another for control of England. Sequel to
   Oberon's Legacy.</description>
  </book>
 </genre>
 <genre name="Romance">
  <book id="bk106">
   <author>Randall, Cynthia</author>
   <title>Lover Birds</title>
   <genre>Romance</genre>
   <price>4.95</price>
   <publish_date>2000-09-02</publish_date>
   <description>When Carla meets Paul at an ornithology
   conference, tempers fly as feathers get ruffled.</description>
  </book>
  <book id="bk107">
   <author>Thurman, Paula</author>
   <title>Splish Splash</title>
   <genre>Romance</genre>
   <price>4.95</price>
   <publish_date>2000-11-02</publish_date>
   <description>A deep sea diver finds true love twenty
   thousand leagues beneath the sea.</description>
  </book>
 </genre>
 <genre name="Horror">
  <book id="bk108">
   <author>Knorr, Stefan</author>
   <title>Creepy Crawlies</title>
   <genre>Horror</genre>
   <price>4.95</price>
   <publish_date>2000-12-06</publish_date>
   <description>An anthology of horror stories about roaches,
   centipedes, scorpions  and other insects.</description>
  </book>
 </genre>
 <genre name="Science Fiction">
  <book id="bk109">
   <author>Kress, Peter</author>
   <title>Paradox Lost</title>
   <genre>Science Fiction</genre>
   <price>6.95</price>
   <publish_date>2000-11-02</publish_date>
   <description>After an inadvertant trip through a Heisenberg
   Uncertainty Device, James Salway discovers the problems
   of being quantum.</description>
  </book>
 </genre>
</catalog>

Addition Links:

• Custom XSLT with Oracle XDK library in ORACLE DB
• Custom XSLT with JAPX library in ORACLE DB
• Using Temporary Trees in XSLT Oracle XDK
• XML Transformation with XSLT 1.0 and 2.0

Читать полностью

In a previous post, I showed you how to create your own transformer xslt, use the Oracle XDK.

Consider the new features that give us their own transformer.

All features can be read here

Now Let's consider "Temporary trees".

In the first version of xslt, you can use the built-in extension must be used ora:node-set.

select ora_20000_xmlutility.XMLTransformClob
        (xml_sample.xmltext,
q'~<?xml version="1.0" encoding="Windows-1251"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ora="http://www.oracle.com/XSL/Transform/java"  
                version="1.0">

  <xsl:output method="xml" indent="yes"/>
  
  <xsl:variable name="v-genre-type">
    <genre>
      <id>1</id>
      <name>Computer</name>
    </genre>
    <genre>
      <id>2</id>
      <name>Fantasy</name>
    </genre>
    <genre>
      <id>3</id>
      <name>Romance</name>
    </genre>
    <genre>
      <id>4</id>
      <name>Horror</name>
    </genre>
    <genre>
      <id>5</id>
      <name>Science Fiction</name>
    </genre>
  </xsl:variable>  

  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="node()|@*" />
    </xsl:element>
  </xsl:template>
  
  <xsl:template match="genre">
    <xsl:variable name="v-genre-value"><xsl:value-of select="."/></xsl:variable>
    <xsl:element name="{local-name()}"><xsl:value-of select="ora:node-set($v-genre-type)/genre[name=$v-genre-value]/id"/></xsl:element>
  </xsl:template>

  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>
  </xsl:template>
  
</xsl:stylesheet>~'                                                        
        )
from   xml_sample;

Result:

<?xml version="1.0" encoding="UTF-8"?>
<catalog>
 <book id="bk101">
  <author>Gambardella, Matthew</author>
  <title>XML Developer's Guide</title>
  <genre>1</genre>
  <price>44.95</price>
  <publish_date>2000-10-01</publish_date>
  <description>An in-depth look at creating applications
  with XML.</description>
 </book>
 <book id="bk102">
  <author>Ralls, Kim</author>
  <title>Midnight Rain</title>
  <genre>2</genre>
  <price>5.95</price>
  <publish_date>2000-12-16</publish_date>
  <description>A former architect battles corporate zombies,
  an evil sorceress, and her own childhood to become queen
  of the world.</description>
 </book>
 <book id="bk103">
  <author>Corets, Eva</author>
  <title>Maeve Ascendant</title>
  <genre>2</genre>
  <price>5.95</price>
  <publish_date>2000-11-17</publish_date>
  <description>After the collapse of a nanotechnology
  society in England, the young survivors lay the
  foundation for a new society.</description>
 </book>
 <book id="bk104">
  <author>Corets, Eva</author>
  <title>Oberon's Legacy</title>
  <genre>2</genre>
  <price>5.95</price>
  <publish_date>2001-03-10</publish_date>
  <description>In post-apocalypse England, the mysterious
  agent known only as Oberon helps to create a new life
  for the inhabitants of London. Sequel to Maeve
  Ascendant.</description>
 </book>
 <book id="bk105">
  <author>Corets, Eva</author>
  <title>The Sundered Grail</title>
  <genre>2</genre>
  <price>5.95</price>
  <publish_date>2001-09-10</publish_date>
  <description>The two daughters of Maeve, half-sisters,
  battle one another for control of England. Sequel to
  Oberon's Legacy.</description>
 </book>
 <book id="bk106">
  <author>Randall, Cynthia</author>
  <title>Lover Birds</title>
  <genre>3</genre>
  <price>4.95</price>
  <publish_date>2000-09-02</publish_date>
  <description>When Carla meets Paul at an ornithology
  conference, tempers fly as feathers get ruffled.</description>
 </book>
 <book id="bk107">
  <author>Thurman, Paula</author>
  <title>Splish Splash</title>
  <genre>3</genre>
  <price>4.95</price>
  <publish_date>2000-11-02</publish_date>
  <description>A deep sea diver finds true love twenty
  thousand leagues beneath the sea.</description>
 </book>
 <book id="bk108">
  <author>Knorr, Stefan</author>
  <title>Creepy Crawlies</title>
  <genre>4</genre>
  <price>4.95</price>
  <publish_date>2000-12-06</publish_date>
  <description>An anthology of horror stories about roaches,
  centipedes, scorpions  and other insects.</description>
 </book>
 <book id="bk109">
  <author>Kress, Peter</author>
  <title>Paradox Lost</title>
  <genre>5</genre>
  <price>6.95</price>
  <publish_date>2000-11-02</publish_date>
  <description>After an inadvertant trip through a Heisenberg
  Uncertainty Device, James Salway discovers the problems
  of being quantum.</description>
 </book>
 <book id="bk110">
  <author>O'Brien, Tim</author>
  <title>Microsoft .NET: The Programming Bible</title>
  <genre>1</genre>
  <price>36.95</price>
  <publish_date>2000-12-09</publish_date>
  <description>Microsoft's .NET initiative is explored in
  detail in this deep programmer's reference.</description>
 </book>
 <book id="bk111">
  <author>O'Brien, Tim</author>
  <title>MSXML3: A Comprehensive Guide</title>
  <genre>1</genre>
  <price>36.95</price>
  <publish_date>2000-12-01</publish_date>
  <description>The Microsoft MSXML3 parser is covered in
  detail, with attention to XML DOM interfaces, XSLT processing,
  SAX and more.</description>
 </book>
 <book id="bk112">
  <author>Galos, Mike</author>
  <title>Visual Studio 7: A Comprehensive Guide</title>
  <genre>1</genre>
  <price>49.95</price>
  <publish_date>2001-04-16</publish_date>
  <description>Microsoft Visual Studio 7 is explored in depth,
  looking at how Visual Basic, Visual C++, C#, and ASP+ are
  integrated into a comprehensive development
  environment.</description>
 </book>
</catalog>

The second version of xslt, Temporary trees are automatically converted into node-set.

select ora_20000_xmlutility.XMLTransformClob
        (xml_sample.xmltext,
q'~<?xml version="1.0" encoding="Windows-1251"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ora="http://www.oracle.com/XSL/Transform/java"  
                version="2.0">

  <xsl:output method="xml" indent="yes"/>
  
  <xsl:variable name="v-genre-type">
    <genre>
      <id>1</id>
      <name>Computer</name>
    </genre>
    <genre>
      <id>2</id>
      <name>Fantasy</name>
    </genre>
    <genre>
      <id>3</id>
      <name>Romance</name>
    </genre>
    <genre>
      <id>4</id>
      <name>Horror</name>
    </genre>
    <genre>
      <id>5</id>
      <name>Science Fiction</name>
    </genre>
  </xsl:variable>  

  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="node()|@*" />
    </xsl:element>
  </xsl:template>
  
  <xsl:template match="genre">
    <xsl:variable name="v-genre-value"><xsl:value-of select="."/></xsl:variable>
    <xsl:element name="{local-name()}"><xsl:value-of select="$v-genre-type/genre[name=$v-genre-value]/id"/></xsl:element>
  </xsl:template>

  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>
  </xsl:template>
  
</xsl:stylesheet>~'                                                        
        )
from   xml_sample;

Additional links:

• Custom XSLT with Oracle XDK library in ORACLE DB
• Custom XSLT with JAPX library in ORACLE DB
• XML Transformation with XSLT 1.0 and 2.0

Читать полностью

Another transformer based on the Oracle XDK. The transformer provides several useful features not included in XSLT 1.0.

About the new features I'll discuss in the next post.

Java Source:

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

import org.xml.sax.SAXException;

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

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

import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XSLException;
import oracle.xml.parser.v2.XSLProcessor;
import oracle.xml.parser.v2.XSLStylesheet;
import oracle.xml.parser.v2.XMLParseException;

import oracle.sql.CLOB;

public class xmlutility {

  private static XMLDocument parseDocument(String xmlText) throws Exception 
  {
    DOMParser parser = new DOMParser();
    parser.setPreserveWhitespace(true);
  
    StringReader sr_xml = new StringReader(xmlText);
    try {
      parser.parse(sr_xml);
    } catch (XMLParseException e) {
      throw new Exception("parseDocument ERROR-XMLParseException: " + e.getMessage());
    } catch (SAXException e) {
      throw new Exception("parseDocument ERROR-SAXException: " + e.getMessage());
    } catch (IOException e) {
      throw new Exception("parseDocument ERROR-IOException: " + e.getMessage());
    }
    return parser.getDocument();
  }

  private static String processXSL(XMLDocument xmlDocument, XMLDocument xsltDocument) throws Exception 
  {
    XSLProcessor processor = new XSLProcessor();
    XSLStylesheet xslStylesheet;
    StringWriter strWriter;

    try {
      xslStylesheet = processor.newXSLStylesheet(xsltDocument);
      processor.showWarnings(true);
      processor.setErrorStream(System.err);
      
      strWriter = new StringWriter();
      processor.processXSL(xslStylesheet, xmlDocument, new PrintWriter(strWriter));
    } catch (XSLException e) {
      String message = "";
      for(int i=0; i < e.getNumMessages(); i++)
      {
        message = message + "Message=" + e.getMessage(i) +  ", Line=" + e.getLineNumber(i) + ", Row=" + e.getLineNumber(i) + "URI=" + e.getSystemId(i);
      }
      throw new Exception("processXSL ERROR-XSLException: " + message);
    } catch (IOException e) {
      throw new Exception("processXSL ERROR-IOException: " + e.getMessage());
    }
    return strWriter.getBuffer().toString();
  }

  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;
    }
    
    XMLDocument xmlDoc = parseDocument(xmlText);
    XMLDocument 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);
  }

}

PL\SQL wrapper:

create or replace package ora_20000_xmlutility is

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

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


end ora_20000_xmlutility;
/
create or replace package body ora_20000_xmlutility is

  function XMLTransformClob( xmlText clob
                            ,xslText clob
                           ) return clob
  is
  language java name 'ru.ora_20000.xml.xmlutility.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.xmlutility.transformXmlString(java.lang.String, java.lang.String) return java.lang.String';

end ora_20000_xmlutility;
/

Читать полностью