5
Creating PL/SQL Upload Templates

5.1 Overview of Uploading Media Data

Oracle interMedia Annotator can use two different methods to upload the media data and associated annotation to your database: import and remote.

In the import upload method, the media source must be visible to the database server (either in a file system or through an HTTP stream). The media source will be loaded directly from the file system to the database. The import upload method uses the Oracle interMedia import( ) method.

In the remote upload method, the media source does not have to be visible to the database. The file is loaded into interMedia Annotator, which loads the file into the database through JDBC calls. The remote upload method uses the ${MANN_UPLOAD_SRC} interMedia Annotator-specific keyword.

Note the following about the upload methods:

• If you use the import upload method and the media source is in a file system, you must specify the path to the directory where the media file resides. The directory path should be specified from the point of view of the Oracle database server to which you are uploading. For example, if you are running interMedia Annotator on Windows NT and you want to upload data to an Oracle database that is running on a UNIX platform, the media data must reside in a directory that can be accessed by both machines. You can do this by mounting a UNIX directory on the server into a Windows NT network drive.

• If you use the import upload method and the media source is an HTTP stream, you can choose either to import the media data into the database, or to store the URL in the database.

• If you use the remote upload method and you are using the JDBC Thin driver, your upload performance may be poor, especially if you are uploading large files.

Using a combination of PL/SQL and keywords specific to Oracle interMedia Annotator, you upload the media data and the annotation into a table in an Oracle database. The table must have at least one column of the appropriate interMedia object type, such as ORDSYS.ORDAudio for annotations for audio files, ORDSYS.ORDImage for annotations for image files, or ORDSYS.ORDVideo for annotations for video files.

You can insert the data into a new row in the table, or you can update an existing row in the table.

Section 5.3 describes the interMedia Annotator-specific keywords.

5.2 Creating a PL/SQL Upload Template

Use any text editor to create the PL/SQL Upload Template. Note the following about the structure of a PL/SQL Upload Template:

• The PL/SQL Upload Template begins with a list of DML and DDL statements. Using this list is optional, depending on your needs.

• One anonymous PL/SQL block follows the list. You cannot have more than one anonymous PL/SQL block, and nothing should appear in the PL/SQL Upload Template after you end the block.

  The anonymous PL/SQL block contains both standard PL/SQL code and keywords that are specific to interMedia Annotator. For more information on the keywords, see Section 5.3. For more information on writing PL/SQL code, see PL/SQL User's Guide and Reference.

Depending on the platform of the database server, there may be a limit on the maximum size of the anonymous PL/SQL block. If you encounter this problem, you can work around it by packaging some of your statements into PL/SQL procedures in order to reduce the size of your PL/SQL block.

5.3 Annotator-Specific Keywords

In addition to standard PL/SQL calls, the PL/SQL Upload Templates contain Annotator-specific keywords. The keywords are delimited by a dollar sign and a left brace (${ ) at the beginning of a keyword and a right brace at the end of the keyword (}). These keywords are interpreted by the interMedia Annotator preprocessor, which then generates the appropriate PL/SQL code.

You can use attributes names as keywords, as described in Section 5.3.1. In addition, you can use the keywords described in Section 5.3.2 through Section 5.3.6.

audioObj.setMimeType('${MEDIA_SOURCE_MIME_TYPE}');
INSERT INTO movieTable VALUES (videoSeq.NEXTVAL -- VideoID
                      '${MEDIA_SOURCE_FILENAME}', 
                      '${MEDIA_TITLE}',
                      '${MOVIE_DIRECTOR}', 
                      '${MOVIE_CAST}',
                      ORDSYS.ORDVIDEO.init());

${MANN_BEGIN_ITERATE} TextSampleAnn
     INSERT INTO txtSampleTable VALUES (currClipId,  -- VideoID 
                                        trackId,
                                        ROUND(${(seconds)SAMPLE_TIMESTAMP}, 4),
                                        '${TEXTSAMPLE_VALUE}' );

${MANN_BEGIN_IFDEF} MEDIA_SOURCE_MIME_TYPE
videoObj.setMimeType('${MEDIA_SOURCE_MIME_TYPE}');
${MANN_END_IFDEF}

${MANN_BEGIN_IFEQUALS} MEDIA_SOURCE_MIME_TYPE audio/basic
audioObj.setMimeType('${MEDIA_SOURCE_MIME_TYPE}');
${MANN_END_IFEQUALS}

${MANN_UPLOAD_SRC} videoObj.source.localData

${MANN_UPLOAD_XML} videoObj.comments

5.4 Complete PL/SQL Upload Template Example

Example 5-7 contains a sample PL/SQL Upload Template. It uploads a video object and its associated annotation to an Oracle database table named MediaTable. The sample contains one anonymous PL/SQL block containing a mix of PL/SQL calls and keywords specific to interMedia Annotator.

DECLARE
   videoObj       ORDSYS.ORDVIDEO;
   ctx            RAW(64) := NULL;
BEGIN
   INSERT INTO MediaTable VALUES (
      1,
      ORDSYS.ORDVIDEO.init();
   SELECT M.mediaSource INTO videoObj 
   FROM   MediaTable M
   WHERE  M.MediaId = 1
   FOR UPDATE;
   ${MANN_BEGIN_IFDEF} MEDIA_SOURCE_MIME_TYPE
   videoObj.setMimeType('${MEDIA_SOURCE_MIME_TYPE}');
   ${MANN_END_IFDEF}
   ${MANN_UPLOAD_SRC} videoObj.source.localData
   ${MANN_UPLOAD_XML} videoObj.comments
   UPDATE MediaTable M SET M.mediaSource = videoObj
   WHERE  M.mediaId = 1;
END;

In addition to setting the MEDIA_SOURCE_MIME_TYPE as shown in Example 5-7, you can set other attributes, such as the MEDIA_TITLE, MEDIA_SOURCE_DIRECTORY, and MEDIA_SOURCE_FILE_FORMAT. See Oracle interMedia User's Guide and Reference for more information.

5.5 Saving Files

After you have written your PL/SQL Upload Template, save it with the suffix .ofm. Oracle interMedia Annotator uses the following default directory for PL/SQL Upload Templates:

• On UNIX: $ORACLE_HOME/ord/Annotator/ofm

• On Windows: ORACLE_HOME\ord\Annotator\ofm

To change the default directory, modify the value of the ofmDirectory parameter in the Annotator.prefs file.