Managing DICOM Format Data in Oracle Database 12<em>c</em>

Before You Begin

Purpose

In this tutorial, you learn how to upload, store, manipulate, and export medical image data inside the Oracle Database using Oracle Multimedia.

Time to Complete

Approximately 30 minutes

Background

Oracle Multimedia DICOM enables the 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:

What Do You Need?

Install Oracle Database 12c Release 1 (12.1)
Download and unzip the dicom.zip file into a working directory.
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.

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:

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>
```
```
SQL> 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.
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:
```
SQL> grant read, write on directory IMAGEDIR to pm;

Grant succeeded.

SQL>
```

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:

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

Execute the following commands:

SQL> connect pm
Enter password: <password>
Connected.

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

SQL>@create_dicom_table
SQL> drop table medical_image_table;
drop table medical_image_table
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> create table medical_image_table
  2  	  (id	       integer primary key,
  3  	   dicom       ordsys.orddicom,
  4  	   imageThumb  ordsys.ordimage,
  5  	   anonDicom   ordsys.orddicom)
  6  	   --
  7  	   -- metadata extraction expands the ORDDicom object
  8  	   pctfree 60
  9  	   --
 10  	   -- lob storage
 11  	   lob(dicom.source.localdata) store as SecureFile
 12  	      (nocache filesystem_like_logging),
 13  	   lob(imageThumb.source.localdata) store as SecureFile
 14  	      (nocache filesystem_like_logging),
 15  	   lob(anonDicom.source.localdata) store as SecureFile
 16  	      (nocache filesystem_like_logging),
 17  	   -- disable in row storage for the extension
 18  	   -- so that it does not consume page space
 19  	   -- it is usually < 4k in size
 20  	   lob(dicom.extension) store as SecureFile
 21  	      ( nocache disable storage in row ),
 22  	   lob(anonDicom.extension) store as SecureFile
 23  	      ( nocache disable storage in row ),
 24  	   -- store the metadata as a CLOB,
 25  	   -- disable storage in row
 26  	   xmltype dicom.metadata store as SecureFile clob
 27  	      ( nocache disable storage in row )
 28  	   xmltype anonDicom.metadata store as SecureFile clob
 29  	      ( nocache disable storage in row )
 30  ;

Table created.

SQL>

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"

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:

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;

SQL> @create_import_procedure
SQL> -- Set Data Model Repository
SQL> execute ordsys.ord_dicom.setDataModel();

PL/SQL procedure successfully completed.

SQL> 
SQL> create or replace procedure image_import(dest_id number, filename varchar2) is
  2    dcm ordsys.orddicom;
  3  begin
  4  	delete from medical_image_table where id = dest_id;
  5  	insert into medical_image_table (id, dicom, imageThumb, anonDicom)
  6  	values (dest_id, ordsys.orddicom('file', 'IMAGEDIR', filename, 0),
  7  		ordsys.ordimage.init(), ordsys.orddicom())
  8  	returning dicom into dcm;
  9  	dcm.import(1);
 10  
 11  	update medical_image_table set dicom=dcm where id=dest_id;
 12  	commit;
 13  end;
 14  /

Procedure created.

SQL> show errors;
No errors.
SQL>

Now you can execute the newly created procedure to import the sample DICOM file. From your SQL*Plus session, enter the following commands:
```
SQL> execute image_import(1,'179.dcm')

PL/SQL procedure successfully completed.

SQL>
```

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`

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;
```
```
SQL> @sop_instance_uid
SQL> select id,
  2  	    t.dicom.getSOPInstanceUID() as SOP_Instance_UID
  3  from medical_image_table t;

	ID
----------
SOP_INSTANCE_UID
--------------------------------------------------------------------------------
	 1
1.2.392.200036.9116.2.2.2.1762676206.1077529882.102147


SQL>
```

Retrieve `SOP_CLASS_UID`

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;
```
```
SQL> @sop_class_uid
SQL> select id,
  2  	    t.dicom.getSOPClassUID() as SOP_Class_UID
  3  from medical_image_table t;

	ID
----------
SOP_CLASS_UID
--------------------------------------------------------------------------------
	 1
1.2.840.10008.5.1.4.1.1.2


SQL>
```

Retrieve `STUDY_INSTANCE_UID`

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;
```
```
SQL> @study_instance_uid
SQL> select id,
  2  	    t.dicom.getStudyInstanceUID() as Study_Instance_UID
  3  from medical_image_table t;

	ID
----------
STUDY_INSTANCE_UID
--------------------------------------------------------------------------------
	 1
1.2.392.200036.9116.2.2.2.1762929498.1080638122.365416


SQL>
```

