Oracle MultiMedia Audio
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
Purpose Multimedia Audio is a multi-object capability built into the Oracle Database that contains native APIs for storing, retrieving, and collecting metadata for in-database stored audio files.
Dependencies
DBMS_LOB ORDDOC ORDX_DEFAULT_AUDIO
ORDAUDIO_PKG ORDUTIL ORDAUDIOEXCEPTIONS
ORDSOURCE ORDAUDIO ORDIMERRORCODES
ONLINE_MEDIA ORDERROR  
ordsys.ORDAudio Columns
Column Name Data Type
DESCRIPTION VARCHAR2(4000)
SOURCE ordsys.ORDSOURCE
FORMAT VARCHAR2(31)
MIMETYPE VARCHAR2(4000)
COMMENTS CLOB
ENCODING VARCHAR2(256)
NUMBEROFCHANNELS NUMBER(38)
SAMPLINGRATE NUMBER(38)
SAMPLESIZE NUMBER(38)
COMPRESSIONTYPE VARCHAR2(4000)
AUDIODURATION NUMBER(38)
ordsys.ORDAudio Methods
appendToComments() getFormat() readFromComments()
checkProperties() getMimeType() readFromSource()
clearLocal() getNumberOfChannels() setAudioDuration()
closeSource() getProperties setCompressionType()
compareComments() getSampleSize() setDescription()
copyCommentsOut() getSamplingRate() setEncoding()
delteComments() getSource() setFormat()
deleteContent() getSourceLocation() setKnownAttributes()
eraseFromComments() getSourceName() setLocal()
export() getSourceType() setMimeType()
getAttribute() getUpdateTime() setNumberOfChannels()
getAttributes() import() setProperties()
getAudioDuration() importFrom() setSampleSize()
getBFile() init() setSampleIngrate()
getCommentLength() isLocal() setSource()
getCompressionType() loadCommentsFromFile() setupDateTime()
getContent() locateInComments() trimComments()
getContentInLOB() openSource() trimSource
getContentLength() ordAudio() writeToComments()
getDescription() processAudioCommand() writeToSource()
getEncoding() processSourceCommand()  
ordsys.ORDSource Columns
Column Name Data Type
LOCALDATA BLOB
SRCTYPE VARCHAR2(4000)
SRCLOCATION VARCHAR2(4000)
SRCNAME VARCHAR2(4000)
UPDATETIME DATE
LOCAL NUMBER
Required Schemas
MDSYS ORDPLUGINS ORDSYS
Source {$ORACLE_HOME}/ord/im/admin

catim.sql
imchkdep.sql
impbs.sql
imtype.sql

oradfsp.sql
ordapksp.plb
ordispec.sql
ordsrcsp.sql
Required System Privileges GRANT create any directory TO <schema_name>;
conn sys@pdbdev as sysdba

GRANT create any directory TO uwlass;
Sample Audio Files Click here to download 911.mp3
Click here to download thing.wav
For the demo on this page copy 911.mp3 to c:\temp or if on a real operating system to $HOME and make the appropriate change to the CREATE DIRECTORY DDL below.
 
MultiMedia Audio Demo
Actions as SYS conn sys@pdbdev as sysdba

-- not required for production use by required for learning about MultiMedia Audio here in the Library
ALTER USER ordsys ACCOUNT UNLOCK IDENTIFIED BY ordsys;

-- not required for production use by required for learning about MultiMedia Audio here in the Library
GRANT create session TO ordsys;

-- if an application schema does not already exist
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;

CREATE OR REPLACE DIRECTORY audiodir AS 'c:\temp';

GRANT ALL ON DIRECTORY audiodir TO uwclass;
Actions as application owner to load audio files conn uwclass/uwclass@pdbdev

-- verify required objects are visible to schema
SELECT owner, object_name, status
FROM all_objects_ae
WHERE object_name LIKE 'ORD%AUD%';

conn ordsys/ordsys@pdbdev

