Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem imdbmig.sql Rem Rem DESCRIPTION Rem runs as SYS Rem Rem performs the upgrade of Oracle Multimedia from all prior releases Rem supported for upgrade (920, 101, and 102). Rem Rem NOTES Rem WHENEVER SQLERROR EXIT; EXECUTE dbms_registry.check_server_instance; REM REM Make sure Oracle Multimedia schemas exist. REM REM Note - it is forbidden to exit in an upgrade script. REM However, it is an assumed impossible condition that we exist in the REM registry (which is the only way this script should be called) and our REM schemas don't exist. Therefore, we will exit here if our schemas don't REM exist so we can figure out how we got in this situation. REM @@imchksch.sql WHENEVER SQLERROR CONTINUE; alter session set current_schema="ORDSYS"; EXECUTE dbms_registry.upgrading('ORDIM', 'Oracle Multimedia', 'validate_ordim', 'ORDSYS'); EXECUTE dbms_registry.set_required_comps('ORDIM', dbms_registry.comp_depend_list_t('JAVAVM','XDB','XML')); Rem =========================================== Rem setup component script filname variable Rem =========================================== COLUMN :script_name NEW_VALUE comp_file NOPRINT Variable script_name varchar2(50) --============================================= -- setup data migration filename variable --============================================= Variable script_datamig varchar2(50) Rem =========================================== Rem select upgrade script to run Rem =========================================== DECLARE version SYS.registry$.version%type; p_version SYS.registry$.version%type; Begin --init :script_datamig := dbms_registry.nothing_script; select dbms_registry.version('ORDIM') into version from dual; If (substr(version, 1, 5) = '9.2.0') then :script_name := '@imu920.sql'; elsif (substr(version, 1, 6) = '10.1.0') then :script_name := '@imu101.sql'; elsif (substr(version, 1, 6) = '10.2.0') then :script_name := '@imu102.sql'; elsif (substr(version, 1, 6) = '11.1.0') then :script_name := '@imu111.sql'; -- data migration script :script_datamig := '@imu111m.sql'; elsif (substr(version, 1, 6) = '11.2.0') then begin -- already upgraded, so re-run original upgrade script EXECUTE IMMEDIATE 'select prv_version from registry$ where cid = ''ORDIM''' into p_version; if (substr(p_version, 1, 5) = '9.2.0') then :script_name := '@imu920.sql'; elsif (substr(p_version, 1, 6) = '10.1.0') then :script_name := '@imu101.sql'; elsif (substr(p_version, 1, 6) = '10.2.0') then :script_name := '@imu102.sql'; elsif (substr(p_version, 1, 6) = '11.1.0') then :script_name := '@imu111.sql'; -- data migration script :script_datamig := '@imu111m.sql'; else :script_name := dbms_registry.nothing_script; end if; EXCEPTION WHEN OTHERS THEN -- no column in 9.2 :script_name := '@imu920.sql'; end; else :script_name := dbms_registry.nothing_script; end if; end; / Rem ================================================================== Rem Clean out previous Java objects. Rem Note, now that we've removed -force on our loadjava commands, this Rem clean java is REQUIRED on upgrade. Do not remove. Rem ================================================================== @@imclnjav.sql Rem =========================================== Rem Invoke version specific upgrade script Rem =========================================== select :script_name from dual; @&comp_file Rem Rem This second call to upgrading is not a mistake. SI_INFORMTN_SCHEMA and ORDDATA Rem might not exist when we make the first call, so we need to pass the Rem schema list at this point. Rem EXECUTE dbms_registry.upgrading('ORDIM', 'Oracle Multimedia', 'validate_ordim', 'ORDSYS', dbms_registry.schema_list_t('ORDPLUGINS','SI_INFORMTN_SCHEMA', 'ORDDATA')); Rem =========================================== Rem Upgrade implementation Rem =========================================== REM Reload public packages, procedures, functions @@impbs.sql REM Reload private packages, procedures, functions and views @@impvs.sql REM Reload Views @@imview.sql REM Reload type bodies @@imtyb.sql REM Reload package bodies @@implb.sql REM Reload Java classes @@initim.sql REM Create the library @@ordlib.sql -- -- Invoke imxrepos.sql to create ORDSYS folder in XML DB repository if -- XDB is installed -- COLUMN file_name NEW_VALUE comp_file NOPRINT; SELECT dbms_registry.script('XDB','@imxrepos.sql') AS file_name FROM DUAL; @&comp_file Rem =========================================== Rem Invoke version specific data migrate script Rem =========================================== COLUMN :script_datamig NEW_VALUE comp_file NOPRINT; select :script_datamig from dual; @&comp_file Rem Rem Clear the table for recording feature usage Rem delete from ORDSYS.ORD_USAGE_RECS; EXECUTE dbms_registry.upgraded('ORDIM'); alter session set current_schema="SYS"; REM recompile invalid ORDSYS types execute utl_recomp.recomp_serial('ORDSYS'); REM REM Invoke Locator upgrade and register MDSYS if SDO is not REM installed and MDSYS schema exists. Note - if MDSYS does REM not exist this is most likely an internal test scenario REM and we should not attempt to upgrade Locator. REM COLUMN :script_name NEW_VALUE comp_file NOPRINT Variable script_name varchar2(50) declare mdsyscnt NUMBER := 0; begin -- Check whether MDSYS exists. -- If not, Do not upgrade Locator select count(*) into mdsyscnt from dba_users where username='MDSYS'; -- Check whether SDO is installed. -- If not, upgrade Locator if ((dbms_registry.is_valid('SDO') is NULL) and (mdsyscnt > 0)) then :script_name := '?/md/admin/locdbmig.sql'; dbms_registry.upgrading ('ORDIM', 'Oracle Multimedia', 'validate_ordim', 'ORDSYS', dbms_registry.schema_list_t('ORDPLUGINS','SI_INFORMTN_SCHEMA', 'MDSYS', 'ORDDATA')); else :script_name := dbms_registry.nothing_script; end if; end; / select :script_name from dual; @&comp_file alter session set current_schema="SYS"; REM Install Oracle Multimedia verification procedure REM and validate Oracle Multimedia Installation @@imvalid.sql