Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1753
  • 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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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