Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\reposasst\Snapshot.pkb

CREATE OR REPLACE PACKAGE BODY Snapshot AS

-- Keep track of debug state.
debugFlag BOOLEAN   := FALSE;
debugUser NUMBER(5) := 0;
debugSeq  NUMBER(11) := 0;

-- Keep track of Oracle DB version.  Needed to avoid Subquery factoring syntax for some versions.
ORACLE_VERSION VARCHAR2(64);

-- this section specifies compare dml constants across snapshots
COMPARE_INSERT_STATE   NUMBER(1):= 0;
COMPARE_DELETE_STATE   NUMBER(1):= 1;
COMPARE_UPDATE_STATE   NUMBER(1):= 2;
COMPARE_UNKNOWN_STATE   NUMBER(1):= 9;

-- this section specifies comparison states on the same object
SAME                   NUMBER(1):= 0;
DIFFERENT              NUMBER(1):= 1;

SUCCESS                NUMBER(1):= 0;

-- follow two consts switch target depending upon CURRENT - SNAPSHOT or
-- SNAPSHOT - CURRENT
CURRENT_MINUS_SNAPSHOT NUMBER(1):= 0;
SNAPSHOT_MINUS_CURRENT NUMBER(1):= 1;

-- this section specifies the constants for restoring the parents
RESTOREPARENTS 	       NUMBER(1):= 0;

COMPARE_LABEL_ID       NUMBER(1):= 0;

-- following section specifies which structure to pick
-- depending upon transient or persistent snapshot
SNAPSHOT_LOOKUP_TABLE 		VARCHAR2(30) := 'SNAPSHOTLOOKUP';
SNAPSHOT_LOOKUP_TABLE_T 	VARCHAR2(30) := 'SNAPSHOTLOOKUPTEMP';
COMPARE_STORE_TABLE 		VARCHAR2(30) := 'COMPARESTORE';
COMPARE_STORE_TABLE_T 		VARCHAR2(30) := 'COMPARESTORETEMP';
COMPARE_STORE_MGR_TABLE 	VARCHAR2(30) := 'COMPARESTOREMANAGER';
COMPARE_STORE_MGR_TABLE_T       VARCHAR2(30) := 'COMPARESTOREMANAGERTEMP';
SNAPSHOT_STORE_TABLE 		VARCHAR2(30) := 'SNAPSHOTSTORETABLE';
SNAPSHOT_STORE_TABLE_T 		VARCHAR2(30) := 'SNAPSHOTSTORETABLETEMP';
LWCONTEXT_TABLE                 VARCHAR2(30) := 'LWCONTEXT';
LWCONTEXT_TABLE_T               VARCHAR2(30) := 'LWCONTEXTTEMP';
LWGRAPH_VIEW                    VARCHAR2(30) := 'LWGRAPH';
LWGRAPH_VIEW_T                  VARCHAR2(30) := 'LWGRAPHTEMP';
LWCOMPTREE_VIEW                 VARCHAR2(30) := 'LWCOMPTREE';
LWCOMPTREE_VIEW_T               VARCHAR2(30) := 'LWCOMPTREETEMP';
COMPARE_SEQUENCE                VARCHAR2(30) := 'COMPARESEQUENCE';
COMPARE_SEQUENCE_T              VARCHAR2(30) := 'COMPARESEQUENCETEMP';
SNAPSHOT_SEQUENCE               VARCHAR2(30) := 'CWMSEQ';
SNAPSHOT_SEQUENCE_T             VARCHAR2(30) := 'SNAPSHOTSEQUENCETEMP';

VIEW_CMPELEMENT_V               VARCHAR2(30) := 'CMPELEMENT_V';
VIEW_CMPELEMENT_X               VARCHAR2(30) := 'CMPELEMENT';
VIEW_CMPPHYSICALOBJECT_V	VARCHAR2(30) := 'CMPPHYSICALOBJECT_V';
VIEW_CMPPHYSICALOBJECT_X	VARCHAR2(30) := 'CMPPHYSICALOBJECT';
VIEW_CMPWBPROJECT_V             VARCHAR2(30) := 'CMPWBPROJECT_V';
VIEW_CMPWBPROJECT_X             VARCHAR2(30) := 'CMPWBPROJECT';
VIEW_FIRSTCLASSOBJECT_V		VARCHAR2(30) := 'FIRSTCLASSOBJECT_V';
VIEW_FIRSTCLASSOBJECT_X 	VARCHAR2(30) := 'FIRSTCLASSOBJECT';
VIEW_SECONDCLASSOBJECT_V	VARCHAR2(30) := 'SECONDCLASSOBJECT_V';
VIEW_SECONDCLASSOBJECT_X 	VARCHAR2(30) := 'SECONDCLASSOBJECT';
VIEW_CMPFUNCTION_V              VARCHAR2(30) := 'CMPFUNCTION_V';
VIEW_CMPFUNCTION_X              VARCHAR2(30) := 'CMPFUNCTION';
VIEW_CMPREFPROPVALUE_V          VARCHAR2(30) := 'CMPREFERENCEPROPERTYVALUE_V';
VIEW_CMPREFPROPVALUE_X          VARCHAR2(30) := 'CMPREFERENCEPROPERTYVALUE';

VIEW_PCTREE                     VARCHAR2(30) := 'PCTREE';
VIEW_PCTREE_X                   VARCHAR2(30) := 'PCTREEDEBUG';
VIEW_INTRALINK                  VARCHAR2(30) := 'INTRALINK';
VIEW_INTRALINK_X                VARCHAR2(30) := 'INTRALINKDEBUG';
VIEW_INTERLINK                  VARCHAR2(30) := 'INTERLINK';
VIEW_INTERLINK_X                VARCHAR2(30) := 'INTERLINKDEBUG';

