eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PROCEDURE: SYS.VALIDATE_ORDIM

Source


1 PROCEDURE     validate_ordim
2 authid current_user
3 IS
4  l_null char(1);
5  b_valid       boolean := TRUE;           -- keep track installation status
6                                           -- TRUE: valid (default),
7                                           -- FALSE: invalid
8  l_num_objects integer     :=0;
9  l_num_classes integer     :=0;
10  l_num_schemas integer     :=0;
11  l_num_docs    integer     :=0;
12  l_num_roles   integer     :=0;
13  l_num_errors  number      :=0;
14  err_code NUMBER;
15  err_msg  VARCHAR2(100);
16  l_prevDbVer varchar2(40);
17  l_status  varchar2(100);
18  l_dsql_block varchar2(500);
19  l_sdo_status varchar2(20) := NULL;
20  b_upgradeMode boolean     := FALSE;
21 
22 BEGIN
23   -- Now check whether Multimedia objects are created
24   begin
25     select count(*) into l_num_objects from sys.obj$ o, sys.user$ u
26       where u.name = 'ORDSYS'
27         and u.user# = o.owner#
28         and o.type# = 13
29         and o.name in
30             ( 'ORDIMAGE', 'ORDAUDIO', 'ORDDOC', 'ORDVIDEO', 'ORDDICOM',
31               'SI_COLOR', 'SI_STILLIMAGE', 'SI_AVERAGECOLOR',
32               'SI_COLORHISTOGRAM', 'SI_POSITIONALCOLOR', 'SI_TEXTURE',
33               'SI_FEATURELIST'
34             )
35     ;
36 
37     if ( l_num_objects != 12 ) then
38       b_valid := FALSE;
39       dbms_output.put_line('ORDIM created ' || l_num_objects || ' objects.');
40       dbms_output.put_line('The following objects are not created:');
41 
42       for r in (
43       (
44         select 'ORDIMAGE' name from dual
45         union
46         select 'ORDAUDIO' name from dual
47         union
48         select 'ORDDOC' name from dual
49         union
50         select 'ORDVIDEO' name from dual
51         union
52         select 'ORDDICOM' name from dual
53         union
54         select 'SI_COLOR' name from dual
55         union
56         select 'SI_STILLIMAGE' name from dual
57         union
58         select 'SI_AVERAGECOLOR' name from dual
59         union
60         select 'SI_COLORHISTOGRAM' name from dual
61         union
62         select 'SI_POSITIONALCOLOR' name from dual
63         union
64         select 'SI_TEXTURE' name from dual
65         union
66         select 'SI_FEATURELIST' name from dual
67       )
68       minus
69       (
70         select o.name from sys.obj$ o, sys.user$ u
71         where u.name = 'ORDSYS'
72           and u.user# = o.owner#
73           and o.type# = 13
74           and o.name in
75               ( 'ORDIMAGE', 'ORDAUDIO', 'ORDDOC', 'ORDVIDEO', 'ORDDICOM',
76                 'SI_COLOR', 'SI_STILLIMAGE', 'SI_AVERAGECOLOR',
77                 'SI_COLORHISTOGRAM', 'SI_POSITIONALCOLOR', 'SI_TEXTURE',
78                 'SI_FEATURELIST'
79               )
80       ) ) loop
81         dbms_output.put_line(r.name);
82       end loop;
83 
84     end if;
85 
86   exception
87     WHEN NO_DATA_FOUND THEN
88       b_valid := FALSE;
89       dbms_output.put_line('ORDIM objects are not created.');
90 
91     WHEN OTHERS THEN
92       b_valid := FALSE;
93       err_code := SQLCODE;
94       err_msg  := SUBSTR(SQLERRM, 1 , 100);
95       DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
96   end;
97 
98   -- Now check whether Multimedia java classes are loaded
99   -- The following jars are checked:
100   --  mlibwrapper_jai.jar, jai_codec.jar, jai_core.jar, ordimimg.jar
101   --  ordimdcm.jar, ordimann.jar
102   begin
103     select count(*) into l_num_classes
104       from sys.obj$ o, sys.user$ u, sys.javasnm$ j
105       where u.name = 'ORDSYS'
106         and u.user# = o.owner#
107         and o.type# = 29
108         and j.short(+) = o.name
109         and nvl(j.longdbcs, o.name) in
110             ( 'javax/media/jai/JAI', 'com/sun/media/jai/codec/ImageCodec',
111               'com/sun/medialib/mlib/mediaLibImage', 'oracle/ord/media/img/PropsAdapter',
112               'oracle/ord/dicom/attr/DicomAttrTag',
113               'oracle/ord/media/annotator/servclt/AnnCltInServer'
114             )
115     ;
116 
117     if ( l_num_classes != 6 ) then
118       b_valid := FALSE;
119       dbms_output.put_line('The following ORDIM Java libs are not loaded correctly:');
120 
121       for r in (
122       (
123         select 'javax/media/jai/JAI' name, 'jai_core.jar' jar from dual
124         union
125         select 'com/sun/media/jai/codec/ImageCodec' name, 'jai_codec.jar' jar from dual
126         union
127         select 'com/sun/medialib/mlib/mediaLibImage' name, 'mlibwrapper_jai.jar' jar from dual
128         union
129         select 'oracle/ord/media/img/PropsAdapter' name, 'ordimimg.jar' jar from dual
130         union
131         select 'oracle/ord/dicom/attr/DicomAttrTag' name, 'ordimdcm.jar' jar from dual
132         union
133         select 'oracle/ord/media/annotator/servclt/AnnCltInServer' name, 'ordimann.jar' jar from dual
134       )
135       minus
136       (
137         select nvl(j.longdbcs, o.name) name,
138           decode( nvl(j.longdbcs, o.name) , 'javax/media/jai/JAI', 'jai_core.jar',
139                     'com/sun/media/jai/codec/ImageCodec', 'jai_codec.jar',
140                     'com/sun/medialib/mlib/mediaLibImage', 'mlibwrapper_jai.jar',
141                     'oracle/ord/media/img/PropsAdapter', 'ordimimg.jar',
142                     'oracle/ord/dicom/attr/DicomAttrTag', 'ordimdcm.jar',
143                     'oracle/ord/media/annotator/servclt/AnnCltInServer', 'ordimann.jar') jar
144           from sys.obj$ o, sys.user$ u, sys.javasnm$ j
145           where u.name = 'ORDSYS'
146             and u.user# = o.owner#
147             and o.type# = 29
148             and j.short(+) = o.name
149             and nvl(j.longdbcs, o.name) in
150                 ( 'javax/media/jai/JAI', 'com/sun/media/jai/codec/ImageCodec',
151                   'com/sun/medialib/mlib/mediaLibImage', 'oracle/ord/media/img/PropsAdapter',
152                   'oracle/ord/dicom/attr/DicomAttrTag',
153                   'oracle/ord/media/annotator/servclt/AnnCltInServer'
154                 )
155       ) ) loop
156         dbms_output.put_line(r.jar);
157       end loop;
158     end if;
159 
160   exception
161     WHEN NO_DATA_FOUND THEN
162       b_valid := FALSE;
163       dbms_output.put_line('ORDIM java classes are not loaded.');
164 
165     WHEN OTHERS THEN
166       b_valid := FALSE;
167       err_code := SQLCODE;
168       err_msg  := SUBSTR(SQLERRM, 1 , 100);
169       DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
170   end;
171 
172 
173   -- Now check whether there are invalid objects
174   BEGIN
175     SELECT NULL INTO l_null  FROM sys.dba_invalid_objects
176     WHERE
177           (owner = 'ORDSYS'
178         OR owner = 'ORDPLUGINS'
179         OR owner = 'SI_INFORMTN_SCHEMA'
180         OR owner = 'ORDDATA')
181        AND rownum <= 1;
182 
183     -- invalid objects found
184     b_valid := FALSE;
185 
186     FOR ob IN (SELECT o.object_name, o.status, o.object_type
187                FROM sys.dba_invalid_objects o
188         WHERE
189               (o.owner = 'ORDSYS'
190             OR o.owner = 'ORDPLUGINS'
191             OR o.owner = 'SI_INFORMTN_SCHEMA'
192             OR o.owner = 'ORDDATA')
193             AND rownum < 20) LOOP
194       dbms_output.put_line ('ORDIM INVALID OBJECTS: ' || ob.object_name || ' - ' || ob.status || ' - ' || ob.object_type);
195     END LOOP;
196 
197   EXCEPTION
198     WHEN NO_DATA_FOUND THEN
199       -- no valid objects, don't need to do anything
200       null;
201 
202     WHEN OTHERS THEN
203       b_valid := FALSE;
204       err_code := SQLCODE;
205       err_msg  := SUBSTR(SQLERRM, 1 , 100);
206       DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
207   END;
208 
209   -- Now check whether XML schemas are registered
210   begin
211     select count(*) into l_num_schemas from sys.all_xml_schemas
212       where owner = 'ORDSYS'
213         and schema_url in
214             (
215              'http://xmlns.oracle.com/ord/meta/ordimage',
216              'http://xmlns.oracle.com/ord/meta/exif',
217              'http://xmlns.oracle.com/ord/meta/iptc',
218              'http://xmlns.oracle.com/ord/meta/xmp',
219              'http://xmlns.oracle.com/ord/meta/dicomImage',
220              'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0',
221              'http://xmlns.oracle.com/ord/dicom/datatype_1_0',
222              'http://xmlns.oracle.com/ord/dicom/mddatatype_1_0',
223              'http://xmlns.oracle.com/ord/dicom/anonymity_1_0',
224              'http://xmlns.oracle.com/ord/dicom/attributeTag_1_0',
225              'http://xmlns.oracle.com/ord/dicom/constraint_1_0',
226              'http://xmlns.oracle.com/ord/dicom/metadata_1_0',
227              'http://xmlns.oracle.com/ord/dicom/mapping_1_0',
228              'http://xmlns.oracle.com/ord/dicom/manifest_1_0',
229              'http://xmlns.oracle.com/ord/dicom/preference_1_0',
230              'http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0',
231              'http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0',
232              'http://xmlns.oracle.com/ord/dicom/orddicom_1_0',
233              'http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0'
234           )
235     ;
236 
237     if ( l_num_schemas != 19 ) then
238       b_valid := FALSE;
239       dbms_output.put_line('ORDIM registered ' || l_num_schemas || ' XML schemas.');
240       dbms_output.put_line('The following XML schemas are not registered:');
241 
242       for r in (
243       (
244         select 'http://xmlns.oracle.com/ord/meta/ordimage' schema_url from dual
245         union
246         select 'http://xmlns.oracle.com/ord/meta/exif' schema_url from dual
247         union
248         select 'http://xmlns.oracle.com/ord/meta/iptc' schema_url from dual
249         union
250         select 'http://xmlns.oracle.com/ord/meta/xmp' schema_url from dual
251         union
252         select 'http://xmlns.oracle.com/ord/meta/dicomImage' schema_url from dual
253         union
254         select 'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0' schema_url from dual
255         union
256         select 'http://xmlns.oracle.com/ord/dicom/datatype_1_0' schema_url from dual
257         union
258         select 'http://xmlns.oracle.com/ord/dicom/mddatatype_1_0' schema_url from dual
259         union
260         select 'http://xmlns.oracle.com/ord/dicom/anonymity_1_0' schema_url from dual
261         union
262         select 'http://xmlns.oracle.com/ord/dicom/attributeTag_1_0' schema_url from dual
263         union
264         select 'http://xmlns.oracle.com/ord/dicom/constraint_1_0' schema_url from dual
265         union
266         select 'http://xmlns.oracle.com/ord/dicom/metadata_1_0' schema_url from dual
267         union
268         select 'http://xmlns.oracle.com/ord/dicom/mapping_1_0' schema_url from dual
269         union
270         select 'http://xmlns.oracle.com/ord/dicom/manifest_1_0' schema_url from dual
271         union
272         select 'http://xmlns.oracle.com/ord/dicom/preference_1_0' schema_url from dual
273         union
274         select 'http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0' schema_url from dual
275         union
276         select 'http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0' schema_url from dual
277         union
278         select 'http://xmlns.oracle.com/ord/dicom/orddicom_1_0' schema_url from dual
279         union
280         select 'http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0' schema_url from dual
281       )
282       minus
283       (
284         select schema_url from sys.all_xml_schemas
285           where owner = 'ORDSYS'
286             and schema_url in
287                 (
288                  'http://xmlns.oracle.com/ord/meta/ordimage',
289                  'http://xmlns.oracle.com/ord/meta/exif',
290                  'http://xmlns.oracle.com/ord/meta/iptc',
291                  'http://xmlns.oracle.com/ord/meta/xmp',
292                  'http://xmlns.oracle.com/ord/meta/dicomImage',
293                  'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0',
294                  'http://xmlns.oracle.com/ord/dicom/datatype_1_0',
295                  'http://xmlns.oracle.com/ord/dicom/mddatatype_1_0',
296                  'http://xmlns.oracle.com/ord/dicom/anonymity_1_0',
297                  'http://xmlns.oracle.com/ord/dicom/attributeTag_1_0',
298                  'http://xmlns.oracle.com/ord/dicom/constraint_1_0',
299                  'http://xmlns.oracle.com/ord/dicom/metadata_1_0',
300                  'http://xmlns.oracle.com/ord/dicom/mapping_1_0',
301                  'http://xmlns.oracle.com/ord/dicom/manifest_1_0',
302                  'http://xmlns.oracle.com/ord/dicom/preference_1_0',
303                  'http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0',
304                  'http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0',
305                  'http://xmlns.oracle.com/ord/dicom/orddicom_1_0',
306                  'http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0'
307               )
308       ) ) loop
309         dbms_output.put_line(r.schema_url);
310       end loop;
311 
312     end if;
313 
314   exception
315     WHEN NO_DATA_FOUND THEN
316       b_valid := FALSE;
317       dbms_output.put_line('ORDIM XML schemas are not registered.');
318 
319     WHEN OTHERS THEN
320       b_valid := FALSE;
321       err_code := SQLCODE;
322       err_msg  := SUBSTR(SQLERRM, 1 , 100);
323       DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
324   end;
325 
326   -- Now check whether default documents are inserted into the DICOM repository
327   begin
328     select count(*) into l_num_docs from orddata.orddcm_docs
329       where doc_name in
330           (
331             'ordcman.xml',
332             'ordcmcmc.xml',
333             'ordcmcmd.xml',
334             'ordcmct.xml',
335             'ordcmmp.xml',
336             'ordcmpf.xml',
337             'ordcmpv.xml',
338             'ordcmsd.xml',
339             'ordcmui.xml'
340           )
341     ;
342 
343     if ( l_num_docs != 9 ) then
344       b_valid := FALSE;
345       dbms_output.put_line('ORDIM DICOM repository has ' || l_num_docs || ' documents.');
346       dbms_output.put_line('The following default DICOM repository documents are not installed:');
347 
348       for r in (
349       (
350         select 'ordcman.xml' doc_name from dual
351         union
352         select 'ordcmcmc.xml' doc_name from dual
353         union
354         select 'ordcmcmd.xml' doc_name from dual
355         union
356         select 'ordcmct.xml' doc_name from dual
357         union
358         select 'ordcmmp.xml' doc_name from dual
359         union
360         select 'ordcmpf.xml' doc_name from dual
361         union
362         select 'ordcmpv.xml' doc_name from dual
363         union
364         select 'ordcmsd.xml' doc_name from dual
365         union
366         select 'ordcmui.xml' doc_name from dual
367       )
368       minus
369       (
370         select doc_name from orddata.orddcm_docs
371           where doc_name in
372             (
373               'ordcman.xml',
374               'ordcmcmc.xml',
375               'ordcmcmd.xml',
376               'ordcmct.xml',
377               'ordcmmp.xml',
378               'ordcmpf.xml',
379               'ordcmpv.xml',
380               'ordcmsd.xml',
381               'ordcmui.xml'
382             )
383       ) ) loop
384         dbms_output.put_line(r.doc_name);
385       end loop;
386 
387     end if;
388 
389   exception
390     WHEN NO_DATA_FOUND THEN
391       b_valid := FALSE;
392       dbms_output.put_line('ORDIM default DICOM repository documents are not installed.');
393 
394     WHEN OTHERS THEN
395       b_valid := FALSE;
396       err_code := SQLCODE;
397       err_msg  := SUBSTR(SQLERRM, 1 , 100);
398       DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
399   end;
400 
401   -- Now check whether DICOM ORDADMIN role is created
402   begin
403     SELECT count(*) INTO l_num_roles  FROM sys.user$ u
404     WHERE type# = 0 and name = 'ORDADMIN';
405 
406     if ( l_num_roles != 1 ) then
407       b_valid := FALSE;
408       dbms_output.put_line('ORDIM DICOM administrator role ORDADMIN is not created.');
409     end if;
410 
411   exception
412     when no_data_found then
413       b_valid := FALSE;
414       dbms_output.put_line('ORDIM DICOM administrator role ORDADMIN is not created.');
415 
416     when others then
417       b_valid := FALSE;
418       err_code := SQLCODE;
419       err_msg  := SUBSTR(SQLERRM, 1 , 100);
420       DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
421   end;
422 
423   -- Check whether Locator is valid if SDO is not installed or is OPTION OFF
424   -- by checking whether there are invalid objects under MDSYS
425   BEGIN
426     l_sdo_status := dbms_registry.status('SDO');
427     if (l_sdo_status is NULL or l_sdo_status = 'OPTION OFF' or
428         l_sdo_status = 'REMOVED') then
429       SELECT NULL INTO l_null  FROM sys.dba_invalid_objects
430       WHERE
431             owner = 'MDSYS'
432         AND rownum <= 1;
433 
434       -- invalid objects found
435       b_valid := FALSE;
436 
437       FOR ob IN (SELECT o.object_name, o.status, o.object_type
438                  FROM sys.dba_invalid_objects o
439           WHERE
440                 o.owner = 'MDSYS'
441             AND rownum < 20) LOOP
442         dbms_output.put_line ('Locator INVALID OBJECTS: ' || ob.object_name || ' - ' || ob.status || ' - ' || ob.object_type);
443       END LOOP;
444     end if;
445 
446   EXCEPTION
447     WHEN NO_DATA_FOUND THEN
448       -- no valid objects, don't need to do anything
449       null;
450 
451     WHEN OTHERS THEN
452       b_valid := FALSE;
453       err_code := SQLCODE;
454       err_msg  := SUBSTR(SQLERRM, 1 , 100);
455       DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
456   END;
457 
458 --
459 -- verify that data from ordsys has been moved to orddata
460 -- Note: this check is performed only if upgrading from 11.1
461 --  and the status is 'UPGRADED'
462 
463   begin
464     select prv_version into l_prevDbVer
465     from sys.registry$ where cid='ORDIM';
466 
467     l_status := sys.dbms_registry.status('ORDIM');
468 
469     if (l_prevDbVer = '11.1.0') and (l_status = 'UPGRADED') then
470     begin
471       -- Dynamic sql is used for compilation purposes because the
472       -- ordsys.orddcm_docs table will not exist for new installations
473       -- or if not upgrading from 11.1.0 database.
474       -- check for documents in ORDSYS schema
475       execute immediate 'select count(*) into l_num_docs from ordsys.orddcm_docs
476           where doc_name in
477               (
478                 ''ordcman.xml'',
479                 ''ordcmcmc.xml'',
480                 ''ordcmcmd.xml'',
481                 ''ordcmct.xml'',
482                 ''ordcmmp.xml'',
483                 ''ordcmpf.xml'',
484                 ''ordcmpv.xml'',
485                 ''ordcmsd.xml'',
486                 ''ordcmui.xml''
487               );';
488       if (l_num_docs <> 9 ) then
489         dbms_output.put_line('ORDSYS DICOM repos: '
490                               || 'expected: 9 got: '
491                               || l_num_docs || ' default docs');
492       end if;
493 
494      exception
495       WHEN NO_DATA_FOUND THEN
496         dbms_output.put_line('ORDSYS DICOM repository documents are not installed.');
497 
498       WHEN OTHERS THEN
499         err_code := SQLCODE;
500         err_msg  := SUBSTR(SQLERRM, 1 , 100);
501         DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
502      end;
503 
504 
505      BEGIN
506        --
507        -- Expect no differences between the ORDSYS and ORDDATA repos
508        --
509        l_dsql_block := 'BEGIN
510              for cur in (select doc_name from ordsys.orddcm_docs
511                   minus select doc_name from orddata.orddcm_docs) loop
512                dbms_output.put_line(''ORDSYS doc: ''
513                             || cur.doc_name || '' not in ORDDATA'');
514              end loop;
515            end;';
516 
517        execute immediate l_dsql_block;
518 
519        l_dsql_block := 'BEGIN
520             for cur in ( select doc_name from orddata.orddcm_docs
521                     minus select doc_name from ordsys.orddcm_docs) loop
522                dbms_output.put_line(''ORDData doc: ''
523                               || cur.doc_name || '' not in ORDSYS'');
524             end loop;
525           END;';
526 
527        execute immediate l_dsql_block;
528 
529 
530      exception
531 
532       WHEN OTHERS THEN
533         b_valid := FALSE;
534         err_code := SQLCODE;
535         err_msg  := SUBSTR(SQLERRM, 1 , 100);
536         DBMS_OUTPUT.put_line('In validate_ordim OTHER EXCEPTION happens: Error code ' || err_code || ': ' || err_msg);
537     END;
538    end if;
539 
540   END;
541 
542   --
543   -- Finally, if in upgrade mode, check if there are errors in registry$error
544   --
545   b_upgradeMode := sys.dbms_registry.is_in_upgrade_mode();
546   if b_upgradeMode then
547     l_num_errors := sys.dbms_registry.count_errors_in_registry('ORDIM');
548     if (l_num_errors > 0) then
549       dbms_output.put_line
550        ('There were ' || l_num_errors ||
551         ' entries in sys.registry$error for ORDIM');
552       b_valid := FALSE;
553     end if;
554   end if;
555 
556   --
557   -- Set the registry status at the end
558   --
559   if NOT b_valid then
560     sys.dbms_registry.invalid('ORDIM');
561   else
562     sys.dbms_registry.valid('ORDIM');
563   end if;
564 
565 END;