Rem Copyright (c) 2003, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      imrelod.sql 
Rem
REM You must connect as SYSDBA prior to running this script
REM
REM This patch script reloads Oracle Multimedia after a downgrade.
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 a reload 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;

REM Install ORDSYS objects
alter session set current_schema="ORDSYS";
EXECUTE dbms_registry.loading('ORDIM','Oracle Multimedia','validate_ordim','ORDSYS', dbms_registry.schema_list_t('ORDPLUGINS','SI_INFORMTN_SCHEMA', 'ORDDATA'));

Rem Recreate libraries
@@ordlib

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 and package bodies
@@imtyb.sql
@@implb.sql

REM Reload Java classes
@@initim.sql

EXECUTE dbms_registry.loaded('ORDIM');

alter session set current_schema="SYS";

REM recompile invalid ORDSYS types
execute DBMS_SESSION.reset_package;
execute utl_recomp.recomp_serial('ORDSYS');

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 reload Locator
  select count(*) into mdsyscnt from dba_users where username='MDSYS';
  -- Check whether SDO is installed.
  -- If not, install Locator and register MDSYS
  --
  if ((dbms_registry.is_valid('SDO') is NULL) and (mdsyscnt > 0)) then
    EXECUTE IMMEDIATE 'alter session set current_schema="MDSYS"';
    :script_name := '@imimdloc.sql';
    dbms_registry.loading
        ('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;
/

REM Install and Check Location objects 

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