-- Create sql in-place upgrade patch for values -- inside owbb_types.xml and owbb_images.xml. -- -- Bug #'s/Table OWBB_IMAGES, OWBB_TYPES updated -- 8283760: ADD SUPPORT FOR ASSIGNING DIFFERENT ICONS/TYPES FOR NATIVE VS GATEWAY MOD/APPL -- SET SERVEROUTPUT ON SET LINESIZE 500 -- ########################################################################## DECLARE -- Outer block for utility functions/variables BUGBUG: Should these be packagized (uprop_util) -- ########################################################################## SCRIPTID CONSTANT VARCHAR2(40) := 'bug 8283760'; -- 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 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; -- ========================================================================== -- Update the icons of DB2Module and SQLServerModule. PROCEDURE updateDB2SSIcons IS -- ========================================================================== db2_count NUMBER := 0; ss_count NUMBER := 0; BEGIN select count(1) into db2_count from owbb_images where image_name = 'DB2Module'; select count(1) into ss_count from owbb_images where image_name = 'SQLServerModule'; if(db2_count = 1 or ss_count = 1) then update owbb_images set gif_file = 'T_dataWarehouseObj.gif' where image_name in ('DB2Module', 'SQLServerModule'); if(db2_count = 1) then put('Updated the gif file for DB2Module!'); end if; if(ss_count = 1) then put('Updated the gif file for SQLServerModule!'); end if; else put('Can not find any records with image name ' || 'DB2Module or SQLServerModule' || '!' || EOL); end if; END; -- ========================================================================== -- Update the icons and type names of DB2Module and SQLServerModule. PROCEDURE updateAppIconTypeName IS -- ========================================================================== qry_text CLOB := 'select '':repos'' as "RepositoryId", ''Y'' as "HasChildren", m.information_system_name as "ObjectName", decode(m.information_system_type, ''DataWarehouse'', ''B'', ''DB2Module'', ''C0'', ''DB2GatewayModule'', ''C0'', ''SqlserverGatewayModule'', ''C1'', ''SQLServerModule'', ''C1'', ''SybaseGatewayModule'', ''C2'', ''SybaseModule'', ''C2'', ''InformixGatewayModule'', ''C3'', ''InformixModule'', ''C3'', ''TeradataGatewayModule'', ''C4'', ''TeradataModule'', ''C4'', ''RDBGatewayModule'', ''C5'', ''RDBModule'', ''C5'', ''DRDAGatewayModule'', ''C6'', ''ODBCGatewayModule'', ''C7'', ''OtherGatewayModule'', ''C8'', ''FileModule'', ''D'', ''CustomerApplicationModule'', ''E5'', ''ProcessModule'', ''J'', ''OracleBIModule'', ''L1'', ''OracleDiscovererModule'', ''L2'', ''TransportableModule'', ''P'', ''DataRuleModule'', ''H'', ''ScheduleModule'', ''K'', ''DataProfile'', ''G'', ''ExpertModule'', ''O'', ''CodeTemplateFolder'', ''N'', ''SAPApplication'', ''E3'', ''OracleEBusinessSuiteApplication'', ''E1'', ''PeopleSoftApplication'', ''E2'', ''SiebelApplication'', ''E4'', ''ApplicationServerModule'', ''F'', ''MappingModule'', ''A'' ) as "sortHidden", decode(m.information_system_type, ''DataWarehouse'', owbb_uix.image(''DataWarehouse'', ''S''), ''DB2Module'', owbb_uix.image(''DB2Module'', ''S''), ''DB2GatewayModule'', owbb_uix.image(''GatewayModule'', ''S''), ''SqlserverGatewayModule'', owbb_uix.image(''GatewayModule'', ''S''), ''SQLServerModule'', owbb_uix.image(''SQLServerModule'', ''S''), ''SybaseGatewayModule'', owbb_uix.image(''GatewayModule'', ''S''), ''SybaseModule'', owbb_uix.image(''SybaseModule'', ''S''), ''InformixGatewayModule'', owbb_uix.image(''GatewayModule'', ''S''), ''InformixModule'', owbb_uix.image(''InformixModule'', ''S''), ''TeradataGatewayModule'', owbb_uix.image(''GatewayModule'', ''S''), ''TeradataModule'', owbb_uix.image(''TeradataModule'', ''S''), ''RDBGatewayModule'', owbb_uix.image(''GatewayModule'', ''S''), ''RDBModule'', owbb_uix.image(''RDBModule'', ''S''), ''DRDAGatewayModule'', owbb_uix.image(''GatewayModule'', ''S''), ''ODBCGatewayModule'', owbb_uix.image(''GatewayModule'', ''S''), ''FileModule'', owbb_uix.image(''FileModule'', ''S''), ''CustomerApplicationModule'', owbb_uix.image(''MIVModule'', ''S''), ''ProcessModule'', owbb_uix.image(''ProcessModule'', ''S''), ''OracleBIModule'', owbb_uix.image(''OracleBIModule'', ''S''), ''OracleDiscovererModule'', owbb_uix.image(''OracleDiscovererModule'', ''S''), ''DataRuleModule'', owbb_uix.image(''DataRuleModule'', ''S''), ''ScheduleModule'', owbb_uix.image(''ScheduleModule'', ''S''), ''DataProfile'', owbb_uix.image(''DataProfile'', ''S''), ''ExpertModule'', owbb_uix.image(''ExpertModule'', ''S''), ''CodeTemplateFolder'', owbb_uix.image(''CodeTemplateFolder'', ''S''), ''SAPApplication'', owbb_uix.image(''SAPApplication'', ''S''), ''OracleEBusinessSuiteApplication'', (select decode(isys.location_id, NUll, owbb_uix.image(''OracleEBusinessSuiteApplication'', ''S''), (select decode(l.location_target_type, ''Oracle Gateway'', owbb_uix.image(''GatewayModule'', ''S''), ''Oracle Database'', owbb_uix.image(''OracleEBusinessSuiteApplication'', ''S'')) from all_iv_information_systems im, all_iv_locations l where im.location_id = l.location_id and im.information_system_id = m.information_system_id)) from all_iv_information_systems isys where isys.information_system_id = m.information_system_id), ''PeopleSoftApplication'', (select decode(isys.location_id, NUll, owbb_uix.image(''PeopleSoftApplication'', ''S''), (select decode(l.location_target_type, ''Oracle Gateway'', owbb_uix.image(''GatewayModule'', ''S''), ''Oracle Database'', owbb_uix.image(''PeopleSoftApplication'', ''S'')) from all_iv_information_systems im, all_iv_locations l where im.location_id = l.location_id and im.information_system_id = m.information_system_id)) from all_iv_information_systems isys where isys.information_system_id = m.information_system_id), ''SiebelApplication'', (select decode(isys.location_id, NUll, owbb_uix.image(''SiebelApplication'', ''S''), (select decode(l.location_target_type, ''Oracle Gateway'', owbb_uix.image(''GatewayModule'', ''S''), ''Oracle Database'', owbb_uix.image(''SiebelApplication'', ''S'')) from all_iv_information_systems im, all_iv_locations l where im.location_id = l.location_id and im.information_system_id = m.information_system_id)) from all_iv_information_systems isys where isys.information_system_id = m.information_system_id), ''ApplicationServerModule'', owbb_uix.image(''ApplicationServerModule'', ''S''), ''MappingModule'', owbb_uix.image(''MappingModule'', ''S'') ) as "ObjectIcon", decode(m.information_system_type, ''DataWarehouse'', ''10466'', ''DB2Module'', ''10925'', ''SQLServerModule'', ''10926'', ''DB2GatewayModule'', ''10786'', ''SqlserverGatewayModule'', ''10787'', ''SybaseGatewayModule'', ''10788'', ''SybaseModule'', ''11046'', ''InformixGatewayModule'', ''10789'', ''InformixModule'', ''11042'', ''TeradataGatewayModule'', ''10790'', ''TeradataModule'', ''11047'', ''RDBGatewayModule'', ''10791'', ''RDBModule'', ''11045'', ''DRDAGatewayModule'', ''10792'', ''ODBCGatewayModule'', ''10793'', ''FileModule'', ''10467'', ''ProcessModule'', ''10468'', ''OracleBIModule'', ''10638'', ''OracleDiscovererModule'', ''10637'', ''DataRuleModule'', ''10795'', ''ScheduleModule'', ''10796'', ''DataProfile'', ''10784'', ''CodeTemplateFolder'', ''10928'', ''ExpertModule'', ''10800'', ''SAPApplication'', ''10801'', ''OracleEBusinessSuiteApplication'', (select decode(isys.location_id, NUll, ''10802'', (select decode(l.location_target_type, ''Oracle Gateway'', ''11116'', ''Oracle Database'', ''10802'') from all_iv_information_systems im, all_iv_locations l where im.location_id = l.location_id and im.information_system_id = m.information_system_id)) from all_iv_information_systems isys where isys.information_system_id = m.information_system_id), ''PeopleSoftApplication'', (select decode(isys.location_id, NUll, ''10918'', (select decode(l.location_target_type, ''Oracle Gateway'', ''11117'', ''Oracle Database'', ''10918'') from all_iv_information_systems im, all_iv_locations l where im.location_id = l.location_id and im.information_system_id = m.information_system_id)) from all_iv_information_systems isys where isys.information_system_id = m.information_system_id), ''SiebelApplication'', (select decode(isys.location_id, NUll, ''10802'',(select decode(l.location_target_type, ''Oracle Gateway'', ''11118'', ''Oracle Database'', ''10927'') from all_iv_information_systems im, all_iv_locations l where im.location_id = l.location_id and im.information_system_id = m.information_system_id)) from all_iv_information_systems isys where isys.information_system_id = m.information_system_id), ''CustomerApplicationModule'', ''10812'', ''ApplicationServerModule'', ''10963'', ''MappingModule'', ''10974'' ) as "ObjectNLSId", m.is_valid as "IsValid", m.information_system_id as "ObjectId", m.information_system_type as "ObjectType" from ALL_IV_INFORMATION_SYSTEMS@# m where m.project_id = :id and m.project_name != ''Admin Project'' and m.project_name != ''PUBLIC_PROJECT'' and m.information_system_type not like ''PreDefinedModule'' and m.information_system_type not like ''CustomerDatabaseModule'' and m.information_system_type not like ''UserDefinedModule'' UNION select '':repos'' as "RepositoryId", ''Y'' as "HasChildren", m.information_system_name as "ObjectName", ''C9'' as "sortHidden", owbb_uix.image(''GatewayModule'',''S'') as "ObjectIcon", d.platform_name as "ObjectNLSId", ''N/A'' as "IsValid", m.information_system_id as "ObjectId", ''CustomerDatabaseModule'' as "ObjectType" from ALL_IV_INFORMATION_SYSTEMS@# m, ALL_IV_DATABASE_MODULES@# d where m.project_id = :id and d.information_system_id = m.information_system_id and m.information_system_type like ''CustomerDatabaseModule'' UNION ALL select '':repos'' as "RepositoryId", ''Y'' as "HasChildren", m.schema_name as "ObjectName", ''M'' as "sortHidden", owbb_uix.image(''UserDefinedModule'', ''S'') as "ObjectIcon", ''10797'' as "ObjectNLSId", ''N/A'' as "IsValid", m.schema_id as "ObjectId", ''UserDefinedModule'' as "ObjectType" from ALL_IV_UDO_MODULES@# m where m.project_id = :id and m.module_type is not null UNION ALL select '':repos'' as "RepositoryId", ''Y'' as "HasChildren", m.tm_name as "ObjectName", ''P'' as "sortHidden", owbb_uix.image(''TransportableModule'', ''S'') as "ObjectIcon", ''10470'' as "ObjectNLSId", ''N/A'' as "IsValid", m.tm_id as "ObjectId", ''TransportableModule'' as "ObjectType" from ALL_IV_TM_MODULES@# m where m.project_id = :id UNION ALL select '':repos'' as "RepositoryId", ''Y'' as "HasChildren", f.activity_folder_name as "ObjectName", ''J2'' as "sortHidden", owbb_uix.image(''ProcessActivityFolder'', ''S'') as "ObjectIcon", ''10799'' as "ObjectNLSId", ''N/A'' as "IsValid", f.activity_folder_id as "ObjectId", ''ProcessActivityFolder'' as "ObjectType" from ALL_IV_ACTIVITY_FOLDERS@# f where f.project_id = :id UNION ALL select '':repos'' as "RepositoryId", ''Y'' as "HasChildren", i.collection_name as "ObjectName", ''Q'' as "sortHidden", owbb_uix.image(''Collection'',''S'') as "ObjectIcon", ''10427'' as "ObjectNLSId", ''N/A'' as "IsValid", i.collection_id as "ObjectId", ''Collection'' as "ObjectType" from ALL_IV_COLLECTIONS@# i where i.project_id = :id UNION ALL select '':repos'' as "RepositoryId", ''Y'' as "HasChildren", i.map_library_name as "ObjectName", ''I2'' as "sortHidden", owbb_uix.image(''PluggableMapFolder'',''S'') as "ObjectIcon", ''10803'' as "ObjectNLSId", ''N/A'' as "IsValid", i.map_library_id as "ObjectId", ''PluggableMapFolder'' as "ObjectType" from ALL_IV_PLUGGABLE_MAP_LIBRARIES@# i where i.project_id = :id UNION ALL select '':repos'' as "RepositoryId", ''Y'' as "HasChildren", i.map_name as "ObjectName", ''I1'' as "sortHidden", owbb_uix.image(''PluggableMap'',''S'') as "ObjectIcon", ''10804'' as "ObjectNLSId", i.is_valid as "IsValid", i.map_id as "ObjectId", ''PluggableMap'' as "ObjectType" from ALL_IV_PLUGGABLE_MAPS@# i where i.project_id = :id and i.map_library_id is null order by 4, 9, 3'; qry_count NUMBER := 0; BEGIN select count(1) into qry_count from owbb_queries where type_name = 'Project' and usage = 'CO'; if(qry_count = 1) then update owbb_queries set query_text = qry_text where type_name = 'Project' and usage = 'CO'; put('Updated the query text for Project with usage ''CO''! ' || EOL); else put('Can not find any queries for Project with usage ''CO''! ' || EOL); end if; END; -- ########################################################################## -- ########################################################################## BEGIN -- Outer block - utility functions/variables now defined -- ########################################################################## -- ########################################################################## updateDB2SSIcons; updateAppIconTypeName; commit; -- ########################################################################## END; -- Outer block for utility functions/variables -- ########################################################################## /