Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\ord\im\admin\imu111.sql

Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      imu111.sql 
Rem
Rem    DESCRIPTION
Rem      run as ORDSYS
Rem      Internal upgrade script for Oracle Multimedia from 11.1
Rem
Rem       !!!!! IMPORTANT !!!!!
Rem      SEE imu111m.sql - Dicom repos data migration 
Rem
Rem    NOTES
Rem      In 11.1 the dicom repository is installed in the ORDSYS schema.
Rem      The repository in future releases is installed in ORDData schema.
Rem      The user-defined documents in the ORDSYS DICOM repository need 
Rem      to be migrated to the ORDDATA Dicom repository.
Rem      SEE imu111m.sql - Dicom repos data migration 
Rem
Rem
-- 
-- create ORDDATA user 
-- 
declare
 l_ord_tbs  varchar2(30);
 ex exception;
 -- ignore error ORA-01920, happens if the script is re-run
 -- ORA-01920: user name 'ORDDATA' conflicts with another user or role name
 pragma exception_init(ex, -1920); 
begin
  -- create orddata user and assign privs 
  -- create ORDData user in the same tablespace ORDSYS
  
  select default_tablespace into l_ord_tbs 
  from sys.dba_users where username='ORDSYS';

begin
    execute immediate 
       'create user orddata identified by orddata default tablespace ' 
        || l_ord_tbs;
    EXCEPTION 
    -- ORA-01920: user name 'ORDDATA' conflicts with another user
    WHEN ex THEN
      NULL;

end;

execute immediate 
   'alter user orddata account lock password expire';

end;
/

-- 
-- call imxreg.sql to register new schemas
-- for stored tag list document 
--      http://xmlns.oracle.com/ord/dicom/attributeTag_1_0(ordcmstl.xsd)
-- Manifest schema
--      http://xmlns.oracle.com/ord/dicom/manifest_1_0(ordcmmft.xsd)
-- The other schemas which are already registered do not change because
-- Oracle XML DB raises an exception if the schema_url being registered 
-- already exists. imxreg.sql handles this exception and continues. 
-- To change existing schemas use inplaceEvolve or copyEvolve 
--
Rem
Rem Invoke imxreg.sql to register new XML Schemas if XDB is installed
Rem
COLUMN file_name NEW_VALUE comp_file NOPRINT;
SELECT sys.dbms_registry.script('XDB','@imxreg.sql') AS file_name FROM DUAL;
@&comp_file

--
-- Perform in place evolve for the IPTC metadata schema.
--   relax minOccurs from 1 to 0 for recordVersion element
-- Relaxed schema is kept during downgrade as old behavior
-- produces compatible documents.
--
declare
  xmlDiff sys.xmltype;
