[Home] [Help]
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
394 WHEN OTHERS THEN
391 b_valid := FALSE;
392 dbms_output.put_line('ORDIM default DICOM repository documents are not installed.');
393
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;