I am receiving something strange.
Joselmo Carvalho, June 26, 2006 - 4:12 pm UTC
This is what I receive when try execute the query. What does it mean?
1* select * from t where id = 5
SQL> /
***
*** Object defn.s out of sync w/ data
******
*** Object defn.s out of sync w/ data
******
*** Object defn.s out of sync w/ data
******
*** Object defn.s out of sync w/ data
******
*** Object defn.s out of sync w/ data
******
*** Object defn.s out of sync w/ data
******
*** Object defn.s out of sync w/ data
***
Thanks
June 26, 2006 - 4:22 pm UTC
if you do my example - does that work?
I need to see XML text that is in XMLType column
Joselmo Carvalho, June 27, 2006 - 8:28 am UTC
So, I am using XE to test but in production I am using Oracle 9i (9.2.0.6.0).
The commands work fine in XE, but I received error using 9i during the insert command.
Look at this:
SQL> insert into t values ( 5, '<tag>data</tag>' );
insert into t values ( 5, '<tag>data</tag>' )
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
SQL>
May my problem is because I need some patch?
Thanks
June 27, 2006 - 9:30 am UTC
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t ( id number, xml xmltype );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( 5, xmltype( '<tag>data</tag>' ) );
1 row created.
ops$tkyte@ORA9IR2> set long 50000
ops$tkyte@ORA9IR2> column xml format a40
ops$tkyte@ORA9IR2> select * from t where id = 5;
ID XML
---------- ----------------------------------------
5 <tag>data</tag>
<b>it is counter productive to develop in version Y and deploy on version X. You'll spend lots of time scratching your head...</b>
I need to see XML text that is in XMLType column
Joselmo Carvalho, June 27, 2006 - 9:50 am UTC
Ok, I agree. But do you have any idea to help me?
Thanks.
June 27, 2006 - 10:20 am UTC
yes, I just posted a 9ir2 example?? see above, cast the text explicitly as xmltype() in the insert.
I need to see XML text that is in XMLType column
Joselmo Carvalho, June 27, 2006 - 1:17 pm UTC
It is that!!!!
Thank you so much!!!
Very useful
Ram, June 27, 2006 - 4:49 pm UTC
It is crystal clear after seeing this..I will remember to Cast to XMLType() for 9i..
thanks so much Tom
Cool feeling
Kamran Haq, June 28, 2006 - 1:22 am UTC
Inspiring dialogue. Looking up to Tom's patience.
Back to VARCHAR2?
Duke Ganote, July 20, 2006 - 5:26 pm UTC
How do we cast XML back to strings? I followed your original answer, then tried:
SQL> begin
2 for rec in ( select * from t ) loop
3 dbms_output.put_line(rec.xml);
4 end loop;
5 end;
6 /
dbms_output.put_line(rec.xml);
*
ERROR at line 3:
ORA-06550: line 3, column 3:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored
July 22, 2006 - 5:28 pm UTC
ops$tkyte%ORA10GR2> begin
2 for x in ( select * from t)
3 loop
4 dbms_output.put_line( x.xml.getStringVal() );
5 end loop;
6 end;
7 /
<tag>data</tag>
PL/SQL procedure successfully completed.
would be one way - use the documented methods of the XMLTYPE to interact with this datatype.
10g CLOB XML Extract
Steve Pilgrim, July 24, 2006 - 10:04 am UTC
Hi Tom,
We've been looking at something similar and your examples work fine - except that the column with the XML is a CLOB. How can we use the functions on that ?
eg:
create table t ( id number, xml clob );
Steve Pilgrim, July 24, 2006 - 10:16 am UTC
ahhhh scrub that ... got it to work with xmltype :
select extractvalue(xml,'/tag'),xclob,extractvalue(xmltype(xclob),'/tagc') from t