Actions As Schema Owner |
-- verify required objects are visible to schema
SELECT owner, object_name, status
FROM all_objects
WHERE object_name LIKE 'ORD%';
-- may need to unlock account and reset password
conn ordsys/ordsys
set linesize 120
col owner format a15
col library_name format a20
col file_spec format a45
SELECT *
FROM all_libraries;
conn uwclass/uwclass
CREATE TABLE t_audio (
id NUMBER,
audio ordsys.ordaudio);
desc t_audio
set describe depth all
desc t_audio
set describe depth all linenum on indent on
desc t_audio
-- explore what ORDAudio is
conn ordsys/ordsys
SELECT object_type
FROM user_objects
WHERE object_name = 'ORDAUDIO';
SELECT DISTINCT object_name
FROM user_arguments
WHERE package_name = 'ORDAUDIO';
conn uwclass/uwclass
-- create initial records
INSERT INTO t_audio VALUES (1, ordsys.ordaudio.init());
INSERT INTO t_audio VALUES (2, ordsys.ordaudio.init());
COMMIT;
SELECT * FROM t_audio;
SELECT id FROM t_audio;
set serveroutput on
-- check audio attributes by calling methods
DECLARE
obj ordsys.ordaudio;
BEGIN
SELECT audio
INTO obj
FROM t_audio
WHERe id = 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.ordaudioexceptions.description_is_not_set THEN
dbms_output.put_line('Description: NOT SET');
END;
/ |
-- Set description then invoke a method
DECLARE
obj ordsys.ordaudio;
BEGIN
SELECT audio into obj
FROM t_audio
WHERE id = 1
FOR UPDATE;
obj.setDescription('My New Audio File');
obj.setMimeType('audio/basic');
dbms_output.put_line('Description: '||obj.getDescription);
dbms_output.put_line('MimeType: '||obj.getMimeType);
UPDATE t_audio
SET audio = obj
WHERE id=1;
EXCEPTION
WHEN ordsys.ordaudioexceptions.description_is_not_set THEN
dbms_output.put_line('Description: NOT SET');
END;
/ |
-- try getting the format
DECLARE
obj ordsys.ordaudio;
BEGIN
SELECT audio
INTO obj
FROM t_audio
WHERE id = 1;
-- access methods
dbms_output.put_line('METHODS');
dbms_output.put_line('-------');
dbms_output.put_line('Format: '|| obj.getFormat);
EXCEPTION
WHEN ordsys.ordaudioexceptions.audio_format_is_null THEN
dbms_output.put_line('Format: NOT SET');
END;
/ |
-- use SQL statements to access length, mimetype and commentlength
SELECT T.audio.getMimeType() MimeType
FROM t_audio t WHERe t.id = 1;
SELECT T.audio.getContentLength()
FROM t_audio T WHERE t.id = 1;
SELECT T.audio.getCommentLength()
FROM t_audio T WHERE t.id = 1;
-- load audio files from disk to table
DECLARE
obj1 ordsys.ordaudio;
obj2 ordsys.ordaudio;
BEGIN
SELECT audio
INTO obj1
FROM t_audio
WHERe id = 1
FOR UPDATE;
SELECT audio
INTO obj2
FROM t_audio
WHERE id = 2
FOR UPDATE;
obj1.setSource('FILE', 'AUDIODIR', 'aud1.wav');
obj2.setSource('FILE', 'AUDIODIR', 'aud2.mp3');
-----------------------------------------------
-- HTTP ACCESS
-----------------------------------------------
----------------------------------------------------------
-- THE NEXT LINE HAS BEEN COMMENTED FOR AUTOMATED TESTING
-- PLEASE UNCOMMENT IT TO TRY OUT THE COMMAND.
----------------------------------------------------------
--obj2.setSource('HTTP','www.psoug.org/intermedia','audio2.au');
dbms_output.put_line(obj1.getSource);
dbms_output.put_line(obj2.getSource);
UPDATE t_audio
SET audio=obj1
WHERE id=1;
UPDATE t_audio
SET audio=obj2
WHERE id=2;
COMMIT;
END;
/ |
-- check content lengths and for unimplemented methods
DECLARE
obj ordsys.ordaudio;
ctx RAW(4000) := NULL;
BEGIN
SELECT audio INTO obj FROM t_audio WHERE id = 1;
dbms_output.put_line(obj.getSource);
dbms_output.put_line(obj.getContentLength(ctx));
EXCEPTION
WHEN ordsys.ordaudioexceptions.method_not_supported THEN
dbms_output.put_line('Cannot Obtained Content length');
END;
/ |
-- check content lengths and also check for source plugin exception with
-- the current scenario, it means that the method is not implemented
DECLARE
obj2 ordsys.ordaudio;
ctx RAW(4000) := NULL;
BEGIN
SELECT audio
INTO obj2
FROM t_audio
WHERE id = 2;
dbms_output.put_line(obj2.getSource);
dbms_output.put_line(obj2.getContentLength(ctx));
EXCEPTION
WHEN ordsys.ordSourceExceptions.source_plugin_exception
THEN
dbms_output.put_line('Source plugin Exception');
WHEN OTHERS THEN
dbms_output.put_line('EXCEPTION CAUGHT');
END;
/ |
-- import data from both the sources
-----------------------------------------------------
-- 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.ordaudio;
obj2 ordsys.ordaudio;
ctx RAW(4000) := NULL;
BEGIN
SELECT audio
INTO obj1
FROM t_audio
WHERE id = 1
FOR UPDATE;
SELECT audio
INTO obj2
FROM t_audio
WHERE id = 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_audio
SET audio = obj1
WHERE id=1;
UPDATE t_audio
SET audio = obj2
WHERE id=2;
END;
/ |
-- try to extract audio attributes
DECLARE
obj1 ordsys.ordaudio;
obj2 ordsys.ordaudio;
ctx RAW(4000) := NULL;
BEGIN
SELECT audio
INTO obj1
FROM t_audio
WHERE id = 1
FOR UPDATE;
SELECT audio
INTO obj2
FROM t_audio
WHERE id = 2
FOR UPDATE;
obj1.setProperties(ctx);
obj1.setCompressionType('NONE');
obj1.setMimeType('audio/basic');
obj1.setDescription('First AUFF formatted audio clip');
obj2.setProperties(ctx);
obj2.setCompressionType('NONE');
obj2.setMimeType('audio/basic');
obj2.setDescription('Second AUFF audio clip');
UPDATE t_audio
SET audio=obj1
WHERE id=1;
UPDATE t_audio
SET audio=obj2
WHERE id=2;
COMMIT;
END;
/ |
-- try to all the audio attributes now for obj1 and obj2
DECLARE
obj1 ordsys.ordaudio;
BEGIN
SELECT audio
INTO obj1
FROM t_audio
WHERE id = 1;
-- access attributes directly
-- NOT RECOMMENDED FOR APPLICATION DEVELOPERS
dbms_output.put_line('DIRECT READ');
dbms_output.put_line('-----------');
dbms_output.put_line('Description: '||obj1.description);
dbms_output.put_line('Format: '||obj1.format);
dbms_output.put_line('MimeType: '||obj1.mimeType);
dbms_output.put_line('Source.srcType: '||obj1.source.srcType);
dbms_output.put_line('Source.srcLocation: '||obj1.source.srcLocation);
dbms_output.put_line('Source.srcName: '||obj1.source.srcName);
----------------------------------------------------------
-- THE NEXT LINE HAS BEEN COMMENTED FOR AUTOMATED TESTING
-- PLEASE UNCOMMENT IT TO TRY OUT THE COMMAND.
----------------------------------------------------------
-- dbms_output.put_line('Source.updateTime:
-- '||obj.source.updateTime);
dbms_output.put_line('Source.local: '||obj1.source.local);
dbms_output.put_line('Encoding: '||obj1.Encoding);
dbms_output.put_line('numberOfChannels: '||obj1.numberOfChannels);
dbms_output.put_line('samplingRate: '||obj1.samplingRate);
dbms_output.put_line('sampleSize: '||obj1.sampleSize);
dbms_output.put_line('compressionType: '||obj1.compressionType);
dbms_output.put_line('audioDuration: '||obj1.audioDuration);
-- call attribute related methods
END;
/ |
DECLARE
obj2 ordsys.ordaudio;
BEGIN
SELECT audio
INTO obj2
FROM t_audio
WHERE id = 2;
-- access attributes directly
-- NOT RECOMMENDED FOR APPLICATION DEVELOPERS
dbms_output.put_line('DIRECT READ');
dbms_output.put_line('-----------');
dbms_output.put_line('Description: '||obj2.description);
dbms_output.put_line('Format: '||obj2.format);
dbms_output.put_line('MimeType: '||obj2.mimeType);
dbms_output.put_line('Source.srcType: '||obj2.source.srcType);
dbms_output.put_line('Source.srcLocation: '||obj2.source.srcLocation);
dbms_output.put_line('Source.srcName: '||obj2.source.srcName);
----------------------------------------------------------
-- THE NEXT LINE HAS BEEN COMMENTED FOR AUTOMATED TESTING
-- PLEASE UNCOMMENT IT TO TRY OUT THE COMMAND.
----------------------------------------------------------
-- dbms_output.put_line('Source.updateTime:
-- '||obj2.source.updateTime);
dbms_output.put_line('Source.local: '||obj2.source.local);
dbms_output.put_line('Encoding: '||obj2.Encoding);
dbms_output.put_line('numberOfChannels: '||obj2.numberOfChannels);
dbms_output.put_line('samplingRate: '||obj2.samplingRate);
dbms_output.put_line('sampleSize: '||obj2.sampleSize);
dbms_output.put_line('compressionType: '||obj2.compressionType);
dbms_output.put_line('audioDuration: '||obj2.audioDuration);
-- call attribute related methods
END;
/ |
-- try all accessor methods
DECLARE
obj ordsys.ordaudio;
ctx RAW(4000) := NULL;
BEGIN
SELECT audio
INTO obj
FROM t_audio
WHERE id = 1;
dbms_output.put_line('METHODS');
dbms_output.put_line('-------');
dbms_output.put_line('Description: '||obj.getDescription);
dbms_output.put_line('Format: '||obj.getFormat);
dbms_output.put_line('MimeType: '||obj.getMimeType);
dbms_output.put_line('Source: '||obj.getSource);
dbms_output.put_line('ContentLength: '||obj.getContentLength(ctx));
----------------------------------------------------------
-- THE NEXT LINE HAS BEEN COMMENTED FOR AUTOMATED TESTING
-- PLEASE UNCOMMENT IT TO TRY OUT THE COMMAND.
----------------------------------------------------------
-- dbms_output.put_line('UpdateTime: '||obj.getUpdateTime);
dbms_output.put_line('Encoding: '||obj.getEncoding);
dbms_output.put_line('numberOfChannels:'||obj.getNumberOfChannels);
dbms_output.put_line('samplingRate: '||obj.getSamplingRate);
dbms_output.put_line('sampleSize: '||obj.getSampleSize);
dbms_output.put_line('compressionType: '||obj.getCompressionType);
dbms_output.put_line('audioDuration: '||obj.getAudioDuration);
END;
/ |
-- try all accessor methods
DECLARE
obj ordsys.ordaudio;
ctx RAW(4000) := NULL;
BEGIN
SELECT audio
INTO obj
FROM t_audio
WHERE id = 2;
dbms_output.put_line('METHODS');
dbms_output.put_line('-------');
dbms_output.put_line('Description: '||obj.getDescription);
dbms_output.put_line('Format: '||obj.getFormat);
dbms_output.put_line('MimeType: '||obj.getMimeType);
dbms_output.put_line('Source: '||obj.getSource);
dbms_output.put_line('ContentLength:'||obj.getContentLength(ctx));
----------------------------------------------------------
-- THE NEXT LINE HAS BEEN COMMENTED FOR AUTOMATED TESTING
-- PLEASE UNCOMMENT IT TO TRY OUT THE COMMAND.
----------------------------------------------------------
-- dbms_output.put_line('UpdateTime: '||obj.getUpdateTime);
dbms_output.put_line('Encoding: '||obj.getEncoding);
dbms_output.put_line('numberOfChannels: '||obj.getNumberOfChannels);
dbms_output.put_line('samplingRate: '||obj.getSamplingRate);
dbms_output.put_line('sampleSize: '||obj.getSampleSize);
dbms_output.put_line('compressionType: '||obj.getCompressionType);
dbms_output.put_line('audioDuration: '||obj.getAudioDuration);
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 users
set serveroutput on
DECLARE
obj ordsys.ordaudio;
BEGIN
SELECT audio into obj
FROM t_audio
WHERE id = 2
FOR UPDATE;
obj.setFormat('DEMO');
dbms_output.put_line('FORMAT NOW IS: '||obj.getFormat);
UPDATE t_audio
SET audio = obj
WHERE id=2;
COMMIT;
END;
/ |
-- make sure that data has already been imported
-- try all accessor methods from the format plugin now
DECLARE
obj ordsys.ordaudio;
outdata RAW(4000);
retdata RAW(4000);
ctx RAW(4000) := NULL;
BEGIN
SELECT audio
INTO obj
FROM t_audio
WHERE id = 2;
dbms_output.put_line('METHODS');
dbms_output.put_line('-------');
dbms_output.put_line('Description: '||obj.getDescription);
dbms_output.put_line('Format: '||obj.getFormat(ctx));
dbms_output.put_line('MimeType: '||obj.getMimeType);
dbms_output.put_line('Source: '||obj.getSource);
dbms_output.put_line('ContentLength: '||obj.getContentLength(ctx));
----------------------------------------------------------
-- THE NEXT LINE HAS BEEN COMMENTED FOR AUTOMATED TESTING
-- PLEASE UNCOMMENT IT TO TRY OUT THE COMMAND.
----------------------------------------------------------
-- dbms_output.put_line('UpdateTime: '||obj.getUpdateTime);
dbms_output.put_line('Encoding: '||obj.getEncoding(ctx));
dbms_output.put_line('numberOfChannels: '||obj.getNumberOfChannels(ctx));
dbms_output.put_line('samplingRate: '||obj.getSamplingRate(ctx));
dbms_output.put_line('sampleSize: '||obj.getSampleSize(ctx));
dbms_output.put_line('audioDuration: '||obj.getAudioDuration(ctx));
-- 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.processAudioCommand(ctx, 'increment','4', outdata);
dbms_output.put_line('Result of incrementing 4: ' ||
utl_raw.cast_to_varchar2(outdata));
END;
/ |
|