FUNCTION getSnapshotLookupTable(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return SNAPSHOT_LOOKUP_TABLE_T;
  else
    return SNAPSHOT_LOOKUP_TABLE;
  end if;
END getSnapshotLookupTable;

FUNCTION getCompareStoreMgrTable(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return COMPARE_STORE_MGR_TABLE_T;
  else
    return COMPARE_STORE_MGR_TABLE;
  end if;
END getCompareStoreMgrTable;

FUNCTION getCompareStoreTable(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return COMPARE_STORE_TABLE_T;
  else
    return COMPARE_STORE_TABLE;
  end if;
END getCompareStoreTable;

FUNCTION getSnapshotStoreTable(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return SNAPSHOT_STORE_TABLE_T;
  else
    return SNAPSHOT_STORE_TABLE;
  end if;
END getSnapshotStoreTable;

FUNCTION getLWContextTable(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return LWCONTEXT_TABLE_T;
  else
    return LWCONTEXT_TABLE;
  end if;
END getLWContextTable;

FUNCTION getLWGraphView(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return LWGRAPH_VIEW_T;
  else
    return LWGRAPH_VIEW;
  end if;
END getLWGraphView;

FUNCTION getLWCompTreeView(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return LWCOMPTREE_VIEW_T;
  else
    return LWCOMPTREE_VIEW;
  end if;
END getLWCompTreeView;

FUNCTION getCompareSeq(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return COMPARE_SEQUENCE_T;
  else
    return COMPARE_SEQUENCE;
  end if;
END getCompareSeq;

FUNCTION getSnapshotSeq(transient IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (transient = TRANSIENT_SNAPSHOT) then
    return SNAPSHOT_SEQUENCE_T;
  else
    return SNAPSHOT_SEQUENCE;
  end if;
END getSnapshotSeq;

FUNCTION getCMPElement(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_CMPELEMENT_V;
  else
    return VIEW_CMPELEMENT_X;
  end if;
END getCMPElement;

FUNCTION getCMPPhysicalObject(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_CMPPHYSICALOBJECT_V;
  else
    return VIEW_CMPPHYSICALOBJECT_X;
  end if;
END getCMPPhysicalObject;

FUNCTION getCMPWBProject(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_CMPWBPROJECT_V;
  else
    return VIEW_CMPWBPROJECT_X;
  end if;
END getCMPWBProject;

FUNCTION getCMPFunction(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_CMPFUNCTION_V;
  else
    return VIEW_CMPFUNCTION_X;
  end if;
END getCMPFunction;

FUNCTION getCMPReferencePropertyValue(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_CMPREFPROPVALUE_V;
  else
    return VIEW_CMPREFPROPVALUE_X;
  end if;
END getCMPReferencePropertyValue;

FUNCTION getFirstClassObject(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_FIRSTCLASSOBJECT_V;
  else
    return VIEW_FIRSTCLASSOBJECT_X;
  end if;
END getFirstClassObject;

FUNCTION getSecondClassObject(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_SECONDCLASSOBJECT_V;
  else
    return VIEW_SECONDCLASSOBJECT_X;
  end if;
END getSecondClassObject;

FUNCTION getPCTree(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_PCTREE;
  else
    return VIEW_PCTREE_X;
  end if;
END getPCTree;

FUNCTION getIntraLink(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_INTRALINK;
  else
    return VIEW_INTRALINK_X;
  end if;
END getIntraLink;

FUNCTION getInterLink(current IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    return VIEW_INTERLINK;
  else
    return VIEW_INTERLINK_X;
  end if;
END getInterLink;

FUNCTION getCMPElementLcBlock(baseview IN VARCHAR2, hintindex IN VARCHAR2,
                                snap IN NUMBER, assoc IN NUMBER) RETURN VARCHAR2 IS
  block VARCHAR2(1000) := '';
  extra VARCHAR2(100) := '';
BEGIN
  if (hintindex is not null) then
    extra := 'index(' || baseview || ', ' || hintindex || ')';
  end if;

block :=
    ' select /*+ordered use_nl(cmp1) ' || extra || '*/'||
    '   ' || snap || ', lc.compuoid, lc.type, lc.elementid, lc.rootid,' ||
    '   lc.uoid, lc.linkfromrole, lc.name, lc.logicalName, lc.classname,' ||
    '   lc.metadatasignature, lc.objtype, cmp1.elementid, cmp1.uoid,' ||
    '   lc.linktorole, cmp1.name, cmp1.logicalname, cmp1.classname,'||
    '   ' || assoc || ', lc.uoid, cmp1.uoid'||
    ' from   lc, ' || baseview ||' cmp1' ||
    ' where  cmp1.elementid = lc.linkto';
  return block;
END getCMPElementLcBlock;

FUNCTION getCMPElementLcUnion(current IN NUMBER, snap IN NUMBER, assoc IN NUMBER) RETURN VARCHAR2 IS
  block VARCHAR2(4000) := '';
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    block := getCMPElementLcBlock('CMPFCOElement_V',    'idx_FCOElementid',    snap, assoc) || ' union all' ||
             getCMPElementLcBlock('CMPSCOElement_V',    'idx_SCOElementid',    snap, assoc) || ' union all' ||
             getCMPElementLcBlock('CMPSCOCfgElement_V', 'idx_SCOCfgElementid', snap, assoc) || ' union all' ||
             getCMPElementLcBlock('CMPSCOMapElement_V', 'idx_SCOMapElementid', snap, assoc) || ' union all' ||
             getCMPElementLcBlock('CMPSCOPrpElement_V', 'idx_SCOPrpElementid', snap, assoc) || ' union all' ||
             getCMPElementLcBlock('CMPSYSElement_V',    'idx_SYSElementid',    snap, assoc);
  else
    block := getCMPElementLcBlock(getCMPElement(current), '', snap, assoc);
  end if;
  return block;
END getCMPElementLcUnion;

FUNCTION getCMPElementTpBlock(baseview IN VARCHAR2, hintindex IN VARCHAR2,
                                snap IN NUMBER, assoc IN NUMBER) RETURN VARCHAR2 IS
  block VARCHAR2(1000) := '';
  extra VARCHAR2(100) := '';
BEGIN
  if (hintindex is not null) then
    extra := 'index(' || baseview || ', ' || hintindex || ')';
  end if;

block :=
    ' select /*+ordered use_nl(cmp) ' || extra || '*/'||
    ' ' || snap || ',' ||
    ' tp.compuoid, tp.type, tp.elementid, tp.rootid, tp.uoid, tp.childrole, tp.name, ' ||
    ' tp.logicalName, tp.classname, tp.metadatasignature, tp.objtype,' ||
    ' cmp.elementid, cmp.uoid, tp.parentrole, cmp.name, ' ||
    ' cmp.logicalname, cmp.classname, ' || assoc || ',' ||
    ' tp.uoid, cmp.uoid from tp, ' || baseview || ' cmp ' ||
    ' where cmp.elementid = tp.parentid';
  return block;
END getCMPElementTpBlock;

FUNCTION getCMPElementTpUnion(current IN NUMBER, snap IN NUMBER, assoc IN NUMBER) RETURN VARCHAR2 IS
  block VARCHAR2(4000) := '';
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    block := getCMPElementTpBlock('CMPFCOElement_V',    'idx_FCOElementid',    snap, assoc) || ' union all' ||
             getCMPElementTpBlock('CMPSCOElement_V',    'idx_SCOElementid',    snap, assoc) || ' union all' ||
             getCMPElementTpBlock('CMPSCOCfgElement_V', 'idx_SCOCfgElementid', snap, assoc) || ' union all' ||
             getCMPElementTpBlock('CMPSCOMapElement_V', 'idx_SCOMapElementid', snap, assoc) || ' union all' ||
             getCMPElementTpBlock('CMPSCOPrpElement_V', 'idx_SCOPrpElementid', snap, assoc) || ' union all' ||
             getCMPElementTpBlock('CMPSYSElement_V',    'idx_SYSElementid',    snap, assoc);
  else
    block := getCMPElementTpBlock(getCMPElement(current), '', snap, assoc);
  end if;
  return block;
END getCMPElementTpUnion;

FUNCTION getCMPElementTxBlock(baseview IN VARCHAR2, hintindex IN VARCHAR2,
             current IN NUMBER, snap IN NUMBER, assoc IN NUMBER) RETURN VARCHAR2 IS
  block VARCHAR2(1000) := '';
  extra VARCHAR2(100) := '';
BEGIN
  if (hintindex is not null) then
    extra := 'index(' || baseview || ', ' || hintindex || ')';
  end if;

block :=
    ' select /*+ordered use_nl(p cmp) ' || extra || '*/'||
    ' ' || snap || ',' ||
    ' t.compuoid, t.type, t.elementid, t.rootid, t.uoid, p.childrole, t.name, ' ||
    ' t.logicalName, t.classname, t.metadatasignature, t.objtype,' ||
    ' cmp.elementid, cmp.uoid, p.parentrole, cmp.name, ' ||
    ' cmp.logicalname, cmp.classname, ' || assoc || ',' ||
    ' t.uoid, cmp.uoid from temptable t, ' || getPCTree(current) || ' p, ' || baseview ||
    ' cmp ' || ' where p.childid(+) = t.elementid and cmp.elementid = p.parentid ';
  return block;
END getCMPElementTxBlock;

FUNCTION getCMPElementTxUnion(current IN NUMBER, snap IN NUMBER, assoc IN NUMBER)
  RETURN VARCHAR2 IS
  block VARCHAR2(4000) := '';
BEGIN
  if (current = CURRENT_SNAPSHOT) then
    block:= getCMPElementTxBlock('CMPFCOElement_V','idx_FCOElementid',current,
            snap,assoc) || ' union all' ||
            getCMPElementTxBlock('CMPSCOElement_V','idx_SCOElementid',current,
            snap,assoc) || ' union all' ||
            getCMPElementTxBlock('CMPSCOCfgElement_V','idx_SCOCfgElementid',
            current,snap,assoc) || ' union all' ||
            getCMPElementTxBlock('CMPSCOMapElement_V','idx_SCOMapElementid',
            current,snap,assoc) || ' union all' ||
            getCMPElementTxBlock('CMPSCOPrpElement_V','idx_SCOPrpElementid',
            current,snap,assoc) || ' union all' ||
            getCMPElementTxBlock('CMPSYSElement_V','idx_SYSElementid',current,
            snap,assoc);
  else
    block:= getCMPElementTxBlock(getCMPElement(current),'',current,snap,assoc);
  end if;
  return block;
END getCMPElementTxUnion;

FUNCTION isSubQueryFactoringOK RETURN BOOLEAN IS
  subQueryFactoringOK BOOLEAN:= FALSE;
  BEGIN
    --Determine DB version.  Subquery factoring (with ... as) syntax support/bugs depend on DB version.
    if (ORACLE_VERSION is null) then
      select version into ORACLE_VERSION from product_component_version where instr(product,'Oracle') != 0;
    end if;

--Subquery factoring not available in 8i and is buggy in 11g Beta1.  Performance is worse without it!
    if (substr(ORACLE_VERSION, 1, 3) = '11.'  or
        substr(ORACLE_VERSION, 1, 3) = '10.') then
      subQueryFactoringOK := TRUE;
    elsif (substr(ORACLE_VERSION, 1, 2) = '9.') then
      subQueryFactoringOK := TRUE;
    else
      subQueryFactoringOK := FALSE;
    end if;

return subQueryFactoringOK;
  END;

FUNCTION isDebug RETURN BOOLEAN IS
  BEGIN
    return debugFlag;
  END;

PROCEDURE debug(str IN VARCHAR2,value IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    if (debugFlag) then
      --if (ServerLog.isOpen()) then
      --  ServerLog.writeLine(str || value);
      --dbms_output.put_line(str || value);
      if (debugUser = 0) then
        select sys_context('userenv','current_userid') into debugUser from dual;
        debugSeq := (debugUser * 1000000);
      end if;
      debugSeq := debugSeq + 1;
      insert into wb_rt_dp_debug(user_id,msg) values (debugSeq,str || value);
      commit;
    end if;
  END debug;

/**
 * Some utility methods for cleaning up temporary tables
 * for later reuse
 */

PROCEDURE cleanTempTable IS
  BEGIN
    delete from temptable;
  END cleanTempTable;

PROCEDURE cleanLWContextTemp IS
  BEGIN
    delete from LWContextTemp;
  END cleanLWContextTemp;

PROCEDURE cleanTempFCO IS
  BEGIN
    delete from tempfco;
  END cleanTempFCO;

PROCEDURE cleanComponent IS
  BEGIN
    delete from mcmcomponent;
  END cleanComponent;

PROCEDURE cleanSourceTargetComponent IS
  BEGIN
    delete from SourceComponent;
    delete from TargetComponent;
    delete from CompareStoreTemp;
  END cleanSourceTargetComponent;

PROCEDURE cleanMCMErrors IS
  BEGIN
    delete from mcmerrors;
  END cleanMCMErrors;

FUNCTION snapshotExists(snap IN NUMBER) RETURN BOOLEAN IS
  numVar cmpelement_v.elementid%TYPE;
  BEGIN
    BEGIN
      select distinct isHeavy into numVar from snapshotstoretable
        where snapshotid = snap;
    EXCEPTION when NO_DATA_FOUND then
      return false;
    END;
      return true;
  END snapshotExists;

FUNCTION elemExists(elemID IN NUMBER,isVersion IN BOOLEAN default false,
  snap IN NUMBER default 0) RETURN BOOLEAN IS
  uoidStr cmpelement_v.uoid%TYPE;
  BEGIN
    if (isVersion) then
      BEGIN
        select uoid into uoidStr from cmpelement_x
          where (snapshotid = snap or snapshotid = 0) and elementid = elemID;
      EXCEPTION when NO_DATA_FOUND then
        return false;
      END;
      return true;
    else
      BEGIN
        select uoid into uoidStr from cmpelement_v
          where elementid = elemID;
      EXCEPTION when NO_DATA_FOUND then
        return false;
      END;
      return true;
    end if;
  END elemExists;

FUNCTION elemExists(uoidStr IN VARCHAR2,isVersion IN BOOLEAN default false,
  snap IN NUMBER default 0) RETURN BOOLEAN IS
  elemID cmpelement_v.elementid%TYPE;
  BEGIN
    if (isVersion) then
      BEGIN
        select elementid into elemID from cmpelement_x
          where snapshotid = snap and uoid = uoidStr;
        select isHeavy into elemID from snapshotstoretable
          where snapshotid = snap and uoid = uoidStr;
        select isRoot into elemID from snapshotstoretable
          where snapshotid = snap and uoid = uoidStr;
      EXCEPTION when NO_DATA_FOUND then
        return false;
      END;
      return true;
    else
      BEGIN
        select elementid into elemID from cmpelement_v
          where uoid = uoidStr;
      EXCEPTION when NO_DATA_FOUND then
        return false;
      END;
      return true;
    end if;
  END elemExists;

/**
 * This gets the snapshot id assigned for the snapshot name
 * @param labelStr snapshot name
 * @param transient 0 if persistent snapshot
 *                  1 if transient snapshot
 * @return snapshot ID the snapshot id associated with the name
 */
FUNCTION getSnapshotID(labelStr IN VARCHAR2,transient IN NUMBER,
  description IN VARCHAR2,isHeavy IN NUMBER,
  snapType IN NUMBER default RESTORE_SNAPSHOT,snap IN NUMBER default 0)
  RETURN NUMBER IS
  snapID NUMBER(9):=0;
  sql_stmt VARCHAR2(1000);
  BEGIN
    sql_stmt := 'select snapshotID from ' || getSnapshotLookupTable(transient)
      || ' where snapshotName = :labelStr';
    EXECUTE IMMEDIATE sql_stmt INTO snapID USING labelStr;
    return snapID;
    EXCEPTION
      when NO_DATA_FOUND then
      if (snap = 0) then
        sql_stmt := 'insert into ' || getSnapshotLookupTable(transient) ||
                  ' values (' || getSnapshotSeq(transient) ||
                  '.nextval, sys_guid(), :labelStr, sysdate, sysdate, ' ||
                  ' :description , :currentUser, :currentUser, :isHeavy, ' ||
                  ' :snapType)';
        EXECUTE IMMEDIATE sql_stmt USING labelStr,description,
          currentUser,currentUser,isHeavy,snapType;
      else
        sql_stmt := 'insert into ' || getSnapshotLookupTable(transient) ||
                  ' values ( :snap, sys_guid(), :labelStr, sysdate, sysdate, ' ||
                  ' :description , :currentUser, :currentUser, :isHeavy, ' ||
                  ' :snapType)';
        EXECUTE IMMEDIATE sql_stmt USING snap,labelStr,description,
          currentUser,currentUser,isHeavy,snapType;
      end if;
      if (snap = 0) then
      sql_stmt := 'select snapshotID from ' || getSnapshotLookupTable(transient)
        || ' where snapshotName = :labelStr';
        EXECUTE IMMEDIATE sql_stmt INTO snapID USING labelStr;
      else
        snapID:= snap;
      end if;
      return snapID;
  END getSnapshotID;

FUNCTION isFirstClassObject(elemID IN NUMBER,current IN NUMBER default 0)
  RETURN BOOLEAN IS
  vElemID cmpelement_v.elementid%TYPE;
  type cType is ref cursor;
  c cType;
  sql_stmt VARCHAR2(100);
  BEGIN
    if (current = CURRENT_SNAPSHOT) then
      sql_stmt:= 'select elementid from firstclassobject_v where elementid = ' || elemID;
    else
      sql_stmt:= 'select elementid from firstclassobject where elementid = ' || elemID;
    end if;
    debug('Snapshot.isFirstClassObject: sql_stmt = ',sql_stmt);
    open c for sql_stmt;
    fetch c into vElemID;
    if (c%NOTFOUND) then
      return FALSE;
    else
    	return TRUE;
    end if;
    return TRUE;
  END isFirstClassObject;

FUNCTION isSecondClassObject(elemID IN NUMBER,current IN NUMBER default 0)
  RETURN BOOLEAN IS
  vElemID cmpelement_v.elementid%TYPE;
  type cType is ref cursor;
  c cType;
  sql_stmt VARCHAR2(100);
  BEGIN
    if (current = CURRENT_SNAPSHOT) then
      sql_stmt:= 'select elementid from secondclassobject_v where elementid = ' || elemID;
    else
      sql_stmt:= 'select elementid from secondclassobject where elementid = ' || elemID;
    end if;
    open c for sql_stmt;
    fetch c into vElemID;
    if (c%NOTFOUND) then
      return FALSE;
    else
    	return TRUE;
    end if;
    return TRUE;
  END isSecondClassObject;

FUNCTION isFunction(elemID IN NUMBER,current IN NUMBER default 0)
  RETURN BOOLEAN IS
  vElemID cmpelement_v.elementid%TYPE;
  type cType is ref cursor;
  c cType;
  sql_stmt VARCHAR2(100);
  BEGIN
    if (current = CURRENT_SNAPSHOT) then
      sql_stmt:= 'select elementid from cmpfunction_v where elementid = ' || elemID;
    else
      sql_stmt:= 'select elementid from cmpfunction where elementid = ' || elemID;
    end if;
    open c for sql_stmt;
    fetch c into vElemID;
    if (c%NOTFOUND) then
      return FALSE;
    else
    	return TRUE;
    end if;
    return TRUE;
  END isFunction;

FUNCTION isAdminProject(elemID IN NUMBER) RETURN BOOLEAN IS
  vElemID cmpelement_v.elementid%TYPE;
  type cType is ref cursor;
  c cType;
  sql_stmt VARCHAR2(100);
  BEGIN
    sql_stmt:= 'select elementid from cmpwbproject_v where name = ' ||
        '''Admin Project'' and elementid = ' || elemID;
    open c for sql_stmt;
    fetch c into vElemID;
    if (c%NOTFOUND) then
      return FALSE;
    else
    	return TRUE;
    end if;
    return TRUE;
  END isAdminProject;

FUNCTION isOracleLibrary(elemID IN NUMBER) RETURN BOOLEAN IS
  vElemID cmpelement_v.elementid%TYPE;
  type cType is ref cursor;
  c cType;
--
-- Bug 5352671 CJS 09/08/06
--
--  sql_stmt VARCHAR2(1000);
  sql_stmt VARCHAR2(4000);
  BEGIN
    sql_stmt:= 'select elementid from cmpwbrepinstalledmodule_v where name = ' ||
      '''Oracle Library'' and elementid = ' || elemID;
    open c for sql_stmt;
    fetch c into vElemID;
    if (c%NOTFOUND) then
      return FALSE;
    else
    	return TRUE;
    end if;
    return TRUE;
  END isOracleLibrary;
/**
 * This stores the rows of the objects into a temporary table.
 * It assumes that entities will have one and only one FCO and folders
 *   are the only ones that can own other FCOs.
 * @param elemID element id of the FCO
 * @param isCascade if 0 then get all owned component FCOs and SCOs
 *                  if 1 then only get owned SCOs
 * April 14,2004:sochen: change code to support ACL Container
 */
PROCEDURE storeComponentRows(elemID IN NUMBER,isCascade IN NUMBER,
  isCurrent IN NUMBER) IS
  sql_stmt VARCHAR2(2000);
  uoidStr VARCHAR2(400);
  BEGIN
    -- Avoid push_pred hint in this block -- optimizer does better without it.
    -- Bug 5640660: Performance: use FirstClassObject view, not CMPElement view, when joining on FCO
    debug('Snapshot.storeComponentRows: ','Casade = ' || TO_CHAR(isCascade));
    if (isFirstClassObject(elemID,isCurrent)) then
      /*
       * FCO snapshots
       */
      debug('Snapshot.storeComponentRows: ','component is FCO');
      if (isCascade = CASCADE_SNAPSHOT) then
        sql_stmt:= 'insert into temptable' ||
          '(select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || getCMPWBProject(isCurrent) ||
          ' where elementid = ' || elemID ||
          ' union all' ||
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || 'CMPACLContainer_v '||
          ' where elementid = ' || elemID ||
          ' union all' ||
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || 'CMPWBMIVDefinition_v '||
          ' where elementid = ' || elemID ||
          ' union all' ||
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || 'CMPWBUser_v '||
          ' where elementid = ' || elemID ||
          ' union all' ||
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || 'CMPUserConfigTemplateSet_v '||
          ' where elementid = ' || elemID ||
          ' union all' ||
          ' select' ||
          ' sco.workspaceid,sco.name,sco.logicalname,nvl(sco.strongtypename,sco.classname) classname,sco.uoid,' ||
          ' sco.elementid,' || elemID || ',sco.metadatasignature, cmp.uoid,' ||
          SCO_TYPE || ',sco.completed from' ||
          ' (select ' ||
          ' workspaceid, name, logicalname, classname, strongtypename, uoid, elementid,' ||
          ' metadatasignature, completed, firstclassobject from ' ||
          getSecondClassObject(isCurrent) ||
          ' where firstclassobject in ' ||
          '(select elementid from ' ||
          getFirstClassObject(isCurrent) || ' start with' ||
          ' elementid = ' || elemID ||
          ' connect by prior elementid = owningfolder)) sco, ' ||
          getFirstClassObject(isCurrent) || ' cmp' ||
          ' where cmp.elementid = sco.firstclassobject' ||
          ' union all' ||
          ' select' ||
          ' fcos.workspaceid, fcos.name, fcos.logicalname, fcos.classname, fcos.uoid,' ||
          ' fcos.elementid, ' || elemID || ', fcos.metadatasignature, cmp.uoid,' ||
          FCO_TYPE || ',fcos.completed from' ||
          '(select workspaceid,name,logicalname,nvl(strongtypename,classname) classname,uoid,' ||
          ' elementid,metadatasignature,completed,owningFolder from ' ||
          getFirstClassObject(isCurrent) ||
          ' start with elementid=' || elemID || ' connect by prior' ||
          ' elementid = owningfolder) fcos,' ||
          getFirstClassObject(isCurrent) || ' cmp' ||
          ' where fcos.owningFolder = cmp.elementid and cmp.classname != ''CMPWorkspace'')';
      else
        sql_stmt:= 'insert into temptable' ||
          '(select ' ||
          ' sco.workspaceid,sco.name,sco.logicalname,nvl(sco.strongtypename,sco.classname) classname,sco.uoid,' ||
          ' sco.elementid,' || elemID ||',sco.metadatasignature, cmp.uoid,' ||
          SCO_TYPE || ',sco.completed from ' || getSecondClassObject(isCurrent) || ' sco, ' ||
          getFirstClassObject(isCurrent) || ' cmp ' ||
          ' where sco.firstclassobject = ' || elemID ||
          ' and cmp.elementid = sco.firstclassobject' ||
          ' union all' ||
          ' select ' ||
          ' fcos.workspaceid,fcos.name, fcos.logicalname, nvl(fcos.strongtypename,fcos.classname) classname, fcos.uoid,' ||
          ' fcos.elementid,' || elemID || ',fcos.metadatasignature,cmp.uoid,' ||
          FCO_TYPE || ',fcos.completed from ' || getFirstClassObject(isCurrent) || ' fcos,' ||
          getFirstClassObject(isCurrent) || ' cmp' ||
          ' where fcos.elementid = ' || elemID ||
          ' and fcos.owningfolder = cmp.elementid and cmp.classname != ''CMPWorkspace''' ||
          ' union all' ||
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || 'CMPACLContainer_v '||
          ' where elementid = ' || elemID ||
          ' union all' ||
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || 'CMPWBMIVDefinition_v '||
          ' where elementid = ' || elemID ||
          ' union all' ||
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || 'CMPWBUser_v '||
          ' where elementid = ' || elemID ||
          ' union all' ||
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || 'CMPUserConfigTemplateSet_v '||
          ' where elementid = ' || elemID ||
          ' union all' ||
         /*
          * this is for project level snapshots
          */
          ' select workspaceid,name,logicalname,classname,uoid,elementid,' ||
          elemID || ' ,metadatasignature,''repository'',' ||
          FCO_TYPE || ',completed from ' || getCMPWBProject(isCurrent) ||
          ' where elementid = ' || elemID || ')';
      end if;
    else
      /*
       * This will handle non-FCO snapshots.
       * compuoid of the SCO is the root of this sco
       */
      debug('Snapshot.storeComponentRows: ','component is SCO');
      select uoid into uoidStr from cmpelement_v where elementid = elemID;
      sql_stmt:= 'insert into temptable' ||
        '(select ' ||
        'cmp.workspaceid,cmp.name,cmp.logicalname,' ||
        'nvl(cmp.strongtypename,cmp.classname) classname,cmp.uoid,cmp.elementid,' ||
        elemID || ',cmp.metadatasignature,' || '''' || uoidStr || ''',' ||
        SCO_TYPE || ',cmp.completed from ' || getSecondClassObject(isCurrent) || ' cmp, ' ||
        '(select childid from pctree start with childid = ' || elemID ||
        ' connect by prior childid = parentid) p' ||
        ' where p.childid = cmp.elementid)';
    end if;
    --debug('Snapshot.storeComponentRows: sql_stmt = ',sql_stmt);
    EXECUTE IMMEDIATE sql_stmt;
    if (isDebug()) then
      debug('Snapshot.storeComponentRows: ','TempTable contents');
      for c in (select * from temptable) loop
        debug(' elementid = ' || c.elementid ||
          ' name = ' || c.name || ' classname = ' || c.classname || 
          ' uoid = ' || c.uoid || ' rootid = ' || c.rootid, '');
      end loop;
    end if;
  EXCEPTION when NO_DATA_FOUND then
    debug('Snapshot.storeComponentRows: ','no data found for elementid = ' || TO_CHAR(elemID));
  END storeComponentRows;

/**
 * This method will be used by both alter and create snapshots
 *   in order to find redundant components in mcmcomponent
 */
PROCEDURE findRedundantComponents IS
  BEGIN
    for c in (select elementid from mcmcomponent where
      cascade = 0 and type = CREATE_SNAPSHOT) loop
      insert into tempfco(elementid,notm)
      select b.elementid,c.elementid from
      (
        select elementid from firstclassobject_v start with
          elementid = c.elementid connect by prior elementid = owningfolder
      ) a,
      (
        select elementid from mcmcomponent where elementid != c.elementid
      ) b where a.elementid = b.elementid;
    end loop;
  END findRedundantComponents;

PROCEDURE computeDeps(iteration IN NUMBER) IS
  i BINARY_INTEGER;
  NEW NUMBER:= 0;
  OLD NUMBER:= 1;
  CHILDREN NUMBER:= 2;
BEGIN
      if (iteration = 1) then
        insert into mcmcomponent(elementid,uoid,cascade,type)
          select distinct f.elementid,f.uoid,0,CHILDREN from firstclassobject_v f,
          mcmcomponent m where m.type = NEW and m.cascade = CASCADE_SNAPSHOT
          start with owningfolder = m.elementid
          connect by prior f.elementid = f.owningfolder;
      end if;
      insert into tempfco(elementid)
      (
        -- When using non-updatable, non-mergable views like SCO, global hints
        -- help performance [gggraham]
        -- sco to sco association
        select  /*+ ordered
                    index(src.CMPSCOClasses    idx_SCOFCOClass    )
                    index(src.CMPSCOCfgClasses idx_SCOCfgFCOClass )
                    index(src.CMPSCOMapClasses idx_SCOMapFCOClass )
                    index(src.CMPSCOPrpClasses idx_SCOPrpFCOClass )
                    index(i AllInterLink_PK)
                    index(tgt.CMPSCOClasses    idx_SCOElementid   )
                    index(tgt.CMPSCOCfgClasses idx_SCOCfgElementid)
                    index(tgt.CMPSCOMapClasses idx_SCOMapElementid)
                    index(tgt.CMPSCOPrpClasses idx_SCOPrpElementid)
                */
                distinct tgt.firstclassobject
          from  mcmcomponent m, secondclassobject_v src,
                interlink i, secondclassobject_v tgt
          where (m.type = NEW or m.type = CHILDREN) and
                src.firstclassobject = m.elementid and
                i.linkfrom = src.elementid and tgt.elementid = i.linkto
        union
        -- fco to fco association
        select  /*+ ordered
                    index(i AllInterLink_PK)
                */
                distinct tgt.elementid
          from  mcmcomponent m, firstclassobject_v src,
                interlink i, firstclassobject_v tgt
          where (m.type = NEW or m.type = CHILDREN) and
                src.elementid = m.elementid and
                i.linkfrom = src.elementid and tgt.elementid = i.linkto
        union
        -- fco to sco association
        select  /*+ ordered
                    index(src.CMPSCOClasses    idx_SCOFCOClass    )
                    index(src.CMPSCOCfgClasses idx_SCOCfgFCOClass )
                    index(src.CMPSCOMapClasses idx_SCOMapFCOClass )
                    index(src.CMPSCOPrpClasses idx_SCOPrpFCOClass )
                    index(i AllInterLink_PK)
                */
                distinct tgt.elementid
          from  mcmcomponent m, secondclassobject_v src,
                interlink i, firstclassobject_v tgt
          where (m.type = NEW or m.type = CHILDREN) and
                src.firstclassobject = m.elementid and
                i.linkfrom = src.elementid and tgt.elementid = i.linkto
        union
        -- sco to fco association
        select  /*+ ordered
                    index(i AllInterLink_PK)
                    index(tgt.CMPSCOClasses    idx_SCOElementid   )
                    index(tgt.CMPSCOCfgClasses idx_SCOCfgElementid)
                    index(tgt.CMPSCOMapClasses idx_SCOMapElementid)
                    index(tgt.CMPSCOPrpClasses idx_SCOPrpElementid)
                */
                distinct tgt.firstclassobject
          from  mcmcomponent m, firstclassobject_v src,
                interlink i, secondclassobject_v tgt
          where (m.type = NEW or m.type = CHILDREN) and
                src.elementid = m.elementid and
                i.linkfrom = src.elementid and tgt.elementid = i.linkto
      );
      delete from mcmcomponent where type = CHILDREN;
      update mcmcomponent
        set type = OLD;
      insert into mcmcomponent(elementid,type)
      select elementid,NEW from tempfco t where not exists
      (
        select 1 from mcmcomponent m where t.elementid = m.elementid
      );
      delete from mcmcomponent where elementid in (select elementid from CMPPlatform_V);
END computeDeps;

PROCEDURE cleanupDeps IS
BEGIN
    findRedundantComponents();
    delete from mcmcomponent m where exists
    (
      select 1 from tempfco t where t.elementid = m.elementid
    );
    update mcmcomponent set type = 0;

END cleanupDeps;

PROCEDURE findDependeeComponents(depth IN NUMBER) IS
  numElems NUMBER;
  BEGIN
    for i in 1..depth loop
      computeDeps(i);
      select count(1) into numElems from tempfco;
      delete from tempfco;
      exit when numElems = 0;
    end loop;
    cleanupDeps;
  END findDependeeComponents;

PROCEDURE findDependeeComponents IS
  i BINARY_INTEGER:= 1;
  NEW NUMBER:= 0;
  OLD NUMBER:= 1;
  numElems NUMBER;
  BEGIN
    loop
      computeDeps(i);
      select count(1) into numElems from tempfco;
      delete from tempfco;
      exit when numElems = 0;
      i:= i+1;
    end loop;
    cleanupDeps;
  END findDependeeComponents;

FUNCTION getFullyQualifiedNameForAssoc(id IN NUMBER) RETURN VARCHAR2 IS
  fqn VARCHAR2(4000):= '';
  objName cmpelement_v.name%TYPE;
  objClassName cmpelement_v.classname%TYPE;
  BEGIN
    for c in (select childid from pctree start with childid = id
      connect by prior parentid = childid) loop
      select name,classname into objName,objClassName from cmpelement_v
        where elementid = c.childid;
      if (id = c.childid) then
        fqn:= objName || '\\/' || objName || '\' || objClassName;
      else
        fqn:= fqn || '/' || objName || '\' || objClassName;
      end if;
    end loop;
    return fqn;
  END getFullyQualifiedNameForAssoc;

PROCEDURE customizeLWContextForAssocs(transient IN NUMBER, snap IN NUMBER) IS
  type numberType is table of cmpelement_v.elementid%TYPE
    index by BINARY_INTEGER;
--
-- Bug 5417117 CJS 09/07/06
--
--  type varcharType is table of cmpelement_v.name%TYPE
  type varcharType is table of cmpelement_v.description%TYPE
    index by BINARY_INTEGER;
  idTable numberType;
  nameTable varcharType;
  i NUMBER:= 0;
  BEGIN
--  Bug 6228363: GGGraham 07/14/07
--  Add "distinct associd" to avoid redundant FQN lookups.
--  Add "distinct associd" to avoid redundant LWContext updates in current snapshot.
--  Bug 6730511: GGGraham 04/08/08 
--  Add transient parameter so "compare object to snapshot" uses LWContextTemp!
    if (getLWContextTable(transient) = LWCONTEXT_TABLE) then
      for c in (select distinct associd from lwcontext where snapshotid = snap
        and assoctype != 0) loop
        idTable(i):= c.associd;
        nameTable(i):= getFullyQualifiedNameForAssoc(c.associd);
        i:= i+1;
      end loop;
      if (idTable.COUNT > 0) then
-- Add "and snapshotid = snap" to avoid incorrect LWContext updates in other snapshots.
        forall indx in idTable.FIRST..idTable.LAST
          update /*+index(lwcontext, Idx_LWContext_AssocId)*/ lwcontext
            set assoclogname = nameTable(indx)
          where associd = idTable(indx) and snapshotid = snap;
      end if;
    else
      for c in (select distinct associd from lwcontexttemp where snapshotid = snap
        and assoctype != 0) loop
        idTable(i):= c.associd;
        nameTable(i):= getFullyQualifiedNameForAssoc(c.associd);
        i:= i+1;
      end loop;
      if (idTable.COUNT > 0) then
-- Add "and snapshotid = snap" to avoid incorrect LWContext updates in other snapshots.
        forall indx in idTable.FIRST..idTable.LAST
          update lwcontexttemp set assoclogname = nameTable(indx)
          where associd = idTable(indx) and snapshotid = snap;
      end if;
    end if;
  END customizeLWContextForAssocs;

PROCEDURE customizeLWContext(transient IN NUMBER,
  current IN NUMBER, snap IN NUMBER) IS
  sql_stmt VARCHAR2(2000);
  BEGIN
    sql_stmt:= 'update ' || getLWContextTable(transient) ||
      ' lw set physicalname = (select b.name from interlink a,' ||
      getCMPElement(current) || ' b where a.linkfrom = lw.elementid' ||
      ' and a.linkto = b.elementid and a.linkfromrole = ''ReferencingPrfTable''' ||
      ' and a.linktorole = ''ReferencedRelation'') ' ||
      ' where lw.snapshotid = ' || snap ||
      ' and lw.classname = ''CMPProfileTable''';
    EXECUTE IMMEDIATE sql_stmt;

sql_stmt:= 'update ' || getLWContextTable(transient) ||
      ' lw set physicalname = (select cmp.signature from ' ||
      getCMPFunction(current) || ' cmp where lw.elementid = cmp.elementid)' ||
      ' where lw.snapshotid = ' || snap ||
      ' and lw.classname like ''oracle.wh.repos.impl.functional.CMP%''';
    EXECUTE IMMEDIATE sql_stmt;

sql_stmt:= 'update ' || getLWContextTable(transient) ||
      ' lw set (physicalname,logicalname) = (select cmp.name,cmp.logicalname from ' ||
      getCMPElement(current) || ' cmp ,' ||
      getCMPReferencePropertyValue(current) || ' ref ' ||
      ' where ref.elementid = lw.elementid and ref.referencedelement = cmp.elementid ' ||
      ' and lw.classname = ''CMPReferencePropertyValue'') where lw.snapshotid = ' || snap ||
      ' and lw.classname = ''CMPReferencePropertyValue''';
    EXECUTE IMMEDIATE sql_stmt;

sql_stmt:= 'update ' || getLWContextTable(transient) ||
      ' lw set (physicalname,logicalname) = (select cmp.name,cmp.logicalname from ' ||
      getCMPElement(current) || ' cmp ,' ||
      getCMPPhysicalObject(current) || ' ref ' ||
      ' where ref.elementid = lw.elementid and ref.logicalobject = cmp.elementid ' ||
      ' and lw.classname in (''CMPPhysicalObject'',''CMPPhysicalMap''))' ||
      ' where lw.snapshotid = ' || snap ||
      ' and lw.classname in (''CMPPhysicalObject'',''CMPPhysicalMap'')';

EXECUTE IMMEDIATE sql_stmt;

-- following sql_stmt updates logical name col of
    -- lwcontext with logicalname || '\\' || fully qualified name physical
    -- name of association. for e.g. if association of object is to column
    -- C of table T, then then assoclogname will
    -- C\\C/T/WH/PROJECT ; C- column name, T-table name, WH - module,

-- clean temp table, hitherto temptable should have served its purpose
    -- in creating snapshot.
    customizeLWContextForAssocs(transient, snap);
    cleanTempTable();
  END customizeLWContext;

/**
 * Take a light-weight snapshot. Can be large, so scalability is an issue.
 * Bug 2930382: Replace use_nl by use_hash in some queries.  It scales better. But...
 * In Paris, restructure to use_nl with split-up CMPAllClasses. Rely on optimizer to scale.
 * Bug 3412246: Make sure SQL using the 9i syntax "with ... as" always contains the equiv-
 * alent pre-9i syntax in an "exception when others" block.  This is so an OWB design time
 * repository can still be hosted on 8i. However, the 9i syntax will perform better.
 * For Tokyo, replace "exception when others" block design for "with ... as"
 * with an "if/then/else" construct.  The condition is dependent on the DB version.
 * @param elemID elemID of the component to be taken a snapshot of
 * @param snap snapshot ID of the snapshot
 * @param matchingStrategy NAMEBASED, UOIDBASED or POSITIONBASED
 * @param transient 0 if persistent snapshot
 *                  1 if transient snapshot
 */
PROCEDURE takeLWSnapshot(elemID IN NUMBER,snap IN NUMBER,transient IN NUMBER,
  current IN NUMBER, matchingStrategy IN NUMBER default 0,
  defaultStrategy IN NUMBER default 0, customize IN NUMBER default 0) IS
  sql_stmt VARCHAR2(4000);
  idcolumn NUMBER;
  subQueryFactoringOK BOOLEAN:= FALSE;

BEGIN
    debug('Snapshot.takeLWSnapshot: ','');
    subQueryFactoringOK := isSubQueryFactoringOK();

BEGIN
     if (subQueryFactoringOK) then
      debug('Snapshot.takeLWSnapshot: ','inserting pctree rows');
      sql_stmt := 'insert into ' || getLWContextTable(transient) ||
        ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
        '  physicalName, logicalName, classname, metadatasignature, ' ||
        '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
        '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
        ' with tp as ' ||
        '  (select p.parentid, ' ||
        '   t.compuoid, t.type, t.elementid, t.rootid, t.uoid, p.childrole, t.name, ' ||
        '   t.logicalName, t.classname, t.metadatasignature, t.objtype, p.parentrole' ||
        '   from temptable t, ' || getPCTree(current) || ' p ' ||
        '   where p.childid(+) = t.elementid) ' ||
      getCMPElementTpUnion(current,snap,PARENT_ASSOC);
      EXECUTE IMMEDIATE sql_stmt;
     else
      sql_stmt := 'insert into ' || getLWContextTable(transient) ||
      ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
      '  physicalName, logicalName, classname, metadatasignature, ' ||
      '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
      '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
      getCMPElementTxUnion(current,snap,PARENT_ASSOC);
      EXECUTE IMMEDIATE sql_stmt;
     end if;
    EXCEPTION when OTHERS then
      debug('Snapshot.takeLWSnapshot: ','failed during insert of pctree rows.');
    END;

debug('Snapshot.takeLWSnapshot: ','inserting project pctree rows');
    sql_stmt := 'insert into ' || getLWContextTable(transient) ||
      ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
      '  physicalName, logicalName, classname, metadatasignature, ' ||
      '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
      '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
      ' select ' || snap || ',
             t.compuoid, t.type, t.elementid, t.rootid, t.uoid, ''projects'' , t.name,
             t.logicalName, t.classname, t.metadatasignature, t.objtype,
             null, null, ''repos'' , null,
             null, null, ' || PARENT_ASSOC || ', t.uoid, null
      from temptable t
      where t.classname = ''CMPWBProject''
            or t.classname = ''CMPWBUser''
            or t.classname = ''CMPUserConfigTemplateSet''
            or t.classname = ''CMPWBMIVDefinition''
            or t.classname = ''CMPACLContainer''';
    EXECUTE IMMEDIATE sql_stmt;

debug('Snapshot.takeLWSnapshot: ','inserting interlink rows');

BEGIN
     if (subQueryFactoringOK) then
      sql_stmt :=
      ' insert into ' || getLWContextTable(transient) ||
      ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
      '  physicalName, logicalName, classname, metadatasignature, ' ||
      '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
      '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
      ' with lc as ' ||
      '   (select' ||
      '           t.compuoid, t.type, t.elementid, t.rootid, t.uoid,' ||
      '           t.name, t.logicalName, t.classname, t.metadatasignature, t.objtype,' ||
      '           inter.linkfromrole, inter.linktorole, inter.linkto' ||
      '    from temptable t, ' || getInterLink(current) || ' inter' ||
      '    where inter.linkfrom = t.elementid)' ||
      getCMPElementLcUnion(current, snap, INTER_ASSOC) ||
      ' union all' ||
      ' select' ||
      '   ' || snap || ', lc.compuoid, lc.type, lc.elementid, lc.rootid, '||
      '   lc.uoid, lc.linkfromrole, lc.name, lc.logicalName, lc.classname,'||
      '   lc.metadatasignature, lc.objtype, null, null,' ||
      '   lc.linktorole, null, null, null, ' ||
      '   ' || INTER_ASSOC || ', lc.uoid, null' ||
      ' from   lc'||
      ' where  not exists (select 1 from ' || getCMPElement(current) || ' cmp2' ||
      '                    where cmp2.elementid = lc.linkto)';
      EXECUTE IMMEDIATE sql_stmt;
     else
      sql_stmt :=
      ' insert into ' || getLWContextTable(transient) ||
      ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
      '  physicalName, logicalName, classname, metadatasignature, ' ||
      '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
      '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
      ' select'||
      '   ' || snap || ', lc.compuoid, lc.type, lc.elementid, lc.rootid,' ||
      '   lc.uoid, lc.linkfromrole, lc.name, lc.logicalName, lc.classname,' ||
      '   lc.metadatasignature, lc.objtype, cmp1.elementid, cmp1.uoid,' ||
      '   lc.linktorole, cmp1.name, cmp1.logicalname, cmp1.classname,'||
      '   ' || INTER_ASSOC || ', lc.uoid, cmp1.uoid'||
      '   from (select' ||
      '           t.compuoid, t.type, t.elementid, t.rootid, t.uoid,' ||
      '           t.name, t.logicalName, t.classname, t.metadatasignature, t.objtype,' ||
      '           inter.linkfromrole, inter.linktorole, inter.linkto' ||
      '    from temptable t, ' || getInterLink(current) || ' inter' ||
      '    where inter.linkfrom = t.elementid) ' ||
      ' lc,' || getCMPElement(current) ||' cmp1' ||
      ' where  cmp1.elementid = lc.linkto';
      EXECUTE IMMEDIATE sql_stmt;
      sql_stmt:=
      ' insert into ' || getLWContextTable(transient) ||
      ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
      '  physicalName, logicalName, classname, metadatasignature, ' ||
      '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
      '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
      ' select' ||
      '   ' || snap || ', lc.compuoid, lc.type, lc.elementid, lc.rootid, '||
      '   lc.uoid, lc.linkfromrole, lc.name, lc.logicalName, lc.classname,'||
      '   lc.metadatasignature, lc.objtype, null, null,' ||
      '   lc.linktorole, null, null, null, ' ||
      '   ' || INTER_ASSOC || ', lc.uoid, null' ||
      '   from (select' ||
      '           t.compuoid, t.type, t.elementid, t.rootid, t.uoid,' ||
      '           t.name, t.logicalName, t.classname, t.metadatasignature, t.objtype,' ||
      '           inter.linkfromrole, inter.linktorole, inter.linkto' ||
      '    from temptable t, ' || getInterLink(current) || ' inter' ||
      '    where inter.linkfrom = t.elementid) lc' ||
      ' where  not exists (select 1 from ' || getCMPElement(current) || ' cmp2' ||
      '                    where lc.linkto = cmp2.elementid)';
      EXECUTE IMMEDIATE sql_stmt;
     end if;
    EXCEPTION when OTHERS then
      debug('Snapshot.takeLWSnapshot: ','failed during insert of interlink rows.');
    END;

debug('Snapshot.takeLWSnapshot: ','inserting intralink rows');
    BEGIN
     if (subQueryFactoringOK) then
      sql_stmt :=
      ' insert into ' || getLWContextTable(transient) ||
      ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
      '  physicalName, logicalName, classname, metadatasignature, ' ||
      '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
      '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
      ' with lc as '||
      '   (select' ||
      '           t.compuoid, t.type, t.elementid, t.rootid, t.uoid,' ||
      '           t.name, t.logicalName, t.classname, t.metadatasignature, t.objtype,' ||
      '           intra.linkfromrole, intra.linktorole, intra.linkto' ||
      '    from   temptable t, ' || getIntraLink(current) || ' intra' ||
      '    where  intra.linkfrom = t.elementid)' ||
      getCMPElementLcUnion(current, snap, INTRA_ASSOC) ||
      ' union all' ||
      ' select' ||
      '   ' || snap || ', lc.compuoid, lc.type, lc.elementid, lc.rootid, ' ||
      '   lc.uoid, lc.linkfromrole, lc.name, lc.logicalName, lc.classname,' ||
      '   lc.metadatasignature, lc.objtype, null, null,' ||
      '   lc.linktorole, null, null, null, ' ||
      '   ' || INTRA_ASSOC || ', lc.uoid, null' ||
      ' from   lc' ||
      ' where  not exists (select 1 from ' || getCMPElement(current) || ' cmp2' ||
      '                    where cmp2.elementid = lc.linkto)';
      EXECUTE IMMEDIATE sql_stmt;
     else
      sql_stmt :=
      ' insert into ' || getLWContextTable(transient) ||
      ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
      '  physicalName, logicalName, classname, metadatasignature, ' ||
      '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
      '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
      ' select' ||
      '   ' || snap || ', lc.compuoid, lc.type, lc.elementid, lc.rootid,' ||
      '   lc.uoid, lc.linkfromrole, lc.name, lc.logicalName, lc.classname,' ||
      '   lc.metadatasignature, lc.objtype, cmp1.elementid, cmp1.uoid,' ||
      '   lc.linktorole, cmp1.name, cmp1.logicalname, cmp1.classname,' ||
      '   ' || INTRA_ASSOC || ', lc.uoid, cmp1.uoid' ||
      ' from (select' ||
      '           t.compuoid, t.type, t.elementid, t.rootid, t.uoid,' ||
      '           t.name, t.logicalName, t.classname, t.metadatasignature, t.objtype,' ||
      '           intra.linkfromrole, intra.linktorole, intra.linkto' ||
      '    from   temptable t, ' || getIntraLink(current) || ' intra' ||
      '    where  intra.linkfrom = t.elementid)' ||
      ' lc, ' || getCMPElement(current) || ' cmp1' ||
      ' where  lc.linkto = cmp1.elementid';
      EXECUTE IMMEDIATE sql_stmt;
      sql_stmt:=
      ' insert into ' || getLWContextTable(transient) ||
      ' (snapshotID, compuoid, type, elementid, rootid, uoid, role, ' ||
      '  physicalName, logicalName, classname, metadatasignature, ' ||
      '  objType, associd, assocUOID, assocRole, assocPhysName, ' ||
      '  assocLogName, assocClassName, assocType, diffCol, assocCol) ' ||
      ' select' ||
      '   ' || snap || ', lc.compuoid, lc.type, lc.elementid, lc.rootid, ' ||
      '   lc.uoid, lc.linkfromrole, lc.name, lc.logicalName, lc.classname,' ||
      '   lc.metadatasignature, lc.objtype, null, null,' ||
      '   lc.linktorole, null, null, null, ' ||
      '   ' || INTER_ASSOC || ', lc.uoid, null' ||
      ' from ' ||
      '   (select' ||
      '           t.compuoid, t.type, t.elementid, t.rootid, t.uoid,' ||
      '           t.name, t.logicalName, t.classname, t.metadatasignature, t.objtype,' ||
      '           intra.linkfromrole, intra.linktorole, intra.linkto' ||
      '    from   temptable t, ' || getIntraLink(current) || ' intra' ||
      '    where  intra.linkfrom = t.elementid) lc' ||
      ' where  not exists (select 1 from ' || getCMPElement(current) || ' cmp2' ||
      '                    where lc.linkto = cmp2.elementid)';
      EXECUTE IMMEDIATE sql_stmt;
     end if;
    EXCEPTION when OTHERS then
      debug('Snapshot.takeLWSnapshot: ','failed during insert of intralink rows.');
    END;

debug('Snapshot.takeLWSnapshot: ','customizing physical properties');
    if (customize = IMMED_CUSTOMIZE) then
      customizeLWContext(transient,current, snap);
    end if;
    cleanTempFCO();

END takeLWSnapshot;

FUNCTION getFullyQualifiedName(elemID IN NUMBER) RETURN VARCHAR2 IS
--
-- Bug 5352671 CJS 09/08/06
--
--  str VARCHAR2(1000):= '';
  str VARCHAR2(4000):= '';
  i NUMBER:= 0;
  objName VARCHAR2(4000);
  BEGIN
    for c in (select name,elementid,level from firstclassobject_v
      start with elementid = elemID connect by prior
      owningfolder = elementid order by level desc) loop
      if (isFunction(c.elementid)) then
        select signature into objName from cmpfunction_v where
          elementid = c.elementid;
      elsif (isAdminProject(c.elementid)) then
        objName:= 'Public Transformations';
      elsif (isOracleLibrary(c.elementid)) then
        objName:= 'Pre-Defined';
      else
        objName:= c.name;
      end if;
      if (i=0) then
        str:= ''; --Tokyo: Skip workspace (the top level)
      elsif (i=1) then
          str:= str || objName;
      else
        str:= str || '/' || objName;
      end if;
      i:= i+1;
    end loop;
    return str;
  END getFullyQualifiedName;

FUNCTION getWholeName(elemID IN NUMBER) RETURN VARCHAR2 IS
  str VARCHAR2(1000):= '';
  i NUMBER:= 0;
  objName VARCHAR2(4000);
  BEGIN
    for c in (select v.name name , v.elementid elementid, a.l
        from
        (
          select childid, level l from pctree
          start with childid = elemID connect by prior
          parentid = childid order by level desc
        ) a,
        cmpelement_v v
          where a.childid = v.elementid
          order by v.elementid asc
        )
    loop
      if (isFunction(c.elementid)) then
        select signature into objName from cmpfunction_v where
          elementid = c.elementid;
      else
        objName:= c.name;
      end if;
      if (i=0) then
        str:= ''; --Tokyo: Skip workspace (the top level)
      elsif (i=1) then
        str:= str || objName;
      else
        str:= str || '/' || objName;
      end if;
      i:= i+1;
    end loop;
    return str;
  END getWholeName;

/**
 * This procedure inserts the snapshot information into the snapshot
 * store table. This assumes that checking if the snapshot already
 * exists is done from the client.
 * @param elemID element id of the root object taking a snapshot of
 * @param labelStr the snapshot name
 * @param description the description of this snapshot
 */
FUNCTION insertSnapshotStore(elemID IN NUMBER,snap IN NUMBER,
  description IN VARCHAR2,isHeavy IN NUMBER,isCascade IN NUMBER) RETURN NUMBER IS
  isCascadeFlag snapshotstoretable.iscascade%TYPE;
  type recordType IS RECORD (
    name cmpelement_v.name%TYPE,
    logicalname cmpelement_v.logicalname%TYPE,
    uoid cmpelement_v.uoid%TYPE,
    classname cmpelement_v.classname%TYPE
  );
  /**
   * get all fco-only element ids of objects
   * that belong to the component being taken
   */
  vRecord recordType;
  isRoot NUMBER(1);
--
-- Bug 5352671 CJS 09/08/06
--
--  fullName VARCHAR2(1000);
  fullName VARCHAR2(4000);
  BEGIN
    debug('Snapshot.insertSnapshotStore: ','');
    for c in (select fco.elementid from firstclassobject_v fco,
      temptable t where fco.elementid = t.elementid) loop
      select name,logicalname,uoid,classname into vRecord
        from cmpelement_v where elementid = c.elementid;
      if (c.elementid = elemID) then
        if (isCascade = 0) then
          isCascadeFlag:= 0;
        else
          isCascadeFlag:= 1;
        end if;
        isRoot:= 0;
      else
        isRoot:= 1;
      end if;
      fullName:= getFullyQualifiedName(c.elementid);
      BEGIN
        insert into snapshotstoretable values (
          snap, fullName, vRecord.logicalname, vRecord.classname,
          vRecord.uoid, sysdate, description, isHeavy, isCascadeFlag,
          isRoot, currentUser, currentUser, sysdate);
      EXCEPTION when DUP_VAL_ON_INDEX then
        SnapshotError.handleGeneralSnapshotError(vRecord.uoid,snap,
          SnapshotError.SNAPSHOT_ALRDY_HAS_OBJECT);
        return SnapshotError.SNAPSHOT_ALRDY_HAS_OBJECT;
      END;
    end loop;
    return 0;
  END insertSnapshotStore;

PROCEDURE deleteNonSemanticProperties IS
  BEGIN
    delete from temptable where logicalname = 'OWBINTERNAL.APPEARANCE';
    delete from temptable where logicalname like '%GenerationResult%';
    delete from temptable where classname = 'CMPGenerationResult';
  END deleteNonSemanticProperties;
/**
 * This creates both the heavy and light snapshots for regular snapshots
 * @param elemID element id of the object to take a snapshot of
 * @param snap snapshot id
 * @param objectType type if it's a PROJECT, MODULE or ENTITY
 * @param transient 0 if persistent snapshot
 *                  1 if transient snapshot
 */
PROCEDURE createSnapshot(elemID IN NUMBER,snap IN NUMBER,
  isHeavy IN NUMBER,isCurrent IN NUMBER,customize IN NUMBER default 0) IS
  BEGIN
    debug('Snapshot.createSnapshot:','');
    /*
     * insert all rows of component into cmpstorage-type base tables
     */
    if (isHeavy = HEAVY_SNAPSHOT) then
      debug('Snapshot.createSnapshot:','entering heavy snapshot');
      SnapshotCreateRestore.takeSnapshot(elemId,snap);
    end if;
    deleteNonSemanticProperties();
    takeLWSnapshot(elemID,snap,PERSISTENT_SNAPSHOT,isCurrent,customize=>customize);
    debug('Snapshot.createSnapshot:','after heavy snapshot');
  END createSnapshot;

/**
 * This is the method for persistent snapshot for one object.
 * It gets called by the takePersistentSnapshots method.
 * @param elemID element id of the component
 * @param labelStr snapshot name
 * @param description user-defined description of the snapshot
 * @param isHeavy    0 for heavy snapshot
 *                   1 for light snapshot
 * @param isCascade  0 for cascade snapshot
 *                   1 for no cascade snapshot
 * @param objectType 0 for project type component
 *                   1 for module type component
 *                   2 for entity type component
 */
FUNCTION takePersistentSnapshot(elemID IN NUMBER,snap IN NUMBER,
  description IN VARCHAR2,isHeavy IN NUMBER,isCurrent IN NUMBER,
  isCascade IN NUMBER,customize IN NUMBER default 0) RETURN NUMBER IS
    uoidStr cmpelement_v.uoid%TYPE;
    retCode NUMBER;
  BEGIN
    debug('Snapshot.takePersistentSnapshot:','');
    storeComponentRows(elemID,isCascade,isCurrent);
    /*
     * insert into snapshot store table
     * the general properties of the snapshot
     */
    if (isCurrent = CURRENT_SNAPSHOT) then
      if (NOT(elemExists(elemID))) then
        return SnapshotError.REPOS_OBJECT_DOES_NOT_EXIST;
      end if;
      retCode:= insertSnapshotStore(elemID,snap,description,isHeavy,
        isCascade);
    else
      if (NOT(elemExists(elemID,TRUE,snap))) then
        debug('Snapshot.takePersistentSnapshot: elemExists = false for elemID = ',
          elemID);
        return SnapshotError.VERSION_OBJECT_DOES_NOT_EXIST;
      end if;
      retCode:= SnapshotImport.insertSnapshotStore(elemID,snap,description,
        isHeavy,isCascade);
      debug('Snapshot.takePersistentSnapshot: retCode = ',retCode);
    end if;
    if (retCode > 0) then
      return retCode;
    end if;
    createSnapshot(elemID,snap,isHeavy,isCurrent,customize);
    cleanTempTable();
    return 0;
  END takePersistentSnapshot;

/**
 * This allows the user to take snapshots on multiple objects.
 * The client must first insert into the TEMPFCO table the following info:
 *
 *   element id in elementid column of table COMPONENT
 *   cascade/no cascade in cascade column of COMPONENT
 *
 * @param snap snapshot ID
 * @param description description of the snapshot
 * @isHeavy 0 if heavy, 1 if light snapshot
 *          (in this case, the whole snapshot is light or heavy)
 */
FUNCTION takePersistentSnapshots(snap IN NUMBER,description IN VARCHAR2,
  isHeavy IN NUMBER,isCurrent IN NUMBER) RETURN NUMBER IS
  retCode NUMBER;
  hasWarnings BOOLEAN:= FALSE;

BEGIN
    debug('Snapshot.takePersistentSnapshots: isCurrent=',isCurrent);
    findRedundantComponents();

for c in (select elementid from tempfco) loop
      SnapshotError.handleGeneralSnapshotError(c.elementid,0,
        SnapshotError.REDUNDANT_COMPONENT);
      delete from mcmcomponent where elementid = c.elementid;
      hasWarnings:= TRUE;
    end loop;

--Bug 7046881: Optimization: Defer customizeLWContext until snapshot has all components
    for c in (select elementid,cascade from mcmcomponent) loop
      debug('Snapshot.takePersistentSnapshots: looping','');
      retCode:= takePersistentSnapshot(c.elementid,snap,description,isHeavy,
        isCurrent,c.cascade,DEFER_CUSTOMIZE);
      if (retCode > 0) then
        SnapshotError.handleGeneralSnapshotError(c.elementid,snap,
          retCode);
        return retCode;
      end if;
    end loop;
    customizeLWContext(PERSISTENT_SNAPSHOT,isCurrent,snap);
    cleanComponent();
    if (hasWarnings) then
      SnapshotError.handleGeneralSnapshotError(0,snap,
        SnapshotError.HAS_WARNINGS);
      return SnapshotError.HAS_WARNINGS;
    end if;
    return 0;
  END takePersistentSnapshots;

/**
 * This function does error checking to make sure that the snapshot
 * still does not exist for the root object in the snapshot store
 * @param labelStr label of the snapshot
 * @return vCount number of instances of this object and snapshot
 *         if it is 0 then error will be thrown from the client
 */
FUNCTION isInSnapshotLookup(labelStr IN VARCHAR2)
  RETURN BOOLEAN IS
  vCount NUMBER(9) := 0;
  snap NUMBER(9) := 0;
  BEGIN
    select snapshotID into snap from snapshotLookup
      where snapshotName = labelStr;
    return TRUE;
    EXCEPTION when NO_DATA_FOUND then
      return FALSE;
  END isInSnapshotLookup;

/**
 * Method called when doing a restore from snapshot manager
 * @param uoidStr the uoid of the aggregate parent of the object
 *        of the snapshot
 * @return returns TRUE if the parent exists in the current repository
 *                 FALSE if it doesn't
 */
FUNCTION isRestorable(uoidStr IN VARCHAR2,snap IN NUMBER) RETURN BOOLEAN IS
  parentUOIDStr cmpelement_v.uoid%TYPE;
  elemID cmpelement_v.elementid%TYPE;
  project cmpelement_v.elementid%TYPE:= 0;
  classnameStr cmpelement_v.classname%TYPE;
  BEGIN
    BEGIN
      /*
       * this assumes that the object to be restored
       *   exists in the version repository
       */
      select classname into classnameStr from cmpelement_x
        where uoid = uoidStr and snapshotid = snap;
      if (classnameStr = 'CMPWBProject') then
        return TRUE;
      end if;
      select elementid into elemID from firstclassobject_x
        where snapshotid = snap and uoid = uoidStr;
      select b.uoid into parentUOIDStr from
        firstclassobject_x a, firstclassobject_x b
        where a.elementid = elemID and
              b.elementid = a.owningfolder and
              a.snapshotid = snap and
              b.snapshotid = snap;
      select elementid into elemID from cmpelement_v where uoid = parentUOIDStr;
    EXCEPTION when NO_DATA_FOUND then
      debug('Snapshot.isRestorable: ','No parent found');
      return FALSE;
    END;
    debug('Snapshot.isRestorable: ','returning TRUE');
    return TRUE;
  END;

/**
 * Restore an object
 * @param elemID element id of the object being restored
 * @param labelStr the snapshot name
 */
FUNCTION restore(uoidStr IN VARCHAR2,restoreParents IN BOOLEAN,snap IN NUMBER)
  RETURN NUMBER IS
  x NUMBER;
  BEGIN
    if (NOT(elemExists(uoidStr,TRUE,snap))) then
      SnapshotError.handleGeneralSnapshotError(uoidStr,snap,
        SnapshotError.VERSION_OBJECT_DOES_NOT_EXIST);
      return SnapshotError.VERSION_OBJECT_DOES_NOT_EXIST;
    end if;

if (NOT(isRestorable(uoidStr,snap))) then
      if (NOT(restoreParents)) then
        SnapshotError.handleRestoreSnapshotError(uoidStr,snap,
          SnapshotError.NOT_RESTORABLE);
        return SnapshotError.NOT_RESTORABLE;
      end if;
    end if;
    return SnapshotCreateRestore.restore(uoidStr,snap,restoreParents);
  END restore;

PROCEDURE deleteSnapshotStorage(uoidStr IN VARCHAR2 default null,
  snap IN NUMBER) IS
  elemID cmpelement_v.elementid%TYPE;
  delParents BOOLEAN := TRUE;
  nComps NUMBER := 0;
  BEGIN
    select elementid into elemID from cmpelement_x
      where uoid = uoidStr and snapshotid = snap;
    -- all elementid's of component
    insert into tempfco (elementid) (
      select elementid from firstclassobject_x where snapshotid = snap
        start with elementid = elemID connect by prior elementid = owningfolder
      union all
      select elementid from secondclassobject_x where snapshotid = snap
        and firstclassobject in (select elementid from firstclassobject_v
            start with elementid = elemID connect by prior elementid = owningfolder)
    );

delete from lwcontext lw where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = lw.elementid
    );

delete from pctreestorage p where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = p.childid
    );

delete from intralinkstorage i where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = i.linkfrom
    );

delete from interlinkstorage i where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = i.linkfrom
    );

/*
     * if only one FCO is left when doing an alter of a snapshot, then
     * delete the parents as well
     */
    select count(1) into nComps from snapshotstoretable where snapshotid = snap;
    if (nComps >= 1) then
      delParents := FALSE;
    else
      delParents := TRUE;
    end if;

if (delParents) then
      delete from CMPFCOElement_x where snapshotID = snap and
        elementid in (
        select elementid from firstclassobject_x where snapshotid = snap
          start with elementid = elemID connect by prior owningfolder = elementid
      );
      delete from CMPSCOElement_x where snapshotID = snap and
        elementid in (
        select elementid from firstclassobject_x where snapshotid = snap
          start with elementid = elemID connect by prior owningfolder = elementid
      );
      delete from CMPSCOCfgElement_x where snapshotID = snap and
        elementid in (
        select elementid from firstclassobject_x where snapshotid = snap
          start with elementid = elemID connect by prior owningfolder = elementid
      );
      delete from CMPSCOMapElement_x where snapshotID = snap and
        elementid in (
        select elementid from firstclassobject_x where snapshotid = snap
          start with elementid = elemID connect by prior owningfolder = elementid
      );
      delete from CMPSCOPrpElement_x where snapshotID = snap and
        elementid in (
        select elementid from firstclassobject_x where snapshotid = snap
          start with elementid = elemID connect by prior owningfolder = elementid
      );
      delete from CMPSYSElement_x where snapshotID = snap and
        elementid in (
        select elementid from firstclassobject_x where snapshotid = snap
          start with elementid = elemID connect by prior owningfolder = elementid
      );
    end if;
    delete from CMPfcoelement_x cmp where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = cmp.elementid
    );
    delete from CMPSCOElement_x cmp where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = cmp.elementid
    );
    delete from CMPSCOCfgElement_x cmp where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = cmp.elementid
    );
    delete from CMPSCOMapElement_x cmp where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = cmp.elementid
    );
    delete from CMPSCOPrpElement_x cmp where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = cmp.elementid
    );
    delete from CMPSYSElement_x cmp where snapshotid = snap and exists
    (
      select 1 from tempfco t where t.elementid = cmp.elementid
    );

delete from tempfco;
  END deleteSnapshotStorage;

PROCEDURE deleteNSStorage(uoidStr IN VARCHAR2 default null,snap IN NUMBER) IS
  elemID cmpelement_v.elementid%TYPE;
  BEGIN
    select elementid into elemID from cmpelement_x
      where uoid = uoidStr and snapshotid = snap;

delete from fcofoldernamespacestorage where elementid = elemID
      and snapshotid = snap;

delete from fcofoldernamespacestorage where snapshotID = snap
      and
      (
        elementid in
        (
          select elementid from firstclassobject_x where snapshotid = snap
            start with elementid = elemID connect by prior elementid = owningfolder
	  union
	  select elementid from secondclassobject_x where snapshotid = snap
	    and firstclassobject in
	  (
              select elementid from firstclassobject_x where snapshotid = snap
              start with elementid = elemID connect by prior elementid = owningfolder
	  )
        )
      );
  END deleteNSStorage;

PROCEDURE deleteSnapshotMgmtStorage(uoidStr IN VARCHAR2 default null,
  snap IN NUMBER) IS
  elemID cmpelement_v.elementid%TYPE;
  BEGIN
    select elementid into elemID from cmpelement_x
      where uoid = uoidStr and snapshotid = snap;
    delete from snapshotstoretable where snapshotID = snap
      and uoid in (
      select distinct uoid from firstclassobject_x where snapshotid = snap
        start with elementid = elemID connect by prior elementid = owningfolder
    );
  END deleteSnapshotMgmtStorage;

PROCEDURE deleteCompareStorage(snap IN NUMBER) IS
  BEGIN
    delete from comparestore comp where exists (
      select 1 from
      (
        select compareid from comparestoremanager where
        srcid = snap or tgtid = snap
      ) mgr
      where mgr.compareid = comp.compareid
    );
    delete from comparestoremanager where srcid = snap or tgtid = snap;
  END deleteCompareStorage;

/**
 *  Delete the snapshot from the all the tables that contain the snapshot
 *    information.
 *  @param uoidStr uoid of the object(and all its children) to be deleted
 *                 if the uoid is null, then delete all the objects that
 *                 belong to this snapshot
 *  @param label snapshot name
 *  @return 0 successful
 *          1 NO_DATA_FOUND exception (fatal error)
 *          2 the object being deleted is not a root FCO
 *            (functionally not allowed)
 */
FUNCTION deleteSnapshot(uoidStr IN VARCHAR2 default null,snap IN NUMBER)
  RETURN NUMBER IS
  isRootFlag NUMBER(1);
  retCode NUMBER;
  BEGIN
    if (uoidStr is null) then
      delete /*+index(cmp, idx_FCOSnapid_ver)*/ from cmpfcostorage cmp where snapshotID = snap;
    --delete /*+index(cmp, idx_MMMSnapid_ver)*/ from cmpmmmstorage cmp where snapshotID = snap;
      delete /*+index(cmp, idx_SCOSnapid_ver)*/ from cmpscostorage cmp where snapshotID = snap;
      delete /*+index(cmp, idx_SCOCfgSnapid_ver)*/ from cmpscocfgstorage cmp where snapshotID = snap;
      delete /*+index(cmp, idx_SCOMapSnapid_ver)*/ from cmpscomapstorage cmp where snapshotID = snap;
      delete /*+index(cmp, idx_SCOPrpSnapid_ver)*/ from cmpscoprpstorage cmp where snapshotID = snap;
      delete /*+index(cmp, idx_SYSSnapid_ver)*/ from cmpsysstorage cmp where snapshotID = snap;
      delete from snapshotlookup where snapshotID = snap;
      delete from lwcontext where snapshotID = snap;
      delete from snapshotstoretable where snapshotID = snap;
      delete from fcofoldernamespacestorage where snapshotID = snap;
      delete from pctreestorage where snapshotID = snap;
      delete from intralinkstorage where snapshotID = snap;
      delete from interlinkstorage where snapshotID = snap;
      return 0;
    else
      if (NOT(elemExists(uoidStr,TRUE,snap))) then
        return SnapshotError.VERSION_OBJECT_DOES_NOT_EXIST;
      end if;
      debug('deleteSnapshot Found elem', uoidStr);
      -- dont change this order. deletion of parent relies on mgmt storage cleaned first
      deleteSnapshotMgmtStorage(uoidStr,snap);
      deleteNSStorage(uoidStr,snap);
      deleteSnapshotStorage(uoidStr,snap);
      return 0;
    end if;
    deleteCompareStorage(snap);
  END deleteSnapshot;

/**
 * Convert a snapshot from heavy to light
 * @param labelStr snapshot name
 * @return 0 if successful
 *         1 if not successful
 */
FUNCTION shrinkSnapshot(snap IN NUMBER) RETURN NUMBER IS
  isHeavyFlag NUMBER(1);
  BEGIN
    select distinct isHeavy into isHeavyFlag from snapshotstoretable
      where snapshotID = snap;
    if (isHeavyFlag = Snapshot.LIGHT_SNAPSHOT) then
      SnapshotError.handleGeneralSnapshotError(0,snap,
        SnapshotError.NOT_HEAVY_SNAPSHOT);
      return SnapshotError.NOT_HEAVY_SNAPSHOT;
    end if;
    delete /*+index(cmp, idx_FCOSnapid_ver)*/ from cmpfcostorage cmp where snapshotID = snap;
    --delete /*+index(cmp, idx_MMMSnapid_ver)*/ from cmpmmmstorage cmp where snapshotID = snap;
    delete /*+index(cmp, idx_SCOSnapid_ver)*/ from cmpscostorage cmp where snapshotID = snap;
    delete /*+index(cmp, idx_SCOCfgSnapid_ver)*/ from cmpscocfgstorage cmp where snapshotID = snap;
    delete /*+index(cmp, idx_SCOMapSnapid_ver)*/ from cmpscomapstorage cmp where snapshotID = snap;
    delete /*+index(cmp, idx_SCOPrpSnapid_ver)*/ from cmpscoprpstorage cmp where snapshotID = snap;
    delete /*+index(cmp, idx_SYSSnapid_ver)*/ from cmpsysstorage cmp where snapshotID = snap;
    update snapshotstoretable set isHeavy = 1 where snapshotID = snap;
    return 0;
  END shrinkSnapshot;

/**
 * Alter a snapshot
 * @param labelStr snapshot name
 * @return   0 if successful
 *         > 0 if any operational errors happen
 */
FUNCTION alterSnapshot(snap IN NUMBER,descStr IN VARCHAR2) RETURN NUMBER IS
  isHeavyFlag snapshotstoretable.isheavy%TYPE;
  descriptionStr snapshotstoretable.description%TYPE;
  retCode NUMBER;
  hasWarnings BOOLEAN:= FALSE;
  nComps NUMBER;
  BEGIN
  	debug('Snapshot.alterSnapshot: description = ',descStr);
    if (NOT(snapshotExists(snap))) then
      SnapshotError.handleFatalSnapshotError(0,snap,
        SnapshotError.SNAPSHOT_NOT_IN_SNAPTABLE);
      return SnapshotError.SNAPSHOT_NOT_IN_SNAPTABLE;
    end if;

select distinct isheavy into isHeavyFlag from snapshotstoretable
      where snapshotid = snap;
    select distinct description into descriptionStr from snapshotstoretable
      where snapshotid = snap;
    if (NOT(descStr = null)) then
      update snapshotstoretable set description = descStr where
      snapshotid = snap;
      descriptionStr:= descStr;
    end if;
    findRedundantComponents();

for c in (select elementid from tempfco) loop
      debug('Snapshot.alterSnapshot: elementid = ',c.elementid);
      SnapshotError.handleGeneralSnapshotError(c.elementid,0,
        SnapshotError.REDUNDANT_COMPONENT);
      delete from mcmcomponent where elementid = c.elementid;
      hasWarnings:= TRUE;
    end loop;

deleteCompareStorage(snap);

for c in (select elementid,uoid,cascade,type
      from mcmcomponent) loop
      debug('Snapshot.alterSnapshot: c.elementid = ',c.elementid);
      debug('Snapshot.alterSnapshot: c.cascade = ',c.cascade);
      if (c.type = CREATE_SNAPSHOT) then
        retCode:= takePersistentSnapshot(c.elementid,snap,descriptionStr,
          isHeavyFlag,CURRENT_SNAPSHOT,c.cascade);
        if (NOT(isModelConsistent(snap) = CONSISTENT)) then
          debug('Snapshot.alterSnapshot: ','inconsistent model');
          retCode:= SnapshotError.INCONSISTENT_MODEL;
        end if;
        if (retCode > 0) then
          SnapshotError.handleGeneralSnapshotError(c.elementid,snap,retCode);
          SnapshotError.handleAlterSnapshotError(CREATE_SNAPSHOT);
          return retCode;
        end if;
      elsif (c.type = MODIFY_SNAPSHOT) then
        retCode:= deleteSnapshot(c.uoid,snap);
        if (NOT(isModelConsistent(snap) = CONSISTENT)) then
          retCode:= SnapshotError.INCONSISTENT_MODEL;
        end if;
        if (retCode > 0) then
          SnapshotError.handleGeneralSnapshotError(c.uoid,snap,retCode);
          SnapshotError.handleAlterSnapshotError(MODIFY_SNAPSHOT);
          return retCode;
        end if;
        retCode:= takePersistentSnapshot(c.elementid,snap,descriptionStr,
          isHeavyFlag,CURRENT_SNAPSHOT,c.cascade);
        if (retCode > 0) then
          SnapshotError.handleGeneralSnapshotError(c.elementid,snap,retCode);
          SnapshotError.handleAlterSnapshotError(MODIFY_SNAPSHOT);
          return retCode;
        end if;
      elsif (c.type = DELETE_SNAPSHOT) then
        retCode:= deleteSnapshot(c.uoid,snap);
        if (retCode > 0) then
          SnapshotError.handleGeneralSnapshotError(c.uoid,snap,retCode);
          SnapshotError.handleAlterSnapshotError(DELETE_SNAPSHOT);
          return retCode;
        end if;
      end if;
    end loop;
    /*
     * if none of the snapshots exist in the snapshotstoretable
     * anymore, then this means the snapshot name and id can be reused
     * and must be deleted from the snapshotlookup table
     */
    select count(1) into nComps from snapshotstoretable
      where snapshotid = snap;
    if (nComps = 0) then
      delete from snapshotlookup where snapshotid = snap;
    end if;

if (hasWarnings) then
      SnapshotError.handleGeneralSnapshotError(0,snap,
        SnapshotError.HAS_WARNINGS);
      return SnapshotError.HAS_WARNINGS;
    end if;
    return 0;
  END alterSnapshot;

FUNCTION restoreSnapshot(snap IN NUMBER) RETURN NUMBER IS
  retCode NUMBER;
  resParents BOOLEAN;
  BEGIN
    /*
     * Bug 8282412: Must process logical objects first, otherwise a just restored
     * physical object may be immediately deleted during the restore processing of its
     * associated logical object. Caused by Tahoe changing physical objects from SCO to FCO.
     */
    for c in (select m.elementid,m.uoid,m.cascade,m.type,
                     decode(f.classname,'CMPPhysicalMap',1,'CMPPhysicalObject',1,0) porder
              from mcmcomponent m, firstclassobject_x f
              where f.snapshotid = snap and f.uoid = m.uoid
              order by porder) loop
      debug('Snapshot.restoreSnapshot: elem / uoid / cascade / type=',
             c.elementid || ' / ' || c.uoid || ' / ' || c.cascade || ' / ' || c.type);
      if (c.type = RESTOREPARENTS) then
        resParents:= TRUE;
      else
        resParents:= FALSE;
      end if;
      retCode:= restore(c.uoid,resParents,snap);
      if (retCode > 0) then
        return retCode;
      end if;
    end loop;
    return 0;
  END restoreSnapshot;

FUNCTION isAlreadyCompared(sID IN NUMBER, tID IN NUMBER,
  uoidStr IN VARCHAR2, transient IN NUMBER) RETURN NUMBER IS
  comparisonID NUMBER(9):=0;
  sql_stmt VARCHAR2(200);
  BEGIN
    sql_stmt := 'select compareID from ' || getCompareStoreMgrTable(transient)
      || ' where srcID = :sID and tgtID = :tID and compUOID = :uoidStr';
    EXECUTE IMMEDIATE sql_stmt INTO comparisonID USING sID, tID, uoidStr;
    return comparisonID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      return 0;
  END isAlreadyCompared;

FUNCTION generateCompareID(sID IN NUMBER, tID IN NUMBER,
                uoidStr IN VARCHAR2, transient IN NUMBER) return NUMBER IS
  comparisonID NUMBER(9) := 0;
  sql_stmt VARCHAR2(1000);
  BEGIN
    sql_stmt := 'insert into ' || getCompareStoreMgrTable(transient) ||
      ' values (:uoidStr, :sID, :tID, ' || getCompareSeq(transient) ||
      '.nextval)';
    EXECUTE IMMEDIATE sql_stmt USING uoidStr, sID, tID;
    sql_stmt := 'select compareID from ' || getCompareStoreMgrTable(transient)
      || ' where srcID = :sID and tgtID = :tID and CompUOID = :uoidStr';
    EXECUTE IMMEDIATE sql_stmt INTO comparisonID USING sID, tID, uoidStr;
    return comparisonID;
  END generateCompareID;

PROCEDURE initComponentLabel(snapID IN NUMBER, transient IN NUMBER, tableName IN VARCHAR2) IS
  sql_stmt VARCHAR2(1000);
  BEGIN
    sql_stmt :=
    'insert into ' || tableName ||
    '(
      select ' || COMPARE_LABEL_ID || ', diffCol, assocCol, elementid, uoid, role, physicalName,
             logicalName, className, metadatasignature, objType,
             associd, assocUOID, assocRole, assocPhysName, assocLogName, assocClassname, assocType
      from ' || getLWContextTable(transient) || ' where snapshotID = :snapID
    )';
    EXECUTE IMMEDIATE sql_stmt USING snapID;
  END initComponentLabel;
/**
 * Note that the with clause is used in this method. This assumes that when this method
 * is invoked, the table is a permanent table and not temporary.
 */
PROCEDURE initComponent(snapID IN NUMBER, vCompUOID IN VARCHAR2,
  comparisonID IN NUMBER, transient IN NUMBER, tableName IN VARCHAR2) IS
  sql_stmt VARCHAR2(3000);
  subQueryFactoringOK BOOLEAN:= FALSE;
  BEGIN
    subQueryFactoringOK := isSubQueryFactoringOK();
    BEGIN
     if (subQueryFactoringOK) then
      sql_stmt:=
       'insert into ' || tableName ||
       '  with lc as (' ||
       'select diffCol, assocCol, elementid, uoid, compuoid, role, physicalName,' ||
       '  logicalName, className, metadatasignature, objType,' ||
       '  associd, assocUOID, assocRole, assocPhysName, assocLogName, ' ||
       '  assocClassname, assocType,type from ' || getLWContextTable(transient) ||
       '  where snapshotID = :snapID)' ||
       'select 0,lc.diffCol, lc.assocCol, lc.elementid, lc.uoid, lc.role, lc.physicalName,' ||
       '  lc.logicalName, lc.className, lc.metadatasignature, lc.objType,' ||
       '  lc.associd, lc.assocUOID, lc.assocRole, lc.assocPhysName, lc.assocLogName,' ||
       '  lc.assocClassname, lc.assocType' ||
       '  from lc where  lc.compuoid in ' ||
       '( select a.uoid from' ||
       '( select uoid,compuoid from lc where type=0 ) a' ||
       '  start with a.uoid = :vCompUOID connect by prior a.uoid = a.compuoid)' ||
       ' union all ' ||
       'select 0, lc.diffCol, lc.assocCol, lc.elementid, lc.uoid, lc.role, lc.physicalName,' ||
       '  lc.logicalName, lc.className, lc.metadatasignature, lc.objType,' ||
       '  lc.associd, lc.assocUOID, lc.assocRole, lc.assocPhysName, lc.assocLogName,' ||
       '  lc.assocClassname, lc.assocType from lc' ||
       '  where lc.uoid = :vCompUOID';
      EXECUTE IMMEDIATE sql_stmt USING snapID, vCompUOID, vCompUOID;
     else
      sql_stmt :=
      'insert into ' || tableName ||
      '(
      select ' || comparisonID || ', diffCol, assocCol, elementid, uoid, role, physicalName,
             logicalName, className, metadatasignature, objType,
             associd, assocUOID, assocRole, assocPhysName, assocLogName, assocClassname, assocType
      from (select * from ' || getLWContextTable(transient) || ' where snapshotID = :snapID)
      where  compuoid in
      (
          select uoid from ' || getLWCompTreeView(transient) ||
          ' start with uoid = :vCompUOID
          connect by prior uoid = compuoid and SnapshotID = :snapID
      )
      union all
      select ' || comparisonID || ', diffCol, assocCol, elementid, uoid, role, physicalName,
             logicalName, className, metadatasignature, objType,
             associd, assocUOID, assocRole, assocPhysName, assocLogName, assocClassname, assocType
      from ' || getLWContextTable(transient) ||
      ' where SnapshotID = :snapID and uoid = :vCompUOID
      )';
      EXECUTE IMMEDIATE sql_stmt USING snapID, vCompUOID, snapID, snapID, vCompUOID;
     end if;
    EXCEPTION when OTHERS then
      debug('Snapshot.initComponent: failed during insert into ',tableName);
    END;

END initComponent;

PROCEDURE findDelta(comparisonID IN NUMBER,  snapID IN NUMBER, transient IN NUMBER,
     compare_state IN NUMBER, srcTable IN VARCHAR2, tgtTable IN VARCHAR2) IS
  sql_stmt VARCHAR2(4000);
  insert_stmt VARCHAR2(300);
  select_stmt VARCHAR2(300);
  from_stmt VARCHAR2(3400);
  filter_stmt VARCHAR2(1000);
  BEGIN
    insert_stmt :=
    'INSERT INTO ' || getCompareStoreTable(transient) ||
       ' (CompareID, diffcol, assocCol, uoid, role, physicalName, logicalName,
       className, CompareDML, objType, assocUOID, assocRole, assocPhysName,
       assocLogName, assocClassName, assocState, assocType, otherUOID ) (' ;
    select_stmt := ' select ' || comparisonID || ' , B.diffCol, B.assocCol,
       B.uoid, B.role, B.physicalName, B.logicalName, B.className, '
       || compare_state || ' , B.objType, B.assocUOID, B.assocRole,
       B.assocPhysName, B.assocLogName, B.assocClassName, B.assocState, B.assocType, B.otherUOID ' ;
    from_stmt := ' from
        ( select t1.diffCol, t1.assocCol, t2.uoid uoid1, t1.uoid, t1.role,
                 t1.physicalName, t1.logicalName, t1.className, t1.objType,
                 t1.assocUOID, t1.assocRole, t1.assocPhysName, t1.assocLogName,
                 t1.assocClassName, t1.assocState, t1.assocType, t1.otherUOID '
          || ' from
          ( select diffCol, assocCol, uoid,
               decode(role,null, ' || '''null''' || ',role) role,
               physicalName, logicalName, className, objType, assocUOID,
               decode(assocRole,null,' || '''null''' || ',assocRole) assocRole,
               assocPhysName, assocLogName, assocClassName, ' || SAME || ' assocState,
               assocType, uoid otherUOID
            from ' || srcTable || ' ) t1,
          ( select diffCol, assocCol, uoid,
               decode(role,null, ' || '''null''' || ',role) role,
               decode(assocRole,null,' || '''null''' ||',assocRole) assocRole,
               assocType
            from ' || tgtTable ||') t2
            where t1.diffCol = t2.diffCol(+) and
               t1.role = t2.role(+) and
               t1.assocRole = t2.assocRole(+) and
               t1.assocType = t2.assocType(+)
     ) B ';
     filter_stmt := ' where B.uoid1 is null ) ';
    sql_stmt := insert_stmt || select_stmt || from_stmt || filter_stmt;
    debug('Snapshot.findDelta: sql_stmt = ',sql_stmt);
    EXECUTE IMMEDIATE sql_stmt;
  END findDelta;

PROCEDURE findUpdate(comparisonID IN NUMBER, tgtID IN NUMBER, transient IN NUMBER, filter IN NUMBER default 0) IS
  sql_stmt VARCHAR2(5000);
  insert_stmt VARCHAR2(500);
  select_stmt VARCHAR2(500);
  from_stmt VARCHAR2(3000);
  filter_stmt VARCHAR2(1000);
  BEGIN
    -- Bug 7116440: Force association change in TypeDefinition to appear as property change also.
    insert_stmt := 'INSERT INTO ' || getCompareStoreTable(transient) ||
        '(compareID, diffcol, assocCol, uoid, role, physicalName, logicalName, className,
        compareDML, signstate, objType, assocUOID, assocRole, assocPhysName, assocLogName,
        assocClassName, assocState, assocType, otheruoid ) (' ;
    select_stmt := ' select ' || comparisonID || ',' || ' A.diffCol, A.assocCol ,
      A.elUOID, A.role, A.physicalName, A.logicalName, A.className, '
      || ' CASE WHEN A.signState = ' || DIFFERENT || ' THEN ' || COMPARE_UPDATE_STATE
      || ' WHEN A.assocState = ' || DIFFERENT || ' THEN ' || COMPARE_UPDATE_STATE
      || ' ELSE ' || COMPARE_UNKNOWN_STATE || ' END, ' 
      || ' CASE WHEN A.assocRole  = ' || '''TypeDefinition''' 
      || '       AND A.assocState = ' || DIFFERENT || ' THEN ' || COMPARE_UPDATE_STATE
      || ' ELSE A.signState END ' || 
      ', A.objType, A.assocUOID,
      A.assocRole, A.assocPhysName, A.assocLogName,
      A.assocClassName, A.assocState, A.assocType, A.otheruoid ' ;
    from_stmt :=
      ' from
        ( select
            t1.diffCol, t1.assocCol, t1.uoid elUOID, t1.role, t1.physicalName,
            t1.logicalName, t1.className,
            decode (t2.metadatasignature,t1.metadatasignature, ' || SAME || ' , ' || DIFFERENT || ' ) signstate,
            t1.objType, t1.assocUOID, t1.assocRole, t1.assocPhysName, t1.assocLogName ,
            t1.assocClassName,
            decode (t2.assocCol,t1.assocCol,' || SAME || ' , ' || DIFFERENT || ' ) assocState,
            t1.assocType, decode (t1.assocType, 0, t2.uoid, t2.assocUOID) otherUOID
          from (select diffCol,
                  decode(assocCol, null, ' || '''null''' || ' , assocCol) assocCol,
                  uoid,
                  decode(role,null,' || '''null''' || ',role) role,
                  physicalName, logicalName, className,
                  metadatasignature, objType, assocUOID,
                  decode(assocRole,null,' || '''null''' || ',assocRole) assocRole,
                  assocPhysName, assocLogName, assocClassName, assocType
                from TargetComponent) t1,
               (select diffCol,
                       decode(assocCol, null, ' || '''null''' || ', assocCol) assocCol,
                       uoid, assocUOID,
                       decode(role,null,' || '''null''' || ',role) role,
                       metadatasignature,
                       decode(assocRole,null,' || '''null''' || ',assocRole) assocRole,
                       assocType
                from SourceComponent) t2
          where t1.diffCol is not null and
                t2.diffCol is not null and
                t1.diffCol = t2.diffCol and
                t1.role = t2.role and
                t1.assocRole = t2.assocRole and
                t1.assocType = t2.assocType
      ) A ';
      -- it seems otherUOID is needed in mxs even if objects are equal
      if (filter = 1) then
        filter_stmt := ' ) ';
      else  -- by default only put different objects in comparestore.
        filter_stmt := ' where A.elUOID is not null and
          (
            A.signstate = ' || DIFFERENT || ' or
            A.assocState = ' || DIFFERENT || ') ) ';
      end if;

sql_stmt := insert_stmt || select_stmt || from_stmt || filter_stmt;
    EXECUTE IMMEDIATE sql_stmt;
  END findUpdate;

/**
 * This function does not store compare results
 */
FUNCTION compareLabel(vSrcSnapshotID IN NUMBER, vTgtSnapshotID IN NUMBER,
    transient IN NUMBER default 0, filter IN NUMBER default 0) RETURN NUMBER IS
  BEGIN
    cleanSourceTargetComponent();
    initComponentLabel(vSrcSnapshotID, transient, 'SourceComponent');
    initComponentLabel(vTgtSnapshotID, transient, 'TargetComponent');
    if (isDebug()) then
      debug('Snapshot.compareLabel: ','SourceComponent');
      for c in (select * from sourcecomponent) loop
        debug('name = ' || c.physicalname || ' elementid = ' || c.elementid ||
          ' associd = ' || c.associd || ' role = ' || c.role || ' assocrole = ' || c.assocrole ||
          ' uoid = ' || c.uoid || ' assocuoid = ' || c.assocuoid ||
          ' diffcol = ' || c.diffcol || ' assoccol = ' || c.assoccol ||
          ' assocPhysName = ' || c.assocphysname || ' assocType = ' || c.assoctype,'');
      end loop;
      debug('Snapshot.compareLabel: ','TargetComponent');
      for c in (select * from targetcomponent) loop
        debug('name = ' || c.physicalname || ' elementid = ' || c.elementid ||
          ' associd = ' || c.associd || ' role = ' || c.role || ' assocrole = ' || c.assocrole ||
          ' uoid = ' || c.uoid || ' assocuoid = ' || c.assocuoid ||
          ' diffcol = ' || c.diffcol || ' assoccol = ' || c.assoccol ||
          ' assocPhysName = ' || c.assocphysname || ' assocType = ' || c.assoctype,'');
      end loop;
    end if;

-- store compare result in temp table only , whe comparing labels;
    findDelta(COMPARE_LABEL_ID, vSrcSnapshotID, TRANSIENT_SNAPSHOT, COMPARE_DELETE_STATE,
      'SourceComponent', 'TargetComponent');
    findDelta(COMPARE_LABEL_ID, vTgtSnapshotID, TRANSIENT_SNAPSHOT, COMPARE_INSERT_STATE,
      'TargetComponent', 'SourceComponent');
    findUpdate(COMPARE_LABEL_ID, vTgtSnapshotID, TRANSIENT_SNAPSHOT, filter);

if (isDebug()) then
      debug('Snapshot.compareLabel: ','Diff Results');
      for c in (select diffcol,assoccol,physicalname,assocphysname,role,
           assocrole,comparedml,signstate,assocstate from comparestoretemp) loop
        debug('diffcol = ' || c.diffcol || ' assoccol = ' || c.assoccol
           || ' physicalname = ' || c.physicalname || ' assocphysname = '
           || c.assocphysname || ' role = ' || c.role || ' assocrole = '
           || c.assocrole || ' comparedml = ' || c.comparedml
           || ' signstate = ' || c.signstate || ' assocstate = ',c.assocstate);
      end loop;
    end if;
    return SUCCESS;
  END compareLabel;

/**
 * This function does not store compare results
 */
FUNCTION compareLabel(srcLabel IN VARCHAR2, targetLabel IN VARCHAR2,
    transient IN NUMBER default 0, filter IN NUMBER default 0) RETURN NUMBER IS
  vSrcSnapshotID  lwcontext.SnapshotID%TYPE;
  vTgtSnapshotID lwcontext.SnapshotID%TYPE;
  comparisonID NUMBER(9);
  sql_stmt VARCHAR2(1000);

BEGIN
  --ServerLog.open('e:\Work\Mcm','diff.txt');
  cleanSourceTargetComponent();
  sql_stmt := 'select snapshotID from ' || getSnapshotLookupTable(transient) ||
    ' where SnapshotName = :snaplabel';
  execute immediate sql_stmt INTO vSrcSnapshotID USING srcLabel;
  execute immediate sql_stmt INTO vTgtSnapshotID USING targetLabel;
  return compareLabel(vSrcSnapshotID,vTgtSnapshotID,transient, filter);
END compareLabel;

/**
 * Compare and store results into CompareStore
 */
FUNCTION compare(compareID out NUMBER, srcLabel IN VARCHAR2, targetLabel IN VARCHAR2,
  elID IN NUMBER, transient IN NUMBER default 0) RETURN NUMBER IS
  vCompUOID lwcontext.compuoid%TYPE;
  BEGIN
    select uoid into vCompUOID from cmpelement_v where elementid = elID;
    return compare(compareID,srcLabel,targetLabel,vCompUOID,transient);
  END compare;
/**
 * Compare and store results into CompareStore
 */
FUNCTION compare(compareID out NUMBER, srcLabel IN VARCHAR2, targetLabel IN VARCHAR2,
  vCompUOID IN VARCHAR2, transient IN NUMBER default 0) RETURN NUMBER IS
  vSrcSnapshotID lwcontext.SnapshotID%TYPE;
  vTargetSnapshotID lwcontext.SnapshotID%TYPE;
  comparisonID NUMBER(9) := 1;
  sql_stmt VARCHAR2(1000);
  elID NUMBER;
  BEGIN

cleanSourceTargetComponent;

select elementid into elID from cmpelement_v where uoid = vCompUOID;
    sql_stmt := 'select snapshotID from ' || getSnapshotLookupTable(transient) ||
                 ' where SnapshotName = :snaplabel';
    execute immediate sql_stmt INTO vSrcSnapshotID USING srcLabel;
    execute immediate sql_stmt INTO vTargetSnapshotID USING targetLabel;
    comparisonID := isAlreadyCompared(vSrcSnapshotID, vTargetSnapshotID, vCompUOID, transient);
    if ( comparisonID > 0) then
      initComponent(vSrcSnapshotID, vCompUOID, comparisonID, transient, 'SourceComponent');
      initComponent(vTargetSnapshotID, vCompUOID, comparisonID, transient, 'TargetComponent');
      return comparisonID;
    end if;
    comparisonID:= generateCompareID(vSrcSnapshotID, vTargetSnapshotID, vCompUOID, transient);
    initComponent(vSrcSnapshotID, vCompUOID, comparisonID, transient, 'SourceComponent');
    initComponent(vTargetSnapshotID, vCompUOID, comparisonID, transient, 'TargetComponent');
    findDelta(comparisonID, vSrcSnapshotID, transient, COMPARE_DELETE_STATE,
      'SourceComponent', 'TargetComponent');
    findDelta(comparisonID, vTargetSnapshotID, transient, COMPARE_INSERT_STATE,
      'TargetComponent', 'SourceComponent');
    findUpdate(comparisonID, vTargetSnapshotID, transient);
    compareID := comparisonID;
    return compareID;
  END compare;

/**
 * Compare current with snapshot
 */
FUNCTION compareCurrentWithSnap(label IN VARCHAR2, vCompUOID IN VARCHAR2,
    target IN NUMBER default 0) RETURN NUMBER IS
  vSrcSnapID lwcontext.SnapshotID%TYPE;
  vTgtSnapID lwcontext.SnapshotID%TYPE;
  tempSnapID lwcontext.SnapshotID%TYPE;
  comparisonID NUMBER(9) := 0;
  sql_stmt VARCHAR2(1000);
  elID cmpelement_v.elementid%TYPE;
  elemID1 cmpelement_v.elementid%TYPE;
  snapName1 snapshotlookup.snapshotname%TYPE;
  snap cmpelement_v.elementid%TYPE;
  BEGIN
    --ServerLog.open('e:\Work\Mcm','log.txt');
    cleanSourceTargetComponent();
    -- take light weight snapshot of component
    select cwmseq.nextval into elemID1 from dual;
    select elementid into elID from cmpelement_v where uoid = vCompUOID;
    snapName1:= TO_CHAR(elemID1);
    storeComponentRows(elID,CASCADE_SNAPSHOT,CURRENT_SNAPSHOT);
    deleteNonSemanticProperties();
    snap:= getSnapshotID(snapName1,TRANSIENT_SNAPSHOT,'',Snapshot.LIGHT_SNAPSHOT);
    takeLWSnapshot(elID,snap,TRANSIENT_SNAPSHOT,CURRENT_SNAPSHOT);
    cleanTempTable();

-- get src and tgt snapshot id
    sql_stmt:= 'select snapshotID from ' || getSnapshotLookupTable(PERSISTENT_SNAPSHOT)
                 || ' where SnapshotName = :snaplabel';
    execute immediate sql_stmt INTO vTgtSnapID USING label;
    vSrcSnapID := snap;

-- check src and tgt
    if (target = CURRENT_MINUS_SNAPSHOT) then
      tempSnapID := vSrcSnapID;
      vSrcSnapID := vTgtSnapID;
      vTgtSnapID := tempSnapID;
      debug('Snapshot.compareCurrentWithSnap: source id ',vSrcSnapID);
      debug('Snapshot.compareCurrentWithSnap: target id ',vTgtSnapID);
      initComponentLabel(vTgtSnapID, Snapshot.TRANSIENT_SNAPSHOT, 'TargetComponent');
      initComponent(vSrcSnapID, vCompUOID, comparisonID, Snapshot.PERSISTENT_SNAPSHOT, 'SourceComponent');
    else
      initComponentLabel(vSrcSnapID, Snapshot.TRANSIENT_SNAPSHOT, 'SourceComponent');
      initComponent(vTgtSnapID, vCompUOID, comparisonID, Snapshot.PERSISTENT_SNAPSHOT, 'TargetComponent');
    end if;

findDelta(comparisonID, vSrcSnapID, Snapshot.TRANSIENT_SNAPSHOT, COMPARE_DELETE_STATE,
      'SourceComponent', 'TargetComponent');
    findDelta(comparisonID, vTgtSnapID, Snapshot.TRANSIENT_SNAPSHOT, COMPARE_INSERT_STATE,
      'TargetComponent', 'SourceComponent');
    findUpdate(comparisonID, vTgtSnapID, Snapshot.TRANSIENT_SNAPSHOT);
    return comparisonID;
  END compareCurrentWithSnap;

FUNCTION isModelConsistent(snap IN NUMBER) RETURN NUMBER IS
  eqCount NUMBER;
  snapCount NUMBER;
  BEGIN
    select count(1) into eqCount from
    (
      select physicalname name,metadatasignature from lwcontext where
      snapshotid = snap and classname = 'ClassDefinition'
    ) src,
    (
      select name,metadatasignature from classdefinition_v
    ) tgt
    where src.name = tgt.name and src.metadatasignature = tgt.metadatasignature;
    select count(1) into snapCount from lwcontext where snapshotid = snap and
      classname = 'ClassDefinition';
    if (snapCount = eqCount) then
      return CONSISTENT;
    end if;
    return NOT_CONSISTENT;
  END isModelConsistent;

PROCEDURE init(owbuser IN VARCHAR2,operType IN NUMBER) IS
  BEGIN
    currentUser:= owbuser;
    cleanMCMErrors();

if (currentUser is null) then
      --select name into currentUser from cmpuser_v;
	select sys_context('userenv', 'current_schema') into currentUser from dual;
    end if;
    --ServerLog.open('e:\work\mcm','log.txt');
    --if (NOT(ServerLog.isOpen())) then
    --  ServerLog.open('d:\temp','Snapshot.log');
    --end if;
  END init;

PROCEDURE insertMCMErrors IS
  BEGIN
    debug('Snapshot.insertMCMErrors: number of errors = ',SnapshotError.errNo);
    for i in 0..SnapshotError.errNo-1 loop
      insert into mcmerrors values (SnapshotError.mcmErrTable(i).errCode,
        SnapshotError.mcmErrTable(i).parameters);
    end loop;
    SnapshotError.errNo:= 0;
  END insertMCMErrors;

/**
 * Do MCM Operations using this public method
 * @param labelStr snapshot name
 * @param operType the operation type
 * @param transient whether it's a transient snapshot or not
 * @param description to be used by transient snapshot creation
 * @param isHeavy to be used by transient snapshot creation
 */
FUNCTION doMCMOperation(operType IN NUMBER,labelStr IN VARCHAR2,retValue OUT NUMBER,
  owbuser IN VARCHAR2 default null,descriptionStr IN VARCHAR2 default null,
  isHeavy IN NUMBER default 0,isCurrent IN NUMBER default 0) RETURN NUMBER IS

snap snapshotstoretable.snapshotid%TYPE;
  retCode NUMBER;

BEGIN
    init(owbuser,operType);

snap:= getSnapshotID(labelStr,PERSISTENT_SNAPSHOT,descriptionStr,isHeavy);

if (operType = CREATE_SNAPSHOT) then
      retCode:= takePersistentSnapshots(snap,descriptionStr,isHeavy,isCurrent);
      update snapshotstoretable set
        updatedby = currentUser,
        userName = currentUser
      where snapshotid = snap;
    elsif (operType = ALTER_SNAPSHOT) then
      retCode:= alterSnapshot(snap,descriptionStr);
      update snapshotstoretable set
        description = decode(descriptionStr,null,description,descriptionStr),
        updatedby = currentUser,
        updatetimestamp = sysdate
      where snapshotid = snap;
    elsif (operType = RESTORE_SNAPSHOT) then
      retCode:= restoreSnapshot(snap);
    elsif (operType = SHRINK_SNAPSHOT) then
      retCode:= shrinkSnapshot(snap);
    elsif (operType = DELETE_SNAPSHOT) then
      retCode:= deleteSnapshot(null,snap);
    else
      return SnapshotError.UNSUPPORTED_OPERATION;
    end if;

debug('Snapshot.doMCMOperation: retCode = ',retCode);
    insertMCMErrors();
    if (isDebug()) then
      for c in (select * from mcmerrors) loop
        debug('Snapshot.doMCMOperation: errCode = ' || c.errcode || ' parameters = ',c.parameters);
      end loop;
    end if;
    --ServerLog.close();
    return retCode;
  END doMCMOperation;

END Snapshot;
/

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de