Managing DICOM Format Data in Oracle Database 11g

Purpose

This tutorial describes how to upload, store, manipulate, and export medical image data inside the Oracle Database using Oracle Multimedia.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating a Directory Object for Import and Export
 Creating a Table with an ORDDicom Column
 Importing Medical Images
 Retrieving Object Attributes
 Retrieving DICOM Metadata
 Creating Thumbnails and Changing Formats
 Making Anonymous Copies of DICOM Objects
 Checking the Conformance of DICOM Objects
 Exporting Images
 Cleaning Up
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Oracle Multimedia DICOM enables Oracle Database to store, manage, and retrieve DICOM format medical images and other objects integrated with other enterprise information. Oracle Multimedia DICOM extends Oracle Database reliability, availability, and data management to media objects in medical applications.

Oracle Multimedia DICOM supports Digital Imaging and Communications in Medicine (DICOM), the standard for medical images.

This tutorial provides simple PL/SQL examples that upload, store, manipulate, and export medical image data inside a database using Oracle Multimedia.

Oracle Multimedia stores DICOM format data in database tables with columns of the ORDDicom type. An example of an ORDDicom object in a database table is illustrated in the following diagram.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g Release 11.1 or later with Oracle Multimedia.

2.

Download and unzip the dicom.zip file into a working directory.

3.

Select a user under which to run this tutorial. This user must have as its default tablespace, an Automatic Segment Space Management (ASSM) tablespace.

This tutorial uses the PM user that is created when you install the sample schemas.

Back to Topic List

Creating a Directory Object for Import and Export

To avoid unnecessary database connects, create the directory object and grants required for import and export activities. Perform the following steps:

1.

Create the directory object while connected as sysdba. Open a SQL*Plus session, and execute the following commands:

                               
                                 
sqlplus sys@<sid> as sysdba
Enter password: <password>

create or replace directory IMAGEDIR as '<file_directory>';
                              
                            

Here, <file_directory> is the location where all the scripts/files you need for this tutorial are located. In this example, we use /home/oracle/wkdir/dicom/files.

Note: if this directory does not exist in the operating system, you need to create it.



2.

Grant read and write access on the directory to the tutorial user. In this tutorial, we use the user PM. If you are using a different user, change the value of PM to your user name.

From your SQL*Plus session, execute the following commands:

                               
grant read, write on directory IMAGEDIR to pm;
                            

 

Back to Topic List

Creating a Table with an ORDDicom Column

This tutorial uses a simple table with four columns: an integer identifier ( id ), an ORDSYS.ORDDicom object ( dicom ), an ORDSYS.ORDImage object ( imageThumb ), and another ORDSYS.ORDDicom object ( anonDicom ). Note that all Oracle-provided multimedia objects and procedures are defined in the ORDSYS schema. Perform the following steps:

1.

From your SQL*Plus session, connect as your tutorial user. In this example, we use the PM user.

Execute the following commands:

                               
                                 
                                   
connect
                                
                              
                              
                                 
                                   
pm
Enter pass
                                
                              
                              
                                 
                                   
word:
                                
                              
                              
                                 
                                   
<password>

@create_dicom_table
                                
                              
                            

The create_dicom_table.sql code is as follows:

set echo on
                               
drop table medical_image_table;
create table medical_image_table
(id integer primary key,
dicom ordsys.orddicom,
imageThumb ordsys.ordimage,
anonDicom ordsys.orddicom)
--
-- metadata extraction expands the ORDDicom object
pctfree 60
--
-- lob storage
lob(dicom.source.localdata) store as SecureFile
(nocache filesystem_like_logging),
lob(imageThumb.source.localdata) store as SecureFile
(nocache filesystem_like_logging),
lob(anonDicom.source.localdata) store as SecureFile
(nocache filesystem_like_logging),
-- disable in row storage for the extension
-- so that it does not consume page space
-- it is usually < 4k in size
lob(dicom.extension) store as SecureFile
( nocache disable storage in row ),
lob(anonDicom.extension) store as SecureFile
( nocache disable storage in row ),
-- store the metadata as a CLOB,
-- disable storage in row
xmltype dicom.metadata store as SecureFile clob
( nocache disable storage in row )
xmltype anonDicom.metadata store as SecureFile clob
( nocache disable storage in row ) ;