Retrieve `SERIES_INSTANCE_UID`

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;

SQL> @series_instance_uid
SQL> select id,
  2  	    t.dicom.getSeriesInstanceUID() as Series_Instance_UID
  3  from medical_image_table t;

	ID
----------
SERIES_INSTANCE_UID
--------------------------------------------------------------------------------
	 1
1.2.392.200036.9116.2.2.2.1762929498.1080638122.503288


SQL>

Retrieve Content Length (Number of Bytes of DICOM Content)

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;
```
```
SQL> @content_length
SQL> select id,
  2  	    t.dicom.getcontentlength() as content_Length
  3  from medical_image_table t;

	ID CONTENT_LENGTH
---------- --------------
	 1	   525974

SQL>
```

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:

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 ;

SQL> @patient_info
SQL> column id format 99;
SQL> column PATIENT_NAME format A30;
SQL> column PATIENT_ID format A10;
SQL> column MODALITY format A10;
SQL> 
SQL> select m.id, t.PATIENT_NAME, t.PATIENT_ID, t.MODALITY
  2  from medical_image_table m,
  3    xmltable
  4  	 (xmlnamespaces
  5  	   (default 'http://xmlns.oracle.com/ord/dicom/metadata_1_0'),
  6  		    '/DICOM_OBJECT'
  7  	    passing m.dicom.metadata
  8  	    columns
  9  	      patient_name varchar2(100)
 10  		path './*[@name="Patient''''s Name"]/VALUE',
 11  	     patient_id varchar2(100)
 12  		path './*[@name="Patient ID"]',
 13  	     modality varchar2(100)
 14  		path './*[@name="Modality"]'
 15  	     ) t ;

 ID PATIENT_NAME		   PATIENT_ID MODALITY
--- ------------------------------ ---------- ----------
  1 CANCIO   2HR A-02-013	   ISRSCT610b CT

SQL>

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:

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;

SQL> @create_thumbnail_procedure
SQL> -- Set Data Model Repository
SQL> execute ordsys.ord_dicom.setDataModel();

PL/SQL procedure successfully completed.

SQL> create or replace procedure generate_thumb(source_id number, verb varchar2) is
  2  	 dcmSrc    ordsys.orddicom;
  3  	 imgDst    ordsys.ordimage;
  4  begin
  5    select dicom, imageThumb into dcmSrc, imgDst from medical_image_table
  6  	      where id = source_id for update;
  7    dcmSrc.processCopy(verb, imgDst);
  8    update medical_image_table set imageThumb = imgDst where id = source_id;
  9    commit;
 10  end;
 11  /

Procedure created.

SQL> show errors;
No errors.
SQL>

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

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

PL/SQL procedure successfully completed.

SQL>

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;

SQL> @select_thumbnail
SQL> column t.imageThumb.getfileformat() format A20;
SQL> select id, t.imageThumb.getWidth(), t.imageThumb.getHeight(),
  2  	    t.imageThumb.getFileFormat()
  3  from medical_image_table t;

 ID T.IMAGETHUMB.GETWIDTH() T.IMAGETHUMB.GETHEIGHT() T.IMAGETHUMB.GETFILE
--- ----------------------- ------------------------ --------------------
  1			 75			 100 JFIF

SQL>

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:

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;

SQL> @create_anonimage_proc
SQL> -- Set Data Model Repository
SQL> execute ordsys.ord_dicom.setDataModel();

PL/SQL procedure successfully completed.

SQL> create or replace procedure generate_anon(source_id number) is
  2  	 dcmSrc    ordsys.orddicom;
  3  	 anonDst   ordsys.orddicom;
  4  	 dest_sop_inst_uid varchar2(128) := '1.2.3';
  5  begin
  6    select dicom, anonDicom into dcmSrc, anonDst from medical_image_table
  7  	      where id = source_id for update;
  8    dcmSrc.makeAnonymous(dest_sop_inst_uid, anonDst);
  9    update medical_image_table set anonDicom = anonDst where id = source_id;
 10    commit;
 11  end;
 12  /

Procedure created.

SQL> show errors;
No errors.
SQL>

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.

Generate an anonymous copy of your test DICOM. Execute the following command from your SQL*Plus session:
```
SQL> execute generate_anon(1);

PL/SQL procedure successfully completed.

