Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\reposasst\upg\property_upgrade_102to10203.sql
-- Example of how to create sql in-place upgrade patch for some up.xml -- property changes. The sql will be shown (as much as possible) in-line with -- the associated up.xml. The xml will be set off from the sql by commenting -- each line with "--up.xml->". Search on that 'symbol' for beginning of 'user' -- code. WARNING: Do not use ampersand ANYWHERE - it is interpreted as bind var -- -- Bug #'s/Properties added -- -- 5479145: add TYPE2_RECORD_GAP_INTERVAL and TYPE2_RECORD_GAP_INTERVAL_UNITS -- 5845656: add dimension operator property DEFAULT_EFFECTIVE_DATE_OF_INITIAL_RECORD -- 5845640: add dimension operator attribute property TYPE_TWO_SCD_TRIGGER -- 5955424: add INSERT_ONLY to value list for cube loading type property -- SET SERVEROUTPUT ON SET LINESIZE 500 -- ########################################################################## DECLARE -- Outer block for utility functions/variables BUGBUG: Should these be packagized (uprop_util) -- ########################################################################## SCRIPTID CONSTANT VARCHAR2(30) := 'property_upgrade_10gr2_r3'; -- 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 -- useful 'constants' PropertyTypeCORE NUMBER := 0; PropertyTypeLOGICAL NUMBER := 1; PropertyTypeCONFIG NUMBER := 2; PropertyTypeUSERDEF NUMBER := 3; -- types for lookup lists TYPE NameList IS TABLE OF VARCHAR2(32); TYPE IdForName IS TABLE OF NUMBER INDEX BY VARCHAR2(32); -- some useful lookups/'translators' from xml type names to coded ids -- will work like functions i.e., id := propertytypeIdOf('logical'); propertytypeIdOf IdForName; -- initialized below datatypeIdOf IdForName; -- initialized below -- will use original install user as create/update id for new records so -- make record to hold info -- ========================================================================== TYPE AuditInfo_rec IS RECORD ( -- ========================================================================== CREATEDBY CMPInstallation_v.CREATEDBY%TYPE DEFAULT null ,CREATIONTIMESTAMP CMPInstallation_v.CREATIONTIMESTAMP%TYPE DEFAULT null ,UPDATEDBY CMPInstallation_v.UPDATEDBY%TYPE DEFAULT null ,UPDATETIMESTAMP CMPInstallation_v.UPDATETIMESTAMP%TYPE DEFAULT null ); g_auditInfo AuditInfo_rec; -- Record definitions for the various views -- Handy sql to make record definitions (some hand editing required (in sqlworksheet anyway) -- search/replace 'PropertyDefinition' with type you need -- after cut/paste results here, delete headings, echo, etc -- CHECK THE RELATED JAVA CLASSES to pick up additional defaults if any. -- repos/impl/domain for domain and XxxType -- repos/impl/metaModel for XxxDefinition /* -- rectype_from_user_tab_columns.sql SET SERVEROUTPUT ON; SET LINESIZE 200; SET ECHO OFF; SET HEADING OFF; SET FEEDBACK OFF; column ColumnName format a10; column ColumnType format a10; column ColumnDflt format a10; column Type format a4; select ('-- ==========================================================================' || CHR(10) || ' TYPE' || ' PropertyDefinition IS RECORD (' || CHR(10) || '-- ==========================================================================') "a" from dual; -- BUGBUG: Depending on LOADINGPARENT to be 1st column. select ( DECODE(column_name ,'LOADINGPARENT', ' ' ,' ,' ) || column_name ) "ColumnName" ,('PropertyDefinition' || '_v.' || column_name || '%TYPE') "ColumnType" ,('DEFAULT ' || DECODE(column_name ,'SEEDED', ' 1' ,'CLASSNAME', '''PropertyDefinition''' ,DECODE(data_type ,'NUMBER', ' 0' ,'null' ) ) ) "ColumnDflt" ,('-- ' || data_type || DECODE(data_type ,'NUMBER', ('(' || data_precision || ',' || data_scale || ')') ,'DATE', '' ,('(' || data_length || ')') ) ) "Type" from user_tab_columns where table_name = UPPER('PropertyDefinition' || '_v') order by column_id; */ -- ========================================================================== TYPE PropertyDefinition IS RECORD ( -- ========================================================================== LOADINGPARENT PropertyDefinition_v.LOADINGPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,DELETIONPARENT PropertyDefinition_v.DELETIONPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,AGGREGATEDSIGNATURE PropertyDefinition_v.AGGREGATEDSIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,CLASSNAME PropertyDefinition_v.CLASSNAME%TYPE DEFAULT 'PropertyDefinition' -- VARCHAR2(255) ,COMPLETED PropertyDefinition_v.COMPLETED%TYPE DEFAULT null -- CHAR(1) ,CREATEDBY PropertyDefinition_v.CREATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,CREATIONTIMESTAMP PropertyDefinition_v.CREATIONTIMESTAMP%TYPE DEFAULT null -- DATE ,CUSTOMERDELETABLE PropertyDefinition_v.CUSTOMERDELETABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMEREDITABLE PropertyDefinition_v.CUSTOMEREDITABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMERRENAMABLE PropertyDefinition_v.CUSTOMERRENAMABLE%TYPE DEFAULT null -- CHAR(1) ,DESCRIPTION PropertyDefinition_v.DESCRIPTION%TYPE DEFAULT null -- VARCHAR2(4000) ,DISABLED PropertyDefinition_v.DISABLED%TYPE DEFAULT null -- CHAR(1) ,EDITABLE PropertyDefinition_v.EDITABLE%TYPE DEFAULT null -- CHAR(1) ,ELEMENTID PropertyDefinition_v.ELEMENTID%TYPE DEFAULT 0 -- NUMBER(9,0) ,FROZEN PropertyDefinition_v.FROZEN%TYPE DEFAULT null -- CHAR(1) ,GENERATED PropertyDefinition_v.GENERATED%TYPE DEFAULT null -- CHAR(1) ,IMPORTED PropertyDefinition_v.IMPORTED%TYPE DEFAULT null -- CHAR(1) ,LOGICALNAME PropertyDefinition_v.LOGICALNAME%TYPE DEFAULT null -- VARCHAR2(1000) ,METADATASIGNATURE PropertyDefinition_v.METADATASIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,NAME PropertyDefinition_v.NAME%TYPE DEFAULT null -- VARCHAR2(255) ,NOTE PropertyDefinition_v.NOTE%TYPE DEFAULT null -- VARCHAR2(4000) ,NOTM PropertyDefinition_v.NOTM%TYPE DEFAULT 0 -- NUMBER(9,0) ,PERSISTENT PropertyDefinition_v.PERSISTENT%TYPE DEFAULT null -- CHAR(1) ,SEEDED PropertyDefinition_v.SEEDED%TYPE DEFAULT 1 -- CHAR(1) ,UOID PropertyDefinition_v.UOID%TYPE DEFAULT null -- VARCHAR2(255) ,UPDATEDBY PropertyDefinition_v.UPDATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,UPDATETIMESTAMP PropertyDefinition_v.UPDATETIMESTAMP%TYPE DEFAULT null -- DATE ,VERSION PropertyDefinition_v.VERSION%TYPE DEFAULT null -- VARCHAR2(40) ,ISLOADINGPARENT PropertyDefinition_v.ISLOADINGPARENT%TYPE DEFAULT null -- CHAR(1) ,PROPERTYTYPE PropertyDefinition_v.PROPERTYTYPE%TYPE DEFAULT 0 -- NUMBER(9,0) ,DEFINITIONKEY PropertyDefinition_v.DEFINITIONKEY%TYPE DEFAULT null -- VARCHAR2(255) ,EXPORTCONTROL PropertyDefinition_v.EXPORTCONTROL%TYPE DEFAULT 0 -- NUMBER(9,0) ,OVERRIDE PropertyDefinition_v.OVERRIDE%TYPE DEFAULT null -- CHAR(1) ,POSITION PropertyDefinition_v.POSITION%TYPE DEFAULT 0 -- NUMBER(9,0) ,CLASSDEFINITION PropertyDefinition_v.CLASSDEFINITION%TYPE DEFAULT 0 -- NUMBER(9,0) ,DATATYPE PropertyDefinition_v.DATATYPE%TYPE DEFAULT 0 -- NUMBER(9,0) ,ISROLE PropertyDefinition_v.ISROLE%TYPE DEFAULT null -- CHAR(1) ,ROLEDEFINITION PropertyDefinition_v.ROLEDEFINITION%TYPE DEFAULT 0 -- NUMBER(9,0) ,TRANSLATABLE PropertyDefinition_v.TRANSLATABLE%TYPE DEFAULT null -- CHAR(1) ,ISPASSWORD PropertyDefinition_v.ISPASSWORD%TYPE DEFAULT null -- CHAR(1) ,HIDDEN PropertyDefinition_v.HIDDEN%TYPE DEFAULT null -- CHAR(1) ,PROPERTYREADONLY PropertyDefinition_v.PROPERTYREADONLY%TYPE DEFAULT null -- CHAR(1) ,NAMECHECKED PropertyDefinition_v.NAMECHECKED%TYPE DEFAULT null -- CHAR(1) ,USERCHOICEALLOWED PropertyDefinition_v.USERCHOICEALLOWED%TYPE DEFAULT null -- CHAR(1) ,DYNAMICDEFAULT PropertyDefinition_v.DYNAMICDEFAULT%TYPE DEFAULT null -- VARCHAR2(255) ,INPLACEEDITOR PropertyDefinition_v.INPLACEEDITOR%TYPE DEFAULT null -- VARCHAR2(255) ,CUSTOMEDITOR PropertyDefinition_v.CUSTOMEDITOR%TYPE DEFAULT null -- VARCHAR2(255) ,PROPERTYGROUPDEFINITION PropertyDefinition_v.PROPERTYGROUPDEFINITION%TYPE DEFAULT 0 -- NUMBER(9,0) ); -- ========================================================================== TYPE PropertyUsageDefinition IS RECORD ( -- ========================================================================== LOADINGPARENT PropertyUsageDefinition_v.LOADINGPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,DELETIONPARENT PropertyUsageDefinition_v.DELETIONPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,AGGREGATEDSIGNATURE PropertyUsageDefinition_v.AGGREGATEDSIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,CLASSNAME PropertyUsageDefinition_v.CLASSNAME%TYPE DEFAULT 'PropertyUsageDefinition' -- VARCHAR2(255) ,COMPLETED PropertyUsageDefinition_v.COMPLETED%TYPE DEFAULT null -- CHAR(1) ,CREATEDBY PropertyUsageDefinition_v.CREATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,CREATIONTIMESTAMP PropertyUsageDefinition_v.CREATIONTIMESTAMP%TYPE DEFAULT null -- DATE ,CUSTOMERDELETABLE PropertyUsageDefinition_v.CUSTOMERDELETABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMEREDITABLE PropertyUsageDefinition_v.CUSTOMEREDITABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMERRENAMABLE PropertyUsageDefinition_v.CUSTOMERRENAMABLE%TYPE DEFAULT null -- CHAR(1) ,DESCRIPTION PropertyUsageDefinition_v.DESCRIPTION%TYPE DEFAULT null -- VARCHAR2(4000) ,DISABLED PropertyUsageDefinition_v.DISABLED%TYPE DEFAULT null -- CHAR(1) ,EDITABLE PropertyUsageDefinition_v.EDITABLE%TYPE DEFAULT null -- CHAR(1) ,ELEMENTID PropertyUsageDefinition_v.ELEMENTID%TYPE DEFAULT 0 -- NUMBER(9,0) ,FROZEN PropertyUsageDefinition_v.FROZEN%TYPE DEFAULT null -- CHAR(1) ,GENERATED PropertyUsageDefinition_v.GENERATED%TYPE DEFAULT null -- CHAR(1) ,IMPORTED PropertyUsageDefinition_v.IMPORTED%TYPE DEFAULT null -- CHAR(1) ,LOGICALNAME PropertyUsageDefinition_v.LOGICALNAME%TYPE DEFAULT null -- VARCHAR2(1000) ,METADATASIGNATURE PropertyUsageDefinition_v.METADATASIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,NAME PropertyUsageDefinition_v.NAME%TYPE DEFAULT null -- VARCHAR2(255) ,NOTE PropertyUsageDefinition_v.NOTE%TYPE DEFAULT null -- VARCHAR2(4000) ,NOTM PropertyUsageDefinition_v.NOTM%TYPE DEFAULT 0 -- NUMBER(9,0) ,PERSISTENT PropertyUsageDefinition_v.PERSISTENT%TYPE DEFAULT null -- CHAR(1) ,SEEDED PropertyUsageDefinition_v.SEEDED%TYPE DEFAULT 1 -- CHAR(1) ,UOID PropertyUsageDefinition_v.UOID%TYPE DEFAULT null -- VARCHAR2(255) ,UPDATEDBY PropertyUsageDefinition_v.UPDATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,UPDATETIMESTAMP PropertyUsageDefinition_v.UPDATETIMESTAMP%TYPE DEFAULT null -- DATE ,VERSION PropertyUsageDefinition_v.VERSION%TYPE DEFAULT null -- VARCHAR2(40) ,ISLOADINGPARENT PropertyUsageDefinition_v.ISLOADINGPARENT%TYPE DEFAULT null -- CHAR(1) ,PROPERTYSETDEFINITION PropertyUsageDefinition_v.PROPERTYSETDEFINITION%TYPE DEFAULT 0 -- NUMBER(9,0) ,PROPERTYDEFINITION PropertyUsageDefinition_v.PROPERTYDEFINITION%TYPE DEFAULT 0 -- NUMBER(9,0) ,DEFINITIONKEY PropertyUsageDefinition_v.DEFINITIONKEY%TYPE DEFAULT null -- VARCHAR2(255) ); -- ========================================================================== TYPE WBPrimitiveProperty IS RECORD ( -- ========================================================================== LOADINGPARENT WBPrimitiveProperty_v.LOADINGPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,DELETIONPARENT WBPrimitiveProperty_v.DELETIONPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,AGGREGATEDSIGNATURE WBPrimitiveProperty_v.AGGREGATEDSIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,CLASSNAME WBPrimitiveProperty_v.CLASSNAME%TYPE DEFAULT 'WBPrimitiveProperty' -- VARCHAR2(255) ,COMPLETED WBPrimitiveProperty_v.COMPLETED%TYPE DEFAULT null -- CHAR(1) ,CREATEDBY WBPrimitiveProperty_v.CREATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,CREATIONTIMESTAMP WBPrimitiveProperty_v.CREATIONTIMESTAMP%TYPE DEFAULT null -- DATE ,CUSTOMERDELETABLE WBPrimitiveProperty_v.CUSTOMERDELETABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMEREDITABLE WBPrimitiveProperty_v.CUSTOMEREDITABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMERRENAMABLE WBPrimitiveProperty_v.CUSTOMERRENAMABLE%TYPE DEFAULT null -- CHAR(1) ,DESCRIPTION WBPrimitiveProperty_v.DESCRIPTION%TYPE DEFAULT null -- VARCHAR2(4000) ,DISABLED WBPrimitiveProperty_v.DISABLED%TYPE DEFAULT null -- CHAR(1) ,EDITABLE WBPrimitiveProperty_v.EDITABLE%TYPE DEFAULT null -- CHAR(1) ,ELEMENTID WBPrimitiveProperty_v.ELEMENTID%TYPE DEFAULT 0 -- NUMBER(9,0) ,FROZEN WBPrimitiveProperty_v.FROZEN%TYPE DEFAULT null -- CHAR(1) ,GENERATED WBPrimitiveProperty_v.GENERATED%TYPE DEFAULT null -- CHAR(1) ,IMPORTED WBPrimitiveProperty_v.IMPORTED%TYPE DEFAULT null -- CHAR(1) ,LOGICALNAME WBPrimitiveProperty_v.LOGICALNAME%TYPE DEFAULT null -- VARCHAR2(1000) ,METADATASIGNATURE WBPrimitiveProperty_v.METADATASIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,NAME WBPrimitiveProperty_v.NAME%TYPE DEFAULT null -- VARCHAR2(255) ,NOTE WBPrimitiveProperty_v.NOTE%TYPE DEFAULT null -- VARCHAR2(4000) ,NOTM WBPrimitiveProperty_v.NOTM%TYPE DEFAULT 0 -- NUMBER(9,0) ,PERSISTENT WBPrimitiveProperty_v.PERSISTENT%TYPE DEFAULT null -- CHAR(1) ,SEEDED WBPrimitiveProperty_v.SEEDED%TYPE DEFAULT 1 -- CHAR(1) ,UOID WBPrimitiveProperty_v.UOID%TYPE DEFAULT null -- VARCHAR2(255) ,UPDATEDBY WBPrimitiveProperty_v.UPDATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,UPDATETIMESTAMP WBPrimitiveProperty_v.UPDATETIMESTAMP%TYPE DEFAULT null -- DATE ,VERSION WBPrimitiveProperty_v.VERSION%TYPE DEFAULT null -- VARCHAR2(40) ,ISLOADINGPARENT WBPrimitiveProperty_v.ISLOADINGPARENT%TYPE DEFAULT null -- CHAR(1) ,OWNER WBPrimitiveProperty_v.OWNER%TYPE DEFAULT 0 -- NUMBER(9,0) ,DATATYPE WBPrimitiveProperty_v.DATATYPE%TYPE DEFAULT 0 -- NUMBER(9,0) ,POSITION WBPrimitiveProperty_v.POSITION%TYPE DEFAULT 0 -- NUMBER(9,0) ,HASNAME WBPrimitiveProperty_v.HASNAME%TYPE DEFAULT null -- CHAR(1) ,HASPOSITION WBPrimitiveProperty_v.HASPOSITION%TYPE DEFAULT null -- CHAR(1) ,VALUE WBPrimitiveProperty_v.VALUE%TYPE DEFAULT null -- VARCHAR2(4000) ); -- ========================================================================== TYPE WBDomain IS RECORD ( -- ========================================================================== LOADINGPARENT WBDomain_v.LOADINGPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,DELETIONPARENT WBDomain_v.DELETIONPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,AGGREGATEDSIGNATURE WBDomain_v.AGGREGATEDSIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,CLASSNAME WBDomain_v.CLASSNAME%TYPE DEFAULT 'WBDomain' -- VARCHAR2(255) ,COMPLETED WBDomain_v.COMPLETED%TYPE DEFAULT null -- CHAR(1) ,CREATEDBY WBDomain_v.CREATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,CREATIONTIMESTAMP WBDomain_v.CREATIONTIMESTAMP%TYPE DEFAULT null -- DATE ,CUSTOMERDELETABLE WBDomain_v.CUSTOMERDELETABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMEREDITABLE WBDomain_v.CUSTOMEREDITABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMERRENAMABLE WBDomain_v.CUSTOMERRENAMABLE%TYPE DEFAULT null -- CHAR(1) ,DESCRIPTION WBDomain_v.DESCRIPTION%TYPE DEFAULT null -- VARCHAR2(4000) ,DISABLED WBDomain_v.DISABLED%TYPE DEFAULT null -- CHAR(1) ,EDITABLE WBDomain_v.EDITABLE%TYPE DEFAULT null -- CHAR(1) ,ELEMENTID WBDomain_v.ELEMENTID%TYPE DEFAULT 0 -- NUMBER(9,0) ,FROZEN WBDomain_v.FROZEN%TYPE DEFAULT null -- CHAR(1) ,GENERATED WBDomain_v.GENERATED%TYPE DEFAULT null -- CHAR(1) ,IMPORTED WBDomain_v.IMPORTED%TYPE DEFAULT null -- CHAR(1) ,LOGICALNAME WBDomain_v.LOGICALNAME%TYPE DEFAULT null -- VARCHAR2(1000) ,METADATASIGNATURE WBDomain_v.METADATASIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,NAME WBDomain_v.NAME%TYPE DEFAULT null -- VARCHAR2(255) ,NOTE WBDomain_v.NOTE%TYPE DEFAULT null -- VARCHAR2(4000) ,NOTM WBDomain_v.NOTM%TYPE DEFAULT 0 -- NUMBER(9,0) ,PERSISTENT WBDomain_v.PERSISTENT%TYPE DEFAULT null -- CHAR(1) ,SEEDED WBDomain_v.SEEDED%TYPE DEFAULT 1 -- CHAR(1) ,UOID WBDomain_v.UOID%TYPE DEFAULT null -- VARCHAR2(255) ,UPDATEDBY WBDomain_v.UPDATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,UPDATETIMESTAMP WBDomain_v.UPDATETIMESTAMP%TYPE DEFAULT null -- DATE ,VERSION WBDomain_v.VERSION%TYPE DEFAULT null -- VARCHAR2(40) ,ISLOADINGPARENT WBDomain_v.ISLOADINGPARENT%TYPE DEFAULT null -- CHAR(1) ,COMPARATOR WBDomain_v.COMPARATOR%TYPE DEFAULT 'PropertyNameComparator' -- VARCHAR2(255) ,DATATYPE WBDomain_v.DATATYPE%TYPE DEFAULT 0 -- NUMBER(9,0) ); -- ========================================================================== TYPE WBRange IS RECORD ( -- ========================================================================== LOADINGPARENT WBRange_v.LOADINGPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,DELETIONPARENT WBRange_v.DELETIONPARENT%TYPE DEFAULT 0 -- NUMBER(9,0) ,AGGREGATEDSIGNATURE WBRange_v.AGGREGATEDSIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,CLASSNAME WBRange_v.CLASSNAME%TYPE DEFAULT 'WBRange' -- VARCHAR2(255) ,COMPLETED WBRange_v.COMPLETED%TYPE DEFAULT null -- CHAR(1) ,CREATEDBY WBRange_v.CREATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,CREATIONTIMESTAMP WBRange_v.CREATIONTIMESTAMP%TYPE DEFAULT null -- DATE ,CUSTOMERDELETABLE WBRange_v.CUSTOMERDELETABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMEREDITABLE WBRange_v.CUSTOMEREDITABLE%TYPE DEFAULT null -- CHAR(1) ,CUSTOMERRENAMABLE WBRange_v.CUSTOMERRENAMABLE%TYPE DEFAULT null -- CHAR(1) ,DESCRIPTION WBRange_v.DESCRIPTION%TYPE DEFAULT null -- VARCHAR2(4000) ,DISABLED WBRange_v.DISABLED%TYPE DEFAULT null -- CHAR(1) ,EDITABLE WBRange_v.EDITABLE%TYPE DEFAULT null -- CHAR(1) ,ELEMENTID WBRange_v.ELEMENTID%TYPE DEFAULT 0 -- NUMBER(9,0) ,FROZEN WBRange_v.FROZEN%TYPE DEFAULT null -- CHAR(1) ,GENERATED WBRange_v.GENERATED%TYPE DEFAULT null -- CHAR(1) ,IMPORTED WBRange_v.IMPORTED%TYPE DEFAULT null -- CHAR(1) ,LOGICALNAME WBRange_v.LOGICALNAME%TYPE DEFAULT null -- VARCHAR2(1000) ,METADATASIGNATURE WBRange_v.METADATASIGNATURE%TYPE DEFAULT null -- VARCHAR2(255) ,NAME WBRange_v.NAME%TYPE DEFAULT null -- VARCHAR2(255) ,NOTE WBRange_v.NOTE%TYPE DEFAULT null -- VARCHAR2(4000) ,NOTM WBRange_v.NOTM%TYPE DEFAULT 0 -- NUMBER(9,0) ,PERSISTENT WBRange_v.PERSISTENT%TYPE DEFAULT null -- CHAR(1) ,SEEDED WBRange_v.SEEDED%TYPE DEFAULT 1 -- CHAR(1) ,UOID WBRange_v.UOID%TYPE DEFAULT null -- VARCHAR2(255) ,UPDATEDBY WBRange_v.UPDATEDBY%TYPE DEFAULT null -- VARCHAR2(40) ,UPDATETIMESTAMP WBRange_v.UPDATETIMESTAMP%TYPE DEFAULT null -- DATE ,VERSION WBRange_v.VERSION%TYPE DEFAULT null -- VARCHAR2(40) ,ISLOADINGPARENT WBRange_v.ISLOADINGPARENT%TYPE DEFAULT null -- CHAR(1) ,DOMAIN WBRange_v.DOMAIN%TYPE DEFAULT 0 -- NUMBER(9,0) ,MINVALUEINCLUSIVE WBRange_v.MINVALUEINCLUSIVE%TYPE DEFAULT null -- CHAR(1) ,MAXVALUEINCLUSIVE WBRange_v.MAXVALUEINCLUSIVE%TYPE DEFAULT null -- CHAR(1) ); -- ************************************************************************** -- ************************************************************************** -- 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; -- ========================================================================== -- return the id (elementid) for the (fully qualified) class name passed in FUNCTION classIdOf( p_className IN VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS l_className VARCHAR2(255) := p_className; l_classId NUMBER; BEGIN putTrace('classIdOf(' || l_className || ')'); SELECT ELEMENTID INTO l_classId FROM ClassDefinition_v WHERE NAME=l_className; putTrace(' RETURN ' || l_classId || EOL); RETURN l_classId; END classIdOf; -- ========================================================================== -- return the id (elementid) for the property set defined by the input parameters FUNCTION psetIdOf( p_classId IN NUMBER ,p_psetType IN NUMBER ,p_psetName IN VARCHAR2 DEFAULT 'DEFAULT' ) RETURN NUMBER -- ========================================================================== IS l_classId NUMBER := p_classId; l_psetType NUMBER := p_psetType; l_psetName VARCHAR2(255) := p_psetName; l_psetId NUMBER; BEGIN putTrace('psetIdOf(' || l_classId || ', ' || l_psetType || ', ' || l_psetName || ')'); SELECT ELEMENTID INTO l_psetId FROM PropertySetDefinition_v WHERE CLASSDEFINITION=l_classId AND NAME=l_psetName AND PROPERTYTYPE=l_psetType; putTrace(' RETURN ' || l_psetId || EOL); RETURN l_psetId; END psetIdOf; -- ========================================================================== -- return the id (elementid) for the property group defined by the input parameters FUNCTION groupIdOf( p_ownerId IN NUMBER ,p_groupName IN VARCHAR2 ) RETURN NUMBER -- ========================================================================== IS l_ownerId NUMBER := p_ownerId; l_groupName VARCHAR2(255) := p_groupName; l_groupId NUMBER; BEGIN putTrace('groupIdOf(' || l_ownerId || ', ' || l_groupName || ')'); SELECT ELEMENTID INTO l_groupId FROM PropertyGroupDefinition_v WHERE GROUPOWNER=l_ownerId AND NAME=l_groupName; putTrace(' RETURN ' || l_groupId || EOL); RETURN l_groupId; END groupIdOf; -- ************************************************************************** -- ************************************************************************** -- Record handling functions/procedures -- new<Recordtype> - create initialized record instance -- toString - debug 'pretty print' similar to java -- getInitFor - print pl/sql initialization for columns thqat need to be -- carried over from a live installation (METADATASIGNATURE, -- UOID, ...) -- add<RecordType> - find or create row represented by this record in the OWB -- repository. Calls getInitFor if row already exists. -- ************************************************************************** -- ************************************************************************** -- ========================================================================== -- PropertyDefinition -- ========================================================================== -- ========================================================================== -- return PropertyDefinition record with given info and audit block initialized FUNCTION newPropertyDefinition( propertyName IN VARCHAR2 DEFAULT null ,classId IN NUMBER DEFAULT 0 ) RETURN PropertyDefinition -- ========================================================================== IS pdef PropertyDefinition; BEGIN pdef.NAME := propertyName; pdef.LOGICALNAME := propertyName; pdef.CLASSDEFINITION := classId; pdef.LOADINGPARENT := classId; pdef.CREATEDBY := g_auditInfo.CREATEDBY; pdef.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP; pdef.UPDATEDBY := g_auditInfo.UPDATEDBY; pdef.UPDATETIMESTAMP := g_auditInfo.UPDATETIMESTAMP; RETURN pdef; END newPropertyDefinition; -- ========================================================================== -- similar to Object.toString() in Java - return a string representation of the record FUNCTION toString( p_pdef PropertyDefinition ) RETURN VARCHAR2 -- ========================================================================== IS l_str VARCHAR2(255) := '(PropertyDefinition)' || p_pdef.NAME || '/id=' || p_pdef.ELEMENTID; BEGIN RETURN l_str; END; -- ========================================================================== -- return pl/sql required to initialize additional PropertyDefinition fields for -- insert (Used when running against new repos to get attributes created by the -- OWB framework during during the install.) FUNCTION getInitFor( pdef IN PropertyDefinition ) RETURN VARCHAR2 -- ========================================================================== IS initsql VARCHAR2(2000); BEGIN initsql := '' || EOL || ' -- getInitFor(' || toString(pdef) || ') =' || EOL || ' pdef.METADATASIGNATURE := ''' || pdef.METADATASIGNATURE || ''';' || EOL || ' pdef.UOID := ''' || pdef.UOID || ''';' || EOL || ' pdef.POSITION := ' || pdef.POSITION || ';' || EOL ; --let caller decide putTrace(' RETURN ' || initsql); RETURN initsql; END getInitFor; -- ========================================================================== -- add record to repository (or say existing one was found) PROCEDURE addPropertyDefinition( pdef IN OUT PropertyDefinition ) -- ========================================================================== IS l_datatype NUMBER; BEGIN -- This one will trigger NO_DATA_FOUND if the property does not yet exist -- BUGBUG: Technically we should be chasing up the heirarchy looking for this -- but since we need uoid etc from real install - will find out when -- looking for it then. HEY DUMMY- why don't you use the nifty property -- definition query functions you wrote for paris!! (Oooops) -- NOT NEEDED for this one so defer doing this for now l_datatype := pdef.DATATYPE; -- save the new datatype SELECT * INTO pdef FROM PropertyDefinition_v WHERE CLASSDEFINITION=pdef.CLASSDEFINITION AND NAME=pdef.NAME; put('Found (' || toString(pdef) || ')' || getInitFor(pdef)); IF (l_datatype <> pdef.DATATYPE) THEN put ('Update DATATYPE for ' || toString(pdef) || ' to ' || l_datatype); -- Domain or datatype has changed, update it in old property def UPDATE PropertyDefinition_v SET DATATYPE = l_datatype WHERE CLASSDEFINITION=pdef.CLASSDEFINITION AND NAME=pdef.NAME; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN -- new def so need new element id pdef.ELEMENTID := getNextElementId(); put('Creating (' || toString(pdef) || ')'); INSERT INTO PropertyDefinition_v VALUES pdef; put('Requery after create...'); SELECT * INTO pdef FROM PropertyDefinition_v WHERE CLASSDEFINITION=pdef.CLASSDEFINITION AND NAME=pdef.NAME; put('Created (' || toString(pdef) || ')'); END; -- NO_DATA_FOUND END addPropertyDefinition; -- ========================================================================== -- PropertyUsageDefinition -- ========================================================================== -- ========================================================================== -- return PropertyUsageDefinition record with given info and audit block initialized FUNCTION newPropertyUsageDefinition( psetId IN NUMBER ,pdef IN PropertyDefinition ) RETURN PropertyUsageDefinition -- ========================================================================== IS puse PropertyUsageDefinition; BEGIN puse.LOADINGPARENT := pdef.LOADINGPARENT; puse.NAME := pdef.NAME; puse.LOGICALNAME := pdef.LOGICALNAME; puse.PROPERTYSETDEFINITION := psetId; puse.PROPERTYDEFINITION := pdef.ELEMENTID; puse.DESCRIPTION := pdef.DESCRIPTION; puse.CREATEDBY := g_auditInfo.CREATEDBY; puse.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP; puse.UPDATEDBY := g_auditInfo.UPDATEDBY; puse.UPDATETIMESTAMP := g_auditInfo.UPDATETIMESTAMP; RETURN puse; END newPropertyUsageDefinition; -- ========================================================================== -- similar to Object.toString() in Java - return a string representation of the record FUNCTION toString( p_puse PropertyUsageDefinition ) RETURN VARCHAR2 -- ========================================================================== IS l_str VARCHAR2(255) := '(PropertyUsageDefinition)' || p_puse.NAME || '/id=' || p_puse.ELEMENTID || '/pdef=' || p_puse.PROPERTYDEFINITION; BEGIN RETURN l_str; END; -- ========================================================================== -- return pl/sql required to initialize additional PropertyDefinition fields for -- insert (Used when running against new repos to get attributes created by the -- OWB framework during during the install.) FUNCTION getInitFor( puse IN PropertyUsageDefinition ) RETURN VARCHAR2 -- ========================================================================== IS initsql VARCHAR2(2000); BEGIN initsql := '' || EOL || ' -- getInitFor(' || toString(puse) || ') =' || EOL || ' puse.METADATASIGNATURE := ''' || puse.METADATASIGNATURE || ''';' || EOL || ' puse.UOID := ''' || puse.UOID || ''';' || EOL ; --let caller decide putTrace(' RETURN ' || initsql); RETURN initsql; END getInitFor; -- ========================================================================== -- add record to repository (or say existing one was found) PROCEDURE addPropertyUsageDefinition( puse IN OUT PropertyUsageDefinition ) -- ========================================================================== IS BEGIN -- This one will trigger NO_DATA_FOUND if the property does not yet exist SELECT * INTO puse FROM PropertyUsageDefinition_v WHERE PROPERTYSETDEFINITION=puse.PROPERTYSETDEFINITION AND PROPERTYDEFINITION=puse.PROPERTYDEFINITION; put('Found (' || toString(puse) || ')' || getInitFor(puse)); EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN -- new def so need new element id puse.ELEMENTID := getNextElementId(); put('Creating (' || toString(puse) || ')'); INSERT INTO PropertyUsageDefinition_v VALUES puse; put('Requery after create...'); SELECT * INTO puse FROM PropertyUsageDefinition_v WHERE PROPERTYSETDEFINITION=puse.PROPERTYSETDEFINITION AND PROPERTYDEFINITION=puse.PROPERTYDEFINITION; put('Created (' || toString(puse) || ')'); END; -- NO_DATA_FOUND END addPropertyUsageDefinition; -- ========================================================================== -- WBPrimitiveProperty -- ========================================================================== -- ========================================================================== -- return WBPrimitiveProperty record with given info and audit block initialized FUNCTION newWBPrimitiveProperty( classId IN NUMBER ,ownerId IN NUMBER ,datatype IN NUMBER ,propertyName IN VARCHAR2 DEFAULT NULL ,propertyValue IN VARCHAR2 DEFAULT NULL ) RETURN WBPrimitiveProperty -- ========================================================================== IS prim WBPrimitiveProperty; BEGIN prim.NAME := propertyName; prim.LOGICALNAME := propertyName; prim.OWNER := ownerId; prim.LOADINGPARENT := classId; prim.DATATYPE := datatype; prim.VALUE := propertyValue; prim.CREATEDBY := g_auditInfo.CREATEDBY; prim.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP; prim.UPDATEDBY := g_auditInfo.UPDATEDBY; prim.UPDATETIMESTAMP := g_auditInfo.UPDATETIMESTAMP; IF (propertyName IS NOT NULL) THEN prim.HASNAME := 1; ELSE prim.HASNAME := 0; END IF; RETURN prim; END newWBPrimitiveProperty; -- similar to Object.toString() in Java - return a string representation of the record FUNCTION toString( p_meta WBPrimitiveProperty ) RETURN VARCHAR2 IS l_str VARCHAR2(255) := '(WBPrimitiveProperty)' || NVL(p_meta.NAME,'NULL') || '/id=' || p_meta.ELEMENTID || '/owner=' || p_meta.OWNER || '/value=''' || p_meta.VALUE || ''''; BEGIN RETURN l_str; END; -- ========================================================================== -- return pl/sql required to initialize additional WBPrimitiveProperty fields for -- insert (Used when running against new repos to get attributes created by the -- OWB framework during during the install.) FUNCTION getInitFor( prim IN WBPrimitiveProperty ) RETURN VARCHAR2 -- ========================================================================== IS initsql VARCHAR2(2000); BEGIN initsql := '' || EOL || ' -- getInitFor(' || toString(prim) || ') =' || EOL || ' prim.METADATASIGNATURE := ''' || prim.METADATASIGNATURE || ''';' || EOL || ' prim.UOID := ''' || prim.UOID || ''';' || EOL ; -- ' prim.HASNAME := ''' || prim.HASNAME || ''';' || EOL || -- ' prim.HASPOSITION := ''' || prim.HASPOSITION || ''';' || EOL || -- ' prim.POSITION := ' || prim.POSITION || ';' || EOL ; --let caller decide putTrace(' RETURN ' || initsql); RETURN initsql; END getInitFor; -- ========================================================================== -- add record to repository (or say existing one was found) PROCEDURE addWBPrimitiveProperty( prim IN OUT WBPrimitiveProperty ) -- ========================================================================== IS l_inputValue VARCHAR(4000) := prim.VALUE; BEGIN -- This one will trigger NO_DATA_FOUND if the property does not yet exist -- BUGBUG: Technically we should be chasing up the heirarchy looking for this -- but since we need uoid etc from real install - will find out when -- looking for it then. HEY DUMMY- why don't you use the nifty property -- definition query functions you wrote for paris!! (Oooops) -- NOT NEEDED for this one so defer doing this for now IF prim.NAME IS NULL THEN put('Looking for primitive with owner = ' || prim.OWNER || ' and value = ' || prim.VALUE || ' and name = ' || prim.NAME); SELECT * INTO prim FROM WBPrimitiveProperty_v WHERE OWNER=prim.OWNER AND VALUE=prim.VALUE AND NAME IS NULL AND ROWNUM = 1; -- indent values ... put('...Found (' || toString(prim) || ')'); put('...Update HASNAME for (' || toString(prim) || ')'); UPDATE WBPrimitiveProperty_v SET HASNAME = 0 WHERE OWNER = prim.OWNER AND VALUE = prim.VALUE AND NAME IS NULL; IF prim.UOID IS NULL THEN put('...Update UOID for (' || toString(prim) || ')'); UPDATE WBPrimitiveProperty_v SET UOID = RAWTOHEX(SYS_GUID()) WHERE OWNER = prim.OWNER AND VALUE = prim.VALUE AND NAME IS NULL; END IF; ELSE put('Looking for primitive with owner = ' || prim.OWNER || ' and name = ' || prim.NAME); SELECT * INTO prim FROM WBPrimitiveProperty_v WHERE OWNER=prim.OWNER AND NAME=prim.NAME AND ROWNUM = 1; put('Found (' || toString(prim) || ')' || getInitFor(prim)); put('...Update HASNAME for (' || toString(prim) || ')'); UPDATE WBPrimitiveProperty_v SET HASNAME = 1 WHERE OWNER = prim.OWNER AND NAME = prim.NAME; put('...Update VALUE for (' || toString(prim) || ')'); put('new value = ' || l_inputValue); UPDATE WBPrimitiveProperty_v SET VALUE = l_inputValue WHERE OWNER = prim.OWNER AND NAME = prim.NAME; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN -- new def so need new element id prim.ELEMENTID := getNextElementId(); -- Create new UOID, for case where we don't have one already (value list) if (prim.UOID IS NULL) THEN SELECT RAWTOHEX(SYS_GUID()) INTO prim.UOID FROM DUAL; END IF; put('Creating (' || toString(prim) || ')'); INSERT INTO WBPrimitiveProperty_v VALUES prim; put('Requery after create...'); IF prim.NAME IS NULL THEN SELECT * INTO prim FROM WBPrimitiveProperty_v WHERE OWNER=prim.OWNER AND VALUE=prim.VALUE AND NAME IS NULL; -- indent values ... put('...Created (' || toString(prim) || ')'); ELSE SELECT * INTO prim FROM WBPrimitiveProperty_v WHERE OWNER=prim.OWNER AND NAME=prim.NAME; put('Created (' || toString(prim) || ')'); END IF; END; -- NO_DATA_FOUND END addWBPrimitiveProperty; -- ========================================================================== -- WBDomain -- ========================================================================== -- ========================================================================== -- return WBDomain record with given info and audit block initialized FUNCTION newWBDomain( name IN VARCHAR2 ,datatype IN NUMBER ) RETURN WBDomain -- ========================================================================== IS domn WBDomain; BEGIN domn.NAME := name; domn.LOGICALNAME := name; domn.DATATYPE := datatype; domn.CREATEDBY := g_auditInfo.CREATEDBY; domn.CREATIONTIMESTAMP := g_auditInfo.CREATIONTIMESTAMP; domn.UPDATEDBY := g_auditInfo.UPDATEDBY; domn.UPDATETIMESTAMP := g_auditInfo.UPDATETIMESTAMP; RETURN domn; END newWBDomain; -- similar to Object.toString() in Java - return a string representation of the record FUNCTION toString( domn WBDomain ) RETURN VARCHAR2 IS l_str VARCHAR2(255) := '(WBDomain)' || domn.NAME || '/id=' || domn.ELEMENTID; BEGIN RETURN l_str; END; -- ========================================================================== -- return pl/sql required to initialize additional WBDomain fields for -- insert (Used when running against new repos to get attributes created by the -- OWB framework during during the install.) FUNCTION getInitFor( domn IN WBDomain ) RETURN VARCHAR2 -- ========================================================================== IS initsql VARCHAR2(2000); BEGIN initsql := '' || EOL || ' -- getInitFor(' || toString(domn) || ') =' || EOL || ' domn.METADATASIGNATURE := ''' || domn.METADATASIGNATURE || ''';' || EOL || ' domn.UOID := ''' || domn.UOID || ''';' || EOL ; -- ' domn.HASNAME := ''' || domn.HASNAME || ''';' || EOL || -- ' domn.HASPOSITION := ''' || domn.HASPOSITION || ''';' || EOL || -- ' domn.POSITION := ' || domn.POSITION || ';' || EOL ; --let caller decide putTrace(' RETURN ' || initsql); RETURN initsql; END getInitFor; -- ========================================================================== -- add record to repository (or say existing one was found) -- also find/add choices (in dlst) as we don't need to keep UIOD, etc PROCEDURE addWBDomain( domn IN OUT WBDomain ,dlst IN NameList ) -- ========================================================================== IS prim WBPrimitiveProperty; BEGIN BEGIN -- need to wrap exception catching block -- This one will trigger NO_DATA_FOUND if the property does not yet exist -- BUGBUG: Technically we should be chasing up the heirarchy looking for this -- but since we need uoid etc from real install - will find out when -- looking for it then. HEY DUMMY- why don't you use the nifty property -- definition query functions you wrote for paris!! (Oooops) -- NOT NEEDED for this one so defer doing this for now SELECT * INTO domn FROM WBDomain_v WHERE NAME=domn.NAME; put('Found (' || toString(domn) || ')' || getInitFor(domn)); EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN -- new def so need new element id domn.ELEMENTID := getNextElementId(); put('Creating (' || toString(domn) || ')'); INSERT INTO WBDomain_v VALUES domn; put('Requery after create...'); SELECT * INTO domn FROM WBDomain_v WHERE NAME=domn.NAME; put('Created (' || toString(domn) || ')'); END; -- NO_DATA_FOUND END; -- dlst is list of choices - saved as unnamed WBPrimitiveProperty children IF dlst IS NULL THEN put('addWBDomain(' || toString(domn) || ', NULL) - no choices to process.' || EOL); ELSE put('addWBDomain(' || toString(domn) || ', dlst) processing ' || dlst.COUNT || ' choices...'); FOR i IN dlst.FIRST .. dlst.LAST LOOP prim := newWBPrimitiveProperty( classId => domn.ELEMENTID ,ownerId => domn.ELEMENTID ,datatype => domn.DATATYPE ,propertyName => NULL ,propertyValue => dlst(i) ); addWBPrimitiveProperty(prim); END LOOP; put('addWBDomain(' || toString(domn) || ', dlst) processing ' || dlst.COUNT || ' choices finished.' || EOL); END IF; END addWBDomain; -- ########################################################################## -- ########################################################################## BEGIN -- Outer block - utility functions/variables now defined -- ########################################################################## -- ########################################################################## -- ======================================================= BEGIN -- Initialize constants and lookup tables -- ======================================================= put('Initialize constants and lookup tables BEG...'); putTrace('-- propertytypeIdOf'); -------------------------------- propertytypeIdOf('core') := 0; propertytypeIdOf('configuration') := 2; propertytypeIdOf('logical') := 1; propertytypeIdOf('physical') := 2; propertytypeIdOf('userdefined') := 3; putTrace('-- datatypeIdOf'); ---------------------------- DECLARE datatypenames NameList := NameList('String','Integer','Long','Float','Double','Timestamp','Date','Boolean'); -- Note: String[] was stringArray in 9.2.0.4 BEGIN FOR i IN datatypenames.FIRST .. datatypenames.LAST LOOP SELECT ELEMENTID INTO datatypeIdOf(datatypenames(i)) FROM WBPrimitiveType_v WHERE NAME=datatypenames(i); END LOOP; -- add special cases datatypeIdOf('int') := datatypeIdOf('Integer'); SELECT ELEMENTID INTO datatypeIdOf('String[]') FROM WBArrayType_v WHERE NAME='String[]'; END; putTrace('-- g_auditInfo'); --------------------------- DECLARE CURSOR c1 IS SELECT CREATEDBY, CREATIONTIMESTAMP, UPDATEDBY, UPDATETIMESTAMP FROM CMPInstallation_v; -- info and g_auditInfo have different types. But because info is based on a -- %ROWTYPE, we can assign it to g_auditInfo as long as they have the right -- number of fields and the fields have the right datatypes. info c1%ROWTYPE; BEGIN -- audit info (just set update timestamp once for all updates.) SELECT CREATEDBY, CREATIONTIMESTAMP, UPDATEDBY, UPDATETIMESTAMP INTO info FROM CMPInstallation_v; g_auditInfo := info; -- Replace installations update timestamp with current sysdate SELECT SYSDATE INTO g_auditInfo.UPDATETIMESTAMP FROM DUAL; END; put('Initialize constants and lookup tables END.' || EOL); -- ======================================================= END; -- Initialize constants and lookup tables -- ======================================================= -- ======================================================= DECLARE -- Inner block for utility function/variable usage -- aka 'user' declarations -- ======================================================= --up.xml-> <!-- definition class for slowly changing entity loading parameter properties only --> -- We will need to determine the context hierarchy for our changes so set up -- local variables from the class definition on down (could use record types -- but that seems overkill as we only need the id's. className VARCHAR2(255); classId NUMBER(9,0); propertySetName VARCHAR2(255); propertySetType NUMBER(9,0); propertySetId NUMBER(9,0); propertyGroupName VARCHAR2(255); propertyGroupId NUMBER(9,0); -- (PropertyDefininition, etc being used will be done with records) -- ======================================================= BEGIN -- Inner block for utility function/variable usage -- aka 'user' processing -- ======================================================= BEGIN -- Set heirarchy navigation variables dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin class SlowlyChangignEntityLoadParamsLogical'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Class --up.xml-> name='oracle.wh.service.impl.mapping.component.dimension.SlowlyChangingEntityLoadParamsLogical' --up.xml-> isMultipleInheritance='true' --up.xml-> > className := 'oracle.wh.service.impl.mapping.component.dimension.SlowlyChangingEntityLoadParamsLogical'; classId := classIdOf(className); --up.xml-> <PropertySet --up.xml-> Position='0' --up.xml-> definitionKey='ENTITY.LOGICAL' --up.xml-> name='DEFAULT' --up.xml-> propertyType='logical' --up.xml-> > propertySetName := 'DEFAULT'; propertySetType := propertytypeIdOf('logical'); propertySetId := psetIdOf(classId, propertySetType, propertySetName); -- NOTE: Since it is the default name, could use psetIdOf(classId, propertySetType); --up.xml-> <PropertyGroup --up.xml-> Position='0' --up.xml-> definitionKey='SCE.POLICIES' --up.xml-> name='HISTORY_LOGGING_POLICIES' --up.xml-> > propertyGroupName := 'HISTORY_LOGGING_POLICIES'; propertyGroupId := groupIdOf(classId, propertyGroupName); --up.xml-> *Attribute HISTORY_LOGGING omitted* --up.xml-> *Attribute SLOWLY_CHANGING_TYPE omitted* --up.xml-> *Attribute DEFAULT_EXPIRATION_TIME_OF_OPEN_RECORD omitted* --up.xml-> *Attribute DEFAULT_EFFECTIVE_TIME_OF_OPEN_RECORD omitted* END; DECLARE -- new property DEFAULT_EFFECTIVE_TIME_OF_INITIAL_RECORD: bug# 5845656 domn WBDomain; dlst NameList; pdef PropertyDefinition; puse PropertyUsageDefinition; dflt WBPrimitiveProperty; BEGIN dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin property DEFAULT_EFFECTIVE_TIME_OF_INITIAL_RECORD'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Attribute --up.xml-> Position='4' --up.xml-> dataType='String' --up.xml-> valueInterpreter='oracle.wh.service.impl.mapping.component.dimension.HistoricDateInterpreter' --up.xml-> description='A date value to be served as the effective time of the initial open record.' --up.xml-> exportControl='public' --up.xml-> isPassword='false' --up.xml-> isTranslatable='false' --up.xml-> name='DEFAULT_EFFECTIVE_TIME_OF_INITIAL_RECORD' --up.xml-> > domn := newWBDomain('DEFAULT_EFFECTIVE_TIME_OF_INITIAL_RECORD', datatypeIdOf('String')); domn.METADATASIGNATURE := '8b22833b1cfaf88cf1151b6fcbac1779'; domn.UOID := '2D2962958E41C3CCE040578CA40A4369'; --up.xml-> valueInterpreter='oracle.wh.service.impl.mapping.component.dimension.HistoricDateInterpreter' -- Note: These are used for value interpretation only - do not need to -- carry over METDATASIGNATURE + UOID addWBDomain(domn, NULL); --up.xml-> initValue='SYSDATE' <-- Domain will need WBPrimitiveProperty('DefaultValue') dflt := newWBPrimitiveProperty( classId => domn.ELEMENTID ,ownerId => domn.ELEMENTID ,datatype => domn.DATATYPE ,propertyName => 'DefaultValue' ,propertyValue => 'SYSDATE' ); -- you guessed it, get real values for these by running against new install -- then come back and put values here dflt.METADATASIGNATURE := 'a1554f1d2c506c4c817ab7f456f7a684'; dflt.UOID := '2D2962958E42C3CCE040578CA40A4369'; addWBPrimitiveProperty(dflt); --up.xml-> valueInterpreter='oracle.wh.service.impl.mapping.component.dimension.HistoricDateInterpreter' dflt := newWBPrimitiveProperty( classId => domn.ELEMENTID ,ownerId => domn.ELEMENTID ,datatype => domn.DATATYPE ,propertyName => 'ValueInterpreter' ,propertyValue => 'oracle.wh.service.impl.mapping.component.dimension.HistoricDateInterpreter' ); -- you guessed it, get real values for these by running against new install -- then come back and put values here dflt.METADATASIGNATURE := '879a74ea655d5230c979efd4367cdf0a'; dflt.UOID := '2D2962958E43C3CCE040578CA40A4369'; addWBPrimitiveProperty(dflt); pdef := newPropertyDefinition('DEFAULT_EFFECTIVE_TIME_OF_INITIAL_RECORD', classId); pdef.PROPERTYGROUPDEFINITION := propertyGroupId; -- set 'other' parent pdef.PROPERTYTYPE := propertySetType; -- and what kind of property pdef.DATATYPE := domn.ELEMENTID; pdef.DEFINITIONKEY := 'defaultEffectiveTimeOfInitialRecord'; -- camel case Name w/underlines removed BUGBUG: Make function for this? pdef.DESCRIPTION := 'A date value to be served as the effective time of the initial open record.'; pdef.EXPORTCONTROL := 0; -- If this ever really gets used, make exportControlIdOf() function pdef.ISPASSWORD := 0; -- is making function booleanIdOf() worth it? pdef.TRANSLATABLE := 0; -- get real values for these by running against new install -- then come back and put values here -- I've already done them all this time -blj pdef.METADATASIGNATURE := 'c822f87e581089bdb4da94c5ee7e1190'; pdef.UOID := '2D296295AE3FC3CCE040578CA40A4369'; pdef.POSITION := 4; addPropertyDefinition(pdef); --up.xml-> definitionKey='SCE.POLICIES.DEFAULTINITIALEFFECTIVEDATE' puse := newPropertyUsageDefinition(propertySetId, pdef); puse.DEFINITIONKEY := 'SCE.POLICIES.DEFAULTINITIALEFFECTIVEDATE'; -- as above, get real values for these by running against new install -- then come back and put values here puse.METADATASIGNATURE := 'bf8d94bdc9958415a7e5ebd45dfcf18f'; puse.UOID := '2D296295AE4EC3CCE040578CA40A4369'; addPropertyUsageDefinition(puse); --up.xml-> </Attribute> END; -- new property DEFAULT_EFFECTIVE_TIME_OF_INITIAL_RECORD DECLARE -- new property TYPE2_RECORD_GAP_INTERVAL pdef PropertyDefinition; puse PropertyUsageDefinition; dflt WBPrimitiveProperty; BEGIN dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin property TYPE2_RECORD_GAP_INTERVAL'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Attribute --up.xml-> Position='4' --up.xml-> dataType='int' --up.xml-> definitionKey='SCE.POLICIES.TYPE2RECORDGAPINTERVAL' --up.xml-> description='A numeric value indicating the number of units (see TYPE2_RECORD_GAP_INTERVAL_UNITS property below) that will be added to the expiration date of the previous closed record, to create the effective date of a new open record.' --up.xml-> exportControl='public' <-- default, don't care --up.xml-> initValue='1' <-- will need WBPrimitiveProperty('DefaultValue') --up.xml-> isPassword='false' <-- default, don't care --up.xml-> isTranslatable='false' <-- default, don't care --up.xml-> name='TYPE2_RECORD_GAP_INTERVAL' --up.xml-> > pdef := newPropertyDefinition('TYPE2_RECORD_GAP_INTERVAL', classId); pdef.PROPERTYGROUPDEFINITION := propertyGroupId; -- set 'other' parent pdef.PROPERTYTYPE := propertySetType; -- and what kind of property pdef.DATATYPE := datatypeIdOf('int'); pdef.DEFINITIONKEY := 'type2RecordGapInterval'; -- camel case Name w/underlines removed BUGBUG: Make function for this? pdef.DESCRIPTION := 'A numeric value indicating the number of units (see TYPE2_RECORD_GAP_INTERVAL_UNITS property below) that will be added to the expiration date of the previous closed record, to create the effective date of a new open record.'; pdef.EXPORTCONTROL := 0; -- If this ever really gets used, make exportControlIdOf() function pdef.ISPASSWORD := 0; -- is making function booleanIdOf() worth it? pdef.TRANSLATABLE := 0; -- get real values for these by running against new install -- then come back and put values here -- I've already done them all this time -blj pdef.METADATASIGNATURE := '025df214ab79d061ce71f7280d4083c4'; pdef.UOID := '2D296295AE40C3CCE040578CA40A4369'; pdef.POSITION := 5; addPropertyDefinition(pdef); --up.xml-> definitionKey='SCE.POLICIES.TYPE2RECORDGAPINTERVAL' puse := newPropertyUsageDefinition(propertySetId, pdef); puse.DEFINITIONKEY := 'SCE.POLICIES.TYPE2RECORDGAPINTERVAL'; -- as above, get real values for these by running against new install -- then come back and put values here puse.METADATASIGNATURE := 'ba7e3f0f417be5b7828edff56c7fb67b'; puse.UOID := '2D296295AE4FC3CCE040578CA40A4369'; addPropertyUsageDefinition(puse); --up.xml-> initValue='1' <-- will need WBPrimitiveProperty('DefaultValue') dflt := newWBPrimitiveProperty( classId => puse.LOADINGPARENT ,ownerId => puse.ELEMENTID ,datatype => pdef.DATATYPE ,propertyName => 'DefaultValue' ,propertyValue => '1' ); -- you guessed it, get real values for these by running against new install -- then come back and put values here dflt.METADATASIGNATURE := 'a1554f1d2c506c4c817ab7f456f7a684'; dflt.UOID := '2D296295AE50C3CCE040578CA40A4369'; addWBPrimitiveProperty(dflt); --up.xml-> </Attribute> END; -- 1st new property TYPE2_RECORD_GAP_INTERVAL DECLARE -- new property TYPE2_RECORD_GAP_INTERVAL_UNITS domn WBDomain; dlst NameList; pdef PropertyDefinition; puse PropertyUsageDefinition; dflt WBPrimitiveProperty; BEGIN dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin property TYPE2_RECORD_GAP_INTERVAL_UNITS'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Attribute --up.xml-> Position='5' --up.xml-> dataType='String' <-- will actually be domain of type String --up.xml-> choices='WEEKS,DAYS,HOURS,MINUTES,SECONDS' <-- consisting of these --up.xml-> definitionKey='SCE.POLICIES.TYPE2RECORDGAPINTERVALUNITS' --up.xml-> description='A value indicating the units used for the gap that will be added to the expiration date of the previous closed record, to create the effective date of a new open record.' --up.xml-> exportControl='public' <-- default, don't care --up.xml-> initValue='SECONDS' <-- DOMAIN will need WBPrimitiveProperty('DefaultValue') --up.xml-> isPassword='false' <-- default, don't care --up.xml-> isTranslatable='true' --up.xml-> name='TYPE2_RECORD_GAP_INTERVAL_UNITS' --up.xml-> > -- Since choices is defined, need to create a WBDomain with choices -- values as unnamed WBPrimitiveProperty children. Domain also gets -- the 'DefaultValue' WBPrimitiveProperty. The WBDomain then gets -- used as the PropertyDefinition datatype. -- Starting from PropertyDefinition 23706, datatype is 17027 -- found WBDomain 17027, NAME=pdef.NAME, DATATYPE=106(String), -- COMPARATOR='PropertyNameComparator'[default] -- Checking WBRange where DOMAIN=17027, no data found -- Checking WBPrimitiveProperty where OWNER=17027 sort by ELEMENTID -- found (id/name/value) 17028 'DefaultValue' 'SECONDS' -- 17029 (null) 'WEEKS' -- etc with 17030-17033 being DAYS,HOURS,MINUTES,SECONDS -- WBDomain needs NAME + DATATYPE (+ COMAPARATOR if != default - RARE) domn := newWBDomain('TYPE2_RECORD_GAP_INTERVAL_UNITS', datatypeIdOf('String')); domn.METADATASIGNATURE := '8beb8e46926e5573c06c7e44474b8e0d'; domn.UOID := '2D2962959495C3CCE040578CA40A4369'; -- Note: These are used for value comparison only - do not need to -- carry over METDATASIGNATURE + UOID dlst := NameList('WEEKS','DAYS','HOURS','MINUTES','SECONDS'); addWBDomain(domn, dlst); --up.xml-> initValue='SECONDS' <-- DOMAIN will need WBPrimitiveProperty('DefaultValue') dflt := newWBPrimitiveProperty( classId => domn.ELEMENTID ,ownerId => domn.ELEMENTID ,datatype => domn.DATATYPE ,propertyName => 'DefaultValue' ,propertyValue => 'SECONDS' ); -- you guessed it, get real values for these by running against new install -- then come back and put values here dflt.METADATASIGNATURE := 'a1554f1d2c506c4c817ab7f456f7a684'; dflt.UOID := '2D2962959496C3CCE040578CA40A4369'; addWBPrimitiveProperty(dflt); pdef := newPropertyDefinition(domn.NAME, classId); pdef.PROPERTYGROUPDEFINITION := propertyGroupId; -- set 'other' parent pdef.PROPERTYTYPE := propertySetType; -- and what kind of property pdef.DATATYPE := domn.ELEMENTID; pdef.DEFINITIONKEY := 'type2RecordGapIntervalUnits'; -- camel case Name w/underlines removed BUGBUG: Make function for this? pdef.DESCRIPTION := 'A value indicating the units used for the gap that will be added to the expiration date of the previous closed record, to create the effective date of a new open record.'; pdef.EXPORTCONTROL := 0; -- If this ever really gets used, make exportControlIdOf() function pdef.ISPASSWORD := 0; -- is making function booleanIdOf() worth it? pdef.TRANSLATABLE := 1; -- get real values for these by running against new install -- then come back and put values here pdef.METADATASIGNATURE := '8b61f46ad35fa13b1f5e3e77603fe5a9'; pdef.UOID := '2D296295AE41C3CCE040578CA40A4369'; pdef.POSITION := 5; addPropertyDefinition(pdef); --up.xml-> definitionKey='SCE.POLICIES.TYPE2RECORDGAPINTERVALUNITS' puse := newPropertyUsageDefinition(propertySetId, pdef); puse.DEFINITIONKEY := 'SCE.POLICIES.TYPE2RECORDGAPINTERVALUNITS'; -- as above, get real values for these by running against new install -- then come back and put values here puse.METADATASIGNATURE := 'bd4d0967835514b3924f6efbf720f402'; puse.UOID := '2D296295AE51C3CCE040578CA40A4369'; addPropertyUsageDefinition(puse); --up.xml-> </Attribute> END; -- 2nd new property TYPE2_RECORD_GAP_INTERVAL_UNITS --up.xml-> *Attribute TYPE2_LOOKUP_MODE omitted* --up.xml-> </PropertyGroup> --up.xml-> </PropertySet> --up.xml-> </Class> BEGIN -- Set heirarchy navigation variables dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin class CMPMapAttribute (SlowlyChangingEntityAttributeLoadParams)'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Class --up.xml-> name='oracle.wh.repos.impl.mapping.CMPMapAttribute' --up.xml-> > className := 'oracle.wh.repos.impl.mapping.CMPMapAttribute'; classId := classIdOf(className); --up.xml-> <PropertySet --up.xml-> Position='0' --up.xml-> definitionKey='SLOWLYCHANGINGENTITY.ATTRIBUTE' --up.xml-> name='#SlowlyChangingEntityAttributeLoadParams' --up.xml-> propertyType='logical' --up.xml-> > propertySetName := 'SlowlyChangingEntityAttributeLoadParams'; propertySetType := propertytypeIdOf('logical'); propertySetId := psetIdOf(classId, propertySetType, propertySetName); -- NOTE: Since it is the default name, could use psetIdOf(classId, propertySetType); --up.xml-> <PropertyGroup --up.xml-> Position='2' --up.xml-> definitionKey='SCE.INOUTGRP.ATTRIBUTE.LOADINGPROPERTIES' --up.xml-> name='LOADING_PROPERTIES' --up.xml-> > propertyGroupName := 'LOADING_PROPERTIES'; propertyGroupId := groupIdOf(classId, propertyGroupName); END; DECLARE -- 1st new property TYPE_TWO_SCD_TRIGGER: bug# 5845640 domn WBDomain; dlst NameList; pdef PropertyDefinition; puse PropertyUsageDefinition; dflt WBPrimitiveProperty; BEGIN dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin property TYPE_TWO_SCD_TRIGGER'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Attribute --up.xml-> Position='5' --up.xml-> choices='YES,NO' --up.xml-> dataType='String' --up.xml-> definitionKey='LEVEL.INOUTGRP.ATTRIBUTE.TYPE_TWO_SCD_TRIGGER' --up.xml-> description='A boolean value to indicate whether this attribute will be used as a slowly changing trigger for SCD type 2 processing.' --up.xml-> exportControl='public' --up.xml-> initValue='NO' --up.xml-> isPassword='false' --up.xml-> isTranslatable='true' --up.xml-> isReadOnly='true' --up.xml-> isHidden='false' --up.xml-> name='TYPE_TWO_SCD_TRIGGER' --up.xml-> > --up.xml-> </Attribute> domn := newWBDomain('TYPE_TWO_SCD_TRIGGER', datatypeIdOf('String')); domn.METADATASIGNATURE := '5eebafc97cbe2b27971883cbdcf8dd76'; domn.UOID := '2D29629594C0C3CCE040578CA40A4369'; -- Note: These are used for value comparison only - do not need to -- carry over METDATASIGNATURE + UOID dlst := NameList('YES','NO'); addWBDomain(domn, dlst); --up.xml-> initValue='SECONDS' <-- DOMAIN will need WBPrimitiveProperty('DefaultValue') dflt := newWBPrimitiveProperty( classId => domn.ELEMENTID ,ownerId => domn.ELEMENTID ,datatype => domn.DATATYPE ,propertyName => 'DefaultValue' ,propertyValue => 'NO' ); -- you guessed it, get real values for these by running against new install -- then come back and put values here dflt.METADATASIGNATURE := 'a1554f1d2c506c4c817ab7f456f7a684'; dflt.UOID := '2D29629594C1C3CCE040578CA40A4369'; addWBPrimitiveProperty(dflt); pdef := newPropertyDefinition(domn.NAME, classId); pdef.PROPERTYGROUPDEFINITION := propertyGroupId; -- set 'other' parent pdef.PROPERTYTYPE := propertySetType; -- and what kind of property pdef.DATATYPE := domn.ELEMENTID; pdef.DEFINITIONKEY := 'typeTwoScdTrigger'; -- camel case Name w/underlines removed BUGBUG: Make function for this? pdef.DESCRIPTION := 'A boolean value to indicate whether this attribute will be used as a slowly changing trigger for SCD type 2 processing.'; pdef.EXPORTCONTROL := 0; -- If this ever really gets used, make exportControlIdOf() function pdef.ISPASSWORD := 0; -- is making function booleanIdOf() worth it? pdef.TRANSLATABLE := 1; pdef.PROPERTYREADONLY := 1; -- get real values for these by running against new install -- then come back and put values here pdef.METADATASIGNATURE := '0388b3405b3b4a2e716e084984857c18'; pdef.UOID := '2D296295A04BC3CCE040578CA40A4369'; pdef.POSITION := 5; addPropertyDefinition(pdef); --up.xml-> definitionKey='SCE.POLICIES.TYPE2RECORDGAPINTERVALUNITS' puse := newPropertyUsageDefinition(propertySetId, pdef); puse.DEFINITIONKEY := 'LEVEL.INOUTGRP.ATTRIBUTE.TYPE_TWO_SCD_TRIGGER'; -- as above, get real values for these by running against new install -- then come back and put values here puse.METADATASIGNATURE := '8d5e75b9ef2323cc3921d729e4021a87'; puse.UOID := '2D296295A0ABC3CCE040578CA40A4369'; addPropertyUsageDefinition(puse); --up.xml-> </Attribute> END; -- new property TYPE_TWO_SCD_TRIGGER BEGIN --up.xml-> <PropertyGroup --up.xml-> Position='3' --up.xml-> definitionKey='LEVEL.INOUTGRP.ATTRIBUTE.LEVELPROPERTIES' --up.xml-> name='LEVEL_PROPERTIES' --up.xml-> > propertyGroupName := 'LEVEL_PROPERTIES'; propertyGroupId := groupIdOf(classId, propertyGroupName); END; DECLARE -- New property LOAD_ON_UPDATE: bug# 6083265 domn WBDomain; dlst NameList; pdef PropertyDefinition; puse PropertyUsageDefinition; dflt WBPrimitiveProperty; BEGIN dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin property LOAD_ON_UPDATE'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Attribute --up.xml-> Position='11' --up.xml-> choices='YES,NO' --up.xml-> dataType='String' --up.xml-> definitionKey='LEVEL.INOUTGRP.ATTRIBUTE.LOADONUPDATE' --up.xml-> description='A boolean value to indicate whether this attribute will be loaded on insert only.' --up.xml-> exportControl='public' --up.xml-> initValue='YES' --up.xml-> isPassword='false' --up.xml-> isTranslatable='true' --up.xml-> maxLength='3' --up.xml-> name='LOAD_ON_UPDATE' --up.xml-> > --up.xml-> </Attribute> domn := newWBDomain('LOAD_ON_UPDATE', datatypeIdOf('String')); domn.METADATASIGNATURE := '680d1d512ecc14a79bb03f10be8135e5'; domn.UOID := '31C9F2A4C7912C87E040578CA40A5EB1'; -- Note: These are used for value comparison only - do not need to -- carry over METDATASIGNATURE + UOID dlst := NameList('YES','NO'); addWBDomain(domn, dlst); --up.xml-> initValue='YES' dflt := newWBPrimitiveProperty( classId => domn.ELEMENTID ,ownerId => domn.ELEMENTID ,datatype => domn.DATATYPE ,propertyName => 'DefaultValue' ,propertyValue => 'YES' ); -- you guessed it, get real values for these by running against new install -- then come back and put values here dflt.METADATASIGNATURE := 'a1554f1d2c506c4c817ab7f456f7a684'; dflt.UOID := '31C9F2A4C7922C87E040578CA40A5EB1'; addWBPrimitiveProperty(dflt); pdef := newPropertyDefinition(domn.NAME, classId); pdef.PROPERTYGROUPDEFINITION := propertyGroupId; -- set 'other' parent pdef.PROPERTYTYPE := propertySetType; -- and what kind of property pdef.DATATYPE := domn.ELEMENTID; pdef.DEFINITIONKEY := 'loadOnUpdate'; -- camel case Name w/underlines removed BUGBUG: Make function for this? pdef.DESCRIPTION := 'A boolean value to indicate whether this attribute will be loaded on insert only.'; pdef.EXPORTCONTROL := 0; -- If this ever really gets used, make exportControlIdOf() function pdef.ISPASSWORD := 0; -- is making function booleanIdOf() worth it? pdef.TRANSLATABLE := 1; pdef.PROPERTYREADONLY := 0; -- get real values for these by running against new install -- then come back and put values here pdef.METADATASIGNATURE := '404a7b19a9644fafc924425ed516bd1c'; pdef.UOID := '31C9F2A4C7A32C87E040578CA40A5EB1'; pdef.POSITION := 11; addPropertyDefinition(pdef); --up.xml-> definitionKey='SCE.POLICIES.TYPE2RECORDGAPINTERVALUNITS' puse := newPropertyUsageDefinition(propertySetId, pdef); puse.DEFINITIONKEY := 'LEVEL.INOUTGRP.ATTRIBUTE.LOADONUPDATE'; -- as above, get real values for these by running against new install -- then come back and put values here puse.METADATASIGNATURE := '2e0a321269cfcf73538370d5f879ea3f'; puse.UOID := '31C9F2A4C7A42C87E040578CA40A5EB1'; addPropertyUsageDefinition(puse); --up.xml-> </Attribute> END; -- new property LOAD_ON_UPDATE BEGIN -- Set heirarchy navigation variables dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin class oracle.wh.service.impl.mapping.component.dimension.CubeLoadParamsLogical'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Class --up.xml-> name='oracle.wh.service.impl.mapping.component.dimension.CubeLoadParamsLogical' --up.xml-> isMultipleInheritance='true' --up.xml-> > className := 'oracle.wh.service.impl.mapping.component.dimension.CubeLoadParamsLogical'; classId := classIdOf(className); --up.xml-> <PropertySet --up.xml-> Position='0' --up.xml-> definitionKey='ENTITY.LOGICAL' --up.xml-> name='DEFAULT' --up.xml-> propertyType='logical' --up.xml-> > propertySetName := 'DEFAULT'; propertySetType := propertytypeIdOf('logical'); propertySetId := psetIdOf(classId, propertySetType, propertySetName); -- NOTE: Since it is the default name, could use psetIdOf(classId, propertySetType); --up.xml-> <PropertyGroup --up.xml-> Position='1' --up.xml-> definitionKey='CUBE.PARMETERS' --up.xml-> name='CUBE_PROPERTIES' --up.xml-> > propertyGroupName := 'CUBE_PROPERTIES'; propertyGroupId := groupIdOf(classId, propertyGroupName); END; DECLARE -- property LOADING_TYPE adds domain value "INSERT_LOAD": bug# 5955424 domn WBDomain; dlst NameList; pdef PropertyDefinition; puse PropertyUsageDefinition; dflt WBPrimitiveProperty; BEGIN dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin property LOADING_TYPE'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Attribute --up.xml-> Position='0' --up.xml-> choices='INSERT_LOAD,LOAD,REMOVE' --up.xml-> dataType='String' --up.xml-> definitionKey='CUBE.PARMETERS.LOADTYPE' --up.xml-> description='The loading operation to be performed when this is a target. If INSERT_LOAD is specified, OWB will try to insert all input data into target. If LOAD is specified, OWB will try to merge input data into target. If REMOVE is specified, OWB will try to match between the input data and target data to compute existing data; it will then remove existing data from target.' --up.xml-> exportControl='public' --up.xml-> initValue='INSERT_LOAD' --up.xml-> isPassword='false' --up.xml-> isTranslatable='true' --up.xml-> name='LOADING_TYPE3' --up.xml-> > --up.xml-> </Attribute> -- Change domain name to "LOADING_TYPE_10203" so it will be unique and a new one will get created. -- The property definition will be updated to use the new domain if it has changed. domn := newWBDomain('LOADING_TYPE_10203', datatypeIdOf('String')); domn.METADATASIGNATURE := 'a46e347660b2ec88136832a76e4d2f09'; domn.UOID := '2D73E4CAB068DE92E040578CB00A413B'; -- Note: These are used for value comparison only - do not need to -- carry over METDATASIGNATURE + UOID dlst := NameList('INSERT_LOAD','LOAD','REMOVE'); addWBDomain(domn, dlst); --up.xml-> initValue='INSERT_LOAD' <-- DOMAIN will need WBPrimitiveProperty('DefaultValue') dflt := newWBPrimitiveProperty( classId => domn.ELEMENTID ,ownerId => domn.ELEMENTID ,datatype => domn.DATATYPE ,propertyName => 'DefaultValue' ,propertyValue => 'LOAD' ); -- you guessed it, get real values for these by running against new install -- then come back and put values here dflt.METADATASIGNATURE := 'a1554f1d2c506c4c817ab7f456f7a684'; dflt.UOID := '2D73E4CAB069DE92E040578CB00A413B'; addWBPrimitiveProperty(dflt); pdef := newPropertyDefinition('LOADING_TYPE', classId); pdef.PROPERTYGROUPDEFINITION := propertyGroupId; -- set 'other' parent pdef.PROPERTYTYPE := propertySetType; -- and what kind of property pdef.DATATYPE := domn.ELEMENTID; pdef.DEFINITIONKEY := 'loadingType'; -- camel case Name w/underlines removed BUGBUG: Make function for this? pdef.DESCRIPTION := 'The loading operation to be performed when this is a target. If INSERT_LOAD is specified, OWB will try to insert all input data into target. If LOAD is specified, OWB will try to merge input data into target. If REMOVE is specified, OWB will try to match between the input data and target data to compute existing data; it will then remove existing data from target.'; pdef.EXPORTCONTROL := 0; -- If this ever really gets used, make exportControlIdOf() function pdef.ISPASSWORD := 0; -- is making function booleanIdOf() worth it? pdef.TRANSLATABLE := 1; -- get real values for these by running against new install -- then come back and put values here pdef.METADATASIGNATURE := 'c6d7d727fc43625a8e3a89f5d361918e'; pdef.UOID := '2D296295ADC2C3CCE040578CA40A4369'; pdef.POSITION := 0; addPropertyDefinition(pdef); --up.xml-> definitionKey='SCE.POLICIES.TYPE2RECORDGAPINTERVALUNITS' puse := newPropertyUsageDefinition(propertySetId, pdef); puse.DEFINITIONKEY := 'CUBE.PARMETERS.LOADTYPE'; -- as above, get real values for these by running against new install -- then come back and put values here puse.METADATASIGNATURE := '2f43c37000b98bc83d71289a86cd4108'; puse.UOID := '2D296295ADD1C3CCE040578CA40A4369'; addPropertyUsageDefinition(puse); --up.xml-> </Attribute> END; -- new property DEFAULT_EFFECTIVE_TIME_OF_INITIAL_RECORD --up.xml-> </PropertyGroup> --up.xml-> </PropertySet> --up.xml-> </Class> BEGIN -- Set heirarchy navigation variables dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin class oracle.wh.service.impl.mapping.component.aggregation.Aggregation'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Class --up.xml-> name='oracle.wh.service.impl.mapping.component.aggregation.Aggregation' --up.xml-> extends='oracle.wh.repos.sdk.mapping.WBMapOperatorDelegate' --up.xml-> > className := 'oracle.wh.service.impl.mapping.component.aggregation.Aggregation'; classId := classIdOf(className); --up.xml-> <PropertySet --up.xml-> Position='0' --up.xml-> definitionKey='AGGREGATION' --up.xml-> name='DEFAULT' --up.xml-> propertyType='logical' --up.xml-> > propertySetName := 'DEFAULT'; propertySetType := propertytypeIdOf('logical'); propertySetId := psetIdOf(classId, propertySetType, propertySetName); -- NOTE: Since it is the default name, could use psetIdOf(classId, propertySetType); --propertyGroupName := 'DEFAULT'; --propertyGroupId := groupIdOf(classId, propertyGroupName); END; DECLARE -- existing property GROUP_BY_CLAUSE gets new custom editor class. Bug# 6075290. domn WBDomain; dlst NameList; pdef PropertyDefinition; puse PropertyUsageDefinition; dflt WBPrimitiveProperty; BEGIN dbms_output.put_line('--------------------------------------------------'); dbms_output.put_line('Begin property GROUP_BY_CLAUSE'); dbms_output.put_line('--------------------------------------------------'); --up.xml-> <Attribute --up.xml-> Position='0' --up.xml-> customEditor='oracle.wh.ui.tsmapping.inspector.MapExpressionCustomEditor' --up.xml-> valueInterpreter='oracle.wh.service.impl.mapping.metadata.ExpressionValueInterpreter' --up.xml-> dataType='ExpressionType' --up.xml-> definitionKey='GROUP_BY_CLAUSE' --up.xml-> description='The Group By clause for the aggregation' --up.xml-> exportControl='public' --up.xml-> isPassword='false' --up.xml-> isTranslatable='false' --up.xml-> name='GROUP_BY_CLAUSE' --up.xml-> > --up.xml-> </Attribute> domn := newWBDomain('String2000', datatypeIdOf('String')); domn.METADATASIGNATURE := 'ad952028261c2f303af7c25d19981138'; domn.UOID := '30AFA4AFCA2E8091E040578CA40A0699'; addWBDomain(domn, NULL); domn := newWBDomain('ExpressionType', domn.ELEMENTID); domn.METADATASIGNATURE := '9a7d11b44c4f4efe852bea57e7a6374b'; domn.UOID := '30AFA4AFF90B8091E040578CA40A0699'; addWBDomain(domn, NULL); domn := newWBDomain('GROUP_BY_CLAUSE', domn.ELEMENTID); domn.METADATASIGNATURE := '3fa7102d49bb56153fe12ca0b107c122'; domn.UOID := '30AFA4AFFF668091E040578CA40A0699'; addWBDomain(domn, NULL); pdef := newPropertyDefinition('GROUP_BY_CLAUSE', classId); pdef.PROPERTYGROUPDEFINITION := 0; -- set 'other' parent pdef.PROPERTYTYPE := propertySetType; -- and what kind of property pdef.DATATYPE := domn.ELEMENTID; pdef.DEFINITIONKEY := 'groupByClause'; -- camel case Name w/underlines removed BUGBUG: Make function for this? pdef.DESCRIPTION := 'The Group By clause for the aggregation'; pdef.EXPORTCONTROL := 0; -- If this ever really gets used, make exportControlIdOf() function pdef.ISPASSWORD := 0; -- is making function booleanIdOf() worth it? pdef.TRANSLATABLE := 0; -- get real values for these by running against new install -- then come back and put values here -- I've already done them all this time -blj pdef.METADATASIGNATURE := 'c822f87e581089bdb4da94c5ee7e1190'; pdef.UOID := '2D296295AE3FC3CCE040578CA40A4369'; pdef.POSITION := 4; addPropertyDefinition(pdef); --up.xml-> definitionKey='GROUP_BY_CLAUSE' puse := newPropertyUsageDefinition(propertySetId, pdef); puse.DEFINITIONKEY := 'GROUP_BY_CLAUSE'; -- as above, get real values for these by running against new install -- then come back and put values here puse.METADATASIGNATURE := 'bf8d94bdc9958415a7e5ebd45dfcf18f'; puse.UOID := '2D296295AE4EC3CCE040578CA40A4369'; addPropertyUsageDefinition(puse); --up.xml-> customEditor='oracle.wh.ui.tsmapping.inspector.MapExpressionCustomEditor' dflt := newWBPrimitiveProperty( classId => puse.ELEMENTID ,ownerId => puse.ELEMENTID ,datatype => datatypeIdOf('String') ,propertyName => 'CustomEditor' ,propertyValue => 'oracle.wh.ui.tsmapping.inspector.MapExpressionCustomEditor' ); -- you guessed it, get real values for these by running against new install -- then come back and put values here dflt.METADATASIGNATURE := '879a74ea655d5230c979efd4367cdf0a'; dflt.UOID := '2D2962958E43C3CCE040578CA40A4369'; addWBPrimitiveProperty(dflt); --up.xml-> </Attribute> END; -- new property DEFAULT_EFFECTIVE_TIME_OF_INITIAL_RECORD EXCEPTION WHEN OTHERS THEN put(SQLERRM); -- ======================================================= END; -- Inner block for utility function/variable usage -- ======================================================= -- ########################################################################## END; -- Outer block for utility functions/variables -- ########################################################################## /
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de