-- Create sql in-place upgrade patch for some up.xml -- property changes. The sql will be shown (as much as -- possible) in-line with associated segment of up.xml. -- The xml will be set off from the sql by commenting each -- line with "-- up.xml ->". -- -- Bug #'s/Properties added -- -- Bug 8569523 - CANNOT SET GENERATION_MODE PROPERTY IN CONFIGURATION_TEMPLATE -- SET SERVEROUTPUT ON SET LINESIZE 500 -- ########################################################################## DECLARE -- Outer block for utility functions/variables BUGBUG: Should these be packagized (uprop_util) -- ########################################################################## SCRIPTID CONSTANT VARCHAR2(40) := 'bug 8569523'; -- For output logging EOL CONSTANT VARCHAR2(01) := CHR(10); -- new line TRACEON CONSTANT BOOLEAN := TRUE; -- Provide function level trace output OUTINITED BOOLEAN := FALSE; -- Flag indicating output has been enabled PROPERTYTYPE_CORE NUMBER:= 0; PROPERTYTYPE_LOGICAL NUMBER:= 1; PROPERTYTYPE_PHYSICAL NUMBER:= 2; PROPERTYTYPE_CONFIGURATION NUMBER:= 2; PROPERTYTYPE_USERDEFINED NUMBER:= 3; -- Record definitions for the various views -- Handy sql to make record definitions (some hand editing required (in sqlworksheet anyway) -- ========================================================================== TYPE CMPPropertyDefinition IS RECORD ( -- ========================================================================== WORKSPACEID CMPPropertyDefinition_r.WORKSPACEID%TYPE DEFAULT 1 -- NUMBER(9,0) 1 ,DELETIONPARENT CMPPropertyDefinition_r.DELETIONPARENT%TYPE DEFAULT null -- NUMBER(9,0) 2 ,AGGREGATEDSIGNATURE CMPPropertyDefinition_r.AGGREGATEDSIGNATURE%TYPE DEFAULT null --sysdate -- TIMESTAMP(6) 3 ,CLASSNAME CMPPropertyDefinition_r.CLASSNAME%TYPE DEFAULT 'CMPPropertyDefinition' -- VARCHAR2(255) 4 ,COMPLETED CMPPropertyDefinition_r.COMPLETED%TYPE DEFAULT 1 -- CHAR(1) 5 ,CREATEDBY CMPPropertyDefinition_r.CREATEDBY%TYPE DEFAULT 'OWBSYS' -- VARCHAR2(40) 6 ,CREATIONTIMESTAMP CMPPropertyDefinition_r.CREATIONTIMESTAMP%TYPE DEFAULT null --sysdate -- DATE 7 ,CUSTOMERDELETABLE CMPPropertyDefinition_r.CUSTOMERDELETABLE%TYPE DEFAULT 1 -- CHAR(1) 8 ,CUSTOMERRENAMABLE CMPPropertyDefinition_r.CUSTOMERRENAMABLE%TYPE DEFAULT 0 -- CHAR(1) 9 ,DELETEINOVERRIDE CMPPropertyDefinition_r.DELETEINOVERRIDE%TYPE DEFAULT 0 -- CHAR(1) 10 ,DESCRIPTION CMPPropertyDefinition_r.DESCRIPTION%TYPE DEFAULT null --DEFINITIONKEY -- VARCHAR2(4000) 11 ,EDITABLE CMPPropertyDefinition_r.EDITABLE%TYPE DEFAULT 1 -- CHAR(1) 12 ,ELEMENTID CMPPropertyDefinition_r.ELEMENTID%TYPE DEFAULT null --ELEMENTID -- NUMBER(9,0) 13 ,IMPORTED CMPPropertyDefinition_r.IMPORTED%TYPE DEFAULT 0 -- CHAR(1) 14 ,LOGICALNAME CMPPropertyDefinition_r.LOGICALNAME%TYPE DEFAULT null --NAME -- VARCHAR2(1000) 15 ,METADATASIGNATURE CMPPropertyDefinition_r.METADATASIGNATURE%TYPE DEFAULT null --METADATASIGNATURE -- VARCHAR2(255) 16 ,NAME CMPPropertyDefinition_r.NAME%TYPE DEFAULT null --NAME -- VARCHAR2(1000) 17 ,NOTE CMPPropertyDefinition_r.NOTE%TYPE DEFAULT null -- VARCHAR2(4000) 18 ,NOTM CMPPropertyDefinition_r.NOTM%TYPE DEFAULT 0 -- NUMBER(9,0) 19 ,OVERRIDEATTRIBUTES CMPPropertyDefinition_r.OVERRIDEATTRIBUTES%TYPE DEFAULT 0 -- CHAR(1) 20 ,OVERRIDECHILDREN CMPPropertyDefinition_r.OVERRIDECHILDREN%TYPE DEFAULT 0 -- CHAR(1) 21 ,OVERRIDEROLES CMPPropertyDefinition_r.OVERRIDEROLES%TYPE DEFAULT 0 -- CHAR(1) 22 ,PERSISTENT CMPPropertyDefinition_r.PERSISTENT%TYPE DEFAULT 1 -- CHAR(1) 23 ,SEEDED CMPPropertyDefinition_r.SEEDED%TYPE DEFAULT 0 -- CHAR(1) 24 ,STRONGTYPENAME CMPPropertyDefinition_r.STRONGTYPENAME%TYPE DEFAULT null -- VARCHAR2(255) 25 ,UOID CMPPropertyDefinition_r.UOID%TYPE DEFAULT null --UOID -- VARCHAR2(40) 26 ,UPDATEDBY CMPPropertyDefinition_r.UPDATEDBY%TYPE DEFAULT 'OWBSYS' -- VARCHAR2(40) 27 ,UPDATETIMESTAMP CMPPropertyDefinition_r.UPDATETIMESTAMP%TYPE DEFAULT null --sysdate -- DATE 28 ,ICONOBJECT CMPPropertyDefinition_r.ICONOBJECT%TYPE DEFAULT null -- NUMBER(9,0) 29 ,EDITORCLASSNAME CMPPropertyDefinition_r.EDITORCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 30 ,PROPERTYSHEETCLASSNAME CMPPropertyDefinition_r.PROPERTYSHEETCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 31 ,WIZARDCLASSNAME CMPPropertyDefinition_r.WIZARDCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 32 ,FIRSTCLASSOBJECT CMPPropertyDefinition_r.FIRSTCLASSOBJECT%TYPE DEFAULT null --FIRSTCLASSOBJECT -- NUMBER(9,0) 33 ,POSITION CMPPropertyDefinition_r.POSITION%TYPE DEFAULT 0 --POSITION -- NUMBER(9,0) 34 ,ADVANCED CMPPropertyDefinition_r.ADVANCED%TYPE DEFAULT 0 -- CHAR(1) 35 ,CONTRIBUTETOSIGNATURE CMPPropertyDefinition_r.CONTRIBUTETOSIGNATURE%TYPE DEFAULT 1 --CONTRIBUTETOSIGNATURE-- CHAR(1) 36 ,CUSTOMEDITOR CMPPropertyDefinition_r.CUSTOMEDITOR%TYPE DEFAULT null -- VARCHAR2(255) 37 ,DEFAULTVALUE CMPPropertyDefinition_r.DEFAULTVALUE%TYPE DEFAULT null -- VARCHAR2(4000) 38 ,DEFINITIONKEY CMPPropertyDefinition_r.DEFINITIONKEY%TYPE DEFAULT null --DEFINITIONKEY -- VARCHAR2(255) 39 ,DYNAMICDEFAULT CMPPropertyDefinition_r.DYNAMICDEFAULT%TYPE DEFAULT null -- VARCHAR2(255) 40 ,EXPORTCONTROL CMPPropertyDefinition_r.EXPORTCONTROL%TYPE DEFAULT 0 -- NUMBER(9,0) 41 ,HIDDEN CMPPropertyDefinition_r.HIDDEN%TYPE DEFAULT 0 -- CHAR(1) 42 ,INPLACEEDITOR CMPPropertyDefinition_r.INPLACEEDITOR%TYPE DEFAULT null -- VARCHAR2(255) 43 ,ISPUBLISHEDTOPUBLICAPI CMPPropertyDefinition_r.ISPUBLISHEDTOPUBLICAPI%TYPE DEFAULT 1 -- CHAR(1) 44 ,ISROLE CMPPropertyDefinition_r.ISROLE%TYPE DEFAULT 0 -- CHAR(1) 45 ,NAMECHECKED CMPPropertyDefinition_r.NAMECHECKED%TYPE DEFAULT 0 -- CHAR(1) 46 ,OVERRIDE CMPPropertyDefinition_r.OVERRIDE%TYPE DEFAULT 0 -- CHAR(1) 47 ,PASSWORD CMPPropertyDefinition_r.PASSWORD%TYPE DEFAULT 0 -- CHAR(1) 48 ,PROPERTYREADONLY CMPPropertyDefinition_r.PROPERTYREADONLY%TYPE DEFAULT 0 -- CHAR(1) 49 ,PROPERTYTYPE CMPPropertyDefinition_r.PROPERTYTYPE%TYPE DEFAULT null --propertyset type -- NUMBER(9,0) 50 ,TRANSLATABLE CMPPropertyDefinition_r.TRANSLATABLE%TYPE DEFAULT 0 -- CHAR(1) 51 ,USERCHOICEALLOWED CMPPropertyDefinition_r.USERCHOICEALLOWED%TYPE DEFAULT 0 -- CHAR(1) 52 ,TYPE CMPPropertyDefinition_r.TYPE%TYPE DEFAULT null --datatype id -- NUMBER(9,0) 53 ,OVERRIDEE CMPPropertyDefinition_r.OVERRIDEE%TYPE DEFAULT null -- NUMBER(9,0) 54 ,PROPERTYGROUPDEFINITION CMPPropertyDefinition_r.PROPERTYGROUPDEFINITION%TYPE DEFAULT null --property group -- NUMBER(9,0) 55 ,OWNINGPROPERTYSETDEFINITION CMPPropertyDefinition_r.OWNINGPROPERTYSETDEFINITION%TYPE DEFAULT null --propertyset -- NUMBER(9,0) 56 ); -- ========================================================================== TYPE CMPAnonymousDomain IS RECORD ( -- ========================================================================== WORKSPACEID CMPAnonymousDomain_r.WORKSPACEID%TYPE DEFAULT 1 -- NUMBER(9,0) 1 ,DELETIONPARENT CMPAnonymousDomain_r.DELETIONPARENT%TYPE DEFAULT null -- NUMBER(9,0) 2 ,AGGREGATEDSIGNATURE CMPAnonymousDomain_r.AGGREGATEDSIGNATURE%TYPE DEFAULT null -- TIMESTAMP(6) 3 ,CLASSNAME CMPAnonymousDomain_r.CLASSNAME%TYPE DEFAULT 'CMPAnonymousDomain' -- VARCHAR2(255) 4 ,COMPLETED CMPAnonymousDomain_r.COMPLETED%TYPE DEFAULT 1 -- CHAR(1) 5 ,CREATEDBY CMPAnonymousDomain_r.CREATEDBY%TYPE DEFAULT 'OWBSYS' -- VARCHAR2(40) 6 ,CREATIONTIMESTAMP CMPAnonymousDomain_r.CREATIONTIMESTAMP%TYPE DEFAULT null -- DATE 7 ,CUSTOMERDELETABLE CMPAnonymousDomain_r.CUSTOMERDELETABLE%TYPE DEFAULT 1 -- CHAR(1) 8 ,CUSTOMERRENAMABLE CMPAnonymousDomain_r.CUSTOMERRENAMABLE%TYPE DEFAULT 0 -- CHAR(1) 9 ,DELETEINOVERRIDE CMPAnonymousDomain_r.DELETEINOVERRIDE%TYPE DEFAULT 0 -- CHAR(1) 10 ,DESCRIPTION CMPAnonymousDomain_r.DESCRIPTION%TYPE DEFAULT null --DEFINITIONKEY -- VARCHAR2(4000) 11 ,EDITABLE CMPAnonymousDomain_r.EDITABLE%TYPE DEFAULT 1 -- CHAR(1) 12 ,ELEMENTID CMPAnonymousDomain_r.ELEMENTID%TYPE DEFAULT null --ELEMENTID -- NUMBER(9,0) 13 ,IMPORTED CMPAnonymousDomain_r.IMPORTED%TYPE DEFAULT 0 -- CHAR(1) 14 ,LOGICALNAME CMPAnonymousDomain_r.LOGICALNAME%TYPE DEFAULT null --NAME -- VARCHAR2(1000) 15 ,METADATASIGNATURE CMPAnonymousDomain_r.METADATASIGNATURE%TYPE DEFAULT null --METADATASIGNATURE -- VARCHAR2(255) 16 ,NAME CMPAnonymousDomain_r.NAME%TYPE DEFAULT null --NAME -- VARCHAR2(1000) 17 ,NOTE CMPAnonymousDomain_r.NOTE%TYPE DEFAULT null -- VARCHAR2(4000) 18 ,NOTM CMPAnonymousDomain_r.NOTM%TYPE DEFAULT 1 -- NUMBER(9,0) 19 ,OVERRIDEATTRIBUTES CMPAnonymousDomain_r.OVERRIDEATTRIBUTES%TYPE DEFAULT 0 -- CHAR(1) 20 ,OVERRIDECHILDREN CMPAnonymousDomain_r.OVERRIDECHILDREN%TYPE DEFAULT 0 -- CHAR(1) 21 ,OVERRIDEROLES CMPAnonymousDomain_r.OVERRIDEROLES%TYPE DEFAULT 0 -- CHAR(1) 22 ,PERSISTENT CMPAnonymousDomain_r.PERSISTENT%TYPE DEFAULT 1 -- CHAR(1) 23 ,SEEDED CMPAnonymousDomain_r.SEEDED%TYPE DEFAULT 0 -- CHAR(1) 24 ,STRONGTYPENAME CMPAnonymousDomain_r.STRONGTYPENAME%TYPE DEFAULT null -- VARCHAR2(255) 25 ,UOID CMPAnonymousDomain_r.UOID%TYPE DEFAULT null --UOID -- VARCHAR2(40) 26 ,UPDATEDBY CMPAnonymousDomain_r.UPDATEDBY%TYPE DEFAULT 'OWBSYS' -- VARCHAR2(40) 27 ,UPDATETIMESTAMP CMPAnonymousDomain_r.UPDATETIMESTAMP%TYPE DEFAULT null -- DATE 28 ,ICONOBJECT CMPAnonymousDomain_r.ICONOBJECT%TYPE DEFAULT null -- NUMBER(9,0) 29 ,EDITORCLASSNAME CMPAnonymousDomain_r.EDITORCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 30 ,PROPERTYSHEETCLASSNAME CMPAnonymousDomain_r.PROPERTYSHEETCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 31 ,WIZARDCLASSNAME CMPAnonymousDomain_r.WIZARDCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 32 ,COMPARATOR CMPAnonymousDomain_r.COMPARATOR%TYPE DEFAULT 'oracle.wh.repos.impl.domain.comparator.PrimitiveValueComparator' -- VARCHAR2(255) 33 ,OWNINGMODEL CMPAnonymousDomain_r.OWNINGMODEL%TYPE DEFAULT null -- NUMBER(9,0) 34 ,CHOOSER CMPAnonymousDomain_r.CHOOSER%TYPE DEFAULT null -- VARCHAR2(255) 35 ,DATATYPEAGSG CMPAnonymousDomain_r.DATATYPEAGSG%TYPE DEFAULT null -- TIMESTAMP(6) 36 ,DATATYPEFQN CMPAnonymousDomain_r.DATATYPEFQN%TYPE DEFAULT null -- VARCHAR2(4000) 37 ,DATATYPELUPD CMPAnonymousDomain_r.DATATYPELUPD%TYPE DEFAULT null -- TIMESTAMP(6) 38 ,DATATYPEUOID CMPAnonymousDomain_r.DATATYPEUOID%TYPE DEFAULT null -- VARCHAR2(40) 39 ,USERCHOICEALLOWED CMPAnonymousDomain_r.USERCHOICEALLOWED%TYPE DEFAULT 0 -- CHAR(1) 40 ,VALUEINTERPRETER CMPAnonymousDomain_r.VALUEINTERPRETER%TYPE DEFAULT null -- VARCHAR2(255) 41 ,DATATYPE CMPAnonymousDomain_r.DATATYPE%TYPE DEFAULT null -- NUMBER(9,0) 42 ,ACLCONTAINER CMPAnonymousDomain_r.ACLCONTAINER%TYPE DEFAULT null -- NUMBER(9,0) 43 ,VALIDATIONRESULT CMPAnonymousDomain_r.VALIDATIONRESULT%TYPE DEFAULT null -- NUMBER(9,0) 44 ,OWNINGFOLDER CMPAnonymousDomain_r.OWNINGFOLDER%TYPE DEFAULT null -- NUMBER(9,0) 45 ,POSITION CMPAnonymousDomain_r.POSITION%TYPE DEFAULT 0 -- NUMBER(9,0) 46 ); -- ************************************************************************** -- ************************************************************************** -- Useful functions/procedures -- ************************************************************************** -- ************************************************************************** -- ========================================================================== -- put message to output (uses DBMS_OUTPUT) PROCEDURE put(msg VARCHAR2) IS -- ========================================================================== BEGIN IF NOT OUTINITED THEN OUTINITED := TRUE; DBMS_OUTPUT.ENABLE(200000); put('DBMS_OUTPUT Enabled.'); END IF; IF (LENGTH(msg) + LENGTH(SCRIPTID) + 2) > 255 THEN -- 10.1 database can't handle messages with length > 255 --DBMS_OUTPUT.put_line('Message length > 255'); --DBMS_OUTPUT.put_line(SUBSTR(msg, 0, 255)); -- Break the string up into lines or 255-byte chunks DECLARE l_msg_line VARCHAR2(255); l_eol_index NUMBER; l_curr_index NUMBER; l_msg_len NUMBER := LENGTH(msg); l_loop_index NUMBER := 0; BEGIN DBMS_OUTPUT.PUT_LINE(SCRIPTID || ':'); l_eol_index := 1; l_curr_index := 1; l_loop_index := 0; WHILE (l_curr_index < l_msg_len AND l_loop_index < 10) LOOP l_eol_index := INSTR(msg, EOL, l_curr_index); --DBMS_OUTPUT.put_line('l_eol_index = ' || TO_CHAR(l_eol_index)); IF (l_eol_index <= 0) OR (l_eol_index - l_curr_index) > 255 THEN l_msg_line := SUBSTR(msg, l_curr_index, 255); l_curr_index := l_curr_index + 255; ELSE l_msg_line := SUBSTR(msg, l_curr_index, l_eol_index - l_curr_index); l_curr_index := l_eol_index + 1; END IF; DBMS_OUTPUT.PUT_LINE(l_msg_line); l_loop_index := l_loop_index + 1; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; ELSE DBMS_OUTPUT.put_line(SCRIPTID || ': ' || msg); END IF; END; -- ========================================================================== -- put message to output controlled by TRACEON variable (uses put) PROCEDURE putTrace(msg VARCHAR2) IS -- ========================================================================== BEGIN IF TRACEON THEN put(msg); END IF; END; -- ========================================================================== -- return the id (elementid) for the platform defined by the input parameters FUNCTION platformIdOf( p_workspaceId IN NUMBER ,p_platformName IN VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS l_workspaceId NUMBER := p_workspaceId; l_platformName VARCHAR2(255) := p_platformName; l_platformId NUMBER; BEGIN putTrace('platformIdOf(' || l_workspaceId || ', ' || l_platformName || ')'); SELECT ELEMENTID INTO l_platformId FROM CMPPlatform_r WHERE NAME = l_platformName and WORKSPACEID = l_workspaceId; putTrace(' RETURN ' || l_platformId || EOL); RETURN l_platformId; END platformIdOf; -- ========================================================================== -- return the id (elementid) for the type set defined by the input parameters FUNCTION typesetIdOf( p_workspaceId IN NUMBER ,p_ownerId IN NUMBER ,p_typesetName IN VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS l_workspaceId NUMBER := p_workspaceId; l_ownerId NUMBER := p_ownerId; l_typesetName VARCHAR2(255) := p_typesetName; l_typesetId NUMBER; BEGIN putTrace('typesetIdOf(' || l_workspaceId || ', ' || l_ownerId || ', ' || l_typesetName || ')'); SELECT ELEMENTID INTO l_typesetId FROM CMPPlatformTypeset_r WHERE OWNINGPLATFORM=l_ownerId AND NAME=l_typesetName and WORKSPACEID = l_workspaceId; putTrace(' RETURN ' || l_typesetId || EOL); RETURN l_typesetId; END typesetIdOf; -- ========================================================================== -- return the id (elementid) for the type defined by the input parameters FUNCTION typeIdOf( p_workspaceId IN NUMBER ,p_ownerId IN NUMBER ,p_typeName IN VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS l_workspaceId NUMBER := p_workspaceId; l_ownerId NUMBER := p_ownerId; l_typeName VARCHAR2(255) := p_typeName; l_typeId NUMBER; BEGIN putTrace('typeIdOf(' || l_workspaceId || ', ' || l_ownerId || ', ' || l_typeName || ')'); SELECT ELEMENTID INTO l_typeId FROM CMPDataType_r WHERE OWNINGPLATFORMTYPESET=l_ownerId AND NAME=l_typeName and WORKSPACEID = l_workspaceId; putTrace(' RETURN ' || l_typeId || EOL); RETURN l_typeId; END typeIdOf; -- ========================================================================== -- similar to Object.toString() in Java - return a string representation of the record FUNCTION toString( pdef CMPPropertyDefinition ) RETURN VARCHAR2 -- ========================================================================== IS l_str VARCHAR2(255) := '(CMPPropertyDefinition)' || pdef.NAME || '/id=' || pdef.ELEMENTID || '/definitionKey=' || pdef.DEFINITIONKEY || '/propertyGroupId=' || pdef.PROPERTYGROUPDEFINITION || '/propertySetId=' || pdef.OWNINGPROPERTYSETDEFINITION ; BEGIN RETURN l_str; END; -- ========================================================================== FUNCTION getPlatformIdByName( p_platformName VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS p_id NUMBER(9,0); BEGIN SELECT ELEMENTID INTO p_id FROM CMPPlatform_r WHERE WORKSPACEID=1 AND NAME=p_platformName; put( 'Found Platform [name: ' || p_platformName || '; elementid: ' || p_id || ']' || EOL); RETURN p_id; END; -- ========================================================================== FUNCTION getClassIdByName( p_platformId NUMBER, p_className VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS p_classId NUMBER(9,0); BEGIN SELECT ELEMENTID INTO p_classId FROM CMPClassDefinition_r WHERE NAME=p_className AND OWNINGFOLDER=p_platformId; put( 'Found ClassDefinition [name: ' || p_className || '; elementid: ' || p_classId || '; owningPlatform: ' || p_platformId || ']' || EOL); RETURN p_classId; END; -- ========================================================================== FUNCTION getPropertySetId( p_classId NUMBER, p_propertyType NUMBER, p_name VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS p_propertySetId NUMBER(9,0); BEGIN SELECT ELEMENTID INTO p_propertySetId FROM CMPPropertySetDefinition_r WHERE FIRSTCLASSOBJECT=p_classId AND PROPERTYTYPE=p_propertyType AND NAME LIKE '%'||p_name||'%'; put( 'Found PropertySetDefinition [name: ' || p_name || '; elementid: ' || p_propertySetId || '; owningClassDef: ' || p_classId || ']' || EOL); RETURN p_propertySetId; END; -- ========================================================================== FUNCTION getPropertyGroupId( p_propertySetId NUMBER, p_definitionKey VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS p_propertyGroupId NUMBER(9,0); BEGIN SELECT ELEMENTID INTO p_propertyGroupId FROM CMPPropertyGroupDefinition_r WHERE PROPERTYGROUPOWNER=p_propertySetId AND LOGICALNAME=p_definitionKey; RETURN p_propertyGroupId; END; -- ========================================================================== FUNCTION getPropertyDefinitionId( p_propertySetId NUMBER, p_definitionKey VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS p_propertyDefinitionId NUMBER(9,0); BEGIN SELECT ELEMENTID INTO p_propertyDefinitionId FROM CMPPropertyDefinition_r WHERE OWNINGPROPERTYSETDEFINITION=p_propertySetId AND DEFINITIONKEY=p_definitionKey; RETURN p_propertyDefinitionId; END; -- ========================================================================== FUNCTION getStringDatatypeId RETURN NUMBER -- ========================================================================== IS p_datatypeId NUMBER(9,0); BEGIN SELECT ELEMENTID INTO p_datatypeId FROM CMPPrimitiveType_r WHERE NAME='String'; RETURN p_datatypeId; END; -- ========================================================================== -- CMPPropertyDefinition -- ========================================================================== FUNCTION newCMPPropertyDefinition( definitionKey IN VARCHAR2 DEFAULT null ,elementId IN NUMBER DEFAULT 0 ,name IN VARCHAR2 DEFAULT null ,metadataSignature IN VARCHAR2 DEFAULT null ,uoid IN VARCHAR2 DEFAULT null ,firstClassObjectId IN NUMBER DEFAULT null ,position IN NUMBER DEFAULT 0 ,contributeToSignature IN VARCHAR DEFAULT null ,propertyTypeId IN NUMBER DEFAULT null ,datatypeId IN NUMBER DEFAULT 0 ,propertyGroupId IN NUMBER DEFAULT 0 ,propertySetId IN NUMBER DEFAULT 0 ) RETURN CMPPropertyDefinition -- ========================================================================== IS pdef CMPPropertyDefinition; BEGIN pdef.AGGREGATEDSIGNATURE := SYSDATE; pdef.CREATIONTIMESTAMP := SYSDATE; pdef.DESCRIPTION := definitionKey; pdef.ELEMENTID := elementId; pdef.LOGICALNAME := name; pdef.METADATASIGNATURE := metadataSignature; pdef.NAME := name; pdef.UOID := uoid; pdef.UPDATETIMESTAMP := SYSDATE; pdef.FIRSTCLASSOBJECT := firstClassObjectId; pdef.POSITION := position; pdef.CONTRIBUTETOSIGNATURE := contributeToSignature; pdef.DEFINITIONKEY := definitionKey; pdef.PROPERTYTYPE := propertyTypeId; pdef.TYPE := datatypeId; pdef.PROPERTYGROUPDEFINITION := propertyGroupId; pdef.OWNINGPROPERTYSETDEFINITION := propertySetId; RETURN pdef; END newCMPPropertyDefinition; -- ========================================================================== FUNCTION getCMPPropertyDefinition( definitionKey IN VARCHAR2 DEFAULT null ,name IN VARCHAR2 DEFAULT null ,propertyTypeId IN NUMBER DEFAULT null ,datatypeId IN NUMBER DEFAULT null ,propertyGroupId IN NUMBER DEFAULT null ,propertySetId IN NUMBER DEFAULT null ) RETURN CMPPropertyDefinition -- ========================================================================== IS pdef CMPPropertyDefinition; l_str VARCHAR2(4000); BEGIN l_str := 'SELECT * FROM CMPPropertyDefinition_r WHERE 1=1'; if (definitionKey is not null) then l_str := l_str || ' and DEFINITIONKEY=''' || definitionKey || ''''; end if; if (name is not null) then l_str := l_str || ' and NAME=''' || name || ''''; end if; if (propertyTypeId is not null) then l_str := l_str || ' and PROPERTYTYPE=' || propertyTypeId; end if; if (datatypeId is not null) then l_str := l_str || ' and TYPE=' || datatypeId; end if; if (propertyGroupId is not null) then l_str := l_str || ' and PROPERTYGROUPDEFINITION=' || propertyGroupId; end if; if (propertySetId is not null) then l_str := l_str || ' and OWNINGPROPERTYSETDEFINITION=' || propertySetId; end if; EXECUTE IMMEDIATE l_str INTO pdef; put('Found (' || toString(pdef) || ')' || EOL); RETURN pdef; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN put ('Not found -->' || l_str || EOL); RETURN null; END; -- NO_DATA_FOUND /* should not happen. Developers have to pass in the correct params that have only one record. WHEN TOO_MANY_ROWS THEN BEGIN put ('Too many rows -->' || l_str); RETURN null; END;*/ RETURN pdef; END getCMPPropertyDefinition; -- ========================================================================== FUNCTION getCMPAnonymousDomain( pdef IN CMPPropertyDefinition ) RETURN CMPAnonymousDomain -- ========================================================================== IS ddef CMPAnonymousDomain; l_str VARCHAR2(4000); BEGIN l_str := 'SELECT * FROM CMPAnonymousDomain_r WHERE ELEMENTID=' || pdef.TYPE; EXECUTE IMMEDIATE l_str INTO ddef; put('Found (' || l_str || ')' || EOL); RETURN ddef; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN put ('Not found -->' || l_str || EOL); RETURN null; END; -- NO_DATA_FOUND /* should not happen. Developers have to pass in the correct params that have only one record. WHEN TOO_MANY_ROWS THEN BEGIN put ('Too many rows -->' || l_str); RETURN null; END;*/ RETURN ddef; END getCMPAnonymousDomain; -- ########################################################################## -- ########################################################################## BEGIN -- Outer block - utility functions/variables now defined -- ########################################################################## -- ########################################################################## -- ======================================================= DECLARE -- Inner block for utility function/variable usage -- aka 'user' declarations -- ======================================================= pdef CMPPropertyDefinition := null; platformId NUMBER; classId NUMBER; propertySetId NUMBER; propertyGroupId NUMBER; propertyDefinitionId NUMBER; datatypeId NUMBER; -- ======================================================= BEGIN -- start handling data types defined in up.xml -- ======================================================= -- up.xml-> platformId := getPlatformIdByName('GENERIC'); -- up.xml -> name='oracle.wh.repos.impl.mapping.CMPBatchMap' -- up.xml -> > classId := getClassIdByName(platformId,'oracle.wh.repos.impl.mapping.CMPBatchMap'); -- up.xml -> Position='0' -- up.xml -> definitionKey='8i.MAPPINGS.ABAPSTEP' -- up.xml -> name='#ABAP' -- up.xml -> propertyType='physical' -- up.xml -> > propertySetId := getPropertySetId(classId, PROPERTYTYPE_PHYSICAL, 'ABAP'); -- up.xml -> Position='9' -- up.xml -> dataType='String' -- up.xml -> chooser='oracle.wh.repos.impl.mapping.GenerationModeChooser' -- up.xml -> definitionKey='MAP.GENERATION_MODE' -- up.xml -> description='The operating modes for which code should be generated' -- up.xml -> exportControl='public' -- up.xml -> isPublishedToPublicApi='false' -- up.xml -> initValue='ALL_MODES' -- up.xml -> isTranslatable='true' -- up.xml -> isHidden='true' -- up.xml -> name='GENERATION_MODE' -- up.xml -> > -- up.xml -> pdef := getCMPPropertyDefinition( 'MAP.GENERATION_MODE', --definitionKey 'GENERATION_MODE', --name null, --propertyTypeId null, --datatypeId null, --propertyGroupId propertySetId --propertySetId ); -- delete previous choice values for model attribute 'DOMAIN_VALUES' delete from CMPPrimitiveModelAttribute_r where modelattributeowner in (select elementid from CMPPrimitiveModelAttribute_r where modelattributeowner=pdef.TYPE and name='DOMAIN_VALUES'); put('Done: delete from CMPPrimitiveModelAttribute_r where modelattributeowner in (select elementid from CMPPrimitiveModelAttribute_r where modelattributeowner=' || pdef.TYPE || ' and name=''DOMAIN_VALUES'')' || EOL); -- delete previous model attribute 'DOMAIN_VALUES' delete from CMPPrimitiveModelAttribute_r where modelattributeowner = pdef.TYPE and name='DOMAIN_VALUES'; put('Done: delete from CMPPrimitiveModelAttribute_r where modelattributeowner = ' || pdef.TYPE || ' and name=''DOMAIN_VALUES''' || EOL); -- set chooser for AnonymousDomain of PropertyDefinition update CMPAnonymousDomain_r set chooser = 'oracle.wh.repos.impl.mapping.GenerationModeChooser' where elementid = pdef.TYPE; put('Done: update CMPAnonymousDomain_r set chooser = ''oracle.wh.repos.impl.mapping.GenerationModeChooser'' where elementid = ' || pdef.TYPE || EOL); -- up.xml -> Position='0' -- up.xml -> definitionKey='8i.MAPPINGS.SQLLOADERSTEP' -- up.xml -> name='#SQLLOADER' -- up.xml -> propertyType='physical' -- up.xml -> > propertySetId := getPropertySetId(classId, PROPERTYTYPE_PHYSICAL, 'SQLLOADER'); -- up.xml -> Position='32' -- up.xml -> chooser='oracle.wh.repos.impl.mapping.GenerationModeChooser' -- up.xml -> dataType='String' -- up.xml -> definitionKey='MAP.GENERATION_MODE' -- up.xml -> description='The operating modes for which code should be generated' -- up.xml -> exportControl='public' -- up.xml -> isPublishedToPublicApi='false' -- up.xml -> initValue='ALL_MODES' -- up.xml -> isTranslatable='true' -- up.xml -> isHidden='true' -- up.xml -> name='GENERATION_MODE' -- up.xml -> > -- up.xml -> pdef := getCMPPropertyDefinition( 'MAP.GENERATION_MODE', --definitionKey 'GENERATION_MODE', --name null, --propertyTypeId null, --datatypeId null, --propertyGroupId propertySetId --propertySetId ); -- delete previous choice values for model attribute 'DOMAIN_VALUES' delete from CMPPrimitiveModelAttribute_r where modelattributeowner in (select elementid from CMPPrimitiveModelAttribute_r where modelattributeowner=pdef.TYPE and name='DOMAIN_VALUES'); put('Done: delete from CMPPrimitiveModelAttribute_r where modelattributeowner in (select elementid from CMPPrimitiveModelAttribute_r where modelattributeowner=' || pdef.TYPE || ' and name=''DOMAIN_VALUES'')' || EOL); -- delete previous model attribute 'DOMAIN_VALUES' delete from CMPPrimitiveModelAttribute_r where modelattributeowner = pdef.TYPE and name='DOMAIN_VALUES'; put('Done: delete from CMPPrimitiveModelAttribute_r where modelattributeowner = ' || pdef.TYPE || ' and name=''DOMAIN_VALUES''' || EOL); -- set chooser for AnonymousDomain of PropertyDefinition update CMPAnonymousDomain_r set chooser = 'oracle.wh.repos.impl.mapping.GenerationModeChooser' where elementid = pdef.TYPE; put('Done: update CMPAnonymousDomain_r set chooser = ''oracle.wh.repos.impl.mapping.GenerationModeChooser'' where elementid = ' || pdef.TYPE || EOL); -- up.xml -> Position='0' -- up.xml -> definitionKey='8i.MAPPINGS.SQLPLUSSTEP' -- up.xml -> name='#SQLPLUS' -- up.xml -> propertyType='physical' -- up.xml -> > propertySetId := getPropertySetId(classId, PROPERTYTYPE_PHYSICAL, 'SQLPLUS'); -- up.xml -> Position='7' -- up.xml -> chooser='oracle.wh.repos.impl.mapping.GenerationModeChooser' -- up.xml -> dataType='String' -- up.xml -> definitionKey='MAP.GENERATION_MODE' -- up.xml -> description='The operating modes for which code should be generated' -- up.xml -> exportControl='public' -- up.xml -> isPublishedToPublicApi='false' -- up.xml -> initValue='ALL_MODES' -- up.xml -> isTranslatable='true' -- up.xml -> isHidden='true' -- up.xml -> name='GENERATION_MODE' -- up.xml -> > pdef := getCMPPropertyDefinition( 'MAP.GENERATION_MODE', --definitionKey 'GENERATION_MODE', --name null, --propertyTypeId null, --datatypeId null, --propertyGroupId propertySetId --propertySetId ); -- delete previous choice values for model attribute 'DOMAIN_VALUES' delete from CMPPrimitiveModelAttribute_r where modelattributeowner in (select elementid from CMPPrimitiveModelAttribute_r where modelattributeowner=pdef.TYPE and name='DOMAIN_VALUES'); put('Done: delete from CMPPrimitiveModelAttribute_r where modelattributeowner in (select elementid from CMPPrimitiveModelAttribute_r where modelattributeowner=' || pdef.TYPE || ' and name=''DOMAIN_VALUES'')' || EOL); -- delete previous model attribute 'DOMAIN_VALUES' delete from CMPPrimitiveModelAttribute_r where modelattributeowner = pdef.TYPE and name='DOMAIN_VALUES'; put('Done: delete from CMPPrimitiveModelAttribute_r where modelattributeowner = ' || pdef.TYPE || ' and name=''DOMAIN_VALUES''' || EOL); -- set chooser for AnonymousDomain of PropertyDefinition update CMPAnonymousDomain_r set chooser = 'oracle.wh.repos.impl.mapping.GenerationModeChooser' where elementid = pdef.TYPE; put('Done: update CMPAnonymousDomain_r set chooser = ''oracle.wh.repos.impl.mapping.GenerationModeChooser'' where elementid = ' || pdef.TYPE || EOL); -- up.xml -> name='oracle.wh.repos.impl.mapping.CMPTrickleFeedMap' -- up.xml -> > classId := getClassIdByName(platformId,'oracle.wh.repos.impl.mapping.CMPTrickleFeedMap'); -- up.xml -> Position='1' -- up.xml -> definitionKey='MAP.LANGUAGE_PLSQL' -- up.xml -> name='#PLSQL' -- up.xml -> propertyType='physical' -- up.xml -> > propertySetId := getPropertySetId(classId, PROPERTYTYPE_PHYSICAL, 'PLSQL'); -- up.xml -> Position='2' -- up.xml -> chooser='oracle.wh.repos.impl.mapping.GenerationModeChooser' -- up.xml -> dataType='String' -- up.xml -> definitionKey='MAP.GENERATION_MODE' -- up.xml -> description='The operating modes for which code should be generated' -- up.xml -> exportControl='public' -- up.xml -> initValue='ROW_BASED' -- up.xml -> isTranslatable='true' -- up.xml -> name='GENERATION_MODE' -- up.xml -> > -- up.xml -> pdef := getCMPPropertyDefinition( 'MAP.GENERATION_MODE', --definitionKey 'GENERATION_MODE', --name null, --propertyTypeId null, --datatypeId null, --propertyGroupId propertySetId --propertySetId ); -- delete previous choice values for model attribute 'DOMAIN_VALUES' delete from CMPPrimitiveModelAttribute_r where modelattributeowner in (select elementid from CMPPrimitiveModelAttribute_r where modelattributeowner=pdef.TYPE and name='DOMAIN_VALUES'); put('Done: delete from CMPPrimitiveModelAttribute_r where modelattributeowner in (select elementid from CMPPrimitiveModelAttribute_r where modelattributeowner=' || pdef.TYPE || ' and name=''DOMAIN_VALUES'')' || EOL); -- delete previous model attribute 'DOMAIN_VALUES' delete from CMPPrimitiveModelAttribute_r where modelattributeowner = pdef.TYPE and name='DOMAIN_VALUES'; put('Done: delete from CMPPrimitiveModelAttribute_r where modelattributeowner = ' || pdef.TYPE || ' and name=''DOMAIN_VALUES''' || EOL); -- set chooser for AnonymousDomain of PropertyDefinition update CMPAnonymousDomain_r set chooser = 'oracle.wh.repos.impl.mapping.GenerationModeChooser' where elementid = pdef.TYPE; put('Done: update CMPAnonymousDomain_r set chooser = ''oracle.wh.repos.impl.mapping.GenerationModeChooser'' where elementid = ' || pdef.TYPE || EOL); -- ======================================================= END; -- Inner block for utility function/variable usage -- ======================================================= -- ########################################################################## END; -- Outer block for utility functions/variables -- ########################################################################## /