Home » Developer & Programmer » JDeveloper, Java & XML » INSERTCHILDXML CLOB Error(3 Merged)
INSERTCHILDXML CLOB Error(3 Merged) [message #504817] Wed, 27 April 2011 17:41 Go to next message
komalbarua
Messages: 3
Registered: April 2011
Junior Member
Hello,

When I run this script:
UPDATE BOOKTABLE
SET clobXmlCol = INSERTCHILDXML(clobXmlCol, 'books','bookname', XMLType('<bookname>Harry Potter</bookname>'))

I'm getting this error:
SQL Error: ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"


clobXmlCol column is of clob type.

Please help.

Re: INSERTCHILDXML CLOB Error(3 Merged) [message #505010 is a reply to message #504817] Thu, 28 April 2011 15:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The clobxmlcol needs to be xmltype not clob. Please see the example below.

SCOTT@orcl_11gR2> create table booktable
  2    (clobxmlcol  xmltype)
  3  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO booktable VALUES (xmltype (
  2  '<books>
  3   </books>'))
  4  /

1 row created.

SCOTT@orcl_11gR2> select * from booktable
  2  /

CLOBXMLCOL
--------------------------------------------------------------------------------
<books>
 </books>


1 row selected.

SCOTT@orcl_11gR2> UPDATE BOOKTABLE
  2  SET clobXmlCol = INSERTCHILDXML
  3  			(clobXmlCol,
  4  			 'books',
  5  			 'bookname',
  6  			 XMLType ('<bookname>Harry Potter</bookname>'))
  7  /

1 row updated.

SCOTT@orcl_11gR2> select * from booktable
  2  /

CLOBXMLCOL
--------------------------------------------------------------------------------
<books>
  <bookname>Harry Potter</bookname>
</books>


1 row selected.

SCOTT@orcl_11gR2>

Re: INSERTCHILDXML CLOB Error(3 Merged) [message #505014 is a reply to message #505010] Thu, 28 April 2011 16:07 Go to previous messageGo to next message
komalbarua
Messages: 3
Registered: April 2011
Junior Member
Thank you for your reply.

The clobxmlcol column type can not be changed from clob to xmltype. Any suggestion how to make it work with this restriction?

Regards
Re: INSERTCHILDXML CLOB Error(3 Merged) [message #505023 is a reply to message #505014] Thu, 28 April 2011 18:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You will need to convert it from clob to xml using xmltype() then back to clob using .getclobval() as shown below.

SCOTT@orcl_11gR2> create table booktable
  2    (clobxmlcol  clob)
  3  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO booktable VALUES ('<books></books>')
  2  /

1 row created.

SCOTT@orcl_11gR2> select * from booktable
  2  /

CLOBXMLCOL
--------------------------------------------------------------------------------
<books></books>

1 row selected.

SCOTT@orcl_11gR2> UPDATE booktable
  2  SET    clobxmlcol =
  3  	    (SELECT INSERTCHILDXML
  4  		      (XMLTYPE (clobxmlcol),
  5  		       'books',
  6  		       'bookname',
  7  		       XMLTYPE ('<bookname>Harry Potter</bookname>')).getclobval()
  8  	     FROM   booktable)
  9  /

1 row updated.

SCOTT@orcl_11gR2> select * from booktable
  2  /

CLOBXMLCOL
--------------------------------------------------------------------------------
<books><bookname>Harry Potter</bookname></books>

1 row selected.

SCOTT@orcl_11gR2>

Re: INSERTCHILDXML CLOB Error(3 Merged) [message #505025 is a reply to message #505023] Thu, 28 April 2011 18:52 Go to previous message
komalbarua
Messages: 3
Registered: April 2011
Junior Member
Thank you so much Barbara. It works like a charm.

Regards
Previous Topic: oracle.apps.fnd.framework.OAException: java.lang.ClassCastException
Next Topic: Reading data from database table that contains control characters.
Goto Forum:
  


Current Time: Tue Jan 19 10:07:33 CST 2021