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?

Oracle CPUApr2008 for DB: 10.2.0.4 patch set and first install

Written by Paweł Barut
To my surprise CPUApr2008 is not available for latest patch-set of Oracle 10gR2 (10.2.0.4) that was released in first quarter 2008. In note that goes together with patch 6810189:

The following are the updated components of this patch set:
  * Up to January 2008 CPU is included in this patch set
...

Based on this I was expecting that CPUApr2008 will be also available for 10.2.0.4 patch-set. In note 552248.1 you will find then:

1.3 Database 10.2.0.4 Patch Set

The Database 10.2.0.4 Patch Set includes the CPUApr2008 content.

So the CPUApt2008 was available to all at least month before official release and was included in 10.2.0.4 patch set! Is this situation secure?

Anyway I had to install CPUApr2008. It's good that there is no need to recompile views this time. I've sucessfully installed it on test server, with minor problem. In installation log I've found one problem:

BEGIN emd_maintenance.recompile_invalid_objects; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYSMAN.EMD_MAINTENANCE" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"SYSMAN.EMD_MAINTENANCE"
ORA-06512: at line 1

Hmm... After reconnecting, this commend run without errors:

SQL> conn / as sysdba
Connected.
SQL> BEGIN sysman.emd_maintenance.recompile_invalid_objects; END;

PL/SQL procedure successfully completed.

After all, server runs ok, and I do not find any problems within applications running on this.


Cheers Paweł

--
Related Articles on Paweł Barut blog:

• CPUJan2008: One thing that every DBA should know about it.
• Security: getting DBA rights quite easy
• Comments in SQL*Plus
• Extracting Data to Excel using only SQL*Plus
• RMAN TIP: Avoiding ORA-27211

Oracle Database 11g Release 2 is coming

Written by Paweł Barut
I've just received Oracle Partner Network newsletter with invitation to Oracle Database 11g Release 2 Roadshow. On this Roadshow Oracle is also going to find partners for beta testing. My guess: This year Oracle 11g R2 will be the main topic on Oracle Open World.
Schedule of Roadshow in Europe:

• May 12, 2008: UK, Reading
• May 13, 2008: Benelux, De Meern
• May 14, 2008: Germany, Muenchen
• May 15, 2008: Italy, Milan
• May 16, 2008: Turkey, Istanbul
• May 19, 2008: Czech Republic, Prague
• May 20, 2008: Poland, Kraków
• May 21, 2008: Sweden, Stockholm
• May 22, 2008: Spain, Madrid

If you are OPN member then you probably also get invitation.
I've registered. I hope to get confirmation soon. I do not expect many technical details. I rather expect marketing speech. For me most important would be to meet people that work on new DB version, and become beta tester for 11gR2.

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Using Named Parameters within SQL
• Testing new OCI8 (1.3.0 beta) for PHP with DRCP Support
• DRCP: Database Resident Connection Pooling
• Oracle 11g is released - my links
• Solving ORA-22905: cannot access rows from a non-nested table item

Which Temporary Tablespace is used for sorts?

Written by Paweł Barut
This time I will write about "Which Temporary Tablespace is used for sorts" ?. I was not wondering about this much, as usually there is only one Temporary tablespace in DB. Lets assume situation, as show on picture:

• User A
• Assigned to Temporary Tablespace TEMP_A
• has table TA
  
• User B
• Assigned to Temporary Tablespace TEMP_B
• has table TB
• owns procedure PB (definer rights)
  
• Both users has access rights for all above object (Select on Tables and Execute on Procedure)
  

So let's discuss some situations:

1. User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
2. User A executes procedure PB. Procedure PB opens cursor on table TB (or TA or any other). If disk sort is required then tablespace TEMP_B is used.
   

For me it was bit surprising. Especially that I did not find anywhere in Oracle documentation description for this behaviour:

TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments.

I was expecting that all sort segments will be created in tablespace that is assigned to that user. I was hopping to solve one of my issues that way. But it occurred that, sort segment is created by user B, because procedure PB uses user B rights. It is reasonable, as this is consistent with granting access to objects, and temporary objects are treated the same way as permanent ones. On the other hand select is run for user A - shouldn't TEMP_A be used in all cases? What is your opinion on that?

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• How to shrink Oracle Context Index
• Using Named Parameters within SQL
• Alternative Syntax for Insert
• Extracting Data to Excel using only SQL*Plus
• Rows to single string or aggregating strings

Horrible practice of storing users passwords

Written by Paweł Barut
I've read today terrifying article about un-ethical storage of users password. It is often common practice that users passwords are stored unencrypted in database. But this case is even more dreadful. It about software, G-Archiver, that is available to everyone. If you ever used this software, you should change your G-mail password right now.
So what is the problem:

• G-Archiver has build in user-name and password for one of g-mail accounts.
• Whenever someone uses G-Archiver and provides his credentials for g-mail, user-name and password is send to author of this software
• Additionally, any one who finds out the user-name and password stored in G-Archiver, can get passwords of thousand of previous users.

Go and read. Hope there are not too many of software that works like this one. It is really terrifying, that some people are so irresponsible. It could be well planed action to collect gmail users and passwords, or it could be just lack of imagination what are the consequences. I would like to believe that it was the second one.

But also big corporations do not care for security enough. It is common practice, that users password is stored internally as plain text. It can be easily found out by using "I forgot my password" feature. If in return you will get email with your original password, then it is a crappy web side. And you should never reuse password passed to such web side as this is big risk for you. Take a look at Password Security: It’s Not That Hard (But You Still Can’t Get It Right) for further explanation and examples.

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Security: getting DBA rights quite easy
• „Do not use Linux, please, pirate our software” - Microsoft
• Spam, Spam filters, Being Spammer, Being Filtered-out ...
• Do not trust Google Maps

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
  

CPUJan2008: One thing that every DBA should know about it.

Written by Paweł Barut
While reading documentation for latest Oracle security patch CPUJan2008 for database I've noticed one new required step. This step is: Recompile Views. One whould say that it's nothing strange, but to run this script database have to be started in UPGRADE MODE. For non-RAC installations it's not big issue, as anyway you have to plan downtime. But Oracle suggest that this recompilation can take 30 minutes:

"In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes."

Practically in my case it took no more than 5 minutes, so it's not big deal.
But it could be problem for RAC installations, that used when CPU is applied on one node, then catcpu.sql is run, then second node is being patched. So normally DB will be all the time available. But if it's needed to start database in UPGRADE mode it means that downtime is required. According to documentation it's not required to run view recompilation script during paching process. This script can be run after,

"however, the CPU installation will not be complete until the view recompilation is completed."

You can ommit this step only if:

• Databases was created with Release 11.1.0.6 or later

• Databases was created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied

I hope it also means that after CPUJan2008 was applied you will not have to run this script after next CPU came out (CPUApr2008, etc).

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Security: getting DBA rights quite easy
• CPUApr2007 and Interim patch 3 for 10.2.0.3 on Windows
• Applying Oracle 10.2.0.3 patch
• Autobackup of SPFILE and CONTROLFILE
• Blogger Beta Backup tool