-- In-place Design Repository Upgrade to OWB 11.2.0.3 ...

ACCEPT V_HOST PROMPT 'Host Name: '
ACCEPT V_PORT PROMPT 'Port Number: '
ACCEPT V_SERVICENAME PROMPT 'Service Name: '
ACCEPT V_OWBSYS_PASSWORD PROMPT 'Existing OWBSYS Password: ' HIDE
ACCEPT V_SYS_USER_NAME PROMPT 'SYS User Name: '
ACCEPT V_SYS_USER_PASSWORD PROMPT 'SYS User Password: '  HIDE
PROMPT 
PROMPT Review above entries and press
ACCEPT V_DUMMY PROMPT ' <ENTER> to continue or Ctrl-C to cancel'

SPOOL LOG.TXT

set serveroutput on
set serveroutput on size 100000

PROMPT ==============================================================
PROMPT Start Repository upgrade from OWB 11.2.0.x to OWB 11.2.0.3 ...
PROMPT ==============================================================
 
WHENEVER SQLERROR EXIT FAILURE
connect OWBSYS/&V_OWBSYS_PASSWORD@(DESCRIPTION=(ADDRESS=(HOST= &V_HOST )(PROTOCOL=tcp)(PORT= &V_PORT ))(CONNECT_DATA=(SERVICE_NAME= &V_SERVICENAME )))


PROMPT ==============================================================
PROMPT Checking Preconditions
PROMPT ==============================================================
declare
    notExecuteRtMgr EXCEPTION;
    hasRunningRtMgr number default 0;
    p_v number default 0;
begin
     select instr(version,'10.') into p_v from v$instance;
     if p_v <> 1 then 
          select count(*) into hasRunningRtMgr from all_objects where owner = 'OWBSYS' and object_type = 'JAVA CLASS' and dbms_java.longname(object_name) = 'oracle/wh/runtime/server/RuntimePropertiesManagement';
          if hasRunningRtMgr = 0 then
               DBMS_OUTPUT.put_line('ERROR: Please run rt_mgr.sh or rt_mgr.bat prior to executing this sql script. rt_mgr.sh or rt_mgr.bat can be located in <OWB_HOME>/owb/bin/unix or <OWB_HOME>\owb\bin\win32.');
               raise notExecuteRtMgr;
          end if;
      end if;    
end;
/

WHENEVER SQLERROR CONTINUE

PROMPT ==============================================================
PROMPT Reposs Differences
PROMPT ==============================================================
@../../reposasst/cre_misc.sql;

PROMPT bug 7359939,9141940,7524397,8313447,8313547,9308040,12368340
@../../reposasst/Snapshot.pks
@../../reposasst/Snapshot.pkb
@../../reposasst/SnapshotCreateRestore.plb

PROMPT bug 10205394
@../../reposasst/upg/bug_10205394.sql

PROMPT bug 8557858,9249708
@../../reposasst/dep_structural_view.sql

PROMPT bug 9141911
@../../reposasst/dep_generic_body.plb

PROMPT bug 9141920
@../../reposasst/dep_util_body.plb

PROMPT bug 9141924
@../../reposasst/OptimizerStatistics.pkb

PROMPT bug 9141939
@../../reposasst/upg/bug_9141939.sql

PROMPT bug 9141964,9234620
@../../reposasst/secHelper.pks
@../../reposasst/secHelper.plb

PROMPT bug 9142080,5966712,9861228,9812613
@bug_5966712.sql
@../../reposasst/WorkspaceManagement.pks
@../../reposasst/WorkspaceManagement_body.plb

PROMPT bug 9142097,9142010,9142006,9142002,9141996,9141990,9141986,9141983,9141980
-- Comment this line out, and utilize wb_rt_trans_plsql.pls to do the upgrade
-- @../../reposasst/trans_plsql.sql

PROMPT bug 8309093
@../../reposasst/upg/bug_8309093.sql
@../../reposasst/owb_user_role_privs.sql
@../../reposasst/dep_dataflow_body.plb

PROMPT bug 9062717 
@../../reposasst/upg/bug_9062717.sql

PROMPT bug 6728479 
@../../reposasst/upg/bug_6728479.sql

PROMPT bug 6785906 
@../../reposasst/upg/bug_6785906.sql

PROMPT bug 9246932
@../../reposasst/upg/bug_9246932.sql

PROMPT bug 8437739
@../../reposasst/upg/bug_8437739.sql

PROMPT bug 8517147
@../../reposasst/upg/bug_8517147.sql

PROMPT bug 8566674
@../../reposasst/upg/bug_8566674.sql

PROMPT bug 8569523
@../../reposasst/upg/bug_8569523.sql

PROMPT bug 9209524
@../../reposasst/upg/bug_9209524.sql

PROMPT bug 9219601
@../../reposasst/upg/bug_9219601.sql

PROMPT bug 8283760
@../../reposasst/upg/bug_8283760.sql

PROMPT bug 8736199
@../../reposasst/upg/bug_8736199.sql

PROMPT bug 9048066
@../../reposasst/upg/bug_9048066.sql

