asked on

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,

DrSQL - Scott Anderson

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!

ShirleyTanner

ASKER

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

ASKER CERTIFIED SOLUTION

DrSQL - Scott Anderson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ShirleyTanner

ASKER

excellent... thanks...that worked...

DrSQL - Scott Anderson

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.