OraFAQ Forum: JDeveloper, Java & XML » INSERTCHILDXML CLOB Error(3 Merged)

Home » Developer & Programmer » JDeveloper, Java & XML » INSERTCHILDXML CLOB Error(3 Merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

INSERTCHILDXML CLOB Error(3 Merged) [message #504817]

Wed, 27 April 2011 17:41

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.

Report message to a moderator

Re: INSERTCHILDXML CLOB Error(3 Merged) [message #505010 is a reply to message #504817]

Thu, 28 April 2011 15:24

Barbara Boehmer
Messages: 9103
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>

Report message to a moderator

Re: INSERTCHILDXML CLOB Error(3 Merged) [message #505014 is a reply to message #505010]

Thu, 28 April 2011 16:07

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

Report message to a moderator

Re: INSERTCHILDXML CLOB Error(3 Merged) [message #505023 is a reply to message #505014]

Thu, 28 April 2011 18:14

Barbara Boehmer
Messages: 9103
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>

Report message to a moderator

Re: INSERTCHILDXML CLOB Error(3 Merged) [message #505025 is a reply to message #505023]

Thu, 28 April 2011 18:52

komalbarua
Messages: 3
Registered: April 2011

Junior Member

Thank you so much Barbara. It works like a charm.

Regards

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 00:52:07 CST 2025