OpenEdge Oracle migration - Case Sensitivity

[andrewt30]

Hi,
I am currently trying to use the OpenEdge Oracle migration tool to migrate our Progress database into Oracle version 9.2. In general the migration is working, but some of our indexes are not being migrated across. All the character fields in our Progress database are case insensitive, and none of the indexes that include character fields in them are getting loaded into the Oracle db - I get Oracle error 01031 about insufficient privileges for each index that includes a character field.

If I open Oracle SQLplus, and manually enter the SQL commands that progress is generating for these indexes I get the same error - for instance entering:-
create index activity##coactdescidx on activity (deleted, companycode, upper(descr), progress_recid);

produces the error message:-
ERROR at line 1:
ORA-01031: insufficient privileges

If I re-enter the above command in SQLplus without including the upper function, then the index loads sucessfully.

Does anyone know if this error really is being caused by an Oracle privileges/permissions issue, and then if so have you any idea what privilege/permission/system setting I need? I have all the permissions set that are detailed in the Progress OpenEdge Oracle Dataserver manual.

Thanks for your help,
Andrew Thornton.

 

[andrewt30]

Hi, For anyone interested we got hold of an Oracle expert who told us the following additional permissions were required in the Oracle database to allow the upper function to be used when creating indexes.

To create function-based indexes the user must be granted CREATE INDEX and QUERY REWRITE, or alternatively be granted CREATE ANY INDEX and GLOBAL QUERY REWRITE. The index owner must have EXECUTE access on the function used for the index. If execute access is revoked the function-based index will be "disabled".

So to grant the additional required permissions to use the Oracle migration tool from OpenEdge when your indexes include case insensitive character fields you will need to log onto sqlplus as either sys or as a sysdba user and type the following:-
grant create any index to yourusername;
grant query rewrite to yourusername;
​