SQL>
```

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

@select_anonimage

The select_anonimage.sql code is as follows:

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

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

SQL> @select_anonimage
SQL> column id format 99;
SQL> column PATIENT_NAME format A30;
SQL> column PATIENT_ID format A10;
SQL> 
SQL> select m.id, t.PATIENT_NAME, t.PATIENT_ID
  2  from medical_image_table m,
  3    xmltable
  4  	 (xmlnamespaces
  5  	    (default 'http://xmlns.oracle.com/ord/dicom/metadata_1_0'),
  6  		     '/DICOM_OBJECT'
  7  	     passing m.anondicom.metadata
  8  	     columns
  9  	       patient_name varchar2(100)
 10  		 path './*[@name="Patient''''s Name"]/VALUE',
 11  	       patient_id   varchar2(100)
 12  		 path './*[@name="Patient ID"]'
 13  	  ) t ;

 ID PATIENT_NAME		   PATIENT_ID
--- ------------------------------ ----------
  1 anonymous			   anonymous

SQL>

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:

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;

SQL> @create_checkconform_proc
SQL> -- Set Data Model Repository
SQL> execute ordsys.ord_dicom.setDataModel();

PL/SQL procedure successfully completed.

SQL> create or replace procedure check_conform(source_id number) is
  2  	 dcmSrc    ordsys.orddicom;
  3  begin
  4    select dicom into dcmSrc from medical_image_table
  5  	      where id = source_id;
  6    dbms_output.put_line('isconformanceValid(OracleOrdObject): ' ||
  7  	 dcmSrc.isConformanceValid('OracleOrdObject'));
  8  end;
  9  /

Procedure created.

SQL> show errors;
No errors.
SQL>

Check to see if the DICOM image conforms with the constraint rules. Execute the following commands from your SQL*Plus session:
```
SQL> set serverout on
SQL> execute check_conform(1);
isconformanceValid(OracleOrdObject): 1

PL/SQL procedure successfully completed.

SQL>
```
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;
```
```
SQL> @review_conform_msgs
SQL> describe orddcm_conformance_vld_msgs;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOP_INSTANCE_UID				    VARCHAR2(128 CHAR)
 RULE_NAME				   NOT NULL VARCHAR2(64 CHAR)
 MESSAGE					    VARCHAR2(1999 CHAR)
 MSG_TYPE				   NOT NULL VARCHAR2(20 CHAR)
 MSG_TIME				   NOT NULL TIMESTAMP(6)

SQL> select * from orddcm_conformance_vld_msgs;

no rows selected

SQL>
```

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:

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;

SQL> @create_export_proc
SQL> create or replace procedure dicom_export (source_id number, filename varchar2) as
  2    dcmSrc ordsys.orddicom;
  3  begin
  4    select dicom into dcmSrc from medical_image_table where id = source_id;
  5    dcmSrc.export('FILE', 'IMAGEDIR', filename);
  6  end;
  7  /

Procedure created.

SQL> show errors;
No errors.
SQL>

Now you can execute the procedure. Execute the following command from your SQL*Plus session:
```
SQL> execute dicom_export(1, 'dicom_orig.dcm');

PL/SQL procedure successfully completed.

SQL>
```
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
-rw-r--r--. 1 oracle oinstall 525974 Jul 19 06:01 dicom_orig.dcm
$
```

Cleaning Up

Perform the following steps to clean up your environment:

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;

SQL> @cleanup01
SQL> drop procedure image_import;

Procedure dropped.

SQL> drop procedure generate_thumb;

Procedure dropped.

SQL> drop procedure generate_anon;

Procedure dropped.

SQL> drop procedure dicom_export;

Procedure dropped.

SQL> drop procedure check_conform;

Procedure dropped.

SQL> drop table     medical_image_table;

Table dropped.

SQL>

$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 06:09:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

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

@cleanup02

The cleanup02.sql code is as follows:

drop directory imagedir;
```
SQL> @cleanup02

Directory dropped.

SQL>
```

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

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$

In your SQL*Plus session, navigate to your working directory and delete the export file, dicom_orig.dcm, that was created in imagedir.
```
$ cd /home/oracle/wkdir/dicom/files/
$ rm dicom_orig.dcm
```

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

Want to Learn More?

For documentation on Oracle Database 12c Release 1 (12.1), visit the Oracle Database Documentation Library.
To learn more about DICOM, refer Multimedia DICOM Developer's Guide.
To learn more about Oracle WebLogic Server, refer to additional OBEs in the Oracle Learning Library

Managing DICOM Format Data in Oracle Database 12c

Options

Before You Begin

Purpose

Time to Complete

Background

What Do You Need?

Creating a Directory Object for Import and Export

Creating a Table with an ORDDicom Column

Importing Medical Images

Retrieving Object Attributes

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)

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

Want to Learn More?

Retrieve `SOP_INSTANCE_UID`

Retrieve `SOP_CLASS_UID`

Retrieve `STUDY_INSTANCE_UID`

Retrieve `SERIES_INSTANCE_UID`