Note: If you use a user with a non-ASSM tablespace, you will see this error on the create table:
>>>> ERROR at line 1:
>>>> ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "SYSTEM"

Back to Topic List

Importing Medical Images

This topic describes the loading of medical images from the database file system into the newly created table named medical_image_table. Note that in most cases you will want to load your data using SQL*Loader rather than the ORDDicom import method that this example shows.

Create a PL/SQL procedure image_import() that inserts a new row into medical_image_table, imports the DICOM content from the filename parameter into the newly created ORDDICOM object, and then extracts DICOM attributes into the metadata attribute based on the default mapping document and into the UID attributes of the ORDDICOM object. Note that the default mapping document, ordcmmp.xml, is loaded during installation. It is possible to create a customized mapping document and to extract attributes into a separate XML document, but that topic is beyond the scope of this tutorial.

Perform the following steps:

1.

From your SQL*Plus session, enter the following commands:

                               
                                 
                                   
@create_import_procedure
                                
                                
                              
                            

The create_import_procedure.sql code is as follows:

-- Set Data Model Repository
execute ordsys.ord_dicom.setDataModel();


create or replace procedure image_import
  (dest_id number, filename varchar2) 
  is
    dcm ordsys.orddicom;
  begin
    delete from medical_image_table where id = dest_id;
    insert into medical_image_table (id, dicom, imageThumb, anonDicom) 
      values (dest_id, ordsys.orddicom('file', 'IMAGEDIR', filename, 0), 
              ordsys.ordimage.init(), ordsys.orddicom()) 
      returning dicom into dcm;
    dcm.import(1);

    update medical_image_table set dicom=dcm where id=dest_id;  
  
    commit;
end;
/ 
show errors;

 

2.

Now you can execute the newly created procedure to import the sample DICOM file. From your SQL*Plus session, enter the following commands:

                               
                                 
                                   
execute image_import(1,'179.dcm')
                                
                              
                            

 

Back to Topic List

Retrieving Object Attributes

When you called the import method in the previous topic, you passed the parameter 1 specifying that you should invoke the setProperties() method on import.  setProperties() is the method that tells Oracle Multimedia to parse the DICOM content and extract DICOM metadata into ORDDicom object attributes.  Certain frequently accessed attributes such as the ones you are querying here ( SOP_INSTANCE_UID, SOP_CLASS_UID, and so on) are stored in specific ORDDicom object attributes.  In addition, all DICOM metadata contained in the DICOM content is extracted into an XML document that adheres to your default metadata mapping document.  The resulting XML metadata document is stored in the ORDDicom metadata attribute and is available for indexing and querying.

 Retrieve SOP_INSTANCE_UID
 Retrieve SOP_CLASS_UID
 Retrieve STUDY_INSTANCE_UID
 Retrieve SERIES_INSTANCE_UID
 Retrieve content length (number of bytes of DICOM content)

Back to Topic List

Retrieve SOP_INSTANCE_UID

1.

This query retrieves the Service-Object Pair Instance UID from the ORDDicom object attribute. This data was extracted from the DICOM content when setProperties was called.  From your SQL*Plus session, execute the following script:

                               
                                 
                                   
@sop_instance_uid
                                
                                
                              
                            

The sop_instance_uid.sql code is as follows:

select id, 
       t.dicom.getSOPInstanceUID() as SOP_Instance_UID 
from medical_image_table t;        


                               
                                 
                                   
                                      
                                                                                                
                            

Back to Topic

Retrieve SOP_CLASS_UID

1.

This query retrieves the Service-Object Pair Class UID from the ORDDicom object attribute. This data was extracted from the DICOM content when setProperties was called. From your SQL*Plus session, execute the following script:

                               
                                 
                                   
@sop_class_uid
                                
                                
                              
                            

