CREATE OR REPLACE PROCEDURE fixclasses AS
    className VARCHAR2(255);
    newName VARCHAR2(255);
    elemId NUMBER;
    idx NUMBER;
    idx2 NUMBER;
    udo_pack VARCHAR2(255) := 'oracle.wh.repos.impl.userDefined.';
    pack_len NUMBER := 33;
    udo_pref VARCHAR2(255) := 'UD_';
    pref_len NUMBER := 3;
BEGIN
    for c in (select name, elementid from ClassDefinition_v 
              where name like udo_pack || '%') loop
        idx := instr(c.name, udo_pack);
        if (idx=1) then
            className := substr(c.name, pack_len+1);
            idx2 := instr(className, udo_pref);
            if (idx2 <> 1) then
                newName := udo_pack || udo_pref || className;
                update CMPElement_v set strongtypename=newName where strongtypename=c.name;
                update ClassDefinition_v set name=newName, logicalName=newName where elementid=c.elementid;
                update DefinitionInterestUsage_v set scriptname=udo_pref || className where interestingobject=c.elementid;
            end if;
        end if;
    end loop;

END;

/