Managing DICOM Format Data in Oracle Database 11g

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

Approximately 30 minutes

This tutorial covers the following topics:

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.

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.

Before you perform this tutorial, you should:

1.

Install Oracle Database Release 11.1 with Oracle Multimedia.

2.

Install Oracle Database 11g products (from the Companion CD).

3.

Back to Topic List

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

1.

You need to make sure that the PM user is unlocked and that the user has the create any directory privilege. Open a SQL*Plus session, and execute the following commands:

sqlplus sys/<syspassword>@<sid> as sysdba
alter user PM identified by PM account unlock;
grant create any directory to PM;


2.

Now you can create the directory object as the PM user. From your SQL*Plus session, execute the following commands:

connect PM/[email protected]<Sid>
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. This could be something like /home/oracle/dicom. Note: if this directory doesn't exist, you need to create it.

 

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, execute the following commands:

@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);

 

Back to Topic List

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 data in file name 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

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 data 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 binary data 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.

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 binary data 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 binary data 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 binary data 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 binary data 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 binary data 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

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:

select id, 
extractValue(t.dicom.metadata,
'/DICOM_OBJECT/*[@name="Patient''s Name"]/VALUE',
'xmlns=http://xmlns.oracle.com/ord/dicom/metadata_1_0') as "PATIENT_NAME",
extractValue(t.dicom.metadata,
'/DICOM_OBJECT/*[@name="Patient ID"]',
'xmlns=http://xmlns.oracle.com/ord/dicom/metadata_1_0') as "PATIENT_ID",
extractValue(t.dicom.metadata,
'/DICOM_OBJECT/*[@name="Modality"]',
'xmlns=http://xmlns.oracle.com/ord/dicom/metadata_1_0') as "MODALITY"
from medical_image_table t;

 

Back to Topic List

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’ing 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');

 

Back to Topic List

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 binary data and the associated ORDDicom object metadata. An XML anonymity definition document specifies which DICOM objects should be removed or replaced and what action should be taken to anonymize each attribute. A default anonymity definition document, ordcman.xml, is loaded during installation. It is beyond the scope of this tutorial to describe customizing an anonymity definition document. For the purposes of this tutorial, the default anonymity definition 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

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

The following example 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.

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

@check_object_conform

The check_object_conform.sql code is as follows:

-- Set Data Model Repository
execute ordsys.ord_dicom.setDataModel();
select id, t.dicom.isconformanceValid('OracleOrdObject') as conformant
from medical_image_table t;

 

2.

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

This topic shows how to export DICOM data from the database to the file system on the database server. Exporting DICOM data 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 where you wish to write your output DICOM file. Perform the following steps:

1.

Create a procedure that will export the DICOM data 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

Perform the following steps to clean up your environment:

1.

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

@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 table medical_image_table;

 

2.

Log in to SQL*Plus as the SYS user:

sqlplus sys/<syspassword>@<Sid> 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;
revoke create any directory from PM;

 

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

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.