set linesize 141
col owner format a10
col library_name format a15
col file_spec format a60
col agent format a6
col leaf_filename format a10

SELECT *
FROM all_libraries;

-- explore what ordaudio is
SELECT object_name, object_type
FROM user_objects
WHERE object_name = 'ORDAUDIO';

SELECT DISTINCT object_name
FROM user_arguments
WHERE package_name = 'ORDAUDIO'
ORDER BY 1;

conn uwclass/uwclass@pdbdev

CREATE TABLE t_audio (
aid   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

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

set serveroutput on

-- check audio attributes by calling methods
DECLARE
 obj ordsys.ordAudio;
BEGIN
  SELECT audio
  INTO obj
  FROM t_audio
  WHERE aid = 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;
/

-- load audio files from disk to table
DECLARE
 obj1 ordsys.ordAudio;
 obj2 ordsys.ordAudio;
BEGIN
  SELECT audio
  INTO obj1
  FROM t_audio
  WHERE aid = 1
  FOR UPDATE;

  SELECT audio
  INTO obj2
  FROM t_audio
  WHERE aid = 2
  FOR UPDATE;

  obj1.setSource('FILE', 'AUDIODIR', '911.mp3');
  obj2.setSource('FILE', 'AUDIODIR', 'thing.wav');

  dbms_output.put_line(obj1.getSource);
  dbms_output.put_line(obj2.getSource);

  UPDATE t_audio
  SET audio=obj1
  WHERE aid=1;

  UPDATE t_audio
  SET audio=obj2
  WHERE aid=2;

  COMMIT;
END;
/
Set Description DECLARE
 obj ordsys.ordaudio;
BEGIN
  SELECT audio
  INTO obj
  FROM t_audio
  WHERE aid = 1 FOR UPDATE;

  obj.setDescription('911 Phone Number');
  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 aid=1;

  SELECT audio
  INTO obj
  FROM t_audio
  WHERE aid = 2 FOR UPDATE;

  obj.setDescription('Always a Thing');
  obj.setMimeType('audio/x-wav');

  dbms_output.put_line('Description: ' || obj.getDescription);
  dbms_output.put_line('MimeType: ' || obj.getMimeType);

  UPDATE t_audio
  SET audio = obj
  WHERE aid=2;
  COMMIT;
EXCEPTION
  WHEN ordsys.ordaudioexceptions.description_is_not_set THEN
    dbms_output.put_line('Description: NOT SET');
END;
/

col mimetype format a40

SELECT t.aid, t.audio.getMimeType() MimeType
FROM t_audio t;

SELECT t.aid, t.audio.getContentLength() Content_Len
FROM t_audio t;

col src format a40
col location format a40
col srctype format a20

SELECT t.aid, t.audio.getSource() Src
FROM t_audio t;

SELECT t.aid, t.audio.getSourceLocation() Location
FROM t_audio t;

SELECT t.aid, t.audio.getSourceName() Location
FROM t_audio t;

SELECT t.aid, t.audio.getSourceType() SrcType
FROM t_audio t;

SELECT t.aid, t.audio.getUpdateTime()<
FROM t_audio t;
Get All Attributes set linesize 80

DECLARE
 ctx    RAW(64);
 obj    ordsys.ordAudio;
 tmpLOB CLOB;
BEGIN
  SELECT t.audio
  INTO obj
  FROM t_audio t
  WHERE t.aid = 2;

  dbms_lob.createTemporary(tmpLOB, FALSE, dbms_lob.call);
  obj.getAllAttributes(ctx, tmpLOB);
  dbms_output.put_line(dbms_lob.substr(tmpLOB, dbms_lob.getLength(tmpLOB), 1));
EXCEPTION
  WHEN ordsys.ordAudioExceptions.audio_plugin_exception THEN
    RAISE;
END;
/

Related Topics
DICOM
Directories
Oracle MultiMedia Audio
Oracle MultiMedia Image
Setup
UTL_RAW
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved