The Oracle Multimedia PL/SQL packages include some common procedures that support the movement of multimedia data stored in BLOBs and BFILEs between Oracle database and the local file system. Because the definitions of these common procedures are identical for each of the following PL/SQL packages, they are described once rather than for each package:
ORD_AUDIO
ORD_DOC
ORD_IMAGE
ORD_VIDEO
The common procedures for these four Oracle Multimedia PL/SQL packages are defined in the ordarpsp.sql, orddrpsp.sql, ordirpsp.sql, and ordvrpsp.sql files, respectively.
See the following topics for details about the common procedures for the Oracle Multimedia PL/SQL packages:
The examples for functions and procedures in the Oracle Multimedia PL/SQL packages use this list of tables:
Table 2-1 Tables for Oracle Multimedia PL/SQL packages
| Name | Purpose | Definition |
|---|---|---|
|
TAUD |
Used to demonstrate the ORD_AUDIO PL/SQL procedures |
|
|
TDOC |
Used to demonstrate the ORD_DOC PL/SQL procedures |
|
|
TIMG |
Used to demonstrate the ORD_IMAGE PL/SQL functions and procedures |
|
|
TVID |
Used to demonstrate the ORD_VIDEO PL/SQL procedures |
When reading through the examples, use the directory definitions and these table definitions with the example for each Multimedia PL/SQL function or procedure.
The examples in Oracle Multimedia PL/SQL packages use Oracle directory objects to represent the directory specifications where your media files are located.
Some examples use mediauser to represent the user, and c:\mydir\work to represent the directory specification where your media files can be located. See the example for each PL/SQL procedure or function for specific directory definitions for media data files and other details specific to that procedure or function.
The export( ) procedure writes only to a database directory object that the user has privilege to access. That is, you can access a directory object that you have created using the SQL statement CREATE DIRECTORY, or one to which you have been granted READ and WRITE access.
For example, the following SQL*Plus commands create a directory object and grant the user mediauser permission to read and write to any file within the directory c:\mydir\work. Before executing these commands, you must be connected as a user with privileges to create a directory object.
CREATE OR REPLACE DIRECTORY AUDIODIR AS 'c:\mydir\work'; GRANT READ,WRITE ON DIRECTORY AUDIODIR TO mediauser;
The importFrom( ) procedures, and any function or procedure with a BFILE parameter, read only from a database directory object that the user has privilege to access. That is, you can access a directory object that you have created using the SQL statement CREATE DIRECTORY, or one to which you have been granted READ access.
For example, the following SQL*Plus commands create a directory object and grant the user mediauser permission to read any file within the directory c:\mydir\work. Before executing these commands, you must be connected as a user with privileges to create a directory object.
CREATE OR REPLACE DIRECTORY DOCDIR AS 'c:\mydir\work'; GRANT READ ON DIRECTORY DOCDIR TO mediauser;
The examples in Oracle Multimedia ORD_AUDIO PL/SQL Package use the audio table TAUD.
CREATE TABLE taud(n NUMBER,
aud BLOB,
attributes CLOB,
mimetype VARCHAR2(80),
format VARCHAR2(31),
encoding VARCHAR2(256),
numberofchannels INTEGER,
samplingrate INTEGER,
samplesize INTEGER,
compressiontype VARCHAR2(4000),
audioduration INTEGER)
LOB(aud) STORE AS SECUREFILE;
INSERT INTO taud VALUES(1,EMPTY_BLOB(),EMPTY_CLOB(), NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL);
INSERT INTO taud VALUES(2,EMPTY_BLOB(),EMPTY_CLOB(), NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL);
COMMIT;
The examples in Oracle Multimedia ORD_DOC PL/SQL Package use the document table TDOC.
CREATE TABLE tdoc(n NUMBER,
document BLOB,
attributes CLOB,
mimetype VARCHAR2(80),
format VARCHAR2(80),
contentlength INTEGER)
LOB(document) STORE AS SECUREFILE;
INSERT INTO tdoc VALUES(1, EMPTY_BLOB(), EMPTY_CLOB(), NULL, NULL, NULL);
INSERT INTO tdoc VALUES(2, EMPTY_BLOB(), EMPTY_CLOB(), NULL, NULL, NULL);
INSERT INTO tdoc VALUES(3, EMPTY_BLOB(), EMPTY_CLOB(), NULL, NULL, NULL);
INSERT INTO tdoc VALUES(4, EMPTY_BLOB(), EMPTY_CLOB(), NULL, NULL, NULL);
COMMIT;
The examples in Oracle Multimedia ORD_IMAGE PL/SQL Package use the image table TIMG.
CREATE TABLE timg(n NUMBER,
img BLOB,
attributes CLOB,
mimetype VARCHAR2(80),
width INTEGER,
height INTEGER,
fileformat VARCHAR2(4000),
contentformat VARCHAR2(4000),
compressionformat VARCHAR2(4000),
contentlength INTEGER)
LOB(img) STORE AS SECUREFILE;
INSERT INTO timg VALUES(1, EMPTY_BLOB(), EMPTY_CLOB(), NULL,
NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO timg VALUES(2, EMPTY_BLOB(), EMPTY_CLOB(), NULL,
NULL, NULL, NULL, NULL, NULL, NULL);
COMMIT;
The examples in Oracle Multimedia ORD_VIDEO PL/SQL Package use the video table TVID.
CREATE TABLE tvid(n NUMBER,
vid BLOB,
attributes CLOB,
mimetype VARCHAR2(80),
format VARCHAR2(31),
width INTEGER,
height INTEGER,
frameresolution INTEGER,
framerate INTEGER,
videoduration INTEGER,
numberofframes INTEGER,
compressiontype VARCHAR2(4000),
numberofcolors INTEGER,
bitrate INTEGER)
LOB(vid) STORE AS SECUREFILE;
INSERT INTO tvid VALUES(1, EMPTY_BLOB(), EMPTY_CLOB(), NULL, NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO tvid VALUES(2, EMPTY_BLOB(), EMPTY_CLOB(), NULL, NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
COMMIT;
Format
export(src IN BLOB,
dest_location IN VARCHAR2,
dest_name IN VARCHAR2);
Description
Copies audio data in a BLOB within the database to an external data source. The data remains in the source BLOB when it is copied to the destination.
Parameters
Usage Notes
After calling the export( ) procedure, you can issue a SQL DELETE statement or call the DBMS_LOB.TRIM procedure to delete the content stored locally, if desired.
The export( ) procedure writes only to a database directory object that the user has privilege to access. That is, you can access a directory object that you have created using the SQL statement CREATE DIRECTORY, or one to which you have been granted READ and WRITE access. (See Directory Definitions and Setup for PL/SQL Packages Examples for details about the directories and permissions you need to run the PL/SQL package examples.)
Pragmas
None.
Exceptions
None.
Examples
Export data from a local source to an external data source:
DECLARE
audio_data BLOB;
ctx RAW(64):=NULL;
BEGIN
SELECT aud INTO audio_data FROM taud WHERE N=1;
-- export blob into a file
ORDSYS.ORD_AUDIO.export(audio_data,'AUDIODIR','testaud.dat');
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
Format
importFrom(dest IN OUT NOCOPY BLOB,
source_type IN VARCHAR2,
source_location IN VARCHAR2,
source_name IN VARCHAR2);
Description
Transfers data from the specified external data source to the BLOB specified by the dest parameter.
Parameters
Usage Notes
If the value of the source_type parameter is FILE, the source_location parameter contains the name of a database directory object that contains the file to be imported, and the source_name parameter contains the name of the file to be imported. You must ensure that the directory for the external source location exists or is created before you use this procedure.
The importFrom( ) procedure reads only from a database directory object that the user has privilege to access. That is, you can access a directory object that you have created using the SQL statement CREATE DIRECTORY, or one to which you have been granted READ access. (See Directory Definitions and Setup for PL/SQL Packages Examples for details about the directories and permissions you need to run the PL/SQL package examples.)
If the value of the source_type parameter is HTTP, the source_location parameter contains the base URL needed to find the directory that contains the object to be imported, and the source_name parameter contains the name of the object to be imported.
This procedure uses the PL/SQL UTL_HTTP package to import media data from an HTTP data source. You can use environment variables to specify the proxy behavior of the UTL_HTTP package. For example, on Linux and UNIX, setting the environment variable http_proxy to a URL specifies that the UTL_HTTP package must use that URL as the proxy server for HTTP requests. Setting the no_proxy environment variable to a domain name specifies that the HTTP proxy server not be used for URLs in the specified domain.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_HTTP package
Pragmas
None.
Exceptions
ORDSourceExceptions.INCOMPLETE_SOURCE_INFORMATION
This exception is raised if you call the importFrom( ) procedure and the value of the source_type parameter is NULL.
ORDSourceExceptions.NULL_SOURCE
This exception is raised if you call the importFrom( ) procedure and the value of the dest parameter is NULL.
Examples
Import data from the specified external data source into the local source:
DECLARE
document_data BLOB;
BEGIN
SELECT document INTO document_data FROM tdoc WHERE N=1 FOR UPDATE;
-- import file into a blob
ORDSYS.ORD_DOC.importFrom(document_data,'file','DOCDIR','testimg.dat');
UPDATE tdoc SET document = document_data WHERE N=1;
COMMIT;
SELECT document INTO document_data FROM tdoc WHERE N=2 FOR UPDATE;
-- import file into a blob
ORDSYS.ORD_DOC.importFrom(document_data,'file','DOCDIR','testaud.dat');
UPDATE tdoc SET document = document_data WHERE N=2;
COMMIT;
SELECT document INTO document_data FROM tdoc WHERE N=3 FOR UPDATE;
-- import file into a blob
ORDSYS.ORD_DOC.importFrom(document_data,'file','DOCDIR','testvid.dat');
UPDATE tdoc SET document = document_data WHERE N=3;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
Format
importFrom(dest IN OUT NOCOPY BLOB,
source_type IN VARCHAR2,
source_location IN VARCHAR2,
source_name IN VARCHAR2,
format OUT VARCHAR2,
mime_type OUT VARCHAR2);
Description
Transfers data from the specified external data source to the BLOB specified by the dest parameter.
Parameters
Usage Notes
If the value of the source_type parameter is FILE, the source_location parameter contains the name of a database directory object that contains the file to be imported, and the source_name parameter contains the name of the file to be imported. You must ensure that the directory for the external source location exists or is created before you use this procedure.
The importFrom( ) (with attributes) procedure reads only from a database directory object that the user has privilege to access. That is, you can access a directory object that you have created using the SQL statement CREATE DIRECTORY, or one to which you have been granted READ access. (See Directory Definitions and Setup for PL/SQL Packages Examples for details about the directories and permissions you need to run the PL/SQL package examples.)
If the value of the source_type parameter is HTTP, the source_location parameter contains the base URL needed to find the directory that contains the object to be imported, and the source_name parameter contains the name of the object to be imported.
This procedure uses the PL/SQL UTL_HTTP package to import media data from an HTTP data source. You can use environment variables to specify the proxy behavior of the UTL_HTTP package. For example, on Linux and UNIX, setting the environment variable http_proxy to a URL specifies that the UTL_HTTP package must use that URL as the proxy server for HTTP requests. Setting the no_proxy environment variable to a domain name specifies that the HTTP proxy server not be used for URLs in the specified domain.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_HTTP package
Pragmas
None.
Exceptions
ORDSourceExceptions.INCOMPLETE_SOURCE_INFORMATION
This exception is raised if you call the importFrom( ) (with attributes) procedure and the value of the source_type parameter is NULL
ORDSourceExceptions.NULL_SOURCE
This exception is raised if you call the importFrom( ) (with attributes) procedure and the value of the dest parameter is NULL or has not been initialized.
Examples
Import image data from the specified external data source into the local source:
DECLARE
image_data BLOB;
img_format VARCHAR2(32):=NULL;
img_mime_type VARCHAR2(80);
BEGIN
SELECT img INTO image_data FROM timg WHERE N=1 FOR UPDATE;
-- import file into a blob
ORDSYS.ORD_IMAGE.importFrom(image_data,'file','IMAGEDIR','testimg.dat',
img_format,img_mime_type);
UPDATE timg SET img=image_data WHERE N=1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/