Solved

ORA-00902: invalid datatype

Posted on 2008-06-26
7
1,654 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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

739 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