Rem Copyright (c) 2003, 2011, Oracle and/or its affiliates. Rem All rights reserved. REM Rem NAME Rem impatch.sql Rem REM You must connect as SYSDBA prior to running this script REM REM This patch script applies bug fixes for Oracle Multimedia 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 patch 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 Make sure Oracle Multimedia dependencies are valid (and log errors and REM continue if not). Check Oracle Multimedia validity. REM @@imchkdep.sql 1 LOADING LOADED REM Grant required privs in case anything has been revoked @@imprv.sql REM Reload ORDSYS components alter session set current_schema="ORDSYS"; EXECUTE sys.dbms_registry.loading('ORDIM','Oracle Multimedia','validate_ordim','ORDSYS', sys.dbms_registry.schema_list_t('ORDPLUGINS','SI_INFORMTN_SCHEMA', 'ORDDATA')); Rem Clean out previous Java objects @@imclnjav.sql REM REM Bug 10315929 - make sure NOCOPY declaration matches in spec and body REM 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; / REM Reload public modifed packages @@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 -- -- 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 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; / -- 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; / REM REM The following updates to the DICOM repository table contents REM are due to the changes in 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; ex exception; 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 ordcmcmd.xml and ordcmct.xml @@imxrupins.sql Rem insert back user inserted docs that have been deleted declare ex exception; 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; / Rem change max value of XML_SKIP_ATTR alter session set current_schema=ORDDATA; update ORDDATA.orddcm_prefs_lookup set max_val=4294967295 where param_name='XML_SKIP_ATTR'; alter session set current_schema="ORDSYS"; execute sys.dbms_registry.loaded('ORDIM'); alter session set current_schema="SYS"; REM recompile invalid ORDSYS types execute sys.dbms_session.reset_package; execute sys.utl_recomp.recomp_serial('ORDSYS'); REM REM Invoke Locator patch and register MDSYS if SDO is not installed REM and MDSYS schema exists. Note - if MDSYS does not exist REM this is most likely an internal test scenario and we REM should not attempt to invoke Locator patch script. REM COLUMN :script_name NEW_VALUE comp_file NOPRINT Variable script_name varchar2(50) declare mdsyscnt NUMBER := 0; sdo_status VARCHAR2(20) := NULL; begin -- Check whether MDSYS exists. -- If not, Do not upgrade Locator and register MDSYS select count(*) into mdsyscnt from sys.dba_users where username='MDSYS'; -- Check whether SDO is installed. -- If not installed or if OPTION OFF, patch Locator sdo_status := dbms_registry.status('SDO'); if ((sdo_status is NULL or sdo_status = 'OPTION OFF') and (mdsyscnt > 0)) then :script_name := '?/md/admin/locpatch.sql'; dbms_registry.loading ('ORDIM', 'Oracle Multimedia', 'validate_ordim', 'ORDSYS', sys.dbms_registry.schema_list_t('ORDPLUGINS','SI_INFORMTN_SCHEMA', 'MDSYS', 'ORDDATA')); else :script_name := sys.dbms_registry.nothing_script; end if; end; / 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