Book Review: Processing XML documents with Oracle JDeveloper 11g

Written by Paweł Barut
This time I would like to recommend a book Processing XML documents with Oracle JDeveloper 11g published by PacktPub.com. As I have some experience with XML processing inside Oracle DB (XMLDB) I was lacking the knowledge of XML manipulation in Java, especially using Oracle JDeveloper. This book is rather not for beginners - you should have basic knowledge of what is XML, at least theoretically. From the book you can learn how to manage XML Schemas in JDeveloper, then write XML documents based on those schema and finally validate them in JDeveloper or write Java code to validate it automatically. Chapters that I value much are those describing how to generate PDF documents. There are two methods described - one using Apache FOP API , and Oracle XML Publisher. As I was not generating PDF's this way so far, I found those 2 chapters very interesting. There is also chapter on generatin Excel by transformation of XML using Apache HSSF API .

Book is very practical. If you want to start with subjects described above then this book is for you. Of course it does not mean that you do not have to study documentation. However, it will be easier for you to make first steps. Beside describing Oracle JDeveloper, author also shows how to install and use Oracle Berkley XML DB to store, query and update XML Documents - using command line and XML DB API.

I especially recommend eBook version. All URLs in eBook version are active, so linking to web pages are very simple. Also coping sample code is easy. This book contains many practical and usefull tips on XML processing and tools as well. So if those XML aspects are in your interest, then it's definitelly good to invest in this book.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

• ORA-00904: "XMLROOT": invalid identifier
• Useful links on Oracle XML DB
• Solving ORA-22905: cannot access rows from a non-nested table item
• Oracle XMLType: exctractvalue vs. extract
• ORA-30625 and XMLType

OOW2008 day 5 – It’s A Wrap

Written by Paweł Barut
Before I start to summarize my Day 5 at Oracle OpenWorld, I would like to add few words to day 4 (Wednesday).

Managing Very, Very Large XML Documents with Oracle XML Database
It was very good session. It one of those where practical experience was shared. Presenters showed step by step how to load very large XML files to DB:

• Setting up XML schema
• Schema annotation technique and few directives
• Direct Insert Store for XML
• Differences with loading XML into XML DB in 10.2g and 11g

The Appreciation Event.
It was very nice concert on Treasure Island. I’ve especially liked Seal. Beside that there was lot of good food and drinks.

Day 5

I will start with session Oracle’s New Database Accelerator: Query Processing Revolutionized. As I’ve expected it was related to announcement made yesterday. My yesterday’s description was not perfect. Now I will try to fix this. First of all we have 2 new machines. But one them is included in the second one.
Oracle Exodata Storage Server - it is hardware from HP: 2 Intel quad-core processors, 12 disks (300GB, 15RPM or 750 GB, 10 RPM) with disk controller optimized for best bandwidth and 2 InfiniBand connectors to connect to external equipment. The code for this product is HP DL180G5 (at least that was on one of slides). This computer is sold with preinstalled Oracle Enterprise Linux 5.1. The main role of this machine is to store database files. It cannot be used to store normal files.
The second hardware is HP-Oracle Exodata DB Machine - This one is rack box equipped with 14 Oracle Exodata Storage Servers and 8 DB Servers each with 8 Intel processors. On those DB Server runs Oracle Enterprise Linux 5.1 and Oracle RDBMS 11g (11.1.0.7). Even more – 6 such DB Machines can be connected into cluster.
Where is the revolution? In the way Oracle DB communicates with storage. There is new protocol iDB that allows to push query predicated down to storage. With this, number of data transferred from Storage to DB Server is minimized. This feature is called Smart Scan. It can be leveraged only when full table (or partition) scans occurs. And it still keeps all read consistency.
And here is technical spec from Oracle.

And short on my other sessions: Oracle Database Performance on Flash Drives
Very interesting session showing results of different approaches for using Flash drives. As an conclusion there was presented formula, when Flash drives can help with performance, and when it is better to stay with fast rotating drives, and when even with low cost but high capacity drives. As an side note when we consider power usage, Flash drives can be even more economic then traditional rotating drives.

Oracle ACE Director: Birds-of-a-Feather Tips and Techniques
Session lead by Oracle ACE’s: Lewis Cunningan, Arup Nunda, Edie Awad, Mark Rittman, Tim Hall, Hans Forbich and Bradley Brown. The session was Q&A style. ACE’s were answering questions based on own experience, and that is sometimes different that Oracle’s official recommendation.

Real-World XML DB Examples from Oracle Support
This was rather chaotic session, and did not gave me useful information- IHMO waste of time.

And the last session by Tom Kyte Reorganizing Objects
Tom have done great job showing different myths about when DBA’s should reorganize tables and indexes. It was really Great speech. Might be there was too much material for an 1h session, and everything was shown little bit in an hurry.

