Oracle, XMLType and Clobs

alphapenguin · **Posted:** Tue Nov 25, 2003 6:54 pm

I've been trying to get this work, but now I'm stuck. I apologize if I've missed something obvious. I have been reading the fine manual, searched the web and newsgroups without finding a solution.

I need to be able to work with an Oracle database that has a table that contains a column of type SYS.XMLType.

Let's say the table was created like this:

Code:

CREATE TABLE doc (
 doc_id int not_null primary key,
 doc_content SYS.XMLType
);

Then my mapping looks like this:

Code:

<class name="myorg.Doc" table="doc">
        <id name="id" column="id" type="int">
            <generator class="sequence">                        
                <param name="sequence">SEQ_DOC</param>
            </generator>
        </id>
   <property name="content" column="doc_content" type="Clob"/>
</class>

The problem is that the Oracle JDBC driver does not allow XMLType columns to be retrieved as Clobs from the ResultSet unless you use this syntax: (the column must be aliased for this to work)

Code:

SELECT d.doc_content.getClobVal() FROM doc d;

I have tried creating a UserType class that would convert the column from XMLType to char[], which works - almost. In order to do that, you first have to create an XMLType object from the Oracle OPAQUE object then get the CharacterStream from XMLType.

Here's the relevant snippet:

Code:

OPAQUE op=ors.getOPAQUE(names[0]);                
XMLType xt=XMLType.createXML(op);                
Reader rd=xt.getClobVal().getCharacterStream();          

I then use rd to convert it into a char[].

The problem with that is that XMLType is internally converted to a DOM object that comes out nice and indented in the character stream. That causes problems for tags where whitespace is significant.

One solution to this would be to convert the table column to Clob instead of XMLType. Using a Clob column is the Oracle recommended way to retain whitespace, but that is currently impossible due to other requirements. For instance, XPath queries and Extract() only work on XMLType columns.

Long story short, my UserType will not work the way I implemented it.

I am at the point where I am ready to create my own Persister class to work around this problem. It would simply make the statement use .getClobVal() for any XMLType columns and read them as Clobs. XMLType columns accept Clobs for INSERTs, so there's no issues I'm aware of in that respect.

This does not strike me as a particularily elegant solution, so I'm looking for suggestions. Does anyone know a better way to do this? If implementing a persister is the best way, then what is the best approach to it?

If I can't get this to work, I might have to abandon Hibernate entirely in favor of a more manual approach.

Yvan · **Joined:** Thu Oct 09, 2003 11:17 am **Posts:** 2

I have the same problem...Does anyone find a solution for this problem ?

Yvan

alphapenguin wrote:

I've been trying to get this work, but now I'm stuck. I apologize if I've missed something obvious. I have been reading the fine manual, searched the web and newsgroups without finding a solution.

I need to be able to work with an Oracle database that has a table that contains a column of type SYS.XMLType.

Let's say the table was created like this:

Code:

CREATE TABLE doc (
 doc_id int not_null primary key,
 doc_content SYS.XMLType
);

Then my mapping looks like this:

Code:

<class name="myorg.Doc" table="doc">
        <id name="id" column="id" type="int">
            <generator class="sequence">                        
                <param name="sequence">SEQ_DOC</param>
            </generator>
        </id>
   <property name="content" column="doc_content" type="Clob"/>
</class>

The problem is that the Oracle JDBC driver does not allow XMLType columns to be retrieved as Clobs from the ResultSet unless you use this syntax: (the column must be aliased for this to work)

Code:

SELECT d.doc_content.getClobVal() FROM doc d;

I have tried creating a UserType class that would convert the column from XMLType to char[], which works - almost. In order to do that, you first have to create an XMLType object from the Oracle OPAQUE object then get the CharacterStream from XMLType.

Here's the relevant snippet:

Code:

OPAQUE op=ors.getOPAQUE(names[0]);                
XMLType xt=XMLType.createXML(op);                
Reader rd=xt.getClobVal().getCharacterStream();          

I then use rd to convert it into a char[].

The problem with that is that XMLType is internally converted to a DOM object that comes out nice and indented in the character stream. That causes problems for tags where whitespace is significant.

One solution to this would be to convert the table column to Clob instead of XMLType. Using a Clob column is the Oracle recommended way to retain whitespace, but that is currently impossible due to other requirements. For instance, XPath queries and Extract() only work on XMLType columns.

Long story short, my UserType will not work the way I implemented it.

I am at the point where I am ready to create my own Persister class to work around this problem. It would simply make the statement use .getClobVal() for any XMLType columns and read them as Clobs. XMLType columns accept Clobs for INSERTs, so there's no issues I'm aware of in that respect.

This does not strike me as a particularily elegant solution, so I'm looking for suggestions. Does anyone know a better way to do this? If implementing a persister is the best way, then what is the best approach to it?

If I can't get this to work, I might have to abandon Hibernate entirely in favor of a more manual approach.

gavin · **Posted:** Mon Dec 01, 2003 11:42 am

You might be able to use a formula property to do this? perhaps?

I dunno, give it a try.... (might require a change to net.sf.hibernate.sgl.Template)

alphapenguin · **Posted:** Mon Dec 01, 2003 12:51 pm

gavin wrote:

You might be able to use a formula property to do this? perhaps?

I dunno, give it a try.... (might require a change to net.sf.hibernate.sgl.Template)

Tried it, didn't work, for various reasons. One is that our tables exist in several (some dynamic) schemas, and a formula can't be used for a data column that also needs to be used for insertion.

Basically, there were many cases where I would have to circumvent the normal operation of Hibernate to accomodate our needs. At that point, trying to fit a square Hibernate into a round hole seems futile. It's not a very large data model, so the benefit of Hibernate was limited to begin with.

Due to this, and various other issues we decided to go with a custom persistence solution. Hibernate works very well if used as intended, but due to the rather exotic nature of our model, it didn't work for us this time.

A piece of advice to others: In a very small number of cases this will happen. Just make sure very early in development to verify what you're trying to do is going to work with Hibernate. It probably doesn't hurt to build your model with Hibernate in mind either. For example, some common practices, like "avoid using surrogate keys", fly straight in the face of effective use of Hibernate. Keep those things in mind.

christian · **Posted:** Mon Dec 01, 2003 1:01 pm

alphapenguin wrote:

For example, some common practices, like "avoid using surrogate keys", fly straight in the face of effective use of Hibernate. Keep those things in mind.

I know that you have written "common", not "best", but I want to make sure that this is not a good practise. Don't try to force natural keys, use surrogate keys if there is no suitable candidate key. :)

alphapenguin · **Posted:** Mon Dec 01, 2003 1:16 pm

christian wrote:

alphapenguin wrote:

For example, some common practices, like "avoid using surrogate keys", fly straight in the face of effective use of Hibernate. Keep those things in mind.

I know that you have written "common", not "best", but I want to make sure that this is not a good practise. Don't try to force natural keys, use surrogate keys if there is no suitable candidate key. :)

Right on. Coming to Oracle from MySQL and AUTO_INCREMENT I have personally almost always used surrogate keys. I'd almost go as far as saying never use natural keys unless you know for 100% sure that they will never change as long as you ever shall live. That doesn't just go for Hibernate, but in other contexts as well. Eventually it comes back to bite you on the a**.

As an example, consider a table of users (to which other tables are related). Username and password right? Not if the usernames ever change. An extra column of user id solves it. Create a unique constraint on the username column and you're set to go.

Hopefully, issues such as these come up in that short, neglected phase called "planning". :)