Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Dependencies
conn sys@pdbdev as sysdba
SELECT object_type, COUNT(*)
FROM cdb_objects_ae
WHERE UPPER(object_name) LIKE '%DICOM%'
GROUP BY object_type
ORDER BY 1;
OBJECT_TYPE COUNT(*)
-------------- --------
PACKAGE 5
PACKAGE BODY 5
SYNONYM 3
TABLE 2
TYPE 7
TYPE BODY 1
SELECT DISTINCT owner, object_name
FROM cdb_objects_ae
WHERE UPPER(object_name) LIKE '%DICOM%'
AND object_type = 'PACKAGE'
ORDER BY 2;
-- if these links are broken use google to find DICOM + "DCM" and you'll find lots of other sources
Create Directory Object
conn sys@pdbdev as sysdba
CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';
GRANT read ON DIRECTORY ctemp TO uwclass;
-- place a dicom image named image1.dcm into the directory pointed to by the Oracle Directory object CTEMP
Create table to hold DICOM images
conn uwclass/uwclass@pdbdev
CREATE TABLE medicalImages(
image_id NUMBER,
description VARCHAR2(40),
metadata XMLType,
image ORDSYS.ORDIMAGE,
thumb ORDSYS.ORDIMAGE)
LOB (image.source.localdata) STORE AS (chunk 32K)
-- store images with 32K chunk
LOB (thumb.source.localdata)
STORE AS (chunk 16K)
-- but the thumbnails with only 16K
-- bind the XMLType columns to the multiMedia metadata columns
XMLType column metadata
XMLSCHEMA "https://xmlns.oracle.com/ord/meta/dicomImage"
ELEMENT "DICOM_IMAGE";
ALTER TABLE medicalimages
ADD CONSTRAINT pk_medicalimages
PRIMARY KEY(image_id)
USING INDEX;
desc medicalImages
set describe depth all linenum on indent on
desc medicalImages
set describe depth 1
SELECT object_name, object_type
FROM user_objects
WHERE created > SYSDATE-10/1440
ORDER BY 1,2;
desc user_lobs
SELECT table_name, segment_name, index_name, chunk, in_row
FROM user_lobs;
SELECT index_name, index_type, table_type
FROM user_indexes
WHERE table_name = 'MEDICALIMAGES'
ORDER BY 1;
-- explore what ORDDICOM is
conn sys@pdbdev as sysdba
SELECT object_type
FROM dba_objects
WHERE object_name = 'ORDDICOM';
OBJECT_TYPE
-------------------------
SYNONYM
TYPE
TYPE BODY
SELECT DISTINCT object_name
FROM dba_arguments
WHERE package_name = 'ORDDICOM'
ORDER BY 1;
DECLARE
obj1 ordsys.ordimage;
BEGIN
SELECT image
INTO obj1
FROM medicalImages
WHERE image_id = 1
FOR UPDATE;
obj1.setSource('FILE', 'CTEMP', 'image1.dcm');
dbms_output.put_line(obj1.getSource || ' has been loaded');
UPDATE medicalImages
SET image=obj1
WHERE image_id=1;
COMMIT;
END;
/
Create metadata extract procedure
CREATE OR REPLACE PROCEDURE extractDicomMetadata(inID IN INTEGER) AUTHID DEFINER IS
local_image ORDSYS.ORDIMAGE;
local_id INTEGER;
dicom_metadata XMLType := NULL;
BEGIN
SELECT image
INTO local_image
FROM medicalImages
WHERE image_id = inID;
-- extract DICOM metadata
dicom_metadata := local_image.getDicomMetadata('imageGeneral');
IF (dicom_metadata IS NULL) THEN
dbms_output.put_line('metadata is NULL');
ELSE
UPDATE medicalImages
SET metadata = dicom_metadata
WHERE image_id = inID;
END IF;
-- print the namespace of the XML document containing the DICOM metadata just extracted
dbms_output.put_line('namespace: ' || dicom_metadata.getNamespace());
END extractDicomMetadata;
/
-- this may take a ~60 seconds the first time on a Windows machine
exec extractDicomMetadata(1);
View metadata
SELECT DISTINCT package_name, object_name
FROM all_arguments
WHERE package_name LIKE '%DICOM%'
ORDER BY 1;