begin
  xmlDiff := xmltype('<?xml version="1.0" encoding="UTF-8"?>
         <xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd 
          http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
          xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" 
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
    <xd:append-node xd:node-type="attribute" 
     xd:parent-xpath="/xsd:schema[1]/xsd:complexType[3]/xsd:sequence[1]/xsd:element[1]" 
     xd:attr-local="minOccurs">
        <xd:content>0</xd:content>
    </xd:append-node>
  </xd:xdiff>');

xdb.dbms_xmlschema.inPlaceEvolve('http://xmlns.oracle.com/ord/meta/iptc', xmlDiff, 1);

end;
/

--
-- Perform in place evolve for the EXIF metadata schema.
--   relax minOccurs from 1 to 0 for GPSVersionID element
-- Relaxed schema is kept during downgrade as old behavior
-- produces compatible documents.
--
declare
  xmlDiff sys.xmltype;
begin
  xmlDiff := xmltype('<?xml version="1.0" encoding="UTF-8"?>
    <xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
    <xd:append-node xd:node-type="attribute" xd:parent-xpath="/xsd:schema[1]/xsd:element[1]/xsd:complexType[1]/xsd:sequence[1]/xsd:element[3]/xsd:complexType[1]/xsd:all[1]/xsd:element[1]" xd:attr-local="minOccurs">
        <xd:content>0</xd:content>
    </xd:append-node>
</xd:xdiff>');

xdb.dbms_xmlschema.inPlaceEvolve('http://xmlns.oracle.com/ord/meta/exif', xmlDiff, 1);

end;
/

--
-- Perform in place evolve for the DICOM metadata ordcmmddt.xsd, 
-- ordcmrdt.xsd and ordcmdts.xsd schema.
--   relax minOccurs from 1 to 0 for DATASET_T element and add optional
-- number attribute to the ATTR_GRP_T. 
-- remove the pattern restriction on VALUE_LOCATOR_T and 
-- VALUE_LOCATOR_MACRO_T. 
-- Relaxed schema is kept during downgrade as old behavior
-- produces compatible documents.
--
declare
  xmlDiff sys.xmltype;
  xml_number sys.xmltype;
begin
  xmlDiff := xmltype('<?xml version="1.0" encoding="UTF-8"?>
    <xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd
      http://xmlns.oracle.com/xdb/xdiff.xsd"
      xmlns:xs="http://www.w3.org/2001/XMLSchema"
      xmlns:xdb="http://xmlns.oracle.com/xdb"
      xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <?oracle-xmldiff
      operations-in-docorder="true" output-model="snapshot"
      diff-algorithm="global"?>
      <xd:update-node
        xd:node-type="text"
        xd:xpath="/xs:schema[1]/xs:complexType[4]/xs:annotation[1]/xs:documentation[1]/text()[1]">
        <xd:content>DICOM VR type SeQuence. 
            Note that item number can be explicitly encoded in XML. 
            Number counts from 1 up.
            Each item is a DATASET_T type, which may contain 
            any combination of DICOM attributes.
        </xd:content>
      </xd:update-node>
      <xd:append-node
        xd:node-type="attribute"
        xd:parent-xpath="/xs:schema[1]/xs:complexType[7]/xs:choice[1]"
        xd:attr-local="minOccurs">
        <xd:content>0</xd:content>
      </xd:append-node>
      <xd:append-node 
        xd:node-type="element"
        xd:parent-xpath="/xs:schema[1]/xs:complexType[7]">
        <xd:content>
          <xs:attribute name="number" type="xs:long" use="optional" default="1"/>
        </xd:content>
      </xd:append-node>
      <xd:update-node xd:node-type="text"
        xd:xpath="/xs:schema[1]/xs:attributeGroup[1]/xs:annotation[1]/xs:documentation[1]/text()[1]">
        <xd:content>
           Attribute group type (ATTR_GRP_T) is used by all DICOM attribute 
           definitions. It defines XML attributes that are used by all DICOM
           attribute types.  The "tag" attribute
           defines DICOM attributes in little-endian encoding.
           The "definer" attribute specifies the organization that has
           created the attribute. By default, all DICOM standard attributes have
           the definer name "DICOM".  The "name" attribute
           specifies the canonical attribute name as defined by the data
           dictionary. For example, in an XML metadata schema definition, 
           you can choose a tag PATIENT_DATE_OF_BIRTH or "DOB" 
           for DICOM attribute (0010,0030), but its name attribute should 
           match that of the DICOM standard: "Patient''s 
           Birth Date".  The "number" attribute is an optional 
           attribute to indicate the ordering of a multivalued attributes.  
           Number counts from 1 up.
           The "truncated" attribute takes a Boolean value. 
           If it is true, it indicates that the original length of the 
           DICOM attribute exceeds the maximum length allowed for this XML 
           value;therefore, it is truncated in XML. When this attribute is true,
           xsi:nill="true" for this attribute.  
           Optionally, the "rawValue" attribute can be used to store 
           values that do not conform to the DICOM standard. The associated
           attribute "byteOrderLE" specifies the byte order
           of the byte stream for the "rawValue" attribute.
           "offset" and "length" are Oracle-reserved 
           attributes.
       </xd:content>
     </xd:update-node>
     <xd:delete-node xd:node-type="element" xd:xpath="/xs:schema[1]/xs:simpleType[25]/xs:restriction[1]/xs:pattern[1]"/>
     <xd:delete-node xd:node-type="element" xd:xpath="/xs:schema[1]/xs:simpleType[26]/xs:restriction[1]/xs:pattern[1]"/>
     <xd:insert-node-before xd:node-type="element"
       xd:xpath="/xs:schema[1]/xs:attributeGroup[1]/xs:attribute[4]">
       <xd:content>
           <xs:attribute name="number" type="xs:long" use="optional" default="1"/>
       </xd:content>
     </xd:insert-node-before>
  </xd:xdiff>');

-- We only do the schema evolution if the schema has not been evolved. 
  -- In the case upgrade/downgrade/upgrade, the last upgrade won't evolve
  -- the schema again 
  xml_number := null;
  select extract(t.schema, 
         '/xs:schema/xs:attributeGroup/xs:attribute[@name="number"]', 
         'xmlns="http://xmlns.oracle.com/ord/dicom/datatype_1_0" ' || 
         'xmlns:xs="http://www.w3.org/2001/XMLSchema" ' || 
         'xmlns:xdb="http://xmlns.oracle.com/xdb"') 
    into xml_number from sys.all_xml_schemas  t
    where schema_url='http://xmlns.oracle.com/ord/dicom/datatype_1_0';
  if (xml_number is null) then
    -- evolve the datatype schema used by the repository document
    xdb.dbms_xmlschema.inPlaceEvolve(
       'http://xmlns.oracle.com/ord/dicom/datatype_1_0', 
       xmlDiff,
       xdb.dbms_xmlschema.inplace_evolve);
  end if;
  
  -- We only do the schema evolution if the schema has not been evolved. 
  -- In the case upgrade/downgrade/upgrade, the last upgrade won't evolve
  -- the schema again 
  xml_number := null;
  select extract(t.schema, 
         '/xs:schema/xs:attributeGroup/xs:attribute[@name="number"]', 
         'xmlns="http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0" ' || 
         'xmlns:xs="http://www.w3.org/2001/XMLSchema" ' || 
         'xmlns:xdb="http://xmlns.oracle.com/xdb"') 
    into xml_number from sys.all_xml_schemas  t
    where schema_url='http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0';
  if (xml_number is null) then
    -- evolve the datatype schema provided for user application. There is
    -- no reference in Oracle repository documents or metadata documents.
    xdb.dbms_xmlschema.inPlaceEvolve(
       'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0', 
       xmlDiff,
       xdb.dbms_xmlschema.inplace_evolve);
  end if;

end;
/

--
-- Validate obsolete, invalid older versions of our objects. 
-- There is no other way to validate them.  (bug 7700436)
--
begin
  UPDATE sys.obj$ SET status = 1
    WHERE type#=13 AND subname LIKE '%$VSN\_%' ESCAPE '\' AND status=6 
      AND owner# in (SELECT u.user# from sys.user$ u
                     WHERE u.name in ('ORDSYS', 'ORDPLUGINS', 
                                      'SI_INFORMTN_SCHEMA', 'ORDDATA'));
    COMMIT;
  exception
    when others then
      -- continue regardless
      null;
end;
/

-- 
-- revoke unneeded SYS privs from ORDSYS, ORDPLUGINS, SI_INFORMTN_SCHEMA
-- comes before imprv.sql
declare 
  cmd     VARCHAR2(100);
begin
  for rec in (select grantee, privilege 
              from sys.dba_sys_privs 
              where grantee in ('ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA')
                and privilege not in ('UNLIMITED TABLESPACE'))
  loop
    begin
      cmd := 'revoke ' || rec.privilege || ' from ' || rec.grantee;
      execute immediate cmd;
      -- ignore errors
      exception 
        when others then null;
    end;
  end loop;
  execute immediate 'revoke UNLIMITED TABLESPACE from ordplugins';
  -- ignore errors
  exception 
    when others then null;
end;
/

-- 
-- revoke unneeded roles from ORDSYS, ORDPLUGINS, SI_INFORMTN_SCHEMA
-- comes before imprv.sql
declare 
  cmd     VARCHAR2(100);
begin
  for rec in (select grantee, granted_role 
              from sys.dba_role_privs
              where grantee in ('ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA'))
  loop
    -- this role is needed
    if( (rec.grantee = 'ORDSYS') and (rec.granted_role = 'JAVAUSERPRIV') ) then
      continue;
    end if;
    begin
      cmd := 'revoke ' || rec.granted_role || ' from ' || rec.grantee;
      execute immediate cmd;
      -- ignore errors
      exception 
        when others then null;
    end;
  end loop;
end;
/

-- 
-- revoke grant with grant option privs
--
declare 
  revoke_cmd     VARCHAR2(200);
  grant_cmd      VARCHAR2(200);
begin
  for rec in (select distinct a.owner, b.object_name, a.privilege, 
                              b.object_type, a.grantee
              from sys.dba_tab_privs a,
              sys.dba_objects b
  where a.grantee='PUBLIC'
    and a.grantable='YES'
    and (a.owner in ('ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA') or
         a.grantor in ('ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA')
        )
    and a.table_name=b.object_name
-- filter out the types created for XML schema registration
  and ( (b.object_type <> 'TYPE') 
         or ((b.object_type = 'TYPE') and 
             (b.object_name not like 'ORD\_DICOM\_HEADER%' escape '\')  and
             ((b.object_name like 'ORD%') or 
              (b.object_name like 'SI\_%' escape '\'))
            )
      )
              )
  loop
    begin
      revoke_cmd := 'revoke ' || rec.privilege || ' on ' || rec.owner ||
             '.' || rec.object_name || ' from ' || rec.grantee || ' force' ;
      grant_cmd := 'grant ' || rec.privilege || ' on ' || rec.owner ||
             '.' || rec.object_name || ' to '   || rec.grantee;
      execute immediate revoke_cmd;
      -- We don't want to grant if revoke fails.  Can no longer think of a 
      -- case where revoke might fail now that we're using force.
      begin
        -- Revoke again because some grants were issued twice - once as 
        -- grantor sys and once as grantor ordsys.  A second revoke is 
        -- needed to clean up the second grant.  Handle exception so we
        -- do go on to regrant if this second revoke fails
        execute immediate revoke_cmd;
        exception 
          when others then null;
      end;
      execute immediate grant_cmd;
      -- We want to continue regardless of errors so ignore errors.
      exception 
        when others then null;
    end;
  end loop;
end;
/

-- revoke execute to public from ORDIMERRORCODES
declare 
  cmd     VARCHAR2(200);
begin
  cmd := 'revoke execute on ordsys.ordimerrorcodes from public force';
  execute immediate cmd;
  -- We want to continue regardless of errors so ignore errors.
  exception 
    when others then null;
end;
/

--
-- Miscellaneous revokes for problems found in Fengting's upgrade
-- verification tests
--
begin
  execute immediate 
        'revoke execute on ordsys.ordimageindexstats from public force';
  -- We want to continue regardless of errors so ignore errors.
  exception 
    when others then null;
end;
/

--
-- The following grants were granted with grant option at one point.  They
-- are not needed.  Revoking.
--

begin
  execute immediate 'revoke execute on sys.dbms_registry from ordsys';
  -- We want to continue regardless of errors so ignore errors.
  exception 
    when others then null;
end;
/

begin
  execute immediate 'revoke select on sys.obj$ from ordsys';
  -- We want to continue regardless of errors so ignore errors.
  exception 
    when others then null;
end;
/

begin
  execute immediate 'revoke select on sys.user$ from ordsys';
  -- We want to continue regardless of errors so ignore errors.
  exception 
    when others then null;
end;
/

begin
  execute immediate 'revoke select on sys.dba_views from ordsys';
  -- We want to continue regardless of errors so ignore errors.
  exception 
    when others then null;
end;
/

begin
  execute immediate 'revoke select on sys.dba_tables from ordsys';
  -- We want to continue regardless of errors so ignore errors.
  exception 
    when others then null;
end;
/

begin
  execute immediate 'revoke select on sys.dba_object_tables from ordsys';
  -- We want to continue regardless of errors so ignore errors.
  exception 
    when others then null;
end;
/

--
-- drop 11.1 DICOM views from ORDSYS
-- The public synonyms have the same name as the views
-- The view name in schema takes precedence over public synonym with same name
-- This causes a problem if the view definition has changed between 11.1( ORDSYS) 
-- and 11.2(ORDDATA).

-- Drop public synonym to public views
begin
  for cur in (select 'orddcm_documents' syn_name from dual
              union 
              select 'orddcm_document_types' sys_name from dual
              union 
              select 'orddcm_document_refs' sys_name from dual
              union 
              select 'orddcm_conformance_vld_msgs' sys_name from dual
              union 
              select 'orddcm_constraint_names' sys_name from dual) loop
    begin
      EXECUTE IMMEDIATE ' drop public synonym ' || cur.syn_name;

EXCEPTION 
       WHEN OTHERS THEN
         NULL;
     END;
  end loop;

end;
/

-- 
-- drop 11.1 dicom views in ORDSYS 
-- 
BEGIN
   for cur in ( select view_name from sys.dba_views 
                where owner='ORDSYS' and view_name like 'ORDDCM_%') loop
    begin
      EXECUTE IMMEDIATE ' DROP VIEW ORDSYS.' || cur.view_name;

EXCEPTION 
       WHEN OTHERS THEN
         NULL;
     END;
   end loop;
END;
/

-- grant privs
@@imprv.sql

-- create DICOM tables in ORDDATA schema
@@ordcrtbl.plb

--create DICOM views in ORDDATA schema
@@ordcrvw.plb

-- Leave data , tables  in ordsys unchanged for downgrade purposes.
-- ordsys might contain user documents.

-- Drop obsolete validate_ordim_with_details procedure.  This should have been
-- dropped in 10.2 to 11.1 upgrade, but wasn't done there.  We'll do it here
-- to catch all upgrades to 11.2.
begin
    execute immediate 'DROP procedure ORDSYS.validate_ordim_with_details';
end;
/
-- add watermark methods
begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage
        ADD MEMBER PROCEDURE applyWatermark(SELF    IN OUT NOCOPY ORDSYS.ORDImage,
                               added_text IN    VARCHAR2,
                               dest    IN OUT NOCOPY ORDSYS.ORDImage,
                               logging OUT VARCHAR2,
                               watermark_properties IN ORDSYS.ORD_STR_LIST default null)
        CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage
        ADD MEMBER PROCEDURE applyWatermark(SELF    IN OUT NOCOPY ORDSYS.ORDImage,
                               added_image IN OUT NOCOPY ORDSYS.ORDImage,
                               dest    IN OUT NOCOPY ORDSYS.ORDImage,
                               logging OUT VARCHAR2,
                               watermark_properties IN ORDSYS.ORD_STR_LIST default null)
        CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage
        ADD STATIC PROCEDURE applyWatermark(imageBlob     IN BLOB,
                               added_text       IN VARCHAR2,
                               dest          IN OUT NOCOPY BLOB,
                               logging OUT VARCHAR2,
                               watermark_properties IN ordsys.ord_str_list default null)
        CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage
        ADD STATIC PROCEDURE applyWatermark(imageBlob     IN BLOB,
                               added_image      IN BLOB,
                               dest          IN OUT NOCOPY BLOB,
                               logging OUT VARCHAR2,
                               watermark_properties IN ordsys.ord_str_list default null)
        CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage
        ADD STATIC PROCEDURE applyWatermark(imageBfile    IN OUT NOCOPY BFILE,
                               added_text       IN VARCHAR2,
                               dest          IN OUT NOCOPY BLOB,
                               logging OUT VARCHAR2,
                               watermark_properties IN ordsys.ord_str_list default null)
        CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage
        ADD STATIC PROCEDURE applyWatermark(imageBfile    IN OUT NOCOPY BFILE,
                               added_image      IN OUT NOCOPY BFILE,
                               dest          IN OUT NOCOPY BLOB,
                               logging OUT VARCHAR2,
                               watermark_properties IN ordsys.ord_str_list default null)
        CASCADE';
end;
/

--
-- Add user-defined object constructors
--
begin
    execute immediate 'ALTER TYPE ORDSYS.ORDImage 
      ADD CONSTRUCTOR FUNCTION ORDImage( 
          SELF IN OUT NOCOPY ORDSYS.ORDImage, 
          data  IN BLOB,
          setProperties IN INTEGER DEFAULT 0)
          RETURN SELF AS RESULT CASCADE';
end;
/

begin
    execute immediate 'ALTER TYPE ORDSYS.ORDImage
      ADD CONSTRUCTOR FUNCTION ORDImage( 
          SELF IN OUT NOCOPY ORDSYS.ORDImage, 
          source_type       IN VARCHAR2 DEFAULT ''LOCAL'',
          source_location   IN VARCHAR2 DEFAULT NULL,
          source_name       IN VARCHAR2 DEFAULT NULL,
          setProperties  IN INTEGER DEFAULT 0)
          RETURN SELF AS RESULT CASCADE';
end;
/

begin
    execute immediate 'ALTER TYPE ORDSYS.ORDAudio 
      ADD CONSTRUCTOR FUNCTION ORDAudio( 
          SELF IN OUT NOCOPY ORDSYS.ORDAudio, 
          data  IN BLOB,
          setProperties IN INTEGER DEFAULT 0)
          RETURN SELF AS RESULT CASCADE';
end;
/

begin
    execute immediate 'ALTER TYPE ORDSYS.ORDAudio
      ADD CONSTRUCTOR FUNCTION ORDAudio( 
          SELF IN OUT NOCOPY ORDSYS.ORDAudio, 
          source_type       IN VARCHAR2 DEFAULT ''LOCAL'',
          source_location   IN VARCHAR2 DEFAULT NULL,
          source_name       IN VARCHAR2 DEFAULT NULL,
          setProperties  IN INTEGER DEFAULT 0)
          RETURN SELF AS RESULT CASCADE';
end;
/

begin
    execute immediate 'ALTER TYPE ORDSYS.ORDVideo 
      ADD CONSTRUCTOR FUNCTION ORDVideo( 
          SELF IN OUT NOCOPY ORDSYS.ORDVideo, 
          data  IN BLOB,
          setProperties IN INTEGER DEFAULT 0)
          RETURN SELF AS RESULT CASCADE';
end;
/

begin
    execute immediate 'ALTER TYPE ORDSYS.ORDVideo
      ADD CONSTRUCTOR FUNCTION ORDVideo( 
          SELF IN OUT NOCOPY ORDSYS.ORDVideo, 
          source_type       IN VARCHAR2 DEFAULT ''LOCAL'',
          source_location   IN VARCHAR2 DEFAULT NULL,
          source_name       IN VARCHAR2 DEFAULT NULL,
          setProperties  IN INTEGER DEFAULT 0)
          RETURN SELF AS RESULT CASCADE';
end;
/

begin
    execute immediate 'ALTER TYPE ORDSYS.ORDDoc 
      ADD CONSTRUCTOR FUNCTION ORDDoc( 
          SELF IN OUT NOCOPY ORDSYS.ORDDoc, 
          data  IN BLOB,
          setProperties IN INTEGER DEFAULT 0)
          RETURN SELF AS RESULT CASCADE';
end;
/

begin
    execute immediate 'ALTER TYPE ORDSYS.ORDDoc
      ADD CONSTRUCTOR FUNCTION ORDDoc( 
          SELF IN OUT NOCOPY ORDSYS.ORDDoc, 
          source_type       IN VARCHAR2 DEFAULT ''LOCAL'',
          source_location   IN VARCHAR2 DEFAULT NULL,
          source_name       IN VARCHAR2 DEFAULT NULL,
          setProperties  IN INTEGER DEFAULT 0)
          RETURN SELF AS RESULT CASCADE';
end;
/

--
-- Drop Deprecated Image Indexing and any user index of type ORDIMAGEINDEX
--

--  
-- First drop user indexes of type ORDSYS.ORDIMAGINDEX
--
declare
  cursor imgidx_curs is select index_name, owner from sys.dba_indexes 
    where index_type = 'DOMAIN' and ityp_name = 'ORDIMAGEINDEX';
  index_name VARCHAR2(30);
  index_owner VARCHAR2(30);
  idxcurs integer;

begin
  idxcurs := sys.dbms_sql.open_cursor;
  open imgidx_curs;
  loop        
    begin
      fetch imgidx_curs into index_name, index_owner;
          exit when imgidx_curs%NOTFOUND;

-- Note: these drop statements are executed immediately upon parse
      -- thus the class name needs to be specified, but no execute is
      --  required
      --
      -- drop the domain index
      sys.dbms_sql.parse(idxcurs, 'drop index ' || 
                         sys.dbms_assert.enquote_name(index_owner) || '.' ||
                         sys.dbms_assert.enquote_name(index_name) || ' force',
                         sys.dbms_sql.native);
      -- drop associated table
      sys.dbms_sql.parse(idxcurs, 'drop table ' || 
                         sys.dbms_assert.enquote_name(index_owner) || '.' ||
                         sys.dbms_assert.enquote_name(index_name) || '_FT$',
                         sys.dbms_sql.native);
    exception when others then
      raise;
    end;
  end loop;
  close imgidx_curs;
  sys.dbms_sql.close_cursor(idxcurs);
end;
/

begin
  execute immediate 'drop indextype ordsys.ORDIMAGEINDEX';
exception when others then
  null;
end;
/
begin
  execute immediate 'drop operator ordsys.IMGScore';
exception when others then
  null;
end;
/
begin
  execute immediate 'drop operator ordsys.IMGSimilar';
exception when others then
  null;
end;
/
begin
  execute immediate 'drop function ordsys.ScoreFunc';
exception when others then
  null;
end;
/
begin
  execute immediate 'drop function ordsys.SimilarFunc'; 
exception when others then
  null;
end;
/
begin
  execute immediate 'drop type ordsys.ORDIMGIDXMethods';
exception when others then
  null;
end;
/
begin
  execute immediate 'drop type ordsys.ORDImageIndexStats';
exception when others then
  null;
end;
/

Rem This should be at the end of the file
alter session set current_schema="ORDSYS";

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de