-- 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 9808664 - LOCATION EDITOR: CONNECTION TO MYSQL AND SYBASE ACCEPTED ONLY IN UPPER CASE -- 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 9808664'; -- 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_LOGICAL NUMBER:= 1; -- Record definitions for the various views -- Handy sql to make record definitions (some hand editing required (in sqlworksheet anyway) -- ========================================================================== TYPE CMPPropertyGroupDefinition IS RECORD ( -- ========================================================================== WORKSPACEID CMPPropertyGroupDefinition_r.WORKSPACEID%TYPE DEFAULT 1 -- NUMBER(9,0) 1 ,DELETIONPARENT CMPPropertyGroupDefinition_r.DELETIONPARENT%TYPE DEFAULT null -- NUMBER(9,0) 2 ,AGGREGATEDSIGNATURE CMPPropertyGroupDefinition_r.AGGREGATEDSIGNATURE%TYPE DEFAULT null --sysdate -- TIMESTAMP(6) 3 ,CLASSNAME CMPPropertyGroupDefinition_r.CLASSNAME%TYPE DEFAULT 'CMPPropertyGroupDefinition' -- VARCHAR2(255) 4 ,COMPLETED CMPPropertyGroupDefinition_r.COMPLETED%TYPE DEFAULT 1 -- CHAR(1) 5 ,CREATEDBY CMPPropertyGroupDefinition_r.CREATEDBY%TYPE DEFAULT 'OWBSYS' -- VARCHAR2(40) 6 ,CREATIONTIMESTAMP CMPPropertyGroupDefinition_r.CREATIONTIMESTAMP%TYPE DEFAULT null --sysdate -- DATE 7 ,CUSTOMERDELETABLE CMPPropertyGroupDefinition_r.CUSTOMERDELETABLE%TYPE DEFAULT 1 -- CHAR(1) 8 ,CUSTOMERRENAMABLE CMPPropertyGroupDefinition_r.CUSTOMERRENAMABLE%TYPE DEFAULT 0 -- CHAR(1) 9 ,DELETEINOVERRIDE CMPPropertyGroupDefinition_r.DELETEINOVERRIDE%TYPE DEFAULT 0 -- CHAR(1) 10 ,DESCRIPTION CMPPropertyGroupDefinition_r.DESCRIPTION%TYPE DEFAULT null -- VARCHAR2(4000) 11 ,EDITABLE CMPPropertyGroupDefinition_r.EDITABLE%TYPE DEFAULT 1 -- CHAR(1) 12 ,ELEMENTID CMPPropertyGroupDefinition_r.ELEMENTID%TYPE DEFAULT null --ELEMENTID -- NUMBER(9,0) 13 ,IMPORTED CMPPropertyGroupDefinition_r.IMPORTED%TYPE DEFAULT 0 -- CHAR(1) 14 ,LOGICALNAME CMPPropertyGroupDefinition_r.LOGICALNAME%TYPE DEFAULT null --DEFINITIONKEY -- VARCHAR2(1000) 15 ,METADATASIGNATURE CMPPropertyGroupDefinition_r.METADATASIGNATURE%TYPE DEFAULT null --METADATASIGNATURE -- VARCHAR2(255) 16 ,NAME CMPPropertyGroupDefinition_r.NAME%TYPE DEFAULT null --NAME -- VARCHAR2(1000) 17 ,NOTE CMPPropertyGroupDefinition_r.NOTE%TYPE DEFAULT null -- VARCHAR2(4000) 18 ,NOTM CMPPropertyGroupDefinition_r.NOTM%TYPE DEFAULT 0 -- NUMBER(9,0) 19 ,OVERRIDEATTRIBUTES CMPPropertyGroupDefinition_r.OVERRIDEATTRIBUTES%TYPE DEFAULT 0 -- CHAR(1) 20 ,OVERRIDECHILDREN CMPPropertyGroupDefinition_r.OVERRIDECHILDREN%TYPE DEFAULT 0 -- CHAR(1) 21 ,OVERRIDEROLES CMPPropertyGroupDefinition_r.OVERRIDEROLES%TYPE DEFAULT 0 -- CHAR(1) 22 ,PERSISTENT CMPPropertyGroupDefinition_r.PERSISTENT%TYPE DEFAULT 1 -- CHAR(1) 23 ,SEEDED CMPPropertyGroupDefinition_r.SEEDED%TYPE DEFAULT 0 -- CHAR(1) 24 ,STRONGTYPENAME CMPPropertyGroupDefinition_r.STRONGTYPENAME%TYPE DEFAULT null -- VARCHAR2(255) 25 ,UOID CMPPropertyGroupDefinition_r.UOID%TYPE DEFAULT null --UOID -- VARCHAR2(40) 26 ,UPDATEDBY CMPPropertyGroupDefinition_r.UPDATEDBY%TYPE DEFAULT 'OWBSYS' -- VARCHAR2(40) 27 ,UPDATETIMESTAMP CMPPropertyGroupDefinition_r.UPDATETIMESTAMP%TYPE DEFAULT null --sysdate -- DATE 28 ,ICONOBJECT CMPPropertyGroupDefinition_r.ICONOBJECT%TYPE DEFAULT null -- NUMBER(9,0) 29 ,EDITORCLASSNAME CMPPropertyGroupDefinition_r.EDITORCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 30 ,PROPERTYSHEETCLASSNAME CMPPropertyGroupDefinition_r.PROPERTYSHEETCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 31 ,WIZARDCLASSNAME CMPPropertyGroupDefinition_r.WIZARDCLASSNAME%TYPE DEFAULT null -- VARCHAR2(255) 32 ,FIRSTCLASSOBJECT CMPPropertyGroupDefinition_r.FIRSTCLASSOBJECT%TYPE DEFAULT null --FIRSTCLASSOBJECT -- NUMBER(9,0) 33 ,POSITION CMPPropertyGroupDefinition_r.POSITION%TYPE DEFAULT 0 --POSITION -- NUMBER(9,0) 34 ,DEFINITIONKEY CMPPropertyGroupDefinition_r.DEFINITIONKEY%TYPE DEFAULT null -- VARCHAR2(255) 35 ,HIDDEN CMPPropertyGroupDefinition_r.HIDDEN%TYPE DEFAULT 0 -- CHAR(1) 36 ,TYPE CMPPropertyGroupDefinition_r.TYPE%TYPE DEFAULT null -- NUMBER(9,0) 37 ,PROPERTYGROUPOWNER CMPPropertyGroupDefinition_r.PROPERTYGROUPOWNER%TYPE DEFAULT null --property set -- NUMBER(9,0) 38 ); -- ========================================================================== 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 ); -- ************************************************************************** -- ************************************************************************** -- 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; -- ========================================================================== -- 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; -- ========================================================================== -- return next element id for new objects FUNCTION getNextElementId RETURN NUMBER -- ========================================================================== IS l_id NUMBER; BEGIN SELECT cwmseq.nextval INTO l_id FROM DUAL; RETURN l_id; 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 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; -- ========================================================================== -- toString CMPPropertyDefinition FUNCTION toSPropertyDefinition(pType CMPPropertyDefinition) RETURN VARCHAR2 IS -- ========================================================================== l_str VARCHAR2(4000); BEGIN l_str := 'CMPPropertyDefinition [ '; if (pType.NAME is not null) then l_str := l_str || '[NAME: ' || pType.NAME || '] '; end if; if (pType.LOGICALNAME is not null) then l_str := l_str || '[LOGICALNAME: ' || pType.LOGICALNAME || '] '; end if; if (pType.ELEMENTID is not null) then l_str := l_str || '[ELEMENTID: ' || pType.ELEMENTID || '] '; end if; if (pType.UOID is not null) then l_str := l_str || '[UOID: ' || pType.UOID || '] '; end if; if (pType.POSITION is not null) then l_str := l_str || '[POSITION: ' || pType.POSITION || '] '; end if; if (pType.PROPERTYTYPE is not null) then l_str := l_str || '[PROPERTYTYPE: ' || pType.PROPERTYTYPE || '] '; end if; if (pType.TYPE is not null) then l_str := l_str || '[TYPE: ' || pType.TYPE || '] '; end if; if (pType.OVERRIDEE is not null) then l_str := l_str || '[OVERRIDEE: ' || pType.OVERRIDEE || '] '; end if; if (pType.FIRSTCLASSOBJECT is not null) then l_str := l_str || '[FIRSTCLASSOBJECT: ' || pType.FIRSTCLASSOBJECT || '] '; end if; if (pType.PROPERTYGROUPDEFINITION is not null) then l_str := l_str || '[PROPERTYGROUPDEFINITION: ' || pType.PROPERTYGROUPDEFINITION || '] '; end if; if (pType.OWNINGPROPERTYSETDEFINITION is not null) then l_str := l_str || '[OWNINGPROPERTYSETDEFINITION: ' || pType.OWNINGPROPERTYSETDEFINITION || '] '; end if; l_str := l_str || ']'; return l_str; END toSPropertyDefinition; -- ========================================================================== -- NewCMPPropertyDefinition FUNCTION newCMPPropertyDefinition( definitionKey IN VARCHAR2 DEFAULT null ,name IN VARCHAR2 DEFAULT null ,metadataSignature 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 := getNextElementId; pdef.LOGICALNAME := name; pdef.METADATASIGNATURE := metadataSignature; pdef.NAME := name; pdef.UOID := RAWTOHEX(SYS_GUID()); 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; putTrace('Created ' || toSPropertyDefinition(pdef) || EOL); RETURN pdef; END newCMPPropertyDefinition; -- ========================================================================== PROCEDURE AddCMPPropertyDefinition( pdef IN OUT CMPPropertyDefinition ) -- ========================================================================== IS l_count NUMBER := 0; l_str VARCHAR2(4000); BEGIN select count(1) into l_count from CMPPropertyDefinition_r where name = pdef.NAME and logicalname=pdef.LOGICALNAME and firstclassobject=pdef.FIRSTCLASSOBJECT and definitionkey=pdef.DEFINITIONKEY and propertyType=pdef.PROPERTYTYPE and owningPropertySetDefinition=pdef.OWNINGPROPERTYSETDEFINITION and workspaceid=pdef.WORKSPACEID; If l_count = 1 then l_str := 'select * from CMPPropertyDefinition_r where name = ''' || pdef.NAME || ''' and logicalname=''' || pdef.LOGICALNAME || ''' and firstclassobject=' || pdef.FIRSTCLASSOBJECT || ' and definitionkey=''' || pdef.DEFINITIONKEY || ''' and propertyType=' || pdef.PROPERTYTYPE || ' and owningPropertySetDefinition=' || pdef.OWNINGPROPERTYSETDEFINITION || ' and workspaceid=' || pdef.WORKSPACEID; EXECUTE IMMEDIATE l_str INTO pdef; putTrace('Found ' || toSPropertyDefinition(pdef) || EOL); else If l_count = 0 then insert into CMPPropertyDefinition_r VALUES pdef; putTrace('Inserted ' || toSPropertyDefinition(pdef) || EOL); else putTrace('Error! Found ' || l_count || ' PropertyDefinitions with same name ''' || pdef.NAME || '''!' || EOL); end if; end if; END; -- ========================================================================== -- toString CMPPropertyGroupDefinition FUNCTION toSPropertyGroupDefinition(pType CMPPropertyGroupDefinition) RETURN VARCHAR2 IS -- ========================================================================== l_str VARCHAR2(4000); BEGIN l_str := 'CMPPropertyGroupDefinition [ '; if (pType.NAME is not null) then l_str := l_str || '[NAME: ' || pType.NAME || '] '; end if; if (pType.LOGICALNAME is not null) then l_str := l_str || '[LOGICALNAME: ' || pType.LOGICALNAME || '] '; end if; if (pType.ELEMENTID is not null) then l_str := l_str || '[ELEMENTID: ' || pType.ELEMENTID || '] '; end if; if (pType.UOID is not null) then l_str := l_str || '[UOID: ' || pType.UOID || '] '; end if; if (pType.POSITION is not null) then l_str := l_str || '[POSITION: ' || pType.POSITION || '] '; end if; if (pType.DEFINITIONKEY is not null) then l_str := l_str || '[DEFINITIONKEY: ' || pType.DEFINITIONKEY || '] '; end if; if (pType.PROPERTYGROUPOWNER is not null) then l_str := l_str || '[PROPERTYGROUPOWNER: ' || pType.PROPERTYGROUPOWNER || '] '; end if; if (pType.FIRSTCLASSOBJECT is not null) then l_str := l_str || '[FIRSTCLASSOBJECT: ' || pType.FIRSTCLASSOBJECT || '] '; end if; l_str := l_str || ']'; return l_str; END toSPropertyGroupDefinition; -- ========================================================================== -- NewCMPPropertyGroupdefinition FUNCTION newCMPPropertyGroupdefinition( definitionKey IN VARCHAR2 DEFAULT null ,name IN VARCHAR2 DEFAULT null ,metadataSignature IN VARCHAR2 DEFAULT null ,firstClassObjectId IN NUMBER DEFAULT null ,position IN NUMBER DEFAULT 0 ,propertySetId IN NUMBER DEFAULT 0 ) RETURN CMPPropertyGroupdefinition -- ========================================================================== IS pgdef CMPPropertyGroupdefinition; BEGIN pgdef.AGGREGATEDSIGNATURE := SYSDATE; pgdef.CREATIONTIMESTAMP := SYSDATE; pgdef.ELEMENTID := getNextElementId; pgdef.LOGICALNAME := definitionKey; pgdef.METADATASIGNATURE := metadataSignature; pgdef.NAME := name; pgdef.UOID := RAWTOHEX(SYS_GUID()); pgdef.UPDATETIMESTAMP := SYSDATE; pgdef.FIRSTCLASSOBJECT := firstClassObjectId; pgdef.POSITION := position; pgdef.PROPERTYGROUPOWNER := propertySetId; putTrace('Created ' || toSPropertyGroupdefinition(pgdef) || EOL); RETURN pgdef; END newCMPPropertyGroupdefinition; -- ========================================================================== PROCEDURE AddCMPPropertyGroupDefinition( pgdeg IN OUT CMPPropertyGroupDefinition ) -- ========================================================================== IS l_count NUMBER := 0; l_str VARCHAR2(4000); BEGIN select count(1) into l_count from CMPPropertyGroupDefinition_r where name = pgdeg.NAME and logicalname=pgdeg.LOGICALNAME and firstclassobject=pgdeg.FIRSTCLASSOBJECT and propertyGroupOwner=pgdeg.PROPERTYGROUPOWNER and workspaceid=pgdeg.WORKSPACEID; If l_count = 1 then l_str := 'select * from CMPPropertyGroupDefinition_r where name = ''' || pgdeg.NAME || ''' and logicalname=''' || pgdeg.LOGICALNAME || ''' and firstclassobject=' || pgdeg.FIRSTCLASSOBJECT || ' and propertyGroupOwner=' || pgdeg.propertyGroupOwner || ' and workspaceid=' || pgdeg.WORKSPACEID; EXECUTE IMMEDIATE l_str INTO pgdeg; putTrace('Found ' || toSPropertyGroupdefinition(pgdeg) || EOL); else If l_count = 0 then insert into CMPPropertyGroupDefinition_r VALUES pgdeg; putTrace('Inserted ' || toSPropertyGroupdefinition(pgdeg) || EOL); else putTrace('Error! Found ' || l_count || ' PropertyGroupDefinitions with same name ''' || pgdeg.NAME || '''!' || EOL); end if; end if; END; -- ########################################################################## -- ########################################################################## BEGIN -- Outer block - utility functions/variables now defined -- ########################################################################## -- ########################################################################## -- ======================================================= DECLARE -- Inner block for utility function/variable usage -- aka 'user' declarations -- ======================================================= pgdef CMPPropertyGroupdefinition; pdef CMPPropertyDefinition; 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.environment.CMPGenericJDBCLocation' -- up.xml-> extends='oracle.wh.repos.impl.environment.CMPURILocation' -- up.xml-> multipleInheritance='oracle.wh.repos.impl.environment.CMPJDBCLocation' -- up.xml-> > classId := getClassIdByName(platformId,'oracle.wh.repos.impl.environment.CMPGenericJDBCLocation'); -- up.xml -> propertySetId := getPropertySetId(classId, PROPERTYTYPE_LOGICAL, 'LOGICAL:DEFAULT'); -- up.xml -> pgdef := newCMPPropertyGroupdefinition( 'UserInfo',--definitionKey 'USERINFO',--name '9dde4af7c31ded26b0be60a0bbf6316b',--metadataSignature classId,--firstClassObjectId 0,--position propertySetId--propertySetId ); AddCMPPropertyGroupDefinition(pgdef); -- up.xml -> name='CONNECT_AS_USER' -- up.xml -> dataType='String' -- up.xml -> description='User name for the location connection.' -- up.xml -> definitionKey='CMPLocation_ConnectAsUser' -- up.xml -> exportControl='public' -- up.xml -> initValue='' -- up.xml -> isPassword='false' -- up.xml -> isTranslatable='false' -- up.xml -> isHidden='true' -- up.xml -> /> datatypeId := getStringDatatypeId(); pdef := newCMPPropertyDefinition( 'CMPLocation_ConnectAsUser',--definitionKey 'CONNECT_AS_USER',--name '5904239f5caccb497e66604cd42b7338',--metadataSignature classId,--firstClassObjectId 0,--position '1',--contributeToSignature PROPERTYTYPE_LOGICAL,--propertyTypeId datatypeId,--datatypeId pgdef.ELEMENTID,--propertyGroupId propertySetId --propertySetId ); pdef.EXPORTCONTROL := 0; pdef.TRANSLATABLE := '0'; pdef.HIDDEN := 1; pdef.ISPUBLISHEDTOPUBLICAPI := 0; AddCMPPropertyDefinition(pdef); -- ======================================================= END; -- Inner block for utility function/variable usage -- ======================================================= -- ########################################################################## END; -- Outer block for utility functions/variables -- ########################################################################## /