Videos and Oracle Forms 10g

Greg is a Senior Database Administrator for Lockheed Martin. He can be contacted at [email protected].

Although Oracle Forms is no longer a flagship development tool, Oracle continues to devote resources so users can store unstructured data such as videos, word documents, and XML documents. In this article, I show how you take advantage of unstructured data in the database -- video, in this case -- and access this data from Forms.

Inserting Videos Into Oracle 9i Databases

The other day I showed a client that I could insert and call a video from Oracle Portal. He suggested that I call the video from our in-house application, which is written in Oracle Forms and Reports 10g release 2. I told him that it could be done, but I didn't know how to do it. Consequently, I began searching Google and Ask.com, using search phrases such as "oracle forms 10g video" and the like. Because this didn't give me useful results, I submitted an SR with Oracle support, who offered up suggestions that led me in the right direction.

What I learned is that before you can insert the video, you need a table to store the video. To store videos, you need a datatype that can hold the video. Since most videos are binary and larger than 4000 characters, a varchar2 datatype won't work. Therefore, the best datatype for binary data source is a BlobÑa binary large object that can store up to 4 GB of unstructured or binary data. Listing One creates a table called Multimedia with a Blob field called theblob.

CREATE TABLE MULTIMEDIA
(
  ID INTEGER,
  THEBLOB BLOB,
  FILE_NAME VARCHAR2(50 BYTE) NOT NULL,
  MIME_TYPE VARCHAR2(20 BYTE) NOT NULL
)
TABLESPACE table_DATA1
LOGGING
NOCOMPRESS
LOB (THEBLOB) STORE AS
         (
           TABLESPACE table_DATA2
           ENABLE STORAGE IN ROW
           CHUNK 8192
           RETENTION
          NOCACHE
          INDEX (
                    TABLESPACE  table_index1
                    )
         )
NOCACHE
NOPARALLEL   
NOMONITORING;

Notice that Multimedia is stored in tablespace table_data1, and Blob data is stored in table_data2. Assuming that the two tablespaces are stored on datafiles located on two different drives, this separation helps in reducing the access time for table Multimedia.

To insert the video into the database, I use a procedure called Load_multimedia(). For this procedure, you need a directory path setup pointing to where the video file is located. Listing Two sets up a directory from the database.

DROP DIRECTORY DOCHOME;
CREATE OR REPLACE DIRECTORY 
      DOCHOME AS '/u03/docHome';

The Load_multimedia() procedure inserts a video from the filesystem into the table Multimedia. In this procedure, I use these procedures from the dbms_lob package:

• dbms_lob.fileopen()
• dbms_lob.loadfromfile()
• dbms_lob.fileclose()

which open, load, and close the video file, respectively; see Listing Three. For this example, I hardcoded the directory name and filename, where DOCHOME is the name of my directory and Gulfport.wmv the name of my video file.

CREATE OR REPLACE PROCEDURE load_multimedia
as
     l_blob blob;
     l_bfile bfile;
begin
    insert into multimedia values
     ( 
       1, empty_blob(),'Gulfport.wmv' ,'video/x-ms-wmv'
      )
     returning theblob into l_blob;
      l_bfile := bfilename('DOCHOME','Gulfport.wmv');
      dbms_lob.fileopen(l_bfile);
      dbms_lob.loadfromfile(
                                l_blob, l_bfile,
                                dbms_lob.getlength( l_bfile)
                                );
      dbms_lob.fileclose(l_bfile);
end;
/

Creating a Stored Procedure

Theshow_video() procedure displays videos. This procedure must be called from the web browser using a Database Access Descriptor (DAD) from your HTTP Server. I ran across this procedure (created by Brian Hill) in the OTN Reports Forum. However, I customized it for my needs; see Listing Four.

CREATE OR REPLACE PROCEDURE show_video (p_file_number IN NUMBER)
AS
     l_file_name multimedia.file_name%TYPE;
     l_mime_type multimedia.mime_type%TYPE;
     l_blob_handle BLOB;
BEGIN
     SELECT file_name, mime_type, theblob
        INTO l_file_name, l_mime_type, l_blob_handle
       FROM multimedia
    WHERE ID = p_file_number;

-- build an http header that includes mime type and length
       OWA_UTIL.mime_header (NVL (l_mime_type, 'application/octet'), FALSE);
       HTP.p ('Content-length: ' DBMS_LOB.getlength (l_blob_handle));
       HTP.p ('Content-Disposition: file_name="' l_file_name '"');
       OWA_UTIL.http_header_close;
  -- download the file
      WPG_Docload.Download_File (l_blob_handle);
EXCEPTION
      WHEN OTHERS
        THEN
        HTP.p (SQLCODE SQLERRM);
END show_video;
/

In this procedure, the select statement selects the filename, mime type, and video (Blob), while the table ID equals the parameter passed in with the procedure. I use the HTP package to display a header on the web page. The HTP package consists of a set of procedures and functions to embed HTML inside PL/SQL code. The package WPG_Docload.Download_File() downloads a Blob datatype into a browser; in this case, display the video on the web page.

Creating a Database Access Descriptor (DAD)

According to MetaGlossary.com a database access descriptor (DAD) "is a set of values that specify how an application connects to an Oracle database to fulfill an HTTP request. " In other words, a DAD is used to call a stored procedure from a web browser. In this case, the procedure is show_video(). To set up the DAD:

1. Access the Oracle HTTP Server's main page. Generally, the URL might be http://hostname:port.
2. Select the mod_plsql option by clicking the mod_plsql communication menu link.
3. Click on the Gateway Database Access Descriptor Settings Link.
4. Click the Add Default (blank configuration) link.
5. Enter the DAD name, username, password, and connect string of the database schema to be associated with this DAD. It isn't required that usernames/passwords be entered; if left blank, users must enter them when accessing the videos.
6. Click OK at the top of the page.

Invoking Stored Procedures

In my form, I create a button and a when-button-pressed trigger for that button. Within this trigger, I added the URL to call the procedure and wrapped Web.Show_Document() around it. This URL was built from the previous sections. The URL without the Web.Show_Document() looks like this:

http://hostname:port/mod_plsql _directory/data_name/procedure_name

where mod_plsql_directory is the directory alias of themod_plsql module of the HTTP Server (usually this is pls), dad_name is the name of the DAD created in step 5, of the section titled "Create a Database Access Descriptor (DAD). " The procedure_name is the name of the database procedure created to show the video on the Web using Oracle's HTP package. Therefore, the URL looks like this:

http://localhost:80/pls/video/show_video?p_file_number=1

where p_file_number is the name of the parameter that's being passed to procedure show_video(). Now when I wrap the previous discussed URL with Web.Show_document() (Oracle's Forms built-in package) it looks like:

Web.Show_Document('http://localhost:80/pls/video/show_video?p_file_number=1','_blank');

where p_file_number=1 is the parameter that is passed to the procedure show_video() and _blank opens a new window to display the video.

Conclusion

It seems that you can do most anything with the Web.Show_Document() package. Besides being able to call a stored procedure by wrapping Web.Show_Document() around a URL, you can use it to call Oracle Reports and Java Server Pages (JSP) all from Oracle Forms. All in all, Oracle has made Forms 10g easier to work with Java than in past releases.