Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: Yet another Oracle XMLType question

This question is not answered. Helpful answers available: 1. Correct answers available: 1.


Permlink Replies: 5 - Pages: 1 - Last Post: Mar 18, 2011 1:00 AM Last Post By: Roger Bjarevall
dshmif

Posts: 2
Registered: 11/30/10
Yet another Oracle XMLType question
Posted: Nov 30, 2010 10:12 PM
 
   thread.click_reply Reply
I attempted to search for this problem in the current threads that involve viewing the Oracle XMLType so I believe I've done everything that's helped others in the past. I've added both the xdb.jar and xmlparserv2.jar libraries to the Device Manager. Before I added these two libraries I was getting the opaque version of the XML data, after installing the libraries I get nothing but null highlighted in red with the error "Error getting value for: 240/1: java.sql.SQLException: Only LOB or thin Storage is supported in Thin XMLType".

I've attached an image of the Device Manager screen and of the SQLException being displayed when I attempt to access the XML data. I'm attempting to get my team to move to DBViz and ditch Toad so it would be great to get this going. I believe this is the last hurdle before I can show them that this wonderful tool does everything that Toad does and more.

Thanks in advance for your time. Please let me know if you need additional information and/or inform me that my "search-fu" is lacking and I'll renew my search of past threads.

UPDATE: It looks as if my google-fu is what was lacking. It looks like there's an issue using the thin client with schema based XMLType versus non-schema based. There seems to be a way to make the schema based XMLType work with the thin client though (http://forums.oracle.com/forums/thread.jspa?threadID=417030). I don't know if this is true or if it is that DBViz uses this method to handle schema based XMLTypes? Not sure if any DBViz developers patrol these boards...hope so. Thanks again for any insight.

Edited by: dshmif on Nov 30, 2010 11:17 PM

Edited by: dshmif on Nov 30, 2010 11:22 PM
Roger Bjarevall


Posts: 5,182
Registered: 12/17/04
Re: Yet another Oracle XMLType question
Posted: Dec 1, 2010 2:00 PM    global.in_response_to.tooltip in response to: dshmif
Helpful
   thread.click_reply Reply
Hi,

Can you please post the DDL command(s) so that we can try this locally?

Also, what version of Oracle and the JDBC driver are you using? (This is listed in the Connection Message box when connected).

Best Regards

Roger
dshmif

Posts: 2
Registered: 11/30/10
Re: Yet another Oracle XMLType question
Posted: Dec 2, 2010 6:00 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Thanks so much for responding.

The contents of the Connection Message Box:
"Oracle
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Oracle JDBC driver
10.2.0.3.0"

DDL to register a schema:
BEGIN
dbms_xmlschema.registerSchema(
schemaurl => 'http://toms.odc.vzwcorp.com/toms_X4.xsd',
schemadoc => '<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"
xmlns:xdb= "http://xmlns.oracle.com/xdb" version="1.0">
<xs:element name="your_root_element" xdb:defaultTable="your_table_name">
...
</xs:element>
</xs:schema>
');
END;

Hopefully that comes out. Thanks again for the help.
Roger Bjarevall


Posts: 5,182
Registered: 12/17/04
Re: Yet another Oracle XMLType question
Posted: Dec 6, 2010 2:33 PM    global.in_response_to.tooltip in response to: dshmif
 
   thread.click_reply Reply
Hi,

Can you please try the following:

For the actual table containing the XML type, right-click the table in the objects tree and select "Script Object to SQL Editor->SELECT".

Run the select. I assume you get the same result as you originally reported?

Modify the SQL by adding a table alias for the table. Now modify the XML column(s) in the select column section so they refer the table alias + use the getClobVal() function. Here is an example:

SELECT
    ID,
    x.my_xml_column.getClobVal()
FROM
    SCOTT.XMLTYPE_TEST x

What is the result when you run this? If you get "CLOB" in the XML column double-click the CLOB to open the data.

Regards

Roger
Sammy

Posts: 49
Registered: 10/26/06
Re: Yet another Oracle XMLType question
Posted: Mar 11, 2011 7:25 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
 global.attachment XML.jpg (52.3 KB )
I'm having this same situation. If I use getClobVal, I get the correct data from my XMLTYPE column. Otherwise, using a straight SELECT, I get part of the XML string, but it has high ASCII characters embedded throughout. See attached picture. Any suggestions?

Thanks,
Sammy
Roger Bjarevall


Posts: 5,182
Registered: 12/17/04
Re: Yet another Oracle XMLType question
Posted: Mar 18, 2011 1:00 AM    global.in_response_to.tooltip in response to: Sammy
 
   thread.click_reply Reply
Sammy,

Please check:

http://www.dbvis.com/forum/thread.jspa?messageID=12090

Regards

Roger

Point your RSS reader here for a feed of the latest messages in all forums