Rem Copyright (c) 2008, 2011, 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(' 0 '); 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(' 0 '); xdb.dbms_xmlschema.inPlaceEvolve('http://xmlns.oracle.com/ord/meta/exif', xmlDiff, 1); end; / -- -- Perform in place evolve for the ordcmpf.xsd schema. -- Update the schema annotation field. -- Since the change to the schema is just replacing the text node of the -- annotation field, this can be done multiple times in the case of -- upgrade/downgrade/upgrade. -- declare xmlDiff sys.xmltype; begin xmlDiff := xmltype(' Introduction This schema defines the run-time preference settings for Oracle Multimedia DICOM features. Structure Overview Question mark "?" means optional items. Plus "+" means one or more items. Asterisk "*" means zero or more items. DICOM_RUNTIME_PREFERENCES DOCUMENT_HEADER? DOCUMENT_CHANGE_LOG* DOCUMENT_MODIFIER DOCUMENT_MODIFICATION_DATE DOCUMENT_VERSION? MODIFICATION_COMMENT? BASE_DOCUMENT? BASE_DOCUMENT_RELEASE_DATE? BASE_DOCUMENT_DESCRIPTION? PREFERENCE_DEF+ PARAMETER DESCRIPTION VALUE The allowed values for the PARAMETER element of a PREFERENCE_DEF entry and its corresponding VALUE element are as follows: PARAMETER: XML_SKIP_ATTR VALUE: an integer type (default 512, 128~ 2^32-1) DESCRIPTION: The value of this parameter specifies size limits for DICOM attributes. Omit DICOM attributes with sizes (in bytes) in the DICOM content larger than the value of this parameter. If an attribute of type SQ is omitted, its child items are also omitted. The smallest value allowed for this parameter is 128. PARAMETER: AVG_ATTR_NUM VALUE: an integer type (default 200, 20~2000) DESCRIPTION: The value of this parameter specifies the average number of root-level attributes expected for each DICOM Part 10 file. Finding the optimal value for a database can help to improve storage efficiency and performance. A value that is too large can result in wasted memory, while a value that is too small can yield poor performance. An optimal value is one where most (suggestion: 95%) of the DICOM content contains fewer root-level attributes than the value specified in this parameter. The smallest value allowed for this parameter is 20. The largest value allowed for this parameter is the total number of defined, not retired standard attributes. PARAMETER: CONFORMANCE_LEVEL VALUE: enum { leastConform, ignoreException(default), mostConform} DESCRIPTION: The value of this parameter specifies how errors and exceptions are handled by Oracle Multimedia DICOM. - "leastConform" instructs all functions to ignore errors and exceptions to maximize the processing of DICOM content. - "ignoreException" instructs all functions to ignore the types of exceptions given in the parameter "IGNORED_EXP_LIST". - "mostConform" instructs all functions to throw an exception when encountering DICOM content that does not conform to the DICOM standard. This behavior does not include backward compatibility cases that are allowed by the DICOM standard. Note: Choosing an option other than "mostConform" might enable some functions to accept invalid DICOM content, which could produce incorrect results. If you must choose an option other than "mostConform", Oracle recommends setting the value of the LOGGING_LEVEL parameter to "warning" or a more detailed logging level, and examining the log file for possible errors. PARAMETER: IGNORED_EXP_LIST VALUE: EmptySpace-separated exception names from the following list: {MISSING_MAGIC, MISSING_HEADER, MISSING_ATTR, FAULTY_VALUE, INVALID_LENGTH, INVALID_VM, INVALID_VR, UNSUPPORT_VALUE, UNDEFINED_VALUE, NOT_AN_IMAGE, PARSE_ERR} Default: {MISSING_ATTR INVALID_LENGTH MISSING_MAGIC MISSING_HEADER INVALID_VR INVALID_VM PARSE_ERR}. DESCRIPTION: This parameter lists the exceptions to be ignored at run time when the value of the CONFORMANCE_LEVEL parameter is "ignoreException". Ignored exceptions will be logged if the LOGGING_LEVEL parameter is set to "warning" or a more detailed level. The program will then continue, skipping the part of the DICOM Part 10 file that triggered the ignored exception. Possible values for this parameter are defined as follows: - MISSING_MAGIC: a DICOM Part 10 file does not contain the file magic number "DICM". - MISSING_HEADER: a DICOM Part 10 file does not have the file meta header (not conformant to Part 10 of the DICOM standard) - MISSING_ATTR: a DICOM Part 10 file does not have the mandatory attributes (type 1) required by the DICOM standard. - FAULTY_VALUE: a DICOM Part 10 file has an attribute value that results in one or more parsing errors. - INVALID_LENGTH: a DICOM Part 10 file contains a length value that is not consistent with the DICOM encoding rules or a length that is not permitted by the DICOM data dictionary. - INVALID_VM: an attribute of a DICOM Part 10 file has an invalid Value Multiplicity value (not consistent with the dictionary definition). - INVALID_VR: an attribute of a DICOM Part 10 file has an invalid Value Representation value, which either conflicts with the data dictionary or has not been defined by the data dictionary. - UNSUPPORTED_VALUE: a DICOM Part 10 file contains attribute values that are outside of the supported range; for example, an unsupported pixel representation value. - UNDEFINED_VALUE: a DICOM Part 10 file contains attribute values that are not defined by the data model; for example, an undefined transfer syntax UID, or an undefined SOP class UID. - NOT_AN_IMAGE: When an image content processing function is invoked on a DICOM Part 10 file, and the SOP class UID is defined but its classification is not "storageClass", or its content type is not "image", an exception is thrown. The exception can indicate that the UID definition document is out of date. If so, an administrator can update the document, redefining the SOP class UID as a "storageClass" of type "image". - PARSE_ERR: When a DICOM Part 10 file contains invalid data, a parsing exception is thrown. When the exception is ignored, the parsing process continues. PARAMETER: OUTPUT_RAW_VALUE VALUE: an integer value (default 0, no output) (-1 ~ 32767) DESCRIPTION: This parameter specifies what to output in an XML metadata document when an attempt to parse DICOM content fails. The VALUE element specifies what to save in the rawValue attribute of a DICOM XML element, with the following options: $VALUE == -1, (not recommended), the entire attribute, up to 32k, is saved in the rawValue attribute in base64 encoding. $VALUE == 0, an empty string is saved in the rawValue attribute (recommended for production systems). $VALUE == N > 0, only the first N bytes of the attribute are saved in the rawValue attribute in base64 encoding. A nonzero value for this parameter is useful for debugging purposes. For a production system, do NOT pick a value larger than 64. The value -1 should never be used outside of a development environment. PARAMETER: LOGGING_LEVEL VALUE: enum {debug, conformance, warning(default), error, none } DESCRIPTION: This parameter specifies the logging level. Valid values, ordered by level of detail from the most to the least, are: "debug", "conformance", "warning", "error", and "none". - "none" means that logging is disabled. - "error" enables logging of irrecoverable messages only. - "warning" enables logging of all recoverable messages that require operator attention. For example, calling an image processing function on a DICOM Part 10 file that Oracle does not recognize as an image produces a logged warning message that the content is not defined as an image. Processing of the content can continue, however, if the CONFORMANCE_LEVEL parameter value "ignoreException" is set to ignore the exception "NOT_AN_IMAGE". - "conformance" enables logging of all errors and exceptions that occur while processing DICOM content that does not conform to the DICOM standard. Non-conformant DICOM content is common in DICOM repositories that contain DICOM content from various sources, such as a hospital or an imaging center. Using this option can produce large log files for most scenarios, and might decrease the performance. - "debug" enables extensive logging of all steps. Use this option only for debugging purposes. Do not use the "debug" option for a deployed system. It adds significant overhead and slows down all DICOM related functions. PARAMETER: VALIDATE_METADATA VALUE: Boolean{true, false(default)} DESCRIPTION: The value of this parameter determines whether to validate the XML documents used in the DICOM functions and procedures. If the value of this parameter is false, the XML documents are not validated. If the value is set to true, the XML documents are validated against a specific XML schema that is registered with Oracle XML DB. All XML documents used in the DICOM functions and procedures, except those that are generated by the method extractMetadata(), are validated against the Oracle default DICOM metadata schema. The XML documents generated by the method extractMetadata() are validated against the XML schema whose namespace is defined in the specified mapping document. PARAMETER: EXP_IF_NULL_ATTR_IN_CONSTRAINT VALUE: Boolean{true(default), false} DESCRIPTION: This parameter determines null or missing attribute handling during conformance validation. When evaluating a constraint predicate, if the value of this parameter is true, an exception is thrown only if all of the following conditions are satisfied: - the attribute being evaluated is missing, or has a null value - there is no preceding "notEmpty" Boolean function predicate on the attribute If the value of this parameter is false, no exception is thrown. This parameter affects the results of the method isConformanceValid( ). PARAMETER: MAX_RECURSION_DEPTH VALUE: an integer type (default 16, 1 ~ 32767) DESCRIPTION: This parameter restricts the number of levels of recursion when evaluating a recursive constraint on a DICOM Part 10 file. If the recursion level exceeds this number, an exception is thrown. This parameter affects the results of the method isConformanceValid( ). PARAMETER: MANDATE_ATTR_TAGS_IN_STL VALUE: Boolean{false(default), true} DESCRIPTION: This parameter is used to enforce the rule that all tags used by the constraint and mapping documents must be listed in the STORED_TAG_LIST(STL) document. This rule is not enforced by default. If this preference parameter is set to true, the rule is enforced. If an existing STL document does not satisfy this rule, the preference value cannot be set to true until the STL document is deleted or updated. PARAMETER: SQ_WRITE_LEN VALUE: boolean{true(default), false} DESCRIPTION: This parameter determines how the DICOM sequence (SQ) types are encoded by the DICOM function writeMetadata(). If the value of this parameter is true, the SQ types are encoded with explicit length and without item or sequence delimiters. This is the default behavior and allows DICOM viewers to skip the sequence attributes easily. If the value of this parameter is false, the SQ types are encoded with variable (or undefined) lengths and terminated with sequence delimiters. This allows backward compatibility with some older DICOM viewers and DICOM applications that only support undefined lengths for SQ types. PARAMETER: SPECIFIC_CHARACTER_SET VALUE: enum {ASCII(default), ISO_IR 100, ISO_IR 101, ISO_IR 109, ISO_IR 110, ISO_IR 144, ISO_IR 127, ISO_IR 126, ISO_IR 138, ISO_IR 148, ISO_IR 13, ISO_IR 166, ISO_IR 192, GB18030} DESCRIPTION: This parameter determines how data elements with value representations of SH (Short String), LO (Long String), ST (Short Text) LT (Long Text), PN (Person Name) or UT (Unlimited Text) are decoded when the Specific Character Set (0008,0005) attribute is missing. The DICOM standard states that the default character set (ISO-IR 6, or ASCII) shall be used for decoding when the Specific Character Set (0008,0005) attribute is not specified. This parameter allows an application to specify a different character set to use in these cases. PARAMETER: BINARY_SKIP_INVALID_ATTR VALUE: boolean{false(default), true} DESCRIPTION: The value of this parameter determines whether Oracle Multimedia DICOM includes or skips invalid attributes (attributes that do not conform to the DICOM standard) and their values in the binary output of DICOM content when making a copy of the DICOM content. The default behavior, which is specified by setting this parameter to "false", is not to skip these values but to include them in the resulting output. When an attribute is skipped, its value is included in the output with length 0. '); xdb.dbms_xmlschema.inPlaceEvolve('http://xmlns.oracle.com/ord/dicom/preference_1_0', xmlDiff, 1); end; / -- bug 8622470 Expand enumeration for bitPerSample_t declare xquery varchar2(2000); yes integer := 0; xmldiff sys.xmltype; begin xquery := 'declare default element namespace ' || ' "http://www.w3.org/2001/XMLSchema";' || ' $x /schema/simpleType[@name="bitsPerSample_t"]' || '/restriction[1]/enumeration[2]'; select 1 into yes from all_xml_schemas where owner='ORDSYS' and schema_url='http://xmlns.oracle.com/ord/meta/exif' and xmlexists(xquery passing schema as "x"); -- do nothing if the second enumeration is found exception when no_data_found then xmldiff := xmltype(' '); 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(' 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. 0 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. '); -- 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/mddatatype_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/mddatatype_1_0'; if (xml_number is null) then -- evolve the datatype schema used by the default metadata schema xdb.dbms_xmlschema.inPlaceEvolve( 'http://xmlns.oracle.com/ord/dicom/mddatatype_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/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; / -- bug 8372736 update PN/VALUE maxLength -- update is not idempotent declare xmlDiff sys.xmltype; xml_number sys.xmltype; oldValue number; type list_t is table of varchar2(100); ordSchemas list_t; begin xmlDiff := xmltype(' 192 '); ordSchemas := list_t( 'http://xmlns.oracle.com/ord/dicom/mddatatype_1_0', 'http://xmlns.oracle.com/ord/dicom/datatype_1_0', 'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0' ); -- conditionally evolve the datatype schemas for i in ordSchemas.first .. ordSchemas.last loop select XMLCast( XMLQuery('declare default element namespace "http://www.w3.org/2001/XMLSchema"; $x/schema/complexType[@name="PN"]/sequence[1]/element[2]/simpleType[1]/restriction[1]/maxLength[1]/@value' passing t.schema as "x" returning content) as number) into oldvalue from sys.all_xml_schemas t where owner = 'ORDSYS' and schema_url=ordSchemas(i); if(oldValue = 64) then begin xdb.dbms_xmlschema.inPlaceEvolve(ordSchemas(i), xmlDiff, xdb.dbms_xmlschema.inplace_evolve); -- continue exception when others then null; end; end if; end loop; 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; / -- -- Bug 10315929 - make sure NOCOPY declaration matches in spec and body -- begin execute immediate 'ALTER TYPE ORDSYS.ORDImage DROP STATIC PROCEDURE processCopy (imageBfile IN OUT BFILE, command IN VARCHAR2, dest IN OUT NOCOPY BLOB) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDImage ADD STATIC PROCEDURE processCopy (imageBfile IN OUT NOCOPY BFILE, command IN VARCHAR2, dest IN OUT NOCOPY BLOB) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP STATIC PROCEDURE importFrom (ctx IN OUT RAW, local_data IN OUT BLOB, source_type IN VARCHAR2, source_location IN VARCHAR2, source_name IN VARCHAR2) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDVideo ADD STATIC PROCEDURE importFrom (ctx IN OUT RAW, local_data IN OUT NOCOPY BLOB, source_type IN VARCHAR2, source_location IN VARCHAR2, source_name IN VARCHAR2) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP STATIC PROCEDURE importFrom (ctx IN OUT RAW, local_data IN OUT BLOB, source_type IN VARCHAR2, source_location IN VARCHAR2, source_name IN VARCHAR2, format OUT VARCHAR2, mime_type OUT VARCHAR2) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDVideo ADD STATIC PROCEDURE importFrom (ctx IN OUT RAW, local_data IN OUT NOCOPY BLOB, source_type IN VARCHAR2, source_location IN VARCHAR2, source_name IN VARCHAR2, format OUT VARCHAR2, mime_type OUT VARCHAR2) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDDoc DROP MEMBER PROCEDURE setMimeType (mime IN VARCHAR2) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDDoc ADD MEMBER PROCEDURE setMimeType (SELF IN OUT NOCOPY ORDDoc, mime IN VARCHAR2) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDDoc DROP MEMBER PROCEDURE setFormat (knownFormat IN VARCHAR2) CASCADE'; end; / begin execute immediate 'ALTER TYPE ORDSYS.ORDDoc ADD MEMBER PROCEDURE setFormat (SELF IN OUT NOCOPY ORDDoc, knownFormat IN VARCHAR2) 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,false) || '.' || sys.dbms_assert.enquote_name(index_name,false) || ' force', sys.dbms_sql.native); -- drop associated table sys.dbms_sql.parse(idxcurs, 'drop table ' || sys.dbms_assert.enquote_name(index_owner,false) || '.' || sys.dbms_assert.enquote_name(index_name || '_FT$',false), 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";