PROMPT bug 9276226
@../../reposasst/upg/bug_9276226.sql

PROMPT bug 8471792
@../../reposasst/upg/bug_8471792.sql

PROMPT bug 10190023,10167139
@../../rtasst/wb_rti_exceptions.pls
@../../rtasst/wb_rti_exceptions.plb
@../../rtasst/wb_rti_execution.plb
@../../reposasst/upg/bug_10190023.sql 

PROMPT bug 9904069
@../../reposasst/upg/bug_9904069.sql

PROMPT bug 9276601
@../../reposasst/upg/bug_9276601.sql

PROMPT bug 9292539
@../../reposasst/upg/bug_9292539.sql

PROMPT bug 8516174
@../../reposasst/upg/bug_8516174.sql

PROMPT bug 7154050
@../../reposasst/upg/bug_7154050.sql

PROMPT bug 11077975
@../../reposasst/upg/bug_11077975.sql

PROMPT bug 10373703
@../../reposasst/upg/bug_10373703.sql

PROMPT bug 12333083
@../../reposasst/upg/bug_12333083.sql

PROMPT bug 8321692 
@../../reposasst/SnapshotDataTypeService.pkb

PROMPT bug 9141956,9141946
@../../reposasst/public_view_sec_util.pks
@../../reposasst/public_view_sec_util.plb
@../../reposasst/upg/bug_9141956.sql
@../../reposasst/public_view_sec.plb


PROMPT ==============================================================
PROMPT Runtime Differences
PROMPT ==============================================================
PROMPT bug 11734298 10224860 10196014
@../../rtasst/wb_rt_deploy_pv.sql;

PROMPT bug 9955095
@../../patches/bug9955095/fix9955095.sql

PROMPT bug 5504823
@../../rtasst/wb_rt_service_management.plb

PROMPT bug 9832043
@../../rtasst/wb_rt_mapaudit_util_invoker.plb

PROMPT bug 9294304,9142072,9832172,9832193,9844434,12382112
@../../rtasst/wb_rt_audit_shadow_table.pls
@../../rtasst/wb_rt_audit_shadow_table.plb

PROMPT bug 11772193
@../../rtasst/wb_rt_reset_location.pls
@../../rtasst/wb_rt_reset_location.plb

PROMPT bug 834876 9834879
@../../rtasst/wb_rt_mapaudit_util.pls
@../../rtasst/wb_rt_mapaudit_util.plb

PROMPT bug 9142086,9834889
@../../rtasst/wb_rt_task_variable.plb
@../../rtasst/wb_rt_conversions.plb

PROMPT bug 9142083
@../../rtasst/wb_rt_upgrade_ts.plb

PROMPT bug 9142063
@../../rtasst/wb_rt_data_map_threshold.plb

PROMPT bug 9142027
@../../rtasst/wb_rtu_transaction_handler.plb

PROMPT bug 9141962,9142097,9142010,9142006,9142002,9141996,9141990,9141986,9141983,9141980
@../../rtasst/wb_rt_trans_plsql.pls

PROMPT bug 9142032,9142037,9142062
@../../rtasst/wb_rt_functionaldependency.sql

PROMPT bug 9402928,9142111,9142101,9196722,9835964,12707096
@../../rtasst/wb_rt_upgrade_actions.pls
@../../rtasst/wb_rt_upgrade_actions.plb
@../../rtasst/wb_rt_upgrade.plb
@../../rtasst/wb_rti_object_catalog.plb

PROMPT bug 9142108
@../../rtasst/wb_rt_pattern_analysis.plb

PROMPT bug 9142088
@../../rtasst/wb_rt_dq.plb

PROMPT bug 9142029
@../../rtasst/wb_rt_data_map_threshold.plb

PROMPT bug 9142118,9832111,9832146,12706895
@../../rtasst/wb_rt_profile_service.pls
@../../rtasst/wb_rt_profile_service.plb

PROMPT bug 9142128
@../../rtasst/wb_rt_store_registration.plb

PROMPT bug 9142094
@../../rtasst/wb_rt_ie_blob.plb

PROMPT bug 9142070,9834884
@../../rtasst/wb_rt_audit_purge.plb

PROMPT bug 9142021,9844747
@../../rtasst/wb_rti_target_schema_privs.plb

PROMPT bug 7441712
@../../rtasst/wb_rt_profile_service.pls
@../../reposasst/upg/bug_7441712.sql

PROMPT bug 9142078,9142013,9141968,9844627
@../../rtasst/wbolapplsql.sql
@../../reposasst/upg/bug_7441712.sql

PROMPT bug 11687172 - run load_java.sql as well
@../../reposasst/upg/bug_11687172.sql

PROMPT bug 9836012
@../../reposasst/upg/bug_9836012.sql

PROMPT bug 10316797
@../../patches/bug10316797/fix10316797.sql

PROMPT ==============================================================
PROMPT Browser Differences
PROMPT ==============================================================

PROMPT bug 8310349
@../../browserasst/views/public_views.sql

PROMPT bug 9577909
@../../reposasst/upg/bug_9577909.sql

