Install Oracle Database Release 11.1 with Oracle Multimedia.

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

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;

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

connect PM/PM@<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.

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

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;

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

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;

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;

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;

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;

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;

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;

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;

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

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.

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

execute generate_anon(1);

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

@select_anonimage

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;

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;

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;

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

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

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

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;

Log in to SQL*Plus as the SYS user:

sqlplus sys/<syspassword>@<Sid> as sysdba

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;

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

exit 

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.