/* * author = Ron Gonzalez */ drop procedure createValueInterpreter; drop function createWBDomain; drop function createPropertyGroupDefinition; drop function createPropertyDefinition; drop procedure createPropertyUsageDefinition; drop procedure upgradeReposForDP; CREATE PROCEDURE createValueInterpreter(owner in NUMBER, metadatasignature IN VARCHAR2,interpreter IN VARCHAR2) IS typeId NUMBER(9); BEGIN select elementid into typeId from wbscalartype_v where name = 'String'; insert into wbprimitiveproperty_v ( loadingparent, deletionparent, aggregatedsignature, classname, completed, createdby, creationtimestamp, customerdeletable, customereditable, customerrenamable, description, disabled, editable, elementid, frozen, generated, imported, logicalname, metadatasignature, name, note, notm, persistent, seeded, uoid, updatedby, updatetimestamp, version, isloadingparent, owner, datatype, position, hasname, hasposition, value ) values ( owner, 0, null, 'WBPrimitiveProperty', 0, null, sysdate, 0, 0, 0, null, 0, 0, cwmseq.nextval, 0, 0, 0, 'ValueInterpreter', metadatasignature, 'ValueInterpreter', null, 0, null, 1, sys_guid(), null, sysdate, null, 0, owner, typeId, 0, 1, 0, interpreter ); END createValueInterpreter; / CREATE FUNCTION createWBDomain(name IN VARCHAR2, type IN VARCHAR2,metadatasignature IN VARCHAR2) RETURN NUMBER IS typeId NUMBER(9); nextId NUMBER(9); BEGIN select elementid into typeId from wbscalartype_v where name = type; select cwmseq.nextval into nextId from dual; insert into wbdomain_v ( loadingparent, deletionparent, aggregatedsignature, classname, completed, createdby, creationtimestamp, customerdeletable, customereditable, customerrenamable, description, disabled, editable, elementid, frozen, generated, imported, logicalname, metadatasignature, name, note, notm, persistent, seeded, uoid, updatedby, updatetimestamp, version, isloadingparent, comparator, datatype ) values ( 0, 0, null, 'WBDomain', 0, null, sysdate, 0, 0, 0, null, 0, 0, nextId, 0, 0, 0, name, metadatasignature, name, null, 1, null, 1, sys_guid(), null, sysdate, null, 1, 'PropertyNameComparator', typeId ); return nextId; END createWBDomain; / CREATE FUNCTION createPropertyGroupDefinition(name IN VARCHAR2,definitionKey IN VARCHAR2,metadatasignature IN VARCHAR2,classDefId IN NUMBER) RETURN NUMBER IS nextId NUMBER(9); BEGIN select cwmseq.nextval into nextId from dual; insert into propertygroupdefinition_v ( loadingparent, deletionparent, aggregatedsignature, classname, completed, createdby, creationtimestamp, customerdeletable, customereditable, customerrenamable, description, disabled, editable, elementid, frozen, generated, imported, logicalname, metadatasignature, name, note, notm, persistent, seeded, uoid, updatedby, updatetimestamp, version, isloadingparent, definitionkey, hidden, groupowner ) values ( classDefId, 0, null, 'PropertyGroupDefinition', 0, null, sysdate, 0, 0, 0, null, 0, 0, nextId, 0, 0, 0, name, metadatasignature, name, null, 0, null, 1, sys_guid(), null, sysdate, null, 0, definitionKey, 0, classDefId ); return nextId; END createPropertyGroupDefinition; / CREATE FUNCTION createPropertyDefinition(name IN VARCHAR2,grpName IN VARCHAR2, type IN VARCHAR2,metadatasignature IN VARCHAR2,description IN VARCHAR2, propertyType IN NUMBER,definitionKey IN VARCHAR2, classDefId IN NUMBER) RETURN NUMBER IS typeId NUMBER(9); nextId NUMBER(9); groupId NUMBER(9); BEGIN select cwmseq.nextval into nextId from dual; BEGIN select elementid into groupId from propertygroupdefinition_v where groupowner = classDefId and name = grpName; EXCEPTION when OTHERS then groupId:= 0; END; select elementid into typeId from wbabstracttype_v where name = type; insert into propertydefinition_v ( loadingparent, deletionparent, aggregatedsignature, classname, completed, createdby, creationtimestamp, customerdeletable, customereditable, customerrenamable, description, disabled, editable, elementid, frozen, generated, imported, logicalname, metadatasignature, name, note, notm, persistent, seeded, uoid, updatedby, updatetimestamp, version, isloadingparent, propertytype, definitionkey, exportcontrol, override, position, classdefinition, datatype, isrole, roledefinition, translatable, ispassword, hidden, propertyreadonly, namechecked, userchoiceallowed, dynamicdefault, inplaceeditor, customeditor, propertygroupdefinition ) values ( classDefId, 0, null, 'PropertyDefinition', 0, null, sysdate, 0, 0, 0, null, 0, 0, nextId, 0, 0, 0, name, metadatasignature, name, null, 0, null, 1, sys_guid(), null, sysdate, null, 0, propertyType, definitionKey, 0, 0, 0, classDefId, typeId, 0, 0, 0, 0, 0, 0, 0, 0, null, null, null, groupId ); return nextId; END createPropertyDefinition; / CREATE PROCEDURE createPropertyUsageDefinition(name IN VARCHAR2, metadatasignature IN VARCHAR2,description IN VARCHAR2, definitionKey IN VARCHAR2,classDefId IN NUMBER,propertyDefId IN NUMBER, propType IN NUMBER) IS nextId NUMBER(9); propertySetId NUMBER(9); BEGIN select cwmseq.nextval into nextId from dual; BEGIN select elementid into propertySetId from propertysetdefinition_v where classdefinition = classDefId and propertytype = propType; EXCEPTION when NO_DATA_FOUND then propertySetId:= 0; END; insert into propertyusagedefinition_v ( loadingparent, deletionparent, aggregatedsignature, classname, completed, createdby, creationtimestamp, customerdeletable, customereditable, customerrenamable, description, disabled, editable, elementid, frozen, generated, imported, logicalname, metadatasignature, name, note, notm, persistent, seeded, uoid, updatedby, updatetimestamp, version, isloadingparent, propertysetdefinition, propertydefinition, definitionkey ) values ( classDefId, 0, null, 'PropertyUsageDefinition', 0, null, sysdate, 0, 0, 0, null, 0, 0, nextId, 0, 0, 0, name, metadatasignature, name, null, 0, null, 1, sys_guid(), null, sysdate, null, 0, propertySetId, propertyDefId, definitionKey ); END createPropertyUsageDefinition; / CREATE PROCEDURE upgradeReposForDP IS type numberType is table of NUMBER index by BINARY_INTEGER; prfClassDefIds numberType; prfGroupDefIds numberType; i NUMBER; domainId NUMBER(9); BEGIN select elementid into domainId from classdefinition_v where name like '%CMPProfile'; prfClassDefIds(0):= domainId; select elementid into domainId from classdefinition_v where name like '%CMPProfileTable'; prfClassDefIds(1):= domainId; select elementid into domainId from classdefinition_v where name like '%CMPProfileAttribute'; prfClassDefIds(2):= domainId; prfGroupDefIds(0):= createPropertyGroupDefinition('PATTERN_CONFIGURATION', '8i.PROFILE_ATTRIBUTES.PATTERN_CONFIGURATION','a2d5471730e7563079bb5039d4ef4bfe', prfClassDefIds(2)); prfGroupDefIds(1):= createPropertyGroupDefinition('PATTERN_CONFIGURATION', '8i.PROFILE_TABLES.PATTERN_CONFIGURATION','a2d5471730e7563079bb5039d4ef4bfe', prfClassDefIds(1)); prfGroupDefIds(2):= createPropertyGroupDefinition('PATTERN_CONFIGURATION', '8i.PROFILES.PATTERN_CONFIGURATION','a2d5471730e7563079bb5039d4ef4bfe', prfClassDefIds(0)); -- changed group of CALCULATE_PATTERNS from LOADING to the new group definition forall indx in 0..2 update propertydefinition_v set propertygroupdefinition = prfGroupDefIds(indx) where name = 'CALCULATE_PATTERNS' and classdefinition = prfClassDefIds(indx); /* -- create property definitions and property usage definitions for each of -- the new property definitions -- MAX_NUM_PATTERNS domainId:= createPropertyDefinition('MAX_NUM_PATTERNS', 'PATTERN_CONFIGURATION','Integer','3ae3785e7409c9e18e5edb32963bedac','', 1,'maxNumPatterns',prfClassDefIds(0)); createPropertyUsageDefinition('MAX_NUM_PATTERNS', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILES.PATTERN_CONFIGURATION.MAX_NUM_PATTERNS',prfClassDefIds(0), domainId,1); domainId:= createPropertyDefinition('MAX_NUM_PATTERNS', 'PATTERN_CONFIGURATION','Integer','3d2d24f00646a26a8cf6d539812e8d1b','', 1,'maxNumPatterns',prfClassDefIds(1)); createPropertyUsageDefinition('MAX_NUM_PATTERNS', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_TABLES.PATTERN_CONFIGURATION.MAX_NUM_PATTERNS', prfClassDefIds(1),domainId,1); domainId:= createPropertyDefinition('MAX_NUM_PATTERNS', 'PATTERN_CONFIGURATION','Integer','3ae3785e7409c9e18e5edb32963bedac','', 1,'maxNumPatterns',prfClassDefIds(2)); createPropertyUsageDefinition('MAX_NUM_PATTERNS', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PATTERN_CONFIGURATION.MAX_NUM_PATTERNS', prfClassDefIds(2),domainId,1); -- USE_IN_FK domainId:= createPropertyDefinition('USE_IN_FK', 'LOAD_CONFIGURATION','Boolean','ee534718f8f4865f807607e566a66b8a','', 1,'useInFk',prfClassDefIds(2)); createPropertyUsageDefinition('USE_IN_FK', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.LOAD_CONFIGURATION.USE_IN_FK',prfClassDefIds(2), domainId,1); -- PATTERN_1_COMPLIANT_QUERY domainId:= createPropertyDefinition('PATTERN_1_COMPLIANT_QUERY', 'PROFILE_RESULTS','String','ee534718f8f4865f807607e566a66b8a','', 1,'pattern1CompliantQuery',prfClassDefIds(2)); createPropertyUsageDefinition('PATTERN_1_COMPLIANT_QUERY', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PROFILE_RESULTS.PATTERN_1_COMPLIANT_QUERY', prfClassDefIds(2),domainId,1); -- PATTERN_1_NONCOMPLIANT_QUERY domainId:= createPropertyDefinition('PATTERN_1_NONCOMPLIANT_QUERY', 'PROFILE_RESULTS','String','e497aa8c4119e254d9609da3db922095','', 1,'pattern1NoncompliantQuery',prfClassDefIds(2)); createPropertyUsageDefinition('PATTERN_1_NONCOMPLIANT_QUERY', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PROFILE_RESULTS.PATTERN_1_NONCOMPLIANT_QUERY', prfClassDefIds(2),domainId,1); -- PATTERN_1_DRILLDOWN_QUERY domainId:= createPropertyDefinition('PATTERN_1_DRILLDOWN_QUERY', 'PROFILE_RESULTS','String','3975a7a6822765f59835ca90f09ef2f1','', 1,'pattern1DrilldownQuery',prfClassDefIds(2)); createPropertyUsageDefinition('PATTERN_1_DRILLDOWN_QUERY', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PROFILE_RESULTS.PATTERN_1_DRILLDOWN_QUERY', prfClassDefIds(2),domainId,1); -- PATTERN_1_LIST_QUERY domainId:= createPropertyDefinition('PATTERN_1_LIST_QUERY', 'PROFILE_RESULTS','String','03007fc8a7b34a24134f1c762d4e1ca4','', 1,'pattern1ListQuery',prfClassDefIds(2)); createPropertyUsageDefinition('PATTERN_1_LIST_QUERY', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PROFILE_RESULTS.PATTERN_1_LIST_QUERY', prfClassDefIds(2),domainId,1); -- PATTERN_2_COMPLIANT_QUERY domainId:= createPropertyDefinition('PATTERN_2_COMPLIANT_QUERY', 'PROFILE_RESULTS','String','a745855265060990a14edd15fd2363d5','', 1,'pattern2CompliantQuery',prfClassDefIds(2)); createPropertyUsageDefinition('PATTERN_2_COMPLIANT_QUERY', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PROFILE_RESULTS.PATTERN_2_COMPLIANT_QUERY', prfClassDefIds(2),domainId,1); -- PATTERN_2_NONCOMPLIANT_QUERY domainId:= createPropertyDefinition('PATTERN_2_NONCOMPLIANT_QUERY', 'PROFILE_RESULTS','String','9317e008ad38983a6c4dd2b9bb7578eb','', 1,'pattern2NoncompliantQuery',prfClassDefIds(2)); createPropertyUsageDefinition('PATTERN_2_NONCOMPLIANT_QUERY', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PROFILE_RESULTS.PATTERN_2_NONCOMPLIANT_QUERY', prfClassDefIds(2),domainId,1); -- PATTERN_2_DRILLDOWN_QUERY domainId:= createPropertyDefinition('PATTERN_2_DRILLDOWN_QUERY', 'PROFILE_RESULTS','String','39853c781a0c586286330056b7873276','', 1,'pattern2DrilldownQuery',prfClassDefIds(2)); createPropertyUsageDefinition('PATTERN_2_DRILLDOWN_QUERY', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PROFILE_RESULTS.PATTERN_2_DRILLDOWN_QUERY', prfClassDefIds(2),domainId,1); -- PATTERN_2_LIST_QUERY domainId:= createPropertyDefinition('PATTERN_2_LIST_QUERY', 'PROFILE_RESULTS','String','ee3707bd9d23ba91c573fe8c8afb493f','', 1,'pattern2ListQuery',prfClassDefIds(2)); createPropertyUsageDefinition('PATTERN_2_LIST_QUERY', '2f0ceae47cf2d9ca735708833d61cf80','', '8i.PROFILE_ATTRIBUTES.PROFILE_RESULTS.PATTERN_2_LIST_QUERY', prfClassDefIds(2),domainId,1); */ domainId:= createWBDomain('COMMON_FORMAT','Integer', '12a0be95794105c0827a546dad8b8659'); createValueInterpreter(domainId,'879a74ea655d5230c979efd4367cdf0a', 'oracle.wh.repos.impl.dataProfile.ProfilePropertyValueInterpreter'); update propertydefinition_v set datatype = domainId where name = 'COMMON_FORMAT' and classdefinition = prfClassDefIds(2); domainId:= createWBDomain('CONSENSUS_DATATYPE','Integer', 'a0d96b5ebee7cca858f45ac9906818fa'); createValueInterpreter(domainId,'879a74ea655d5230c979efd4367cdf0a', 'oracle.wh.repos.impl.dataProfile.ProfilePropertyValueInterpreter'); update propertydefinition_v set datatype = domainId where name = 'CONSENSUS_DATATYPE' and classdefinition = prfClassDefIds(2); domainId:= createWBDomain('DERIVED_PATTERN_1','String', 'a3db4b057b53b8a29d53d75945060ee8'); createValueInterpreter(domainId,'879a74ea655d5230c979efd4367cdf0a', 'oracle.wh.repos.impl.dataProfile.ProfilePropertyValueInterpreter'); update propertydefinition_v set datatype = domainId where name = 'DERIVED_PATTERN_1' and classdefinition = prfClassDefIds(2); domainId:= createWBDomain('DERIVED_PATTERN_2','String', '80a12d6e17b79a1422db26d64debf503'); createValueInterpreter(domainId,'879a74ea655d5230c979efd4367cdf0a', 'oracle.wh.repos.impl.dataProfile.ProfilePropertyValueInterpreter'); update propertydefinition_v set datatype = domainId where name = 'DERIVED_PATTERN_2' and classdefinition = prfClassDefIds(2); END upgradeReposForDP; / begin upgradeReposForDP(); end; / drop procedure createValueInterpreter; drop function createWBDomain; drop function createPropertyGroupDefinition; drop function createPropertyDefinition; drop procedure createPropertyUsageDefinition; drop procedure upgradeReposForDP;