PROMPT bug 9141928
@../../browserasst/owbb/owbb_uix_body.plb

PROMPT bug 9141932,10333013
@../../browserasst/views/owb_uprop.sql
@../../browserasst/views/owb_uprop_body.plb

PROMPT bug 9141938
@../../browserasst/views/public_utils_body.plb

PROMPT ===============================================================
PROMPT Trickfeed Mapping Upgrade
PROMPT ==============================================================
PROMPT Bug 9758359
@../../reposasst/upg/bug_9758359.sql

PROMPT ==============================================================
PROMPT PLSQL Mapping Upgrade
PROMPT ==============================================================

PROMPT Bug 8904197
@../../rtasst/wb_rt_tf_map_operations.plb

PROMPT Target Load Ordering License Management
@../../reposasst/upg/TLO.sql

PROMPT Chunking upgrade
@../../reposasst/upg/chunking_update.sql
@../../rtasst/wb_rt_mapaudit.pls
@../../rtasst/wb_rt_mapaudit.plb

PROMPT ==============================================================
PROMPT Update property INTEGRATION_TYPE for Disco Location
PROMPT ==============================================================

PROMPT Update property INTEGRATION_TYPE
@../../reposasst/upg/update_disco_loc.sql

PROMPT ==============================================================
PROMPT Update property DATA_SEGMENT_COMPRESSION for Table Compression
PROMPT ==============================================================

PROMPT Update property DATA_SEGMENT_COMPRESSION
@../../reposasst/upg/table_compression.sql

PROMPT ==============================================================
PROMPT Update for External Table Preprocessor
PROMPT ==============================================================
@../../reposasst/upg/et_preprocessor.sql
@../../reposasst/upg/et_preprocessor_tm.sql

PROMPT ==============================================================
PROMPT Update for External Table Datapump
PROMPT ==============================================================
@../../reposasst/upg/et_datapump.sql
@../../reposasst/upg/et_datapump_tm.sql

PROMPT ==============================================================
PROMPT CT Mapping Upgrade
PROMPT ==============================================================

PROMPT Use enclosure char, also for bug 8221560
@@../../reposasst/upg/bug_8221560.sql

PROMPT Source file operator upgrade for bug 6834663
@../../reposasst/upg/bug_6834663.sql

PROMPT upgrade for bug 11740184
@../../reposasst/upg/bug_11740184.sql

PROMPT ==============================================================
PROMPT 11.2.0.3 new features
PROMPT ==============================================================
PROMPT Partition support in table
@../../reposasst/upg/partition.sql

PROMPT bug11077931: Column Encryption
@../../reposasst/upg/stored_password_location.sql
@../../reposasst/upg/tde_support.sql
PROMPT Compiled PLSQL
@../../reposasst/upg/compiled_plsql_update.sql

PROMPT Bug 9808664
@../../reposasst/upg/bug_9808664.sql

PROMPT Bug 10039619
@../../reposasst/upg/bug_10039619.sql

PROMPT Bug 12563827
@../../reposasst/upg/bug_12563827.sql

PROMPT Partition DML, bug 11077964 
@../../reposasst/upg/partition_dml.sql

PROMPT ==============================================================
PROMPT Update Internal Version Numbers
PROMPT ==============================================================

PROMPT Invoke upversion_11203.sql ...
@../../reposasst/upg/upversion_11203.sql;

PROMPT update OWBRT_SYS.OWBRTP table
@../../reposasst/upg/owbrt_sys.sql;
commit;

PROMPT Re-load jar files
@../../reposasst/upg/load_java.sql OWBSYS V_OWBSYS_PASSWORD

PROMPT Load role object rights
@../../rtasst/roles_object_rights.sql;


PROMPT Connect to SYS user ...
WHENEVER SQLERROR EXIT FAILURE
connect &V_SYS_USER_NAME/&V_SYS_USER_PASSWORD@(DESCRIPTION=(ADDRESS=(HOST= &V_HOST )(PROTOCOL=tcp)(PORT= &V_PORT ))(CONNECT_DATA=(SERVICE_NAME= &V_SERVICENAME ))) AS SYSDBA
WHENEVER SQLERROR CONTINUE


WHENEVER SQLERROR EXIT FAILURE
connect OWBSYS/&V_OWBSYS_PASSWORD@(DESCRIPTION=(ADDRESS=(HOST= &V_HOST )(PROTOCOL=tcp)(PORT= &V_PORT ))(CONNECT_DATA=(SERVICE_NAME= &V_SERVICENAME )))
WHENEVER SQLERROR CONTINUE

PROMPT ==============================================================
PROMPT Recompile packages... 1st pass
exec DBMS_UTILITY.COMPILE_SCHEMA ('OWBSYS');
PROMPT Recompile packages... 2nd pass
exec DBMS_UTILITY.COMPILE_SCHEMA ('OWBSYS');
PROMPT Recompile packages... 3rd pass
exec DBMS_UTILITY.COMPILE_SCHEMA ('OWBSYS');
PROMPT ==============================================================

PROMPT End of the upgrade.
SPOOL OFF
--exit