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ł