• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1840
  • Last Modified:

ORA-00902: invalid datatype

i currently have a table with one column defined as a clob... i would like to rename that column to datatype Xmltype. I have the followig syntax....

ALTER TABLE STANNER.DELIVERY_REQUEST MODIFY (PAYLOAD_XML(XMLTYPE))
I am getting an invalid datatype message... How do I alter the datatype?

I then tried to add another column using the same datatype... and got the same message..
ALTER TABLE STANNER.DELIVERY_REQUEST add (PAYLOAD_XML2 (XMLTYPE));

I am not sure how to change the data type of the clob column short of dropping and recreating this table..
But I am looking for options...I don't want to loose the data..

Thanks,
0
ShirleyTanner
Asked:
ShirleyTanner
  • 3
  • 2
1 Solution
 
DrSQLCommented:
To change it you'll have to create a new one as an XMLType, update the new column and then drop the old column.  Then, if you want, you can rename the column.

alter table STANNER.DELIVERY_REQUEST add (PAYLOAD_XML2 XMLTYPE); -- you had bad syntax
update STANNER.DELIVERY_REQUEST  set PAYLOAD_XML2=PAYLOAD_XML;
alter table STANNER.DELIVERY_REQUEST drop column payload_xml;


Good luck!
0
 
ShirleyTannerAuthor Commented:
oh... I didn't see the bad syntax...exactly what you have suggested is what I was going to do next... but ran into the syntax issue :0

however, this is what I get when I try to perform the update...

update STANNER.DELIVERY_REQUEST  set PAYLOAD_XML2=PAYLOAD_XML
 
ORA-00932: inconsistent datatypes: expected - got CLOB
0
 
DrSQLCommented:
Sorry, I forgot to add the casting of the clob to an xmltype:

update STANNER.DELIVERY_REQUEST  set PAYLOAD_XML2=xmltype(PAYLOAD_XML);

Good luck!
0
 
ShirleyTannerAuthor Commented:
excellent... thanks...that worked...
0
 
DrSQLCommented:
I think Shirley meant to accept the solution I gave, but instead selected the "thank you" as the solution.  As I'm the only one on the thread, I don't think we need to keep it open for 7 more days.

250 points to 21878442 by DrSQL

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now