Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Saturday, May 17, 2008

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:

5 comments:

Stacy said...

Saved me lots of time. Thanks for posting this.

Jörg said...

Thank you for saving my time.

Randall said...
This comment has been removed by a blog administrator.
Anonymous said...

Very helpful for me today

Anonymous said...

saved me some hours of searching, thanks :-)

 

Copyright © Paweł Barut
Printing from DOS to USB Printer