The sop_class_uid.sql code is as follows:

select id, 
       t.dicom.getSOPClassUID() as SOP_Class_UID
from medical_image_table t;

Back to Topic

Retrieve STUDY_INSTANCE_UID

1.

This query retrieves the Study Instance UID from the ORDDicom object attribute.  This data was extracted from the DICOM content when setProperties was called. From your SQL*Plus session, execute the following script:

                               
                                 
                                   
@study_instance_uid
                                
                                
                                   
                                                              
                            

The study_instance_uid.sql code is as follows:

select id, 
       t.dicom.getStudyInstanceUID() as Study_Instance_UID 
from medical_image_table t;

Back to Topic

Retrieve SERIES_INSTANCE_UID

1.

This query retrieves the Series Instance UID from the ORDDicom object attribute. This data was extracted from the DICOM content when setProperties was called. From your SQL*Plus session, execute the following script:

                               
                                 
                                   
@series_instance_uid
                                
                                
                              
                            

The series_instance_uid.sql code is as follows:

select id, 
       t.dicom.getSeriesInstanceUID() as Series_Instance_UID
from medical_image_table t;

Back to Topic

Retrieve Content Length (Number of Bytes of DICOM Content)

1.

This query retrieves the length of the DICOM content stored in the source attribute. From your SQL*Plus session, execute the following script:

                               
                                 
                                   
@content_length
                                
                                
                              
                            

The content_length.sql code is as follows:

select id,
       t.dicom.getcontentlength() as content_Length 
from medical_image_table t;

Back to Topic

Retrieving DICOM Metadata

Patient Name, Patient ID, and Modality are some of the many DICOM standard attributes that were embedded in the DICOM image and extracted into an XML document when setProperties was called during import. Perform the following steps:

1.

This query shows how you can extract information from the extracted XML metadata document. From your SQL*Plus session, execute the following script:

                               
                                 
                                   
@patient_info
                                
                                
                              
                            

The patient_info.sql code is as follows:

column id format 99;
column PATIENT_NAME format A30;
column PATIENT_ID format A10;
column MODALITY format A10;


