[Building Sakai] Oracle change LONG to CLOB

[Neal Caidin]

Does this merit a Jira ticket, converting LONGs to CLOBs and Long RAWS to BLOBs?

Thanks,
Neal

On Mar 22, 2013, at 9:16 PM, "Adams, David" <da1 at vt.edu> wrote:

> I can verify that this has not been fixed as of 2.9.1, at least for all of the should-be-CLOB-or-BLOB fields. However, to answer the original question, it's completely safe and appropriate to just convert all the LONGs to CLOBs and LONG RAWs to BLOBs.
> 
> -dave 
> ________________________________________
> From: sakai-dev-bounces at collab.sakaiproject.org [sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Matthew Jones [matthew at longsight.com]
> Sent: Friday, March 22, 2013 5:27 PM
> To: Joshua Swink
> Cc: Sakai-Dev
> Subject: Re: [Building Sakai] Oracle change LONG to CLOB
> 
> Yea, it seems like this should have been fixed sometime in 2.5 or 2.6.
> 
> But you can run that and afterward you'd have to run a script to detect and repair indexes in an invalid state. Otherwise there's no problems. All LONGs really should be CLOBS
> 
> Fix for invalid state indexes
> https://jira.sakaiproject.org/browse/SAK-16553
> 
> Original comments for 2.5/2.6 to change LONG's to CLOBS
> https://jira.sakaiproject.org/browse/SAK-7977
> 
> 
> On Fri, Mar 22, 2013 at 5:06 PM, Joshua Swink <joshua.swink at gmail.com<mailto:joshua.swink at gmail.com>> wrote:
> Hi, wondering if I can just change ANNOUNCEMENT_MESSAGE's XML data type from LONG to CLOB.
> 
> While running sakai_2_8_0-2_8_1_oracle_conversion.sql, this is executed:
> 
> update ANNOUNCEMENT_MESSAGE set MESSAGE_ORDER='1', XML=replace(XML, ' subject=', ' message_order="1" subject=') where MESSAGE_ORDER is null;
> 
> The db responds: ORA-00932: inconsistent datatypes: expected NUMBER got LONG
> 
> Not sure why it said expected NUMBER, when that is a character field... but anyway, it seems like a good solution is to convert XML to a CLOB as follows:
> 
> alter table ANNOUNCEMENT_MESSAGE modify (XML CLOB);
> 
> Josh
> 
> 
> 
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> 
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
> 
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> 
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"