Solved

ORA-00902: invalid datatype

Posted on 2008-06-26
7
1,614 Views
Last Modified: 2013-12-18
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
Comment
Question by:ShirleyTanner
  • 3
  • 2
7 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 21878261
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
 

Author Comment

by:ShirleyTanner
ID: 21878360
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
 
LVL 22

Accepted Solution

by:
DrSQL earned 250 total points
ID: 21878442
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
 

Author Comment

by:ShirleyTanner
ID: 21878763
excellent... thanks...that worked...
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21878921
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now