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 Video is a multi-object capability built into the Oracle Database that contains native APIs for storing, retrieving, and collecting metadata for in-database stored video files.
Create the UWCLASS user if it does not already exist using the SETUP link at page bottom.
Create Directory
-- create a directory named VIDEODIR pointing to a temp directory on the server hosting the instance.
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE DIRECTORY videodir AS 'c:\temp';
Verify the required objects are visible to the application user
conn uwclass/uwclass@pdbdev
-- verify required objects are visible to schema
SELECT owner, object_name, status
FROM all_objects
WHERE object_name LIKE 'ORD%';
/* if you do not see objects owned by MDSYS, ORDPLUGINS, and ORDSYS connect to the PDBDEV container as SYS and grant the appropriate privileges to UWCLASS */
Explore the ORDSYS Schema
conn uwclass/uwclass@pdbdev
col owner format a7
col library_name format a13
col file_spec format a57
col leaf_filename format a14
col agent format a6
SELECT *
FROM all_libraries
WHERE owner = 'ORDSYS';
-- explore what ordvideo is
SELECT object_type
FROM all_objects
WHERE object_name = 'ORDVIDEO';
SELECT DISTINCT object_name
FROM all_arguments
WHERE package_name = 'ORDVIDEO'
ORDER BY 1;
Create and Prepare Application Table
conn uwclass/uwclass@pdbdev
CREATE TABLE t_video (
vid NUMBER,
video ordsys.ordvideo);
desc t_video
set describe depth all
desc t_video
set describe depth all linenum on indent on
desc t_video
-- create initial records
INSERT INTO t_video VALUES (1, ordsys.ordvideo.init());
INSERT INTO t_video VALUES (2, ordsys.ordvideo.init());
COMMIT;
SELECT * FROM t_video;
Use ORDVIDEO methods to set and get video properties
set serveroutput on
-- check video attributes by calling methods
DECLARE
obj ordsys.ordvideo;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 1;
-- access methods
dbms_output.put_line('METHODS');
dbms_output.put_line('-------');
dbms_output.put_line('Description: ' || obj.getDescription);
dbms_output.put_line('MimeType: '|| obj.getMimeType);
EXCEPTION
WHEN ordsys.ordvideoexceptions.description_is_not_set THEN
dbms_output.put_line('Description: NOT SET');
END;
/
-- set description then invoke a method
DECLARE
obj ordsys.ordvideo;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 1 FOR UPDATE;
obj.setDescription('My New Video File');
obj.setMimeType('video/avi');
UPDATE t_video
SET video = obj
WHERE vid=1;
EXCEPTION
WHEN ordsys.ordvideoexceptions.description_is_not_set THEN
dbms_output.put_line('Description: NOT SET');
END;
/
-- try to retrieve the format
DECLARE
obj ordsys.ordvideo;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 1;
-- access methods
dbms_output.put_line('METHODS');
dbms_output.put_line('-------');
dbms_output.put_line('Format: '|| obj.getFormat);
EXCEPTION
WHEN ordsys.ordvideoexceptions.video_format_is_null THEN
dbms_output.put_line('Format: NOT SET');
END;
/
-- set all attributes
DECLARE
obj ordsys.ordvideo;
ctx RAW(4000) := NULL;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 1
FOR UPDATE;
-- set description
obj.setDescription('Video from a BFILE');
-- set mimetype
obj.setMimeType('video/x-quicktime');
-- set source
obj.setSource('FILE', 'VIDEODIR','sphere.mpg');
-- import data
obj.import(ctx);
-- set video attributes
obj.setKnownAttributes('MOOV', 400, 300, 1024, 10, 3600, 36000, 'NONE', 256, 28000);
UPDATE t_video
SET video = obj
WHERE vid = 1;
END;
/
-- do it again and display different attributes
DECLARE
obj ordsys.ordvideo;
ctx RAW(4000) := NULL;
width INTEGER;
height INTEGER;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 1;
-- use SQL statements to access length, mimetype and comment length
SELECT t.video.getMimeType() MimeType
FROM t_video t
WHERE t.vid = 1;
SELECT t.video.getContentLength()
FROM t_video t
WHERE t.vid = 1;
SELECT t.video.getCommentLength()
FROM t_video t
WHERE t.vid = 1;
-- set the source for 2 rows
-----------------------------------------------------------------
-- NOTE THAT VIDEODIR has been defined and the appropriate video
-- file exists in the directory
--
-- If you are using URL then make sure that the specified URL
-- exists and that the video file is accessible from it
-----------------------------------------------------------------
DECLARE
obj1 ordsys.ordvideo;
obj2 ordsys.ordvideo;
BEGIN
SELECT video
INTO obj1
FROM t_video
WHERE vid = 1
FOR UPDATE;
SELECT video
INTO obj2
FROM t_video
WHERE vid = 2
FOR UPDATE;
UPDATE t_video
SET video = obj2
WHERE vid=2;
COMMIT;
END;
/
-- check content lengths and also method not implemented exception
DECLARE
obj ordsys.ordvideo;
ctx RAW(4000) := NULL;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 1 ;
dbms_output.put_line(obj.getSource);
dbms_output.put_line(obj.getContentLength(ctx));
EXCEPTION
WHEN ordsys.ordvideoexceptions.method_not_supported THEN
dbms_output.put_line('Content length not available for this source');
END;
/
-- check content lengths and also check for source plugin exception
-- with the current scenario, it means that the method is not implemented DECLARE
obj ordsys.ordvideo;
ctx RAW(4000) := NULL;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 2;
dbms_output.put_line(obj.getSource);
dbms_output.put_line(obj.getContentLength(ctx));
EXCEPTION
WHEN ordsys.ordsourceexceptions.source_plugin_exception THEN
dbms_output.put_line('Source plugin raised an exception');
WHEN OTHERS THEN
dbms_output.put_line('EXCEPTION CAUGHT');
END;
/
Import Data
-- all the methods from this point on will only be useful and will not raise exception if the data has been made available
DECLARE
obj1 ordsys.ordvideo;
obj2 ordsys.ordvideo;
ctx RAW(4000) := NULL;
BEGIN
SELECT video
INTO obj1
FROM t_video
WHERE vid = 1
FOR UPDATE;
SELECT video
INTO obj2
FROM t_video
WHERE vid = 2
FOR UPDATE;
dbms_output.put_line(obj1.getSource);
IF obj1.source.isLocal THEN
dbms_output.put_line('OBJ1: Data is local');
ELSE
dbms_output.put_line('OBJ1: Data is external');
END IF;
obj1.import(ctx);
dbms_output.put_line(obj1.getContentLength(ctx));
IF obj1.source.isLocal THEN
dbms_output.put_line('OBJ1: Data is local');
ELSE
dbms_output.put_line('OBJ1: Data is external');
END IF;
dbms_output.put_line(obj2.getSource);
IF obj2.source.isLocal THEN
dbms_output.put_line('OBJ2: Data is local');
ELSE
dbms_output.put_line('OBJ2: Data is external');
END IF;
obj2.import(ctx);
dbms_output.put_line(obj2.getContentLength(ctx));
IF obj2.source.isLocal THEN
dbms_output.put_line('OBJ2: Data is local');
ELSE
dbms_output.put_line('OBJ2: Data is external');
END IF;
UPDATE t_video
SET video = obj1
WHERE vid = 1;
UPDATE t_video
SET video = obj2
WHERE vid = 2;
COMMIT;
END;
/
-- INSTALL YOUR OWN FORMAT PLUGIN
-- 1. log onto ordplugins/<ordplugins_password>
-- 2. create the type ... see fplugins.sql and fpluginb.sql
-- 3. grant privs to public so that the plugin is visible to all the users
DECLARE
obj ordsys.ordvideo;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 2
FOR UPDATE;
-- set description
obj.setDescription('Video from a BFILE');
-- set mimetype
obj.setMimeType('video/x-quicktime');
UPDATE t_video
SET VIDEO = obj
WHERE vid=2;
END;
/
Install your own plugins
-- 1. log onto ordplugins/<ordplugins_password>
-- 2. create the type ... see fplugins.sql and fpluginb.sql
-- 3. grant privs to public so that the plugin is visible to all the users
Test using the new plugins
DECLARE
obj ordsys.ordvideo;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 2
FOR UPDATE;
-- set description
obj.setDescription('Video from a BFILE');
-- set mimetype
obj.setMimeType('video/x-quicktime');
UPDATE t_video
SET VIDEO = obj
WHERE vid=2;
END;
/
DECLARE
obj ordsys.ordvideo;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 2
FOR UPDATE;
obj.setFormat('DEMO');
dbms_output.put_line('FORMAT NOW IS: '||obj.getFormat);
UPDATE t_video
SET video = obj
WHERE vid=2;
COMMIT;
END;
/
-- get all stored attributes
DECLARE
obj ordsys.ordvideo;
ctx RAW(4000) := NULL;
width INTEGER;
height INTEGER;
BEGIN
SELECT video
INTO obj
FROM t_video
WHERE vid = 2;
-- invoke plugin to get attributes
DECLARE
obj ordsys.ordVideo;
ctx RAW(4000) := NULL;
outdata RAW(4000);
retdata RAW(4000);
width INTEGER;
height INTEGER;
BEGIN
SELECT video
INTO obj
FROM uwclass.t_video
WHERE vid = 2;
-- the last lines are commented out while I get a fix from MOS.
-- get attributes by name
-- dbms_output.put_line('Copyright: ' || obj.getAttribute(ctx, 'Copyright'));
-- dbms_output.put_line('Owner: ' || obj.getAttribute(ctx, 'Owner'));
-- send a command to be processed
-- retdata := obj.processVideoCommand(ctx, 'increment','4', outdata);
-- dbms_output.put_line('Result of incrementing 4: '|| utl_raw.cast_to_varchar2(outdata));
END;
/
