[Deploying Sakai] 2.5 -> 2.6 SQL conversion scripts and Oracle 9i

[Matthew Jones]

1) Yea, because of enhancements to MERGE in 10g the WHEN MATCHED or WHEN NOT
MATCHED became optional clauses. I only tested on 10g and didn't know about
this. [1] You'd just have to missing the other clause do nothing.

2) Hopefully that REGEXP_REPLACE works. Otherwise the only easy ways to fix
this will be
 A. A code change
 B. Dumping this database table doing a regexp replacement and reloading
 C. Writing a command line script in your favorite scripting language to
check every XML column in that table and replace these fields in the XML
blob. :( Likely they will only contain "No Grade" for an English language
Sakai. All of the the unicoded languages were mostly for non-English
installations.

Note: This really was a pretty minor issue, as most assignments are of a
graded type, I think when running it only like 1% of all of our assignments
were converted, and in that case, the display would just have a blank space
instead of a localized message saying "No grade". Perhaps confusing, but not
a blocker.

Ideally we would have a repeatable process in the future for database
conversions to do step "2-C".


[1] http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php

On Thu, Jul 22, 2010 at 3:37 PM, Seth Theriault <slt at columbia.edu> wrote:

> Hello,
>
> I am in the middle of upgrading from 2.5 to 2.6 and was bitten by
> the use of non-Oracle 9i compatible SQL statements in the
> conversions scripts. More specifically, it concerns the use of
> the MERGE statement and the REGEXP_REPLACE function.
>
> -- MERGE
>
> Both the 2.6 and 2.6.0- > 2.6.1 conversion scripts use the MERGE
> statement to conditionally add or modify permissions. See:
>
> SAK-12016 (original INSERT) & SAK-16122 (changed to use MERGE)
> SAK-11096 (original INSERT) & SAK-16847 (MERGE)
>
> It looks as though the MERGE syntax used in the conversion
> scripts is only valid in Oracle 10 and above: We are missing the
> required WHEN NOT MATCHED condition.
>
> As it turns out, I don't have any of these changes yet, so I can
> probably just use the original inserts (in the case of
> SAK-11096/SAK-16847, I just commented out the SAK-16847 MERGE).
>
> -- REGEXP_REPLACE
>
> This function doesn't exist in Oracle 9, so this presents a
> thornier problem for the SAK-16548 conversion. I am hopeful that
> this script:
>
>
> http://phil-sqltips.blogspot.com/2009/06/regexpreplace-regexplike-for-oracle-9i.html
>
> might do the trick and save me. I'll be testing it shortly.
>
> If anyone has advice, pointers, or comments, I am all ears.
>
> Seth
> _______________________________________________
> production mailing list
> production at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/production
>
> TO UNSUBSCRIBE: send email to
> production-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20100722/8404984e/attachment.html