-- $Header: whdev/2.0/owb/shiphome/owb/reposasst/upg/bug_6785906.sql /main/2 2010/12/20 01:02:48 dawsun Exp $ -- Create sql in-place upgrade patch for some attributes of DB2UDB sequence. -- -- Note: PLEASE connect as OWBSYS before execute the file. -- -- Bug 6785906 - MORE CONFIG OPTIONS FOR DB2 SEQUENCE NEEDED -- 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 8437739'; -- 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 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 ); -- ************************************************************************** -- ************************************************************************** -- 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 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; -- ========================================================================== -- 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; 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; RETURN p_classId; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN RETURN null; END; -- NO_DATA_FOUND END; -- ========================================================================== FUNCTION getPropertySetId( p_classId NUMBER, p_propertyType NUMBER, p_definitionKey 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 LOGICALNAME LIKE '%:'||p_definitionKey||':%'; RETURN p_propertySetId; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN RETURN null; END; -- NO_DATA_FOUND 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; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN RETURN null; END; -- NO_DATA_FOUND 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; -- ========================================================================== -- CMPPropertyGroupDefinition -- ========================================================================== FUNCTION newCMPPropertyGroupDefinition( 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 ,propertySetId IN NUMBER DEFAULT 0 ) RETURN CMPPropertyGroupDefinition -- ========================================================================== IS pdef CMPPropertyGroupDefinition; BEGIN pdef.AGGREGATEDSIGNATURE := SYSDATE; pdef.CREATIONTIMESTAMP := SYSDATE; pdef.ELEMENTID := elementId; pdef.LOGICALNAME := definitionKey; pdef.METADATASIGNATURE := metadataSignature; pdef.NAME := name; pdef.UOID := uoid; pdef.UPDATETIMESTAMP := SYSDATE; pdef.FIRSTCLASSOBJECT := firstClassObjectId; pdef.POSITION := position; pdef.PROPERTYGROUPOWNER := propertySetId; RETURN pdef; END newCMPPropertyGroupDefinition; -- ========================================================================== 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; -- ########################################################################## -- ########################################################################## 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; pgrp CMPPropertyGroupDefinition := null; strDatatypeId NUMBER; intDatatypeId NUMBER; --================================================================= -- Custom functions --================================================================= -- ========================================================================== FUNCTION getIntDatatypeId(definitionKey IN VARCHAR2) RETURN NUMBER -- ========================================================================== IS p_datatypeId NUMBER(9,0); pdef CMPPropertyDefinition := null; platformId NUMBER; classId NUMBER; propertySetId NUMBER; propertyGroupId NUMBER; BEGIN platformId := getPlatformIdByName('ORACLE'); classId := getClassIdByName(platformId,'oracle.wh.repos.impl.relational.CMPWBSequence'); propertySetId := getPropertySetId(classId, PROPERTYTYPE_PHYSICAL, '8i.SEQUENCE'); propertyGroupId := getPropertyGroupId(propertySetId, '8i.SEQUENCE.SEQUENCEPARAMS'); pdef := getCMPPropertyDefinition( definitionKey, --definitionKey null, --name null, --propertyTypeId null, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); RETURN pdef.TYPE; END; -- ======================================================= BEGIN -- -- ======================================================= -- up.xml-> platformId := getPlatformIdByName('DB2UDB'); -- up.xml-> name='oracle.wh.repos.impl.relational.CMPWBSequence' -- up.xml-> > classId := getClassIdByName(platformId,'oracle.wh.repos.impl.relational.CMPWBSequence'); -- up.xml-> Position='0' -- up.xml-> definitionKey='DB2.SEQUENCES' -- up.xml-> name='DEFAULT' -- up.xml-> propertyType='physical' -- up.xml-> > propertySetId := getPropertySetId(classId, PROPERTYTYPE_PHYSICAL, 'DB2.SEQUENCES'); -- up.xml-> Position='1' -- up.xml-> definitionKey='8i.SEQUENCE.SEQUENCEPARAMS' -- up.xml-> name='SEQUENCE_PARAMETERS' -- up.xml-> > propertyGroupId := getPropertyGroupId(propertySetId, '8i.SEQUENCE.SEQUENCEPARAMS'); if (propertyGroupId is null) then put('Add property group 8i.SEQUENCE.SEQUENCEPARAMS' || EOL); pgrp := newCMPPropertyGroupDefinition( '8i.SEQUENCE.SEQUENCEPARAMS', --definitionKey getNextElementId, --elementId 'SEQUENCE_PARAMETERS', --name 'e2dbcf1db5495bb19b82b4ba09d94755', --metadataSignature RAWTOHEX(SYS_GUID()), --UOID classId, --firstClassObjectId 1, --position propertySetId --propertySetId ); INSERT INTO CMPPropertyGroupDefinition_r VALUES pgrp; propertyGroupId := pgrp.elementId; else put('Exist property group ->' || propertyGroupId || EOL); end if; -- up.xml-> Position='0' -- up.xml-> dataType='int' -- up.xml-> definitionKey='8i.SEQUENCE.INCREMENTBY' -- up.xml-> description='Sequence Incremented By' -- up.xml-> exportControl='public' -- up.xml-> initValue='1' -- up.xml-> maxValue='2147483647' -- up.xml-> minValue='-2147483648' -- up.xml-> name='INCREMENT_BY' -- up.xml-> validator='oracle.wh.service.impl.integrator.validator.NonZeroValidator' -- up.xml-> > put('CMPWBSequence.up.xml -> 8i.SEQUENCE.INCREMENTBY'); intDatatypeId := getIntDatatypeId('8i.SEQUENCE.INCREMENTBY'); pdef := getCMPPropertyDefinition( '8i.SEQUENCE.INCREMENTBY', --definitionKey null, --name null, --propertyTypeId intDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); if (pdef.ELEMENTID is null) then begin put('Add attribute 8i.SEQUENCE.INCREMENTBY' || EOL); pdef := newCMPPropertyDefinition( '8i.SEQUENCE.INCREMENTBY', --definitionKey getNextElementId, --elementId 'INCREMENT_BY', --name '9aa9bcd3b78e9478ae62099f92b25eda', --metadataSignature RAWTOHEX(SYS_GUID()), --UOID classId, --firstClassObjectId 0, --position 1, --contributeToSignature PROPERTYTYPE_PHYSICAL, --propertyTypeId intDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); INSERT INTO CMPPropertyDefinition_r VALUES pdef; end; else put('Exist record ->' || toString(pdef) || EOL); end if; -- up.xml-> Position='1' -- up.xml-> dataType='int' -- up.xml-> definitionKey='8i.SEQUENCE.STARTWITH' -- up.xml-> description='Sequence Starts With' -- up.xml-> exportControl='public' -- up.xml-> initValue='1' -- up.xml-> maxValue='2147483647' -- up.xml-> minValue='-2147483648' -- up.xml-> name='START_WITH' -- up.xml-> > put('CMPWBSequence.up.xml -> 8i.SEQUENCE.STARTWITH'); intDatatypeId := getIntDatatypeId('8i.SEQUENCE.STARTWITH'); pdef := getCMPPropertyDefinition( '8i.SEQUENCE.STARTWITH', --definitionKey null, --name null, --propertyTypeId intDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); if (pdef.ELEMENTID is null) then begin put('Add attribute 8i.SEQUENCE.STARTWITH' || EOL); pdef := newCMPPropertyDefinition( '8i.SEQUENCE.STARTWITH', --definitionKey getNextElementId, --elementId 'START_WITH', --name 'f1c53e5101e6ea16d8c6a6dc60f5a0b1', --metadataSignature RAWTOHEX(SYS_GUID()), --UOID classId, --firstClassObjectId 1, --position 1, --contributeToSignature PROPERTYTYPE_PHYSICAL, --propertyTypeId intDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); INSERT INTO CMPPropertyDefinition_r VALUES pdef; end; else put('Exist record ->' || toString(pdef) || EOL); end if; strDatatypeId := getStringDatatypeId; -- up.xml-> Position='2' -- up.xml-> dataType='String' -- up.xml-> definitionKey='8i.SEQUENCE.MINVALUE' -- up.xml-> description='Sequence min value' -- up.xml-> exportControl='public' -- up.xml-> initValue='' -- up.xml-> name='MINVALUE' -- up.xml-> > put('CMPWBSequence.up.xml -> 8i.SEQUENCE.MINVALUE'); pdef := getCMPPropertyDefinition( '8i.SEQUENCE.MINVALUE', --definitionKey null, --name null, --propertyTypeId strDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); if (pdef.ELEMENTID is null) then begin put('Add attribute 8i.SEQUENCE.MINVALUE' || EOL); pdef := newCMPPropertyDefinition( '8i.SEQUENCE.MINVALUE', --definitionKey getNextElementId, --elementId 'MINVALUE', --name 'fa038dc863cab1d317335a22d4f80711', --metadataSignature RAWTOHEX(SYS_GUID()), --UOID classId, --firstClassObjectId 2, --position 1, --contributeToSignature PROPERTYTYPE_PHYSICAL, --propertyTypeId strDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); INSERT INTO CMPPropertyDefinition_r VALUES pdef; end; else put('Exist record ->' || toString(pdef) || EOL); end if; -- up.xml-> Position='3' -- up.xml-> dataType='String' -- up.xml-> definitionKey='8i.SEQUENCE.MAXVALUE' -- up.xml-> description='Sequence max value' -- up.xml-> exportControl='public' -- up.xml-> initValue='' -- up.xml-> name='MAXVALUE' -- up.xml-> > put('CMPWBSequence.up.xml -> 8i.SEQUENCE.MAXVALUE'); pdef := getCMPPropertyDefinition( '8i.SEQUENCE.MAXVALUE', --definitionKey null, --name null, --propertyTypeId strDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); if (pdef.ELEMENTID is null) then begin put('Add attribute 8i.SEQUENCE.MAXVALUE' || EOL); pdef := newCMPPropertyDefinition( '8i.SEQUENCE.MAXVALUE', --definitionKey getNextElementId, --elementId 'MAXVALUE', --name 'f96f394c4d0f9692472e85b8b76d2250', --metadataSignature RAWTOHEX(SYS_GUID()), --UOID classId, --firstClassObjectId 3, --position 1, --contributeToSignature PROPERTYTYPE_PHYSICAL, --propertyTypeId strDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); INSERT INTO CMPPropertyDefinition_r VALUES pdef; end; else put('Exist record ->' || toString(pdef) || EOL); end if; -- up.xml-> Position='4' -- up.xml-> dataType='String' -- up.xml-> definitionKey='8i.SEQUENCE.CYCLE' -- up.xml-> description='Sequence cycle' -- up.xml-> exportControl='public' -- up.xml-> initValue='' -- up.xml-> name='CYCLE' -- up.xml-> > put('CMPWBSequence.up.xml -> 8i.SEQUENCE.CYCLE'); pdef := getCMPPropertyDefinition( '8i.SEQUENCE.CYCLE', --definitionKey null, --name null, --propertyTypeId strDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); if (pdef.ELEMENTID is null) then begin put('Add attribute 8i.SEQUENCE.CYCLE' || EOL); pdef := newCMPPropertyDefinition( '8i.SEQUENCE.CYCLE', --definitionKey getNextElementId, --elementId 'CYCLE', --name '9cc06a0587f98839859052408bf1b50e', --metadataSignature RAWTOHEX(SYS_GUID()), --UOID classId, --firstClassObjectId 4, --position 1, --contributeToSignature PROPERTYTYPE_PHYSICAL, --propertyTypeId strDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); INSERT INTO CMPPropertyDefinition_r VALUES pdef; end; else put('Exist record ->' || toString(pdef) || EOL); end if; -- up.xml-> Position='5' -- up.xml-> dataType='String' -- up.xml-> definitionKey='8i.SEQUENCE.CACHE' -- up.xml-> description='Sequence cache' -- up.xml-> exportControl='public' -- up.xml-> initValue='' -- up.xml-> name='CACHE' -- up.xml-> > put('CMPWBSequence.up.xml -> 8i.SEQUENCE.CACHE'); pdef := getCMPPropertyDefinition( '8i.SEQUENCE.CACHE', --definitionKey null, --name null, --propertyTypeId strDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); if (pdef.ELEMENTID is null) then begin put('Add attribute 8i.SEQUENCE.CACHE' || EOL); pdef := newCMPPropertyDefinition( '8i.SEQUENCE.CACHE', --definitionKey getNextElementId, --elementId 'CACHE', --name '8b7d5782d3c95ff7f80e1d9a2c0c811b', --metadataSignature RAWTOHEX(SYS_GUID()), --UOID classId, --firstClassObjectId 5, --position 1, --contributeToSignature PROPERTYTYPE_PHYSICAL, --propertyTypeId strDatatypeId, --datatypeId propertyGroupId, --propertyGroupId propertySetId --propertySetId ); INSERT INTO CMPPropertyDefinition_r VALUES pdef; end; else put('Exist record ->' || toString(pdef) || EOL); end if; -- ======================================================= END; -- Inner block for utility function/variable usage -- ======================================================= -- ########################################################################## END; -- Outer block for utility functions/variables -- ########################################################################## /