The day has ended with small party It’s A WRAP
While writing this I’m sitting in hotel and watching Fireworks over the San Francisco Port. Tomorrow I’m leaving San Francisco for 18h trip back to home.

Cheers,
Paweł
--
Related Articles on Paweł Barut blog:

• Oracle OpenWorld 2008
• Preparing for Oracle OpenWorld 2008
• Arriving San Francisco – OOW2008 day 1.
• OOW2008 day 2 – Oracle Beehive
• OOW2008 day 3
• OOW2008 day 4 – HP-Oracle Exadata Server Announcement = Extreme Performance
• OOW2008 day 5 – It’s A Wrap

ORA-00904: "XMLROOT": invalid identifier

Written by Paweł Barut
Some time ago I've had noticed strange problem with XMLRoot function. I was installing application on production server and I've noticed that code:

SQL> select XMLRoot(xmltype('<a>a</a>'))
  2  from dual;

gives error:

select XMLRoot(xmltype('<a>a</a>'))
       *
Error in line 1:
ORA-00904: "XMLROOT": invalid identifier

WTF, it was running perfectly on development and test environment!
Quick search revealed that XMLROOT is function in XDB schema, which was missing in production environment. I've just copies source code for function from test environment and I could proceed further.
After some time, I've decided to check why this function was missing?
Quick search showed that function is created by script ?\demo\schema\order_entry\xdbUtilities.sql
Strange, well documented function is created only when you install demo schemas? Seems that there should be another explanation.
Then I've found that in documentation this function has 2 mandatory attributes, while my code has only one attribute. So there are 2 versions of XMLRoot function:

1. SQL function; see documentation
2. Simplified version created by demo in XDB schema - this version can be also used in PL/SQL

Conclusion: my original code should look like that:

SQL> select XMLRoot(xmltype('<a>a</a>'), version '1.0', standalone yes)
  2  from dual;

XMLROOT(XMLTYPE('<A>A</A>'),VERSION'1.0',STANDALONEYES)
------------------------------------------------------------------------

<?xml version="1.0" standalone="yes"?>
<a>a</a>

This can run without XMLROOT function in XDB schema.

Hope this will help someone to save some time.
Cheers,Paweł

--
Related Articles on Paweł Barut blog:

• Useful links on Oracle XML DB
• Solving ORA-22905: cannot access rows from a non-nested table item
• Oracle XMLType: exctractvalue vs. extract
• Binding list variable
• Empty String IS NOT NULL?

Useful links on Oracle XML DB

Written by Paweł Barut
Here is bunch of useful links related to Oracle XML DB and handling XML data in Oracle PL/SQL. It's mostly for my own reference, but I hope it will be useful for others too.

• How do i store the counts of all tables by Laurent Schneider
• Converting Between XML and Oracle Objects by Lewis Cunningham
  
• Problems Calling .Net Web Services from UTL_DBWS by Stellan
• First Tests of 11g Native Web Services and Revisiting 11g Native Web Services by Paul Gallagher
• Oracle 11g Security - XMLDB is here to stay… by Marco Gralike
• HOWTO: Create XMLType Table for Binary XML Usage by Marco Gralike
• HOWTO: Saving XML data directly to disk by Marco Gralike
• select from a comma-separated string by Laurent Schneider. BTW. Note that I've demonstrated this technique 7 month earlier :)

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Solving ORA-22905: cannot access rows from a non-nested table item
• Binding list variable
• Oracle XMLType: exctractvalue vs. extract
• ORA-30625 and XMLType
  

Operating on XMLType containing & (Ampersands)

I was reading today Lewis post on How to Deal With Oracle XML and Ampersands ("&") in XML Documents. Personally I prefer different way. I use XMLElement function to generate XML documents. In my opinion this is more elegant way. The sample from Lewis post will look like that:

set define off
declare
  v_xml xmltype;
begin
  select xmlelement("ROWSET", 
           xmlelement("ROW", 
             xmlelement("COLA", 'lewis&me')))
    into v_xml 
    from dual;
end;
/

Disadvantage of this method is that you have to use SQL. But in most cases it's used while retrieving data from database, so it should not be a problem. Some time ago I was proposing to read document Mastering XML Generation in Oracle Database 10g Release 2. It is really good source of knowledge on XML manipulation inside Oracle database.
If you want to extract correctly ampersands and other special characters like < and > from XML document I suggest to read my post on differences between extract and extractvalue functions. If you want to manually convert &amp; to & you can use function dbms_xmlgen.convert with second parameter set to 1:

SQL> select dbms_xmlgen.convert('me&lewis', 1) from dual;

