Rem
Rem imvalid.sql
Rem
Rem Copyright (c) 1998, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      imvalid.sql - Multimedia validation procedure
Rem
Rem    DESCRIPTION
Rem      This procedure checks the installation of Multimedia
Rem      components and prints the status.
Rem
Rem    NOTES
Rem      This procedure should be created in SYS.
Rem      Before running this procedure, user must be connected
Rem      AS SYSDBA.
Rem     
Rem

REM This procedure tests the Multimedia components installation
CREATE OR REPLACE PROCEDURE SYS.validate_ordim 
authid current_user
IS
 l_null char(1);
 l_valid integer := 1;               -- keep track installation status 1: valid (default), 0: invalid
 l_num_objects integer;
 l_num_classes integer;
 l_num_schemas integer;
 l_num_docs    integer;
 l_num_roles   integer;
 err_code NUMBER;
 err_msg  VARCHAR2(100);
 l_prevDbVer varchar2(40);
 l_status  varchar2(100);
 l_dsql_block varchar2(500);
BEGIN
  -- Now check whether Multimedia objects are created
  begin
    select count(*) into l_num_objects from sys.obj$ o, sys.user$ u
      where u.name = 'ORDSYS' 
        and u.user# = o.owner#
        and o.type# = 13
        and o.name in 
            ( 'ORDIMAGE', 'ORDAUDIO', 'ORDDOC', 'ORDVIDEO', 'ORDDICOM',
              'SI_COLOR', 'SI_STILLIMAGE', 'SI_AVERAGECOLOR',
              'SI_COLORHISTOGRAM', 'SI_POSITIONALCOLOR', 'SI_TEXTURE',
              'SI_FEATURELIST'
            )
    ;
    
    if ( l_num_objects != 12 ) then
      l_valid := 0;
      dbms_output.put_line('ORDIM created ' || l_num_objects || ' objects.');
      dbms_output.put_line('The following objects are not created:');
    
      for r in (
      (
        select 'ORDIMAGE' name from dual
        union
        select 'ORDAUDIO' name from dual
        union
        select 'ORDDOC' name from dual
        union
        select 'ORDVIDEO' name from dual
        union
        select 'ORDDICOM' name from dual
        union
        select 'SI_COLOR' name from dual
        union
        select 'SI_STILLIMAGE' name from dual
        union
        select 'SI_AVERAGECOLOR' name from dual
        union
        select 'SI_COLORHISTOGRAM' name from dual
        union
        select 'SI_POSITIONALCOLOR' name from dual
        union
        select 'SI_TEXTURE' name from dual
        union
        select 'SI_FEATURELIST' name from dual
      )
      minus
      (
        select o.name from sys.obj$ o, sys.user$ u
        where u.name = 'ORDSYS' 
          and u.user# = o.owner#
          and o.type# = 13
          and o.name in 
              ( 'ORDIMAGE', 'ORDAUDIO', 'ORDDOC', 'ORDVIDEO', 'ORDDICOM',
                'SI_COLOR', 'SI_STILLIMAGE', 'SI_AVERAGECOLOR',
                'SI_COLORHISTOGRAM', 'SI_POSITIONALCOLOR', 'SI_TEXTURE',
                'SI_FEATURELIST'
              )
      ) ) loop
        dbms_output.put_line(r.name);
      end loop;

    end if;

  exception
    WHEN NO_DATA_FOUND THEN
      l_valid := 0;
      dbms_output.put_line('ORDIM objects are not created.');

    WHEN OTHERS THEN
      l_valid := 0;
      err_code := SQLCODE;
      err_msg  := SUBSTR(SQLERRM, 1 , 100); 
      DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
  end;

  -- Now check whether Multimedia java classes are loaded
  -- The following jars are checked:
  --  mlibwrapper_jai.jar, jai_codec.jar, jai_core.jar, ordimimg.jar
  --  ordimdcm.jar, ordimann.jar 
  begin
    select count(*) into l_num_classes
      from sys.obj$ o, sys.user$ u, sys.javasnm$ j
      where u.name = 'ORDSYS'
        and u.user# = o.owner# 
        and o.type# = 29
        and j.short(+) = o.name
        and nvl(j.longdbcs, o.name) in
            ( 'javax/media/jai/JAI', 'com/sun/media/jai/codec/ImageCodec',
              'com/sun/medialib/mlib/mediaLibImage', 'oracle/ord/media/img/PropsAdapter',
              'oracle/ord/dicom/attr/DicomAttrTag',
              'oracle/ord/media/annotator/servclt/AnnCltInServer'
            )
    ;
    
    if ( l_num_classes != 6 ) then
      l_valid := 0;
      dbms_output.put_line('The following ORDIM Java libs are not loaded correctly:');
    
      for r in (
      (
        select 'javax/media/jai/JAI' name, 'jai_core.jar' jar from dual
        union
        select 'com/sun/media/jai/codec/ImageCodec' name, 'jai_codec.jar' jar from dual
        union
        select 'com/sun/medialib/mlib/mediaLibImage' name, 'mlibwrapper_jai.jar' jar from dual
        union
        select 'oracle/ord/media/img/PropsAdapter' name, 'ordimimg.jar' jar from dual
        union
        select 'oracle/ord/dicom/attr/DicomAttrTag' name, 'ordimdcm.jar' jar from dual
        union
        select 'oracle/ord/media/annotator/servclt/AnnCltInServer' name, 'ordimann.jar' jar from dual
      )
      minus
      (
        select nvl(j.longdbcs, o.name) name,
          decode( nvl(j.longdbcs, o.name) , 'javax/media/jai/JAI', 'jai_core.jar',
                    'com/sun/media/jai/codec/ImageCodec', 'jai_codec.jar',
                    'com/sun/medialib/mlib/mediaLibImage', 'mlibwrapper_jai.jar',
                    'oracle/ord/media/img/PropsAdapter', 'ordimimg.jar',
                    'oracle/ord/dicom/attr/DicomAttrTag', 'ordimdcm.jar',
                    'oracle/ord/media/annotator/servclt/AnnCltInServer', 'ordimann.jar') jar
          from sys.obj$ o, sys.user$ u, sys.javasnm$ j
          where u.name = 'ORDSYS'
            and u.user# = o.owner# 
            and o.type# = 29
            and j.short(+) = o.name
            and nvl(j.longdbcs, o.name) in
                ( 'javax/media/jai/JAI', 'com/sun/media/jai/codec/ImageCodec',
                  'com/sun/medialib/mlib/mediaLibImage', 'oracle/ord/media/img/PropsAdapter',
                  'oracle/ord/dicom/attr/DicomAttrTag',
                  'oracle/ord/media/annotator/servclt/AnnCltInServer'
                )
      ) ) loop
        dbms_output.put_line(r.jar);
      end loop;
    end if;

  exception
    WHEN NO_DATA_FOUND THEN
      l_valid := 0;
      dbms_output.put_line('ORDIM java classes are not loaded.');

    WHEN OTHERS THEN
      l_valid := 0;
      err_code := SQLCODE;
      err_msg  := SUBSTR(SQLERRM, 1 , 100); 
      DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
  end;
  

  -- Now check whether there are invalid objects
  BEGIN
    SELECT NULL INTO l_null  FROM sys.obj$ o, sys.user$ u
    WHERE o.status > 1 
      AND (u.name = 'ORDSYS' 
        OR u.name = 'ORDPLUGINS' 
        OR u.name = 'SI_INFORMTN_SCHEMA'
        OR u.name = 'ORDDATA')
      AND u.user#= o.owner# 
      AND rownum <= 1;

    -- invalid objects found
    l_valid := 0;

    FOR ob IN (SELECT o.name, o.status, o.type# FROM sys.obj$ o, sys.user$ u
        WHERE o.status > 1 
          AND (u.name = 'ORDSYS' 
            OR u.name = 'ORDPLUGINS' 
            OR u.name = 'SI_INFORMTN_SCHEMA'
            OR u.name = 'ORDDATA')
          AND u.user# = o.owner#
          AND rownum < 20) LOOP
      dbms_output.put_line ('ORDIM INVALID OBJECTS: ' || ob.name || ' - ' || ob.status || ' - ' || ob.type#);
    END LOOP;

  EXCEPTION                                                                      
    WHEN NO_DATA_FOUND THEN
      -- no valid objects, don't need to do anything
      null;

    WHEN OTHERS THEN
      l_valid := 0;
      err_code := SQLCODE;
      err_msg  := SUBSTR(SQLERRM, 1 , 100); 
      DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
  END; 

  -- Now check whether XML schemas are registered
  begin
    select count(*) into l_num_schemas from sys.all_xml_schemas 
      where owner = 'ORDSYS' 
        and schema_url in 
            (
             'http://xmlns.oracle.com/ord/meta/ordimage',
             'http://xmlns.oracle.com/ord/meta/exif',
             'http://xmlns.oracle.com/ord/meta/iptc',
             'http://xmlns.oracle.com/ord/meta/xmp',
             'http://xmlns.oracle.com/ord/meta/dicomImage',
             'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0',
             'http://xmlns.oracle.com/ord/dicom/datatype_1_0',
             'http://xmlns.oracle.com/ord/dicom/mddatatype_1_0',
             'http://xmlns.oracle.com/ord/dicom/anonymity_1_0',
             'http://xmlns.oracle.com/ord/dicom/attributeTag_1_0',
             'http://xmlns.oracle.com/ord/dicom/constraint_1_0',
             'http://xmlns.oracle.com/ord/dicom/metadata_1_0',
             'http://xmlns.oracle.com/ord/dicom/mapping_1_0',
             'http://xmlns.oracle.com/ord/dicom/manifest_1_0',
             'http://xmlns.oracle.com/ord/dicom/preference_1_0',  
             'http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0',
             'http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0',
             'http://xmlns.oracle.com/ord/dicom/orddicom_1_0',
             'http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0'
          )
    ;
    
    if ( l_num_schemas != 19 ) then
      l_valid := 0;
      dbms_output.put_line('ORDIM registered ' || l_num_schemas || ' XML schemas.');
      dbms_output.put_line('The following XML schemas are not registered:');
    
      for r in (
      (
        select 'http://xmlns.oracle.com/ord/meta/ordimage' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/meta/exif' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/meta/iptc' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/meta/xmp' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/meta/dicomImage' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/datatype_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/mddatatype_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/anonymity_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/attributeTag_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/constraint_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/metadata_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/mapping_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/manifest_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/preference_1_0' schema_url from dual  
        union
        select 'http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/orddicom_1_0' schema_url from dual
        union
        select 'http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0' schema_url from dual
      )
      minus
      (
        select schema_url from sys.all_xml_schemas 
          where owner = 'ORDSYS' 
            and schema_url in 
                (
                 'http://xmlns.oracle.com/ord/meta/ordimage',
                 'http://xmlns.oracle.com/ord/meta/exif',
                 'http://xmlns.oracle.com/ord/meta/iptc',
                 'http://xmlns.oracle.com/ord/meta/xmp',
                 'http://xmlns.oracle.com/ord/meta/dicomImage',
                 'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0',
                 'http://xmlns.oracle.com/ord/dicom/datatype_1_0',
                 'http://xmlns.oracle.com/ord/dicom/mddatatype_1_0',
                 'http://xmlns.oracle.com/ord/dicom/anonymity_1_0',
                 'http://xmlns.oracle.com/ord/dicom/attributeTag_1_0',
                 'http://xmlns.oracle.com/ord/dicom/constraint_1_0',
                 'http://xmlns.oracle.com/ord/dicom/metadata_1_0',
                 'http://xmlns.oracle.com/ord/dicom/mapping_1_0',
                 'http://xmlns.oracle.com/ord/dicom/manifest_1_0',
                 'http://xmlns.oracle.com/ord/dicom/preference_1_0',  
                 'http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0',
                 'http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0',
                 'http://xmlns.oracle.com/ord/dicom/orddicom_1_0',
                 'http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0'
              )
      ) ) loop
        dbms_output.put_line(r.schema_url);
      end loop;

    end if;

  exception
    WHEN NO_DATA_FOUND THEN
      l_valid := 0;
      dbms_output.put_line('ORDIM XML schemas are not registered.');

    WHEN OTHERS THEN
      l_valid := 0;
      err_code := SQLCODE;
      err_msg  := SUBSTR(SQLERRM, 1 , 100); 
      DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
  end;

  -- Now check whether default documents are inserted into the DICOM repository 
  begin
    select count(*) into l_num_docs from orddata.orddcm_docs
      where doc_name in 
          (
            'ordcman.xml',
            'ordcmcmc.xml',
            'ordcmcmd.xml',
            'ordcmct.xml',
            'ordcmmp.xml',
            'ordcmpf.xml',
            'ordcmpv.xml',
            'ordcmsd.xml',
            'ordcmui.xml'
          )
    ;
    
    if ( l_num_docs != 9 ) then
      l_valid := 0;
      dbms_output.put_line('ORDIM DICOM repository has ' || l_num_docs || ' documents.');
      dbms_output.put_line('The following default DICOM repository documents are not installed:');
    
      for r in (
      (
        select 'ordcman.xml' doc_name from dual
        union
        select 'ordcmcmc.xml' doc_name from dual
        union
        select 'ordcmcmd.xml' doc_name from dual
        union
        select 'ordcmct.xml' doc_name from dual
        union
        select 'ordcmmp.xml' doc_name from dual
        union
        select 'ordcmpf.xml' doc_name from dual
        union
        select 'ordcmpv.xml' doc_name from dual
        union
        select 'ordcmsd.xml' doc_name from dual
        union
        select 'ordcmui.xml' doc_name from dual
      )
      minus
      (
        select doc_name from orddata.orddcm_docs
          where doc_name in 
            (
              'ordcman.xml',
              'ordcmcmc.xml',
              'ordcmcmd.xml',
              'ordcmct.xml',
              'ordcmmp.xml',
              'ordcmpf.xml',
              'ordcmpv.xml',
              'ordcmsd.xml',
              'ordcmui.xml'
            )
      ) ) loop
        dbms_output.put_line(r.doc_name);
      end loop;

    end if;

  exception
    WHEN NO_DATA_FOUND THEN
      l_valid := 0;
      dbms_output.put_line('ORDIM default DICOM repository documents are not installed.');

    WHEN OTHERS THEN
      l_valid := 0;
      err_code := SQLCODE;
      err_msg  := SUBSTR(SQLERRM, 1 , 100); 
      DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
  end;

  -- Now check whether DICOM ORDADMIN role is created
  begin
    SELECT count(*) INTO l_num_roles  FROM sys.user$ u
    WHERE type# = 0 and name = 'ORDADMIN';

    if ( l_num_roles != 1 ) then
      l_valid := 0;
      dbms_output.put_line('ORDIM DICOM administrator role ORDADMIN is not created.');
    end if;

  exception                                                                      
    when no_data_found then
      l_valid := 0;
      dbms_output.put_line('ORDIM DICOM administrator role ORDADMIN is not created.');

    when others then
      l_valid := 0;
      err_code := SQLCODE;
      err_msg  := SUBSTR(SQLERRM, 1 , 100); 
      DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
  end; 

  -- Check whether Locator is valid
  -- by checking whether there are invalid objects under MDSYS
  BEGIN
    if (dbms_registry.is_valid('SDO') is NULL) then
      SELECT NULL INTO l_null  FROM sys.obj$ o, sys.user$ u
      WHERE o.status > 1 
        AND u.name = 'MDSYS' 
        AND u.user#= o.owner# 
        AND rownum <= 1;

      -- invalid objects found
      l_valid := 0;

      FOR ob IN (SELECT o.name, o.status, o.type# FROM sys.obj$ o, sys.user$ u
          WHERE o.status > 1 
            AND u.name = 'MDSYS' 
            AND u.user# = o.owner#
            AND rownum < 20) LOOP
        dbms_output.put_line ('Locator INVALID OBJECTS: ' || ob.name || ' - ' || ob.status || ' - ' || ob.type#);
      END LOOP;
    end if;

  EXCEPTION                                                                      
    WHEN NO_DATA_FOUND THEN
      -- no valid objects, don't need to do anything
      null;

    WHEN OTHERS THEN
      l_valid := 0;
      err_code := SQLCODE;
      err_msg  := SUBSTR(SQLERRM, 1 , 100); 
      DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
  END; 

--
-- verify that data from ordsys has been moved to orddata
-- Note: this check is performed only if upgrading from 11.1
--  and the status is 'UPGRADED'

  begin
    select prv_version into l_prevDbVer
    from sys.registry$ where cid='ORDIM';

    l_status := sys.dbms_registry.status('ORDIM');

    if (l_prevDbVer = '11.1.0') and (l_status = 'UPGRADED') then
    begin
      -- Dynamic sql is used for compilation purposes because the 
      -- ordsys.orddcm_docs table will not exist for new installations 
      -- or if not upgrading from 11.1.0 database.
      -- check for documents in ORDSYS schema
      execute immediate 'select count(*) into l_num_docs from ordsys.orddcm_docs
          where doc_name in 
              (
                ''ordcman.xml'',
                ''ordcmcmc.xml'',
                ''ordcmcmd.xml'',
                ''ordcmct.xml'',
                ''ordcmmp.xml'',
                ''ordcmpf.xml'',
                ''ordcmpv.xml'',
                ''ordcmsd.xml'',
                ''ordcmui.xml''
              );';
      if (l_num_docs <> 9 ) then
        dbms_output.put_line('ORDSYS DICOM repos: '
                              || 'expected: 9 got: '
                              || l_num_docs || ' default docs');
      end if;
      
     exception
      WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('ORDSYS DICOM repository documents are not installed.');

      WHEN OTHERS THEN
        err_code := SQLCODE;
        err_msg  := SUBSTR(SQLERRM, 1 , 100); 
        DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
     end;  

        
     BEGIN
       --
       -- Expect no differences between the ORDSYS and ORDDATA repos
       --
       l_dsql_block := 'BEGIN 
             for cur in (select doc_name from ordsys.orddcm_docs 
                  minus select doc_name from orddata.orddcm_docs) loop
               dbms_output.put_line(''ORDSYS doc: '' 
                            || cur.doc_name || '' not in ORDDATA'');  
             end loop;
           end;';

       execute immediate l_dsql_block;
       
       l_dsql_block := 'BEGIN
            for cur in ( select doc_name from orddata.orddcm_docs
                    minus select doc_name from ordsys.orddcm_docs) loop
               dbms_output.put_line(''ORDData doc: '' 
                              || cur.doc_name || '' not in ORDSYS'');
            end loop;
          END;';

       execute immediate l_dsql_block;


     exception

      WHEN OTHERS THEN
        l_valid := 0;
        err_code := SQLCODE;
        err_msg  := SUBSTR(SQLERRM, 1 , 100); 
        DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
    END; 
   end if;

  END;

  -- 
  -- Set the registry status at the end
  -- 
  if l_valid = 0 then
    sys.dbms_registry.invalid('ORDIM');
  else
    sys.dbms_registry.valid('ORDIM');
  end if;

END; 
/
show errors;

REM Validate Oracle Multimedia Installation 
set serveroutput on
EXECUTE sys.validate_ordim();
set serveroutput off