select m.id, t.PATIENT_NAME, t.PATIENT_ID, t.MODALITY
from medical_image_table m,
  xmltable
    (xmlnamespaces
      (default 'http://xmlns.oracle.com/ord/dicom/metadata_1_0'),
               '/DICOM_OBJECT' 
       passing m.dicom.metadata
       columns
         patient_name varchar2(100) 
           path './*[@name="Patient''''s Name"]/VALUE',
        patient_id varchar2(100) 
           path './*[@name="Patient ID"]',
        modality varchar2(100) 
           path './*[@name="Modality"]'
        ) t ;

                               
                                 
                                   
                                      
                                                                                                
                            

Back to Topic List

Creating Thumbnails and Changing Formats

This topic illustrates some image processing operations that can be invoked within the database. To create a JPEG thumbnail image from a DICOM image, a new ORDImage object is generated from the ORDDicom object and then processed. To do this, you describe the desired properties of the new ORDImage object. For example, the following description generates a JPEG thumbnail image of size 75x100 pixels: ‘ fileformat=jfif fixedscale=75 100’.

The following example defines generate_thumb() that populates the imageThumb column of medical_image_table with identifier source_id and generates an ORDImage in the column by executing processCopy() on the ORDDicom in the source row.

Perform the following steps:

1.

To create the generate_thumb procedure, execute the following script from your SQL*Plus session:

                               
                                 
                                   
@create_thumbnail_procedure
                                
                                
                              
                            

The create_thumbnail_procedure.sql code is as follows:

-- Set Data Model Repository
execute ordsys.ord_dicom.setDataModel();
         
create or replace procedure generate_thumb(source_id number, verb varchar2) 
is
   dcmSrc    ordsys.orddicom;
   imgDst    ordsys.ordimage;
begin
   select dicom, imageThumb 
     into dcmSrc, imgDst 
     from medical_image_table 
     where id = source_id for update;
   dcmSrc.processCopy(verb, imgDst);
         
   update medical_image_table 
   set imageThumb = imgDst 
   where id = source_id;
   commit;
end;
/
show errors;      

 

2.

From your SQL*Plus session, execute the following script:

                               
                                 
                                   
@create_thumbnail_image
                                
                                
                              
                            

The create_thumbnail_image.sql code is as follows:

-- Create a JPEG thumbnail image for our test DICOM
execute generate_thumb(1, 'fileformat=jfif fixedscale=75 100');      

 

3.

Invoke the select_thumbnail.sql script and observe that there is a thumbnail image of size 75 X 100.

From your SQL*Plus session, execute the following script:

                               
                                 
                                   
@select_thumbnail
                                
                              
                            

The select_thumbnail.sql code is as follows:

column t.imageThumb.getfileformat() format A20;
select id, t.imageThumb.getWidth(), t.imageThumb.getHeight(), 
       t.imageThumb.getFileFormat() 
from medical_image_table t;


                               
                                 
                                   
                                     
                                        

                                                                                                                                    
                            

Back to Topic List

Making Anonymous Copies of DICOM Objects

This topic shows how to protect patient privacy by making DICOM objects anonymous. To make DICOM objects anonymous, create a new DICOM object with certain user-specifiable DICOM attributes either removed or overwritten in the new DICOM content and the associated ORDDicom object metadata. An XML anonymity document specifies which DICOM attributes should be removed or replaced and what action should be taken to anonymize each attribute. A default anonymity document, ordcman.xml, is loaded during installation. It is beyond the scope of this tutorial to describe customizing an anonymity document. For the purposes of this tutorial, the default anonymity document is used.

The following example defines generate_anon() that populates the anonDicom column of medical_image_table with identifier source_id and generates an ORDDicom in the column by invoking makeAnonymous() on the DICOM in the source row.

Perform the following steps:

1.

To create the generate_anon procedure, execute the following script from your SQL*Plus session:

                               
                                 
                                   
@create_anonimage_proc
                                
                                
                              
                            

The create_anonimage_proc.sql code is as follows:

-- Set Data Model Repository
execute ordsys.ord_dicom.setDataModel();

create or replace procedure generate_anon(source_id number) is
   dcmSrc    ordsys.orddicom;
   anonDst   ordsys.orddicom;
   dest_sop_inst_uid varchar2(128) := '1.2.3';
         
begin
  select dicom, anonDicom into dcmSrc, anonDst from medical_image_table 
    where id = source_id for update;
  dcmSrc.makeAnonymous(dest_sop_inst_uid, anonDst);
  update medical_image_table set anonDicom = anonDst where id = source_id;
  commit;
end;
/
show errors;

You should replace the temporary UID for the dest_sop_instance_uid variable in generate_anon with a globally-unique UID. Note, the procedure will run if you don’t do this, but you should then destroy the resulting anonymous DICOM image.

 

2.

Generate an anonymous copy of your test DICOM. Execute the following command from your SQL*Plus session:

                               
                                 
                                   
execute generate_anon(1);
                                
                              
                            

 

3.

You can now review the results. Execute the following script from your SQL*Plus session:

                               
                                 
                                   
@select_anonimage
                                
                              
                            

 

Back to Topic List

Checking the Conformance of DICOM Objects

The sample code in this topic shows how to check the conformance of DICOM content against a set of user-specified conformance rules. Conformance rules are specified in one or more constraint documents, which are XML documents that specify attribute relationships and semantic constraints that cannot be expressed by the DICOM metadata schema. A default constraint document, ordcmct.xml, is loaded during installation. It is beyond the scope of this tutorial to describe customizing constraint documents. For the purposes of this tutorial, the default constraint document is used.

The following example defines check_conform() that checks the conformance of the DICOM column of medical_image_table with identifier source_id by invoking isConformanceValid() on the DICOM in the source row.

Perform the following steps:

1.

To create the check_conform procedure, execute the following script from your SQL*Plus session:

                               
                                 
                                   
@create_checkconform_proc
                                
                                
                              
                            

The create_checkconform_proc.sql code is as follows:

-- Set Data Model Repository
execute ordsys.ord_dicom.setDataModel();
create or replace procedure check_conform(source_id number) is
    dcmSrc    ordsys.orddicom;
begin
  select dicom into dcmSrc from medical_image_table 
         where id = source_id;
  dbms_output.put_line('isconformanceValid(OracleOrdObject): ' ||
    dcmSrc.isConformanceValid('OracleOrdObject'));
end;
/
show errors;

 

2.

Check to see if the DICOM image conforms with the constraint rules. Execute the following commands from your SQL*Plus session:

                               
set serverout on
execute check_conform(1);
                            

 

3.

If the DICOM image does not conform to the constraint definitions, a message or messages are inserted into a table viewable by querying the ORDDCM_CONFORMANCE_VLD_MSGS view. This view lists the constraint messages generated during constraint validation. Execute the following script from your SQL*Plus session:

                               
                                 
                                   
@review_conform_msgs
                                
                              
                            

The review_conform_msgs.sql code is as follows:

describe orddcm_conformance_vld_msgs;
select * from orddcm_conformance_vld_msgs;       

 

Back to Topic List

Exporting Images

This topic shows how to export DICOM content from the database to the file system on the database server. Exporting DICOM content from the database with Oracle Multimedia’s export() method requires that the database writes to the database server’s file system. Writing to the file system requires granting write permission to your user (the PM user) on the directory object (see the section on Creating a Directory Object for Import and Export) where you wish to write your output DICOM file. Perform the following steps:

1.

Create a procedure that will export the DICOM content to a file in the IMAGEDIR directory. Execute the following script from your SQL*Plus session:

                               
                                 
                                   
@create_export_proc
                                
                                
                              
                            

The create_export_proc.sql code is as follows:

create or replace procedure dicom_export 
(source_id number, filename varchar2) 
as
  dcmSrc ordsys.orddicom;
begin
   select dicom into dcmSrc from medical_image_table where id = source_id;
   dcmSrc.export('FILE', 'IMAGEDIR', filename);
end;
/
show errors;

 

2.

Now you can execute the procedure. Execute the following command from your SQL*Plus session:

                               
                                 
                                   
execute dicom_export(1, 'dicom_orig.dcm');
                                
                              
                            

 

3.

To see the file that was created, open another terminal window and execute the following command from the IMAGEDIR directory.

                               
                                 
                                   
ls -al dicom_orig.dcm
                                
                              
                            

 

Back to Topic List

Cleaning Up

Perform the following steps to clean up your environment:

1.

Execute the following script from your SQL*Plus session as the user under which you ran the tutorial:

                               
                                 
                                   
@cleanup01
                                
                                
                              
                            

The cleanup01.sql code is as follows:

drop procedure image_import;
                              
drop procedure generate_thumb;
drop procedure generate_anon;
drop procedure dicom_export;
drop procedure check_conform;
drop table medical_image_table;
2.

Log in to SQL*Plus as the SYS user:

                               
sqlplus sys as sysdba
                            

 

3.

Execute the following script from your SQL*Plus session as the SYS user:

                               
                                 
                                   
@cleanup02
                                
                                
                              
                            

The cleanup02.sql code is as follows:

drop directory imagedir;

4.

To close your SQL*Plus session, execute the following command:

exit  

 

5.

In your SQL*Plus session, navigate to your working directory and delete the export file, dicom_orig.dcm, that was created in imagedir.

rm dicom_orig.dcm

If prompted, confirm your deletion. 

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Create a directory object for import and export
 Create a table with an ORDDicom column
 Import medical images
 Select and view DICOM attributes
 Create a thumbnail and change formats
 Make anonymous copies of DICOM objects
 Check the conformance of DICOM objects
 Export images

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve
Untitled Document