me&lewis

Cheers, Paweł

Solving ORA-22905: cannot access rows from a non-nested table item

Some time ago i wrote about Binding list variable. In fact it is conversion of string to rows. My example works fine in SQL but when used in PL/SQL it might cause error ORA-22905: cannot access rows from a non-nested table item. I do know what are the necessary conditions for this error, as it does not always happen in PL/SQL. I faced it usually for quite complicated queries. To overcome this problem I just cast binding variable to varchar2 as shown below:

  procedure test_proc(p_list in varchar2)
  is
  begin
    for r in (select items.extract('/l/text()').getStringVal() item
              from table(xmlSequence(
                extract(XMLType(''||
                  replace(cast (p_list as varchar2(4000)),
                          ',','')||'')
                      ,'/all/l'))) items) loop
      null;
    end loop;
  end;

It worked for me in all situations where I faced ORA-22905 on Oracle 10gR2 (10.2.0.3)

Paweł

Oracle XMLType: exctractvalue vs. extract

I always thought that

exctarctvalue(xml, '/Node')

is equivalent to

xml.extract('/Node/text()').getstringval()

Usually it is true, but in some cases it is not. When node contains special characters like &, >, < etc... then results are different. Let me give an example:

SQL> set define off
SQL> select aa.a.extract('Node/text()').getStringVal() extr
  2       , extractvalue(aa.a, 'Node') extractval
  3    from (select XMLRoot(
  4              XMLElement("Node", 'test & < and >'),
  5             VERSION '1.0') a from dual) aa;

EXTR
----------------------------------------
EXTRACTVAL
----------------------------------------
test &amp; &lt; and &gt;
test & < and >

First look on documentation even confirms my expectations:
"extractValue – This takes an XPath expression and returns the corresponding leaf node. The XPath expression passed to extractValue should identify a single attribute or an element that has precisely one text node child. The result is returned in the appropriate SQL data type. Function extractValue is essentially a shortcut for extract plus either getStringVal() or getNumberVal()."
XML DB Developer's Guide - b14259.pdf page 1-17.

But there is small note on that:
"Note: Function extractValue and XMLType method getStringVal() differ in their treatment of entity encoding. Function extractValue unescapes any encoded entities; method getStringVal() returns the data with entity encoding intact."
XML DB Developer's Guide - b14259.pdf page 4-10.

Cheers, Paweł

Links 2006-11-20

Links 2006-11-20:

• Tutorial for Oracle HA
• Easy XML - Let the Database do the Work
• Mastering XML Generation in Oracle Database 10g Release 2
• Extracting Master-Detail data from an XMLType with a single SQL statement

Cheers, Paweł

Blogger Beta Backup tool

Finally i found time to prepare solution to backup my blog. I've decided to do that as I had problems to backup my blog using available tools. To archive that I use Blogger Beta Atom feeds, few tables in Oracle, and PL/SQL package to read blog posts, parse them using XML DB features and store in DB tables.

Requirements

• Oracle 10g (tested on Oracle XE on Windows and Oracle Enterprise 10.2.0.2 on Windows). Oracle9i should work also, but it's not tested.
• Blogs maintained by Beta Blogger and hosted on blogspot.com. You must allow to publish full content of your posts and comments.
• Direct connection to Internet

Installation

1. Download installation package
2. Create new Oracle schema and grant to it:
   1. roles CONNECT and RESOURCE
   2. EXECUTE on SYS.UTL_HTTP
3. run script install.sql

Usage

First You have to register blog you want to backup. Use BLOGSPOT_BACKUP.register_blog function:

begin
 dbms_output.put_line(
 BLOGSPOT_BACKUP.register_blog
 ('Software Engineer Thoughts', 'http://pbarut.blogspot.com/'));
 commit;
end;

To download all your posts with comments andl labels use this PL/SQL block:

begin 
 BLOGSPOT_BACKUP.synchronize_blog(null, false); 
 BLOGSPOT_BACKUP.synchronize_labels(null, null); 
 BLOGSPOT_BACKUP.synchronize_comments(null, null, false); 
 commit; 
end;

For more information take a look into BLOGSPOT_BACKUP specification.

Data storage

Table name	Desription
BLOGS	Blogs registered for backup.
BLOG_POSTS	Blog posts downloaded.
BLOG_LABELS	Labels associated to blog posts.
BLOG_COMMENTS	Comments related to blog posts.
FEED_CACHE	Used speed up tests. In tests phase feeds are read from this table instead of downloading every time.

End notes

I'm publishing this tool, as some of you might find it useful. It should work on all beta blogger blogs, but I do not give ant warranty. In case of any problems fill free to contact me.

Regards, Paweł

ORA-30625 and XMLType

