Rem Rem $Header: owb_uprop_views.sql 15-apr-2008.14:09:34 xxzhang Exp $ Rem Rem owb_uprop_views.sql Rem Rem Copyright (c) 2006, Oracle. All rights reserved. Rem Rem NAME Rem owb_uprop_views.sql - Additional/revised views for property information Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem See owb_uprop package for (much) more efficient table functions for Rem common retrieval patterns. Rem Rem MODIFIED (MM/DD/YY) Rem xxzhang 09/17/07 - Rem bjeffrie 02/18/06 - Created Rem -- WARNING! service/impl/assistant/RuntimeInstaller will throw exception if -- these are enabled. --SET ECHO ON --SET FEEDBACK 1 --SET NUMWIDTH 10 --SET LINESIZE 80 --SET TRIMSPOOL ON --SET TAB OFF --SET PAGESIZE 100 --set serveroutput on size 999999 --set line 120 begin -- Outer block for exec function -- Create public views for unified property system metadata/data retrieval. DECLARE PROCEDURE exec(p_stmt IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE p_stmt; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('===>' ||sqlcode || ' ' || sqlerrm); dbms_output.put_line(' ' || substr(p_stmt, 1, 250)); END; BEGIN -- Inner block for exec function usage exec(' CREATE OR REPLACE VIEW SUB_FQ_PUSAGE_DEF AS /* Set of all usage definitions by class type */ -- handy headers to stick on top of sqlworkbook results -- classTypeName pusageId pdefId propName pusageType storageType datatype typeName hidden publishToPublicApi contributeToSignature dynamicDefault defaultValue psetId psetName psetClassName nlsKey description SELECT * FROM ( SELECT cdefs.cdefTypeName classTypeName ,puses.id pusageId, puses.pdefId pdefId, puses.name propName, puses.usageType pusageType, puses.storageType ,puses.datatype, puses.typeName, puses.hidden ,NVL(puses.publishToPublicApi, DECODE(puses.hidden, ''true'', ''false'', ''false'', ''true'', ''true'')) publishToPublicApi ,puses.contributeToSignature, puses.dynamicDefault ,puses.defaultValue, puses.psetId ,psets.superPsetName psetName, psets.superPsetClassName psetClassName, puses.puseKey nlsKey, puses.description ,puses.groupId AS groupId ,groupDef.name AS groupName ,groupDef.definitionKey AS groupDefinitionKey ,puses.createdBy AS createdBy, puses.created as created ,puses.updatedBy AS updatedBy, puses.updated AS updated FROM PropertyGroupDefinition_v groupDef, --2 propdef.cdefs BEG ( /* SUB_ALL_RELATED_CDEFS cdefs */ -- CREATE OR REPLACE VIEW SUB_ALL_RELATED_CDEFS AS -- name: 21 char /* Set of classDef ids to process: starting element''s classDef + all superclasses */ SELECT elementId AS cdefId ,DECODE(classDef.weaklyTyped, 1, classDef.name, SUBSTR(classDef.name, INSTR(classDef.name, ''.'', -1)+1)) AS cdefTypeName ,elementId AS parentCdefId ,DECODE(classDef.weaklyTyped, 1, classDef.name, SUBSTR(classDef.name, INSTR(classDef.name, ''.'', -1)+1)) AS parentCdefTypeName -- ,0 AS parentCdefLevel FROM ClassDefinition_v classDef --WHERE classDef.name= --WHERE classDef.name LIKE ''%.ExternalTable'' UNION ALL SELECT UNIQUE CONNECT_BY_ROOT childClassDef.elementId AS cdefId ,CONNECT_BY_ROOT DECODE(childClassDef.weaklyTyped, 1, childClassDef.name, SUBSTR(childClassDef.name, INSTR(childClassDef.name, ''.'', -1)+1)) AS cdefTypeName ,parentClassDef.elementId AS parentCdefId ,DECODE(parentClassDef.weaklyTyped, 1, parentClassDef.name, SUBSTR(parentClassDef.name, INSTR(parentClassDef.name, ''.'', -1)+1)) AS parentCdefTypeName -- ,LEVEL AS parentCdefLevel FROM ClassInheritanceDefinition_v hierDef ,ClassDefinition_v childClassDef ,ClassDefinition_v parentClassDef WHERE childClassDef.elementId=hierDef.inheritanceChild AND parentClassDef.elementId=hierDef.inheritanceParent --START WITH childClassdef.elementId= --START WITH childClassDef.elementId=(SELECT elementId from Classdefinition_v WHERE name LIKE ''%.ExternalTable'') CONNECT BY PRIOR inheritanceParent=inheritanceChild /* ORDER BY cdefTypeName, parentCdefId */ ) /* SUB_ALL_RELATED_CDEFS */ cdefs --2 propdef.cdefs END --2 propdef.psets BEG ,( /* SUB_ALL_RELATED_PSETS psets */ -- CREATE OR REPLACE VIEW SUB_ALL_RELATED_PSETS AS -- name: 21 char /* Set of psetDef ids to process*/ SELECT UNIQUE CONNECT_BY_ROOT psetDef.elementId AS psetId ,CONNECT_BY_ROOT decode(psetDef.propertyType, 0, ''CORE'', 1, ''LOGICAL'', 2, ''CONFIG'', 3, ''USERDEF'') AS psetType ,CONNECT_BY_ROOT psetDef.classDefinition AS classDef ,CONNECT_BY_ROOT psetDef.parentClassDefinition AS parentClassDef ,SUBSTR(classDef.name, INSTR(classDef.name, ''.'', -1)+1) psetClassName -- for debug ,CONNECT_BY_ROOT psetDef.name AS psetName -- for debug ,superPsetDef.elementId AS superPsetId ,decode(superPsetDef.propertyType, 0, ''CORE'', 1, ''LOGICAL'', 2, ''CONFIG'', 3, ''USERDEF'') AS superPsetType ,SUBSTR(superClassDef.name, INSTR(superClassDef.name, ''.'', -1)+1) superPsetClassName -- for debug ,superPsetDef.name AS superPsetName -- for debug FROM PropertySetDefinition_v psetDef ,PropertySetDefinition_v superPsetDef ,ClassDefinition_v classDef -- debug ,ClassDefinition_v superClassDef -- debug WHERE psetDef.superPropertySetDefinition=superPsetDef.elementId AND psetDef.classDefinition=classDef.elementId -- debug AND superPsetDef.classDefinition=superClassDef.elementId -- debug AND NOT psetDef.propertyType=0 CONNECT BY PRIOR psetDef.superPropertySetDefinition=psetDef.elementId UNION ALL SELECT psetDef.elementId AS psetId ,decode(psetDef.propertyType, 0, ''CORE'', 1, ''LOGICAL'', 2, ''CONFIG'', 3, ''USERDEF'') AS psetType ,psetDef.classDefinition AS classDef ,psetDef.parentClassDefinition AS parentClassDef ,SUBSTR(classDef.name, INSTR(classDef.name, ''.'', -1)+1) psetClassName -- for debug ,psetDef.name AS psetName -- for debug ,psetDef.elementId AS superPsetId ,decode(psetDef.propertyType, 0, ''CORE'', 1, ''LOGICAL'', 2, ''CONFIG'', 3, ''USERDEF'') AS superPsetType ,SUBSTR(classDef.name, INSTR(classDef.name, ''.'', -1)+1) superPsetClassName -- for debug ,psetDef.name AS superPsetName -- for debug FROM PropertySetDefinition_v psetDef ,ClassDefinition_v classDef -- debug WHERE psetDef.classDefinition=classDef.elementId -- debug AND NOT psetDef.propertyType = 0 /* ORDER BY psetId, superPsetId */ ) /* SUB_ALL_RELATED_PSETS */ psets --2 propdef.psets END --2 propdef.puses BEG ,( /* SUB_TYPED_PUSES_W_DFLT puses */ -- CREATE OR REPLACE VIEW SUB_TYPED_PUSES_W_DFLT -- name: 22 char /* property usages with datatype and defaults */ SELECT UNIQUE puse2.id AS id ,puse2.pdefId AS pdefId ,puse2.name AS name ,puse2.description AS description ,puse2.usageType AS usageType ,puse2.storageType AS storageType ,puse2.datatype AS datatype ,delem.name AS typeName ,DECODE(puse2.hidden,''0'', ''false'', ''1'', ''true'', puse2.hidden) AS hidden ,puse2.contributeToSignature AS contributeToSignature ,puse2.dynamicDefault AS dynamicDefault ,NVL(puse2.defaultValue, dtype.defaultValue) AS defaultValue ,puse2.publishToPublicApi AS publishToPublicApi ,puse2.psetId AS psetId ,puse2.puseKey AS puseKey ,puse2.groupId AS groupId ,puse2.createdBy AS createdBy, puse2.created as created ,puse2.updatedBy AS updatedBy, puse2.updated AS updated FROM --3 propdef.puses.dtype BEG ( /* SUB_DEFAULT_FOR_DATATYPE dtype */ -- CREATE OR REPLACE VIEW SUB_DEFAULT_FOR_DATATYPE AS -- name: 24 char /* default value for domain/datatype */ SELECT ''Domain'' typeClass -- debug ,CONNECT_BY_ROOT domain.elementId typeId ,CONNECT_BY_ROOT domain.name typeName ,nvl(defaultValueProp.value, ( SELECT value FROM WBPrimitiveProperty_v WHERE owner=domain.datatype AND name=''DefaultValue'') ) defaultValue --,MIN(LEVEL) pathLen -- real - but get error - how to do? ,LEVEL pathLen -- debug ,SYS_CONNECT_BY_PATH(domain.name, ''/'') path -- debug FROM WBDomain_v domain LEFT OUTER JOIN WBPrimitiveProperty_v defaultValueProp ON (defaultValueProp.owner=domain.elementId) WHERE defaultValueProp.name=''DefaultValue'' CONNECT BY PRIOR domain.datatype=domain.elementId UNION ALL SELECT ''Type'' -- debug ,datatype.elementId typeId ,datatype.name typeName ,defaultValueProp.value defaultValue ,0 pathLen -- debug ,'''' path -- debug FROM WBAbstractType_v datatype LEFT OUTER JOIN WBPrimitiveProperty_v defaultValueProp ON (defaultValueProp.owner=datatype.elementId) WHERE defaultValueProp.name=''DefaultValue'' ) /* SUB_DEFAULT_FOR_DATATYPE */ dtype --3 propdef.puses.dtype END --3 propdef.puses.puse2 BEG ,( /* SUB_TYPED_PUSES puse2 */ -- CREATE OR REPLACE VIEW SUB_TYPED_PUSES /* puse def with datatype and maybe defaultValue */ SELECT puse.id AS id ,pdef.id AS pdefId ,puse.name AS name ,puse.description AS description ,puse.usageType AS usageType ,pdef.storageType AS storageType ,NVL(puse.datatype, pdef.datatype) AS datatype ,NVL(puse.hidden, NVL(pdef.grpHidden,pdef.hidden)) AS hidden ,NVL(pdef.contributeToSignature, ''true'') AS contributeToSignature ,NVL(puse.dynamicDefault, pdef.dynamicDefault) AS dynamicDefault ,NVL(puse.defaultValue, pdef.defaultValue) AS defaultValue ,puse.publishToPublicApi AS publishToPublicApi ,puse.psetId AS psetId ,puse.puseKey AS puseKey ,pdef.groupId AS groupId ,puse.createdBy AS createdBy, puse.created as created ,puse.updatedBy AS updatedBy, puse.updated AS updated FROM --4 propdef.puses.puse2.pdef BEG ( /* SUB_PDEF_W_META pdef */ -- CREATE OR REPLACE VIEW SUB_PDEF_W_META /* property definition with meta DefaultValue */ SELECT pdef.elementId AS id ,pdef.name AS name ,pdef.description AS description ,decode(pdef.propertyType, 0, ''CORE'', 1, ''LOGICAL'', 2, ''CONFIG'', 3, ''USERDEF'') AS storageType ,pdef.datatype AS datatype ,pdef.hidden AS hidden ,(SELECT hidden FROM PropertyGroupDefinition_v WHERE elementId=pdef.PropertyGroupDefinition AND hidden=''1'') AS grpHidden ,(SELECT value FROM WBPrimitiveProperty_v WHERE name=''ContributeToSignature'' AND owner=pdef.elementId) AS contributeToSignature ,pdef.dynamicDefault AS dynamicDefault ,dflt.value AS defaultValue ,pdef.propertyGroupDefinition AS groupId FROM PropertyDefinition_v pdef ,WBPrimitiveProperty_v dflt WHERE dflt.owner(+)=pdef.elementId AND dflt.name(+)=''DefaultValue'' ) /* SUB_PDEF_W_META */ pdef --4 propdef.puses.puse2.pdef END --4 propdef.puses.puse2.puse BEG ,( /* SUB_PUSE_W_META puse */ -- CREATE OR REPLACE VIEW SUB_PUSE_W_META /* property usage definition with meta DefaultValue, Hidden, DynamicDefault, USAGE_DATATYPE */ SELECT puse.elementId AS id ,puse.name AS name ,puse.description AS description ,decode(pset.propertyType, 0, ''CORE'', 1, ''LOGICAL'', 2, ''CONFIG'', 3, ''USERDEF'') AS usageType ,(SELECT datatype FROM WBPrimitiveProperty_v WHERE name=''USAGE_DATATYPE'' AND owner=puse.elementId) AS datatype ,(SELECT value FROM WBPrimitiveProperty_v WHERE name=''Hidden'' AND owner=puse.elementId) AS hidden ,(SELECT value FROM WBPrimitiveProperty_v WHERE name=''DynamicDefault'' AND owner=puse.elementId) AS dynamicDefault ,(SELECT value FROM WBPrimitiveProperty_v WHERE name=''DefaultValue'' AND owner=puse.elementId) AS defaultValue ,(SELECT value FROM WBPrimitiveProperty_v WHERE name=''isPublishedToPublicApi'' AND owner=puse.elementId) AS publishToPublicApi ,puse.definitionKey AS puseKey ,pset.elementId AS psetId ,puse.propertyDefinition AS pdefId ,puse.createdBy AS createdBy, puse.creationTimeStamp as created ,puse.updatedBy AS updatedBy, puse.updateTimeStamp AS updated FROM PropertyUsageDefinition_v puse ,PropertySetDefinition_v pset WHERE puse.propertySetDefinition=pset.elementId ) /* SUB_PUSE_W_META */ puse --4 propdef.puses.puse2.puse END WHERE puse.pdefId=pdef.id ) /* SUB_TYPED_PUSES */ puse2 --3 propdef.puses.puse2 END ,DefinitionElement_v delem WHERE puse2.datatype=dtype.typeId(+) AND puse2.datatype=delem.elementId )/* SUB_TYPED_PUSES_W_DFLT */ puses --2 propdef.puses END WHERE cdefs.parentCdefId=psets.classDef AND psets.superPsetId=puses.psetId AND puses.groupId=groupDef.elementId(+) ) WITH READ ONLY '); exec(' CREATE OR REPLACE VIEW SUB_ALL_PERSISTED_PROPS AS /* Set of all actually persisted extended property values */ SELECT * FROM ( --0 pprops BEG SELECT powners.objId, powners.objType, powners.objClass, powners.objName ,powners.propOwnerId, powners.propOwnerObjType, powners.propOwnerClass ,powners.propOwnerName, powners.propOwnerType, powners.configurationid ,powners.configuration ,pvalue.id pvalueId, pvalue.objType pvalueObjType ,pvalue.objClass pvalueObjClass, pvalue.nlsKey, pvalue.position ,pvalue.name pvalueName, pvalue.created created ,pvalue.CreatedBy createdBy, pvalue.UpdatedBy updatedBy ,pvalue.updated updated, pvalue.value FROM --1 powners BEG ( /* SUB_ALL_RELATED_PROP_OWNERS */ SELECT obj.elementId AS objId ,NVL(obj.strongTypeName, obj.classname) AS objClass ,obj.className AS objType ,obj.name AS objName -- debug ,obj.elementId AS propOwnerId ,NVL(obj.strongTypeName, obj.classname) AS propOwnerClass ,obj.className AS propOwnerObjType ,obj.name AS propOwnerName -- debug ,''LOGICAL'' AS propOwnerType -- debug ,0 AS configurationid ,'''' AS configuration FROM FirstClassobject_v obj UNION ALL SELECT fco.elementId AS objId ,NVL(fco.strongTypeName, fco.classname) AS objClass ,fco.className AS objType ,fco.name AS objName -- debug ,obj.elementId AS propOwnerId ,NVL(obj.strongTypeName, obj.classname) AS propOwnerClass ,obj.className AS propOwnerObjType ,obj.name AS propOwnerName -- debug ,''PHYSICAL'' AS propOwnerType -- debug ,cfg.elementId AS configurationid ,cfg.name AS configuration FROM FirstClassObject_v fco, CMPPhysicalObject_v obj, CMPNamedConfiguration_v cfg, CMPWBProject_v prj WHERE obj.logicalObject=fco.elementId AND obj.namedConfiguration=cfg.elementId AND cfg.owningProject=prj.elementId UNION ALL SELECT sco.elementId AS objId ,NVL(sco.strongTypeName, sco.classname) AS objClass ,sco.className AS objType ,sco.name AS objName -- debug ,sco.elementId AS propOwnerId ,NVL(sco.strongTypeName, sco.classname) AS propOwnerClass ,sco.className AS propOwnerObjType ,sco.name AS propOwnerName -- debug ,''LOGICAL'' AS propOwnerType -- debug ,0 AS configurationid ,'''' AS configuration FROM SecondClassObject_v sco WHERE NOT sco.className = ''CMPPhysicalObject'' UNION ALL SELECT sco.elementId AS objId ,NVL(sco.strongTypeName, sco.classname) AS objClass ,sco.className AS objType ,sco.name AS objName -- debug ,obj.elementId AS propOwnerId ,NVL(obj.strongTypeName, obj.classname) AS propOwnerClass ,obj.className AS propOwnerObjType ,obj.name AS propOwnerName -- debug ,''PHYSICAL'' AS propOwnerType -- debug ,cfg.elementId AS configurationid ,cfg.name AS configuration FROM SecondClassObject_v sco, CMPPhysicalObject_v obj, CMPNamedConfiguration_v cfg, CMPWBProject_v prj WHERE obj.logicalObject=sco.elementId AND obj.namedConfiguration=cfg.elementId AND cfg.owningProject=prj.elementId UNION ALL SELECT sco.elementId AS objId ,NVL(sco.strongTypeName, sco.classname) AS objClass ,sco.className AS objType ,sco.name AS objName -- debug ,obj.elementId AS propOwnerId ,NVL(obj.strongTypeName, obj.classname) AS propOwnerClass ,obj.className AS propOwnerObjType ,obj.name AS propOwnerName -- debug ,''PHYSICAL'' AS propOwnerType -- debug ,cfg.elementId AS configurationid ,cfg.name AS configuration FROM FirstClassObject_v fco, SecondClassObject_v sco, CMPPhysicalObject_v phy, CMPReferencePropertyValue_v obj, CMPNamedConfiguration_v cfg, CMPWBProject_v prj WHERE phy.logicalObject=fco.elementId AND fco.elementId=sco.firstClassObject AND obj.referencedElement=sco.elementId AND phy.namedConfiguration=cfg.elementId AND cfg.owningProject=prj.elementId /* set of property owners for object */ ) /* SUB_ALL_RELATED_PROP_OWNERS */ powners --1 powners END --1 pvalue BEG ,( /* SUB_ALL_PROP_VALUES */ -- CREATE OR REPLACE VIEW SUB_ALL_PROP_VALUES /* CMPPropertyValue with display values */ SELECT propertyOwner AS owner ,logicalName AS nlsKey ,position AS position ,name AS name ,elementId AS id ,classname AS objType ,NVL(strongTypeName, classname) AS objClass ,creationTimeStamp AS created ,createdBy AS createdBy ,updatedBy AS updatedBy ,updateTimeStamp AS updated ,value AS value FROM CMPStringPropertyValue_v UNION ALL SELECT propertyOwner ,logicalName ,position ,name ,elementId ,classname AS objType ,NVL(strongTypeName, classname) AS objClass ,creationTimeStamp AS created ,createdBy AS createdBy ,updatedBy AS updatedBy ,updateTimeStamp AS updated ,TO_CHAR(value) AS value FROM CMPCLOBPropertyValue_v UNION ALL SELECT refp.propertyOwner ,refp.logicalName ,refp.position ,refp.name ,refp.elementId ,refp.classname AS objType ,NVL(refp.strongTypeName, refp.classname) AS objClass ,refp.creationTimeStamp AS created ,refp.createdBy AS createdBy ,refp.updatedBy AS updatedBy ,refp.updateTimeStamp AS updated ,elem.name AS value FROM CMPReferencePropertyValue_v refp ,CMPElement_v elem WHERE refp.referencedElement=elem.elementId(+) UNION ALL SELECT propertyOwner ,logicalName ,position ,name ,elementId ,classname AS objType ,NVL(strongTypeName, classname) AS objClass ,creationTimeStamp AS created ,createdBy AS createdBy ,updatedBy AS updatedBy ,updateTimeStamp AS updated ,name AS value FROM CMPPurePhysicalObject_v ) /* SUB_ALL_PROP_VALUES */ pvalue --1 pvalue END WHERE powners.propOwnerId = pvalue.owner ORDER BY objid, nlsKey, position ) WITH READ ONLY /* SUB_ALL_PERSISTED_PROPS */ -- pprops --0 pprops END -- CREATE OR REPLACE VIEW SUB_ALL_PERSISTED_PROPS END '); /* Handy snippet for getting col name/type list for table/view column ColumnName format a30; column Type format a30; select column_name "ColumnName", (data_type || '(' || data_length || ')') "Type" from user_tab_columns where table_name like 'SUB%'; ColumnName Type ------------------------------ ------------------------------ */ exec(' CREATE OR REPLACE VIEW ALL_IV_EXT_OBJECT_PROPERTIES AS /* Set of all property usages by object with (default or user modified) values */ SELECT objId AS object_id, OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(objid, objclass) AS object_type, objName AS object_name, configurationid AS configuration_id, configuration AS configuration_name, groupid AS property_group_id, groupName AS property_group_name, groupdefinitionkey AS property_group_nls_key, psetid AS property_set_id, psetClassName AS property_set_class_name, psetname AS property_set_name, pusesid AS property_usage_id, storagetype AS property_storage_type, usagetype AS property_usage_type, propName AS property_name, nlskey AS property_nls_key, datatype AS property_datatype_id, typename AS property_datatype_name, contributeToSignature AS property_affects_signature, dynamicdefault AS property_dynamic_default, hidden AS property_is_hidden, publishToPublicApi AS property_publish_to_public_api, defaultvalue AS property_default_value, propOwnerId AS property_value_owner_id, pvalueid AS property_value_id, pvalueidx AS property_value_index, pvaluename AS property_value_name, -- If property_datatype_name begins with EXPRESSION, need to pass -- through fct to get display value (resolve references) DECODE(INSTR(typename, ''EXPRESSION''), 1, OWM_VIEW_UTILITIES.PARAMETER_EXPRESSION2(value, objid), value) AS property_value, description, updated_on, created_on, updated_by, created_by FROM ( SELECT propdef.objId, propdef.objClass, propdef.objName, pvalue.configuration, pvalue.configurationid, pvalue.propOwnerId ,propdef.pusesId, propdef.propName, propdef.description, propdef.usageType, propdef.storageType ,propdef.datatype, propdef.typeName, propdef.hidden, propdef.publishToPublicApi, propdef.contributeToSignature, propdef.dynamicDefault ,propdef.defaultValue, pvalue.position AS pvalueidx, pvalue.pvalueId as pvalueid, pvalue.pvalueName as pvaluename ,NVL(pvalue.value, propdef.defaultValue) AS value, propdef.psetId ,propdef.psetName, propdef.psetClassName, propdef.nlsKey ,propdef.groupId, propdef.groupName, propdef.groupDefinitionKey ,NVL(pvalue.created, propdef.created) AS created_on ,NVL(pvalue.createdBy, propDef.createdBy) AS created_by ,NVL(pvalue.updated, propdef.updated) AS updated_on ,NVL(pvalue.updatedBy, propDef.updatedBy) AS updated_by FROM ( SELECT element.elementId AS objId, propdef2.classTypeName AS objClass, element.name AS objName ,propdef2.pusageId pusesId, propdef2.propName, propdef2.description, propdef2.pusageType AS usageType, propdef2.storageType ,propdef2.datatype, propdef2.typeName, propdef2.hidden, propdef2.publishToPublicApi, propdef2.contributeToSignature, propdef2.dynamicDefault ,propdef2.defaultValue, propdef2.psetId, propdef2.psetName, propdef2.psetClassName, propdef2.nlsKey ,propdef2.groupId, propdef2.groupName, propdef2.groupDefinitionKey ,propdef2.createdby, propdef2.created, propdef2.updatedby, propdef2.updated FROM ( SELECT elementId, name , NVL(strongTypeName, classname) AS classTypeName FROM CMPElement_V ) element ,SUB_FQ_PUSAGE_DEF propdef2 WHERE element.classTypeName = propdef2.classTypeName ) propdef ,SUB_ALL_PERSISTED_PROPS pvalue WHERE ( propdef.objId = pvalue.objId(+) AND propdef.nlsKey = pvalue.nlsKey(+) ) ) WITH READ ONLY '); exec(' CREATE OR REPLACE VIEW ALL_IV_OBJECT_CONFIGURATIONS AS /* Revised version of previously existing query now based on new views */ SELECT object_id AS configured_object_id, object_type AS configured_object_type, object_name AS configured_object_name, ''8i.DUMMY'' AS configuration_parameter_key, property_name AS configuration_parameter_name, property_nls_key AS parameter_nlskey, ''CONFIGURATION_PARAMETER'' AS configuration_parameter_type, property_value AS argument, property_group_name AS group_name, property_group_nls_key AS group_nlskey, ''en'' AS language FROM ALL_IV_EXT_OBJECT_PROPERTIES WHERE property_usage_type = ''CONFIG'' AND property_is_hidden = ''false'' WITH READ ONLY '); exec(' CREATE OR REPLACE VIEW ALL_IV_XFORM_MAP_PROPERTIES AS /* Revised version of previously existing query now based on new views */ SELECT object_id AS map_component_id, object_name AS map_component_name, property_value_id AS property_id, property_name AS property_name, property_nls_key AS business_name, description AS description, property_group_name AS property_group_name, property_value AS property_value FROM ALL_IV_EXT_OBJECT_PROPERTIES WHERE object_id IN (SELECT elementid FROM CMPMapoperator_V) AND property_usage_type = ''LOGICAL'' AND property_is_hidden = ''false'' WITH READ ONLY '); exec(' create or replace view ALL_IV_SCHEDULABLE as /* Set of schedulable objects and their schedules */ select obj.object_id as object_id ,obj.object_type as object_type ,obj.object_name as object_name ,obj.configuration_id as configuration_id ,obj.configuration_name as configuration_name ,cal.elementid as schedule_id ,cal.name as schedule_name from ( select fco.elementid as object_id ,OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(fco.elementid, fco.classname) as object_type ,fco.name as object_name ,cfg.namedconfiguration as configuration_id ,cfg.name as configuration_name ,prop.value as schedule_uoid from FirstClassObject_V fco ,CMPPhysicalObject_V cfg ,CMPStringPropertyValue_V prop where fco.classname in (''CMPMap'', ''CMPDataRuleMap'', ''CMPProcess'') and cfg.logicalobject = fco.elementid and prop.propertyowner(+) = cfg.elementid and prop.logicalname(+) = ''SCHEDULABLE.PROPERTY'' ) obj ,CMPCalendar_V cal where cal.uoid(+) = obj.schedule_uoid with read only '); exec(' create or replace view ALL_IV_CONFIGURATIONS as /* Set of projects and the configurations defined for each */ select prj.elementid as PROJECT_ID ,prj.name as PROJECT_NAME ,cfg.elementid as CONFIGURATION_ID ,cfg.name as CONFIGURATION_NAME ,cfg.createdBy AS CREATED_BY ,cfg.updatedBy AS UPDATED_BY ,cfg.creationTimeStamp AS CREATED_ON ,cfg.updateTimeStamp AS UPDATED_ON ,temp.name as CONFIG_TEMPLATE_SET from cmpwbproject_v prj ,cmpnamedconfiguration_v cfg ,cmpuserconfigtemplateset_v temp where cfg.owningproject = prj.elementid and temp.elementid (+) = cfg.configtemplateset with read only '); /* Table for view text - used by owb_uprop[_tune] packages */ exec(' DROP TABLE PUBLIC_VIEWS '); exec(' CREATE TABLE PUBLIC_VIEWS ( VIEW_NAME VARCHAR2(40) NOT NULL, SQL_TEXT CLOB, USERNAME CHAR(40) NOT NULL ) '); exec(' ALTER TABLE PUBLIC_VIEWS ADD CONSTRAINT PUBLIC_VIEWS_PK PRIMARY KEY ( VIEW_NAME ) ENABLE '); exec(' INSERT INTO PUBLIC_VIEWS (VIEW_NAME, SQL_TEXT, USERNAME) SELECT VIEW_NAME, TO_LOB(TEXT), ''OWB'' FROM USER_VIEWS WHERE VIEW_NAME LIKE ''ALL_IV%'' '); -- Don't forget all system names (e.g., view names) are UPPERCASE no matter -- how they were specified! exec(' BEGIN --WARNING! DBMS_OUTPUT not supported by RuntimeInstaller --DBMS_OUTPUT.ENABLE(100000); INSERT INTO PUBLIC_VIEWS (VIEW_NAME, SQL_TEXT, USERNAME) SELECT VIEW_NAME, TO_LOB(TEXT), ''OWB_UPROP'' FROM USER_VIEWS WHERE VIEW_NAME = ''SUB_FQ_PUSAGE_DEF''; --DBMS_OUTPUT.PUT_LINE(''PUBLIC_VIEWS INSERTED''); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN BEGIN UPDATE PUBLIC_VIEWS SET SQL_TEXT=( SELECT TO_LOB(TEXT) FROM USER_VIEWS WHERE VIEW_NAME = ''SUB_FQ_PUSAGE_DEF'' ) WHERE VIEW_NAME=''SUB_FQ_PUSAGE_DEF''; --DBMS_OUTPUT.PUT_LINE(''PUBLIC_VIEWS UPDATED''); END; END; '); exec(' BEGIN --DBMS_OUTPUT.ENABLE(100000); INSERT INTO PUBLIC_VIEWS (VIEW_NAME, SQL_TEXT, USERNAME) SELECT VIEW_NAME, TO_LOB(TEXT), ''OWB_UPROP'' FROM USER_VIEWS WHERE VIEW_NAME = ''SUB_ALL_PERSISTED_PROPS''; --DBMS_OUTPUT.PUT_LINE(''PUBLIC_VIEWS INSERTED''); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN BEGIN -- MUST begin its own line for RuntimeInstaller UPDATE PUBLIC_VIEWS SET SQL_TEXT=( SELECT TO_LOB(TEXT) FROM USER_VIEWS WHERE VIEW_NAME = ''SUB_ALL_PERSISTED_PROPS'' ) WHERE VIEW_NAME=''SUB_ALL_PERSISTED_PROPS''; --DBMS_OUTPUT.PUT_LINE(''PUBLIC_VIEWS UPDATED''); END; END; '); -- Parameterized queries supporting table functions. These are put in a -- table so SQL tuning can be run against them (generally giving exponential -- performance improvement.) -- getFqPropertyUsageDefinitions(cdefTypeName, includeHidden default 'false') exec(' BEGIN DECLARE my_procname VARCHAR2(32) := ''getFqPropertyUsageDefinitions''; my_qry CLOB; BEGIN SELECT SQL_TEXT || '' WHERE classTypeName LIKE :type '' || '' AND hidden IN (''''false'''', :hidden) '' || '' ORDER BY classTypeName, propName'' INTO my_qry FROM PUBLIC_VIEWS WHERE VIEW_NAME = ''SUB_FQ_PUSAGE_DEF''; INSERT INTO PUBLIC_VIEWS (VIEW_NAME, SQL_TEXT, USERNAME) VALUES(my_procname, my_qry, ''OWB_UPROP''); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE PUBLIC_VIEWS SET SQL_TEXT=my_qry WHERE VIEW_NAME=my_procname; END; END; '); -- getProperties(object_id, configuration_id, property_types{}, includeHidden default 'false') exec(' BEGIN DECLARE my_procname VARCHAR2(32) := ''getProperties''; my_qry CLOB; BEGIN SELECT SQL_TEXT || '' WHERE object_id = :objid '' || '' AND configuration_id IN (0, :cfgid) '' || '' AND property_usage_type IN (:ptype1, :ptype2, :ptype3) '' || '' AND hidden IN (''''false'''', :hidden) '' INTO my_qry FROM PUBLIC_VIEWS WHERE VIEW_NAME = ''ALL_IV_EXT_OBJECT_PROPERTIES''; INSERT INTO PUBLIC_VIEWS (VIEW_NAME, SQL_TEXT, USERNAME) VALUES(my_procname, my_qry, ''OWB_UPROP''); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE PUBLIC_VIEWS SET SQL_TEXT=my_qry WHERE VIEW_NAME=my_procname; END; END; '); END; -- Inner block for exec function usage END; -- Outer block for exec function /