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

Seth Theriault slt at columbia.edu
Thu Jul 22 12:37:06 PDT 2010


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


More information about the production mailing list