I will present some differences in handling XMLType in SQL and PL/SQL by Oracle. Test case was done on Oracle 10.2.0.2. Lets define variable and fill it with sample data. It is important to have empty value in tag <empty>.

SQL> var xm varchar2(100);
SQL> exec :xm := '<root><empty></empty></root>';
PL/SQL procedure successfully completed.

Lets try to extract value of empty tag:

SQL> var xmv varchar2(100);
SQL> exec :xmv := XMLType(:xm).extract('//empty/text()').getstringval();
BEGIN :xmv := XMLType(:xm).extract('//empty/text()').getstringval(); END;
*
ERROR at line 1:
ORA-30625: method dispatch on NULL SELF argument is disallowed 
ORA-06512: at line 1 

Lets do the same with SQL:

SQL> select XMLType(:xm).extract('//empty/text()').getstringval() from dual;
XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL()
--------------------------------------------------------------------------------

Now we get NULL. You can confirm this with that query:

SQL> select nvl(XMLType(:xm).extract('//empty/text()').getstringval(),
'(NULL)') from dual;
NVL(XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL(),'(NULL)')
--------------------------------------------------------------------------------
(NULL)

So, be careful when you extract data from XMLType in PL/SQL. If you want NULL to be returned for empty tags then you should use function like this:

CREATE OR REPLACE
FUNCTION XML_EXTRACT_NO_EXCEPTION
( p_xml IN XMLTYPE
, p_xpath IN VARCHAR2
, p_namespace IN VARCHAR2 default NULL
) RETURN VARCHAR2 AS
BEGIN
return case when p_xml.extract(p_xpath, p_namespace) is not null
then p_xml.extract(p_xpath, p_namespace).getstringval()
else NULL
end;
END XML_EXTRACT_NO_EXCEPTION;

And then you can use it safe in PL/SQL:

SQL> exec :xmv := XML_EXTRACT_NO_EXCEPTION(XMLType(:xm), '//empt/text()');
PL/SQL procedure successfully completed.
SQL> print :xmv
XMV
--------------------------------------------------------------------------------

Another reason for ORA-30625 can be that you did not provide namespace in execution of extract. More on this in my post
XMLType and Namespace problem.

Paweł

XMLType and Namespace problem

I've run into some strange problem with XMLType and XML namespaces. I'll try to make a showcase. Lets define variable and simple XML content

SQL> var x varchar2(200);
SQL> exec :x := '<feed
xmlns="http://www.w3.org/2005/Atom"><id>test_id</id></feed>'
PL/SQL procedure successfully completed.

Try to find out what tag is on root:

SQL> select XMLType(:x).getRootElement() from dual;
XMLTYPE(:X).GETROOTELEMENT()
--------------------------------------------------------------------------------
feed

Lets find out the namespace:

SQL> select XMLType(:x).getNamespace() from dual;
XMLTYPE(:X).GETNAMESPACE()
--------------------------------------------------------------------------------

Seems to be no namespace. But there was namespace in XML definition. Lets try extract data:

SQL> select XMLType(:x).extract('/feed').getStringVal() from dual;
XMLTYPE(:X).EXTRACT('/FEED').GETSTRINGVAL()
--------------------------------------------------------------------------------

So nothing was extracted. It's not what I was expecting. Ok, lets try to force namespace:

SQL> select XMLType(:x).extract('/feed',
'xmlns="http://www.w3.org/2005/Atom"').getStringVal() from dual;
XMLTYPE(:X).EXTRACT('/FEED','XMLNS="HTTP://WWW.W3.ORG/2005/ATOM"').GETSTRINGVAL(
--------------------------------------------------------------------------------
<feed
xmlns="http://www.w3.org/2005/Atom"> <id>test_id</id>
</feed> 

Now works fine.
For me it is little confusing: there is no namespace, but you must provide namespace if you want to extract data.
Paweł

Binding list variable

Today I was working with XMLType, and as a side effect I found interesting solution to transform list of values contained in single string to rows. You can find some solutions on Tom Kyte blog. But my solution is new approach that uses xmlType and xmlSequence

SQL> var list varchar2(200)
SQL> exec :list := '2,4,6,8,10,34,33';

PL/SQL procedure successfully completed.

SQL> select items.extract('/l/text()').getStringVal() item
  2  from table(xmlSequence(
  3    extract(XMLType('<all><l>'||
  4      replace(:list,',','</l><l>')||'</l></all>')
  5          ,'/all/l'))) items;

ITEM                                                                            
--------------------------------------------------------------------------------
2                                                                               
4                                                                               
6                                                                               
8                                                                               
10                                                                              
34                                                                              
33                                                                              

7 rows selected.

Simple! Isn’t it?
Cheers, Paweł