[Building Sakai] Oracle change LONG to CLOB

[Adams, David]

Based on interest in this issue, I cleaned up the SQL*Plus script we used to do this conversion ourselves and posted it at:

https://github.com/daveadams/sakai-tools/blob/master/convert-longs-to-lobs.sql

By default it will scan your tables for LONG and LONG RAW fields but it won't change anything. Add "CONVERT" to the command line to actually perform the conversion (and rebuild the indexes in the involved tables, a step we found necessary, though I don't recall why).

Please be sure you understand what the script will be doing before you run it, and always always test thoroughly on a non-production environment before you attempt to run this on your production system.

This script works fine in my environment, but I can't speak to other versions of Oracle. We run 11.2.0.3.

-dave
________________________________________
From: Adams, David
Sent: Saturday, March 23, 2013 4:53 PM
To: Neal Caidin
Cc: Matthew Jones; Joshua Swink; Sakai-Dev
Subject: RE: [Building Sakai] Oracle change LONG to CLOB

There is a ticket: https://jira.sakaiproject.org/browse/SAK-7977

It's a Hibernate auto ddl issue.
________________________________________
From: Neal Caidin [nealcaidin at sakaifoundation.org]
Sent: Saturday, March 23, 2013 4:34 PM
To: Adams, David
Cc: Matthew Jones; Joshua Swink; Sakai-Dev
Subject: Re: [Building Sakai] Oracle change LONG to CLOB

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"