Procedures Common to All Oracle Multimedia PL/SQL Package APIs

2 Procedures Common to All Oracle Multimedia PL/SQL Package APIs

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.

These common procedures are defined in this chapter because they are identical for each of the following PL/SQL packages:

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:

2.1 Examples for 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	TAUD Table Definition
TDOC	Used to demonstrate the ORD_DOC PL/SQL procedures	TDOC Table Definition
TIMG	Used to demonstrate the ORD_IMAGE PL/SQL functions and procedures	TIMG Table Definition
TVID	Used to demonstrate the ORD_VIDEO PL/SQL procedures	TVID Table Definition

When reading through the examples, use the directory definitions and these table definitions with the example for each Multimedia PL/SQL function or procedure.

2.1.1 Directory Definitions and Setup for PL/SQL Packages Examples

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.

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;

2.1.2 TAUD Table Definition

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;

2.1.3 TDOC Table Definition

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;

2.1.4 TIMG Table Definition

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;

2.1.5 TVID Table Definition

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;

2.2 Common Procedures in PL/SQL Packages: export( )

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

src: The source BLOB location of the data to be exported.
dest_location: The file directory object into which the source data is to be exported.
dest_name: The name of the destination file.

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

2.3 Common Procedures in PL/SQL Packages: importFrom( )

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

dest: The BLOB location in which to import the data.
source_type: The type of the source data (FILE or URL).
source_location: The location from which the source data is to be imported.
source_name: The name of the source data.

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.

2.4 Common Procedures in PL/SQL Packages: importFrom( ) (with attributes)

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

dest: The BLOB location in which to import the data.
source_type: The type of the source data (FILE or URL).
source_location: The location from which the source data is to be imported.
source_name: The name of the source data.
format: The format of the data.
mime_type: The MIME type of the data.

Usage Notes

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.)