?
Solved

ORA-00902: invalid datatype

Posted on 2008-06-26
7
Medium Priority
?
1,694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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

Industry Leaders: 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!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

762 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