,

Automatic Statistics Gathering and Stale Statistics

понедельник, 29 ноября 2010 г. 0 коммент.

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 Читать полностью

, ,

Strange too_many_rows

понедельник, 22 ноября 2010 г. 0 коммент.

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>
Читать полностью

, , , ,

Using Standard functions in XSLT Oracle XDK.

четверг, 18 ноября 2010 г. 0 коммент.

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:


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

, , , ,

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:


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

, , , ,

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:


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

, , , ,

Using Grouping in XSLT Oracle XDK

вторник, 16 ноября 2010 г. 0 коммент.

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:


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

, , , , , , ,

Using Temporary Trees in XSLT Oracle XDK

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

понедельник, 1 ноября 2010 г. 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;
/
Читать полностью

, , , ,

Java properties in Oracle DB

четверг, 28 октября 2010 г. 0 коммент.

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>
Читать полностью

, , ,

Custom XSLT with JAPX library in ORACLE DB

вторник, 26 октября 2010 г. 0 коммент.

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>
Читать полностью

, ,

How to add, change and remove namespace in xml in ORACLE DB

вторник, 12 октября 2010 г. 0 коммент.

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>
Читать полностью

,

q{Oracle's quote operator}

вторник, 5 октября 2010 г. 0 коммент.

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
Читать полностью

, , , , , , ,

Вывод неразрывного пробела при формирование html с помощью xmltransform (outputting nbsp to HTML when use xmltransform)

пятница, 9 апреля 2010 г. 0 коммент.

При формировании HTML, в котором используется неразрывный пробел надо учесть пару особенностей:

  • Для вывода надо использовать команду


  • <xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text>

  • Неразрывный пробел является недопустимым элементов в xml. Поэтому попытка получить xml после преобразования завершиться ошибкой


  • ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00118: Warning: undefined entity "nbsp"
    Error at line 1

    поэтому преобразование должно вернуть не xml, а clob или varchar2, для этого необходимо использовать функцию getclobval().

Пример:

select xmltransform(xmldata.depts,
xmltype('<?xml version="1.0" encoding="Windows-1251"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html"/>
<xsl:template match="/">
<html>
<body>
<table border="1" cols="3">
<xsl:for-each select="/DEPARTMENTS/DEPARTMENT">
<tr><td><xsl:value-of select="@DEPTNO"/></td>
<td colspan="2"><xsl:value-of select="@DNAME"/></td>
</tr>
<xsl:for-each select="EMPLOYEE">
<tr><td><xsl:value-of select="EMPNO"/></td>
<td><xsl:value-of select="ENAME"/></td>
<td><xsl:value-of select="JOB"/></td>
</tr>
</xsl:for-each>
<xsl:if test="position() != last()">
<tr><td colspan="3">
<xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text>
</td></tr>
</xsl:if>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>')
).getclobval()
from (
select xmlelement(departments, xmlagg(
xmlelement(department,
xmlattributes(emp.deptno,
(select dname from dept where deptno = emp.deptno) as dname),
xmlagg(xmlelement(employee,
xmlforest(emp.empno, emp.ename, emp.job))
))
)) as depts
from emp
group by emp.deptno
) xmldata

Результат:

<?xml version="1.0" encoding="UTF-8"?>
<html>
<body>
<table border="1" cols="3">
<tr>
<td>10</td>
<td colspan="2">ACCOUNTING</td>
</tr>
<tr>
<td>7782</td>
<td>CLARK</td>
<td>MANAGER</td>
</tr>
<tr>
<td>7839</td>
<td>KING</td>
<td>PRESIDENT</td>
</tr>
<tr>
<td>7934</td>
<td>MILLER</td>
<td>CLERK</td>
</tr>
<tr>
<td colspan="3">&nbsp;</td>
</tr>
<tr>
<td>20</td>
<td colspan="2">RESEARCH</td>
</tr>
<tr>
<td>7369</td>
<td>SMITH</td>
<td>CLERK</td>
</tr>
<tr>
<td>7902</td>
<td>FORD</td>
<td>ANALYST</td>
</tr>
<tr>
<td>7876</td>
<td>ADAMS</td>
<td>CLERK</td>
</tr>
<tr>
<td>7788</td>
<td>SCOTT</td>
<td>ANALYST</td>
</tr>
<tr>
<td>7566</td>
<td>JONES</td>
<td>MANAGER</td>
</tr>
<tr>
<td colspan="3">&nbsp;</td>
</tr>
<tr>
<td>30</td>
<td colspan="2">SALES</td>
</tr>
<tr>
<td>7499</td>
<td>ALLEN</td>
<td>SALESMAN</td>
</tr>
<tr>
<td>7698</td>
<td>BLAKE</td>
<td>MANAGER</td>
</tr>
<tr>
<td>7654</td>
<td>MARTIN</td>
<td>SALESMAN</td>
</tr>
<tr>
<td>7844</td>
<td>TURNER</td>
<td>SALESMAN</td>
</tr>
<tr>
<td>7900</td>
<td>JAMES</td>
<td>CLERK</td>
</tr>
<tr>
<td>7521</td>
<td>WARD</td>
<td>SALESMAN</td>
</tr>
</table>
</body>
</html>


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

, , , ,

xsd datetime в Oracle DB

понедельник, 5 апреля 2010 г. 0 коммент.

Для преобразования даты и времени из DB Oracle в дату и время XSD можно воспользоваться следующим шаблоном 'yyyy-mm-dd"T"hh24:mi:ss.ff6tzr'.
Почитать о дате и времени в xsd можно тут. О форматах шаблона преобразования в Oracle DB тут.

Посмотрим на примерах, преобразуем дату и время в datetime xsd.


select xmlelement(depts, xmlagg(
xmlelement(dept,
xmlforest(deptno,
dname,
loc,
to_char(systimestamp+numtodsinterval(rownum,'minute'),
'yyyy-mm-dd"T"hh24:mi:ss.ff6tzr') as ts
)
)
)).getclobval()
from dept;

Результат:

<DEPTS>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
<TS>2010-03-29T18:59:48.898873+04:00</TS>
</DEPT>
<DEPT>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<LOC>DALLAS</LOC>
<TS>2010-03-29T19:00:48.898873+04:00</TS>
</DEPT>
<DEPT>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<LOC>CHICAGO</LOC>
<TS>2010-03-29T19:01:48.898873+04:00</TS>
</DEPT>
<DEPT>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
<TS>2010-03-29T19:02:48.898873+04:00</TS>
</DEPT>
</DEPTS>

А теперь datetime xsd преобразуем в timestamp Oracle DB.

SQL> COLUMN DEPTNO FORMAT A8
SQL> COLUMN DNAME FORMAT A12
SQL> COLUMN LOC FORMAT A12
SQL>
SQL> select extractvalue(value(row_dept), '/DEPT/DEPTNO') as deptno,
2 extractvalue(value(row_dept), '/DEPT/DNAME') as dname,
3 extractvalue(value(row_dept), '/DEPT/LOC') as loc,
4 to_timestamp_tz(extractvalue(value(row_dept), '/DEPT/TS'),
5 'yyyy-mm-dd"T"hh24:mi:ss.ff6tzh:tzm') as ts
6 from (
7 select xmltype(
8 '<DEPTS>
9 <DEPT>
10 <DEPTNO>10</DEPTNO>
11 <DNAME>ACCOUNTING</DNAME>
12 <LOC>NEW YORK</LOC>
13 <TS>2010-03-29T18:59:48.898873+04:00</TS>
14 </DEPT>
15 <DEPT>
16 <DEPTNO>20</DEPTNO>
17 <DNAME>RESEARCH</DNAME>
18 <LOC>DALLAS</LOC>
19 <TS>2010-03-29T19:00:48.898873+04:00</TS>
20 </DEPT>
21 <DEPT>
22 <DEPTNO>30</DEPTNO>
23 <DNAME>SALES</DNAME>
24 <LOC>CHICAGO</LOC>
25 <TS>2010-03-29T19:01:48.898873+04:00</TS>
26 </DEPT>
27 <DEPT>
28 <DEPTNO>40</DEPTNO>
29 <DNAME>OPERATIONS</DNAME>
30 <LOC>BOSTON</LOC>
31 <TS>2010-03-29T19:02:48.898873+04:00</TS>
32 </DEPT>
33 </DEPTS>') as xml_data
34 from dual
35 ) xml_table,
36 table(xmlsequence(extract(xml_table.xml_data, '/DEPTS/DEPT'))) row_dept;

DEPTNO DNAME LOC TS
-------- ------------ ------------ -------------------------------------------------
10 ACCOUNTING NEW YORK 29.03.10 18:59:48,898873000 +04:00
20 RESEARCH DALLAS 29.03.10 19:00:48,898873000 +04:00
30 SALES CHICAGO 29.03.10 19:01:48,898873000 +04:00
40 OPERATIONS BOSTON 29.03.10 19:02:48,898873000 +04:00


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

, , , ,

Неправильно формированный тэг br при выполнении xmltransform (wrong br after xmltransform)

суббота, 27 марта 2010 г. 0 коммент.

Под 11 версией базы неожиданно перестал правильно работать xmltransform.

В соответсвии с W3C рекомендациями элементы написанные как
<br/> или <br></br> должны быть трансформированы в <br>, но этого не происходит.

Рассмотрим на примере. XML для трансформации:


<?xml version="1.0" encoding="Windows-1251"?>
<REQUEST xmlns="http://localhost/schemas/data" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DATA>
<ID>12345</ID>
<ID_PARENT>1234</ID_PARENT>
<NAME>Узел 12345</NAME>
<FULL_NAME>Узел 12345, родитель 1234</FULL_NAME>
</DATA>
</REQUEST>

XSLT для трансформации:


<?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>Элемент дерева</title>
</head>
<body>
ИД: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID"/></b>< br></br>
ИД родителя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID_PARENT"/></b>< br/>
Имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:NAME"/></b>< br> </br>
Полное имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:FULL_NAME"/></b>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Выполним трансформацию в Oracle DB 10g и 11g. Сначала в Oracle DB 11g


SQL> set long 32000
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL>
SQL> select xmltransform(xmltype(
2 '<?xml version="1.0" encoding="Windows-1251"?>
3 <REQUEST xmlns="http://localhost/schemas/data" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
4 <DATA>
5 <ID>12345</ID>
6 <ID_PARENT>1234</ID_PARENT>
7 <NAME>Узел 12345</NAME>
8 <FULL_NAME>Узел 12345, родитель 1234</FULL_NAME>
9 </DATA>
10 </REQUEST>'), xmltype(
11 '<?xml version="1.0" encoding="Windows-1251"?>
12 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
13 xmlns:sdt="http://localhost/schemas/data" version="1.0">
14 <xsl:output method="html"/>
15 <xsl:template match="/">
16 <html>
17 <head>
18 <title>Элемент дерева</title>
19 </head>
20 <body>
21 ИД: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID"/></b>< br></br>
22 ИД родителя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID_PARENT"/></b>< br/>
23 Имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:NAME"/></b>< br> </br>
24 Полное имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:FULL_NAME"/></b>
25 </body>
26 </html>
27 </xsl:template>
28 </xsl:stylesheet>')).getclobval()
29 from dual;

XMLTRANSFORM(XMLTYPE('<?XMLVER
--------------------------------------------------------------------------------
<html><head><title>Элемент дерева</title></head><body>
ИД: <b>12345</b>< br></br>
ИД родителя: <b>1234</b>< br></br>
Имя: <b>Узел 12345</b>< br></br>
Полное имя: <b>Узел 12345, родитель 1234</b></body></html>

SQL>

Теперь в Oracle DB 10g


SQL> set long 32000
SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL>
SQL> select xmltransform(xmltype(
2 '<?xml version="1.0" encoding="Windows-1251"?>
3 <REQUEST xmlns="http://localhost/schemas/data" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
4 <DATA>
5 <ID>12345</ID>
6 <ID_PARENT>1234</ID_PARENT>
7 <NAME>Узел 12345</NAME>
8 <FULL_NAME>Узел 12345, родитель 1234</FULL_NAME>
9 </DATA>
10 </REQUEST>'), xmltype(
11 '<?xml version="1.0" encoding="Windows-1251"?>
12 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
13 xmlns:sdt="http://localhost/schemas/data" version="1.0">
14 <xsl:output method="html"/>
15 <xsl:template match="/">
16 <html>
17 <head>
18 <title>Элемент дерева</title>
19 </head>
20 <body>
21 ИД: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID"/></b>< br></br>
22 ИД родителя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID_PARENT"/></b>< br/>
23 Имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:NAME"/></b>< br> </br>
24 Полное имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:FULL_NAME"/></b>
25 </body>
26 </html>
27 </xsl:template>
28 </xsl:stylesheet>')).getclobval()
29 from dual;


XMLTRANSFORM(XMLTYPE('<?XMLVER
--------------------------------------------------------------------------------
<html><head><title>Элемент дерева</title></head><body>
ИД: <b>12345</b>< br/>
ИД родителя: <b>1234</b>< br/>
Имя: <b>Узел 12345</b>< br/>
Полное имя: <b>Узел 12345, родитель 1234</b></body></html>

SQL>

И хотя в 10.2 тоже неправильно формируется тэг br, вместо <br /> мы видим <br/>, браузеры правильно отображают этот тэг.
А сочетание элемент <br /></br> интерпретируется браузерами, как два тега <br />.
Для того чтобы в Oracle DB 11g правильно выводились тэги <br />, их необходимо экранировать с помощью инстркции xsl:text.
Трансформация будет выглядеть следующим образом.


<?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>Элемент дерева</title>
</head>
<body>
ИД: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID"/></b><xsl:text disable-output-escaping="yes"><![CDATA[< br>]]></xsl:text>
ИД родителя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID_PARENT"/></b><xsl:text disable-output-escaping="yes"><![CDATA[< br>]]></xsl:text>
Имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:NAME"/></b><xsl:text disable-output-escaping="yes"><![CDATA[< br>]]></xsl:text>
Полное имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:FULL_NAME"/></b>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Смотрим результат:


SQL> set long 32000
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL>
SQL> select xmltransform(xmltype(
2 '<?xml version="1.0" encoding="Windows-1251"?>
3 <REQUEST xmlns="http://localhost/schemas/data" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
4 <DATA>
5 <ID>12345</ID>
6 <ID_PARENT>1234</ID_PARENT>
7 <NAME>Узел 12345</NAME>
8 <FULL_NAME>Узел 12345, родитель 1234</FULL_NAME>
9 </DATA>
10 </REQUEST>'), xmltype(
11 '<?xml version="1.0" encoding="Windows-1251"?>
12 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sdt="http://localhost/schemas/data" version="1.0">
13 <xsl:output method="html"/>
14 <xsl:template match="/">
15 <html>
16 <head>
17 <title>Элемент дерева</title>
18 </head>
19 <body>
20 ИД: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID"/></b><xsl:text disable-output-escaping="yes"><![CDATA[< br>]]></xsl:text>
21 ИД родителя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:ID_PARENT"/></b><xsl:text disable-output-escaping="yes"><![CDATA[< br>]]></xsl:text>
22 Имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:NAME"/></b><xsl:text disable-output-escaping="yes"><![CDATA[< br>]]></xsl:text>
23 Полное имя: <b><xsl:value-of select="/sdt:REQUEST/sdt:DATA/sdt:FULL_NAME"/></b>
24 </body>
25 </html>
26 </xsl:template>
27 </xsl:stylesheet>')).getclobval()
28 from dual;

XMLTRANSFORM(XMLTYPE('<?XMLVER
--------------------------------------------------------------------------------
<html><head><title>Элемент дерева</title></head><body>
ИД: <b>12345</b>< br>
ИД родителя: <b>1234</b>< br>
Имя: <b>Узел 12345</b>< br>
Полное имя: <b>Узел 12345, родитель 1234</b></body></html>

SQL>

Теперь все правильно.


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

, ,

Ошибки в при работе с адаптерами при установке J2EE + BPEL

вторник, 16 февраля 2010 г. 0 коммент.

При установке BPEL 10.3.1.1.0 на чистый J2EE, не устанавливаются адаптеры для работы с базой данных, очередями AQ и JMS.

При попытке использовать неустановленные адаптеры, возникают ошибки:

для jms - Missing class: oracle.tip.adapter.jms.outbound.JmsProduceInteractionSpec
для БД - Missing class: oracle.tip.adapter.db.DBStoredProcedureInteractionSpec

Решение можно найти на My Oracle Support:


  • Article: Adapter Modules Missing On SOA Installation [ID 754146.1]

  • Problem: Error Running BPEL Process Using Adapters - Adapters Missing On Install [ID 433524.1]

  • Bug 5935034: BPEL ADAPATER ARE NOT THERE / CANNOT BE PATCHED AFTER CLEAN INSTALL J2EE+BPEL

Для установки адаптеров в ручную необходимо необходимо выполнить следующие шаги:


  1. Проверить, что файл $ORACLE_HOME/j2ee/<bpel container>/config/oc4j-connectors.xml содержит описание адаптеров. <bpel container> – OC4J контейнер, где запускается BPEL, как приложение.

  2. Задеплоить адаптеры используя AS Control. <bpel_container>/Applications/выпадающий список Standalone Resource Adapters. Нажимаем деплой и выбираем необходимые файлы адаптеров из директории $ORACLE_HOME/bpel/system/services/lib.

Соответствие имени файла адаптера и имени адаптера:


Имя файла Имя адаптера
AqAdapter.rar AqAdapter
DBAdapter.rar DbAdapter
fileAdapter.rar FileAdapter
ftpAdapter.rar FtpAdapter
JmsAdapter.rar JmsAdapter
MQSeriesAdapter.rar MQSeriesAdapter

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