Rem
Rem ime112.sql
Rem
Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      ime112.sql 
Rem
Rem    DESCRIPTION
Rem    runs as ORDSYS
Rem
Rem      Internal downgrade script from Oracle Multimedia 11.2.0.2 patchset to
Rem      11.2.0.1
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jiezhan     06/30/11 - Backport jiezhan_imageio_definer from main
Rem    smavris     05/20/10 - Check that dependencies exist and are valid
Rem                           before install, upgrade, downgrade, reload, patch
Rem    jiezhan     09/25/09 - change max of XML_SKIP_ATTR
Rem    dolin       09/15/09 - Created
Rem

whenever sqlerror exit;

REM 
REM Make sure Oracle Multimedia schemas exist.
REM
REM Note - it is forbidden to exit in a downgrade 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
REM IMPORTANT... Dependencies must be checked in top level downgrade script.
REM
REM Make sure Oracle Multimedia dependencies are valid (and log errors and
REM continue if not).  Check Oracle Multimedia validity.
REM
@@imchkdep.sql 1 DOWNGRADING DOWNGRADED

alter session set current_schema="ORDSYS";
execute sys.dbms_registry.downgrading('ORDIM', 'Oracle Multimedia');

-- revoke imageio access permission grant to ordsys
-- which is needed in processcopy
call  dbms_java.revoke_permission(
     'ORDSYS',
     'SYS:java.lang.RuntimePermission',
     'accessClassInPackage.com.sun.imageio.plugins.jpeg', '');

REM 
REM Restore the DICOM repository. Rollback the changes in 
REM ordcmcmd.xml (bug 8625537).
REM
exec ordsys.ord_dicom_admin.editDataModel();

Rem Delete user inserted docs that depend on ordcmcmd.xml and ordcmct.xml
Rem then delete ordcmct.xml and ordcmcmd.xml
create global temporary table orddata.temp_docs 
  ON COMMIT PRESERVE ROWS 
  as
  select d.* from orddata.orddcm_docs d, orddata.orddcm_doc_types t,
    ( select unique ref_by_doc_name doc_name from orddata.orddcm_doc_refs 
        where ref_by_doc_name != 'ordcmct.xml'
        start with doc_name in ('ordcmcmd.xml', 'ordcmct.xml')
        connect by doc_id = prior ref_by_doc_id
    ) r
    where d.doc_name = r.doc_name
      and t.doc_type = 'CONSTRAINT' and t.doc_type_id = d.doc_type_id;

declare 
  l_cnt integer;
begin
  for cur in (select doc_name from orddata.temp_docs order by doc_id desc)
  loop
    ordsys.ord_dicom_admin.deleteDocument(cur.doc_name);
  end loop;

  ordsys.ord_dicom_admin_prv.deleteAnyDoc('ordcmct.xml');
  ordsys.ord_dicom_admin_prv.deleteAnyDoc('ordcmcmd.xml');

  exception when others then
    ordsys.ord_dicom_admin.rollbackDataModel();
    raise;
end;
/

Rem insert back the 11.2.0.1 ordcmcmd.xml and ordcmct.xml
@@imxreins.sql

Rem Re-insert user constraint docs that were deleted in this script
begin
  for cur in (select doc_name, doc_content from orddata.temp_docs order by doc_id asc)
  loop
    ordsys.ord_dicom_admin.insertDocument(cur.doc_name, 'CONSTRAINT', cur.doc_content);
  end loop;
  exception when others then
    ordsys.ord_dicom_admin.rollbackDataModel();
    raise;
end;
/

exec ordsys.ord_dicom_admin.publishDataModel();
begin
  EXECUTE IMMEDIATE 'truncate table orddata.temp_docs';
  EXECUTE IMMEDIATE 'drop table orddata.temp_docs';
exception when others then
  null;
end;
/

-- change max value of XML_SKIP_ATTR 
alter session set current_schema=ORDDATA;
update ORDDATA.orddcm_prefs_lookup 
  set max_val=32767 where param_name='XML_SKIP_ATTR';

-- The following should be at the end of the ORDIM part of the file
-- executed as ORDSYS
alter session set current_schema="ORDSYS";
REM Drop all Oracle Multimedia java classes
@@imclnjav

EXECUTE sys.dbms_registry.downgraded('ORDIM', '11.2.0');

alter session set current_schema="SYS";