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.
Purpose
Multimedia Image is a multi-object capability built into the Oracle Database that contains native APIs for storing, retrieving, and collecting metadata for in-database stored image files.
Click here to download demo1.jpg Click here to download demo2.png
For the demo on this page copy demo.jpg to c:\temp or if on a real operating system to $HOME and make the appropriate change to the CREATE DIRECTORY DDL below.
Right click on the images when they are displayed and select [Save Image As] or its equivalent in your browser.
MultiMedia Image Demo
Actions as SYS
conn / as sysdba
ALTER USER ordsys ACCOUNT UNLOCK IDENTIFIED BY ordsys;
GRANT create session TO ordsys;
-- if an application schema does not already exist conn sys@pdbdev as sysdba
CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA UNLIMITED ON uwdata;
GRANT create session TO uwclass;
GRANT create any directory TO uwclass;
GRANT create table TO uwclass;
OrdImage Methods
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE DIRECTORY imagedir AS 'c:\temp';
-- verify required objects are visible to schema
SELECT owner, object_name, object_type, status
FROM all_objects
WHERE object_name LIKE 'ORD%IM%G%'
AND owner NOT IN ('OE', 'IX')
ORDER BY 1,2;
desc dba_type_methods
col method_name format a30
SELECT method_name, COUNT(*)
FROM dba_type_methods
WHERE type_name = 'ORDIMAGE'
GROUP BY method_name
ORDER BY 1;
conn ordsys/ordsys@pdbdev
set linesize 141
col owner format a7
col agent format a6
col library_name format a15
col file_spec format a58
col leaf_filename format a15
desc all_libraries
SELECT *
FROM all_libraries;
-- explore what ordimage is
SELECT object_name, object_type
FROM user_objects
WHERE object_name = 'ORDIMAGE';
SELECT DISTINCT object_name
FROM user_arguments
WHERE package_name = 'ORDIMAGE'
ORDER BY 1;
-- create initial records
INSERT INTO t_image VALUES (1, ordsys.ordimage.init());
INSERT INTO t_image VALUES (2, ordsys.ordimage.init());
COMMIT;
SELECT * FROM t_image;
set serveroutput on
-- set all attributes
-- be sure that you have saved the files demo1.jpg and demo2.png
-- to the directory IMAGEDIR created above first
DECLARE
obj ordsys.ordimage;
ctx RAW(4000) := NULL;
BEGIN
SELECT image
INTO obj
FROM t_image
WHERE img_id = 1
FOR UPDATE;
obj.setSource('FILE', 'IMAGEDIR', 'demo1.jpg');
-- import data
obj.import(ctx);
-- set image attributes in the IMAGE column metadata
obj.setProperties;
UPDATE t_image
SET image = obj
WHERE img_id = 1;
SELECT image
INTO obj
FROM t_image
WHERE img_id = 2
FOR UPDATE;
obj.setSource('FILE', 'IMAGEDIR', 'demo2.png');
-- import data
obj.import(ctx);
-- set image attributes in the IMAGE column metadata
obj.setProperties;
-- checkProperties returns TRUE if the metadata matches the image
IF obj.checkProperties() THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
UPDATE t_image
SET image = obj
WHERE img_id = 2;
END;
/
-- use SQL statements to access object metadata
col ImgCompression format a15
SELECT t.img_id, t.image.getCompressionFormat() ImgCompression
FROM t_image t;
col ImgFormat format a15
SELECT img_id, t.image.getContentFormat() ImgFormat
FROM t_image t;
col ByteSize format 9999999
SELECT t.img_id, t.image.getContentLength() ByteSize
FROM t_image t;
col FileType format a10
SELECT t.img_id, t.image.getFileformat() FileType
FROM t_image t;
col ImageSize format a20
SELECT t.img_id, t.image.getHeight() ||' x '|| t.image.getWidth() ||' pixels' ImageSize
FROM t_image t;
SELECT t.img_id, t.image.getMetadata() MetaData
FROM t_image t;
col MimeType format a20
SELECT t.img_id, t.image.getMimeType() MimeType
FROM t_image t;
col Src format a40
SELECT t.img_id, t.image.getSource() Src
FROM t_image t;
col SrcLocation format a15
SELECT t.img_id, t.image.getSourceLocation() SrcLocation
FROM t_image t;
col SrcName format a15
SELECT t.img_id, t.image.getSourceName() SrcName
FROM t_image t;
col SrcType format a15
SELECT t.img_id, t.image.getSourceType() SrcType
FROM t_image t;
SELECT t.img_id, t.image.getUpdatetime() UpdateTime
FROM t_image t;
-- display attributes using PL/SQL
set serveroutput on
DECLARE
obj ordsys.ordImage;
b BLOB;
c CLOB;
n1 NUMBER;
n2 NUMBER;
n3 NUMBER;
v1 VARCHAR2(30);
v2 VARCHAR2(30);
v3 VARCHAR2(30);
v4 VARCHAR2(30);
BEGIN
SELECT image
INTO obj
FROM t_image
WHERE img_id = 1;
-- getContent Demo
b := obj.getContent();
dbms_output.put_line(dbms_lob.getlength(b));
