7
Creating PL/SQL Upload Templates

interMedia Annotator can upload media data and an associated annotation into an Oracle database where Oracle interMedia has been installed. It does so through an Oracle PL/SQL Upload Template, which contains both PL/SQL calls and Annotator-specific keywords.

You create your own PL/SQL Upload Templates. Advanced users with PL/SQL experience can write PL/SQL Upload Templates using a text editor.

7.1 Creating Upload Templates Manually

interMedia Annotator users with experience in PL/SQL and JDBC may want to create their own PL/SQL Upload Templates instead of using the PL/SQL Template Wizard in the Annotator GUI (see Chapter 4 for more information). You can create a PL/SQL Upload Template using any text editor.

7.1.1 Structure of Upload Templates

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 interMedia Annotator-specific keywords. For more information on the keywords, see Section 7.1.2. 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.

7.1.2 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 (${ and }). These keywords are interpreted by the Annotator preprocessor, which interprets the keywords and generates the appropriate PL/SQL code.

Note:

An Annotator-specific keyword must appear on its own line in the PL/SQL Upload Template. You cannot have multiple keywords on the same line.

The following sections provide more information on the keywords.

7.1.2.1 Attribute Values

Instead of hard-coding values for specific attributes in your PL/SQL Upload Template, you provide the name of the attribute, enclosed by the ${ and } characters. This tells the preprocessor to get the actual value of the attribute from the current annotation, and to use that value to replace the keyword in the PL/SQL Upload Template. This simple replacement lets you use the same PL/SQL Upload Template for multiple annotations.

Example 7-1 shows keywords that will later be replaced with attribute values.

Example 7-1 Attribute Values as Keywords

audioObj.setMimeType('${MEDIA_SOURCE_MIME_TYPE}');
INSERT INTO SongsTable VALUES('${AUDIO_CD_TRACK_CDID}');

7.1.2.2 ${MANN_BEGIN_ITERATE} and ${MANN_END_ITERATE}

The ${MANN_BEGIN_ITERATE} and ${MANN_END_ITERATE} keywords indicate that the code enclosed by the keywords should be repeated for each sub-annotation of the given type. The name of the annotation type follows the ${MANN_BEGIN_ITERATE} keyword.

Example 7-2 shows a block of code that will be run for each AudioCDTrackAnn annotation that exists as a sub-annotation of the current annotation.

Example 7-2 ${MANN_BEGIN_ITERATE} and ${MANN_END_ITERATE}

${MANN_BEGIN_ITERATE} AudioCDTrackAnn
INSERT INTO SongsTable VALUES('${AUDIO_CD_TRACK_CDID}',
                              '${AUDIO_CD_TRACK_ID}');
${MANN_END_ITERATE}

7.1.2.3 ${MANN_BEGIN_TRACK} and ${MANN_END_TRACK}

The ${MANN_BEGIN_TRACK} and ${MANN_END_TRACK} keywords indicate that the code enclosed by the keywords should be run on the first instance of a sub-annotation of the given class. The name of the annotation type follows the ${MANN_BEGIN_TRACK} keyword.

Example 7-3 shows a block of code that will be run upon the first AudioCDTrackAnn annotation that exists as a sub-annotation of the current annotation.

Example 7-3 ${MANN_BEGIN_TRACK} and ${MANN_END_TRACK}

${MANN_BEGIN_TRACK} AudioCDTrackAnn
INSERT INTO SongsTable VALUES('${AUDIO_CD_TRACK_CDID}',
                              '${AUDIO_CD_TRACK_ID}');
${MANN_END_TRACK}

7.1.2.4 ${MANN_BEGIN_IFDEF} and ${MANN_END_IFDEF}

The ${MANN_BEGIN_IFDEF} and ${MANN_END_IFDEF} keywords indicate that the code enclosed by the keywords should be run only if the current annotation has a defined value for a given attribute. The name of the attribute follows the ${MANN_BEGIN_IFDEF} keyword.

Example 7-4 shows a block of code that will be run only if the MEDIA_SOURCE_MIME_TYPE attribute is defined in the current annotation.

Example 7-4 ${MANN_BEGIN_IFDEF} and ${MANN_END_IFDEF}

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

7.1.2.5 ${MANN_BEGIN_IFEQUALS} and ${MANN_END_IFEQUALS}

The ${MANN_BEGIN_IFEQUALS} and ${MANN_END_IFEQUALS} keywords indicate that the code enclosed by the keywords should be run only if the current annotation contains a given attribute of a given value. The name of the attribute and the value follow the ${MANN_BEGIN_IFEQUALS} keyword. The string comparison is case-sensitive.

Example 7-5 shows a block of code that will be run only if the MEDIA_SOURCE_MIME_TYPE attribute is defined as audio/basic in the current annotation.

Example 7-5 ${MANN_BEGIN_IFEQUALS} and ${MANN_END_IFEQUALS}

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

7.1.2.6 ${MANN_UPLOAD_SRC}

The ${MANN_UPLOAD_SRC} keyword indicates that the media source data associated with the current annotation should be uploaded to the current Oracle database table using JDBC; the file is loaded into Annotator, and Annotator loads the file into the database. The name of the server-side object and attribute (of the BLOB type) follows the ${MANN_UPLOAD_SRC} keyword.

Upload performance with the ${MANN_UPLOAD_SRC} keyword may be slow if you are using the JDBC Thin driver to upload a large media source, or if you have a slow network connection. You may get better results by using the interMedia import( ) method. See Section 4.1.2 for more information on the differences between the two upload options. See Oracle interMedia User's Guide and Reference for more information on the import( ) method.

Example 7-6 shows a block of code that will upload the current media source data to the source.localData attribute of the server-side interMedia object videoObj.

Example 7-6 ${MANN_UPLOAD_SRC}

${MANN_UPLOAD_SRC} videoObj.source.localData

7.1.2.7 ${MANN_UPLOAD_XML}

The ${MANN_UPLOAD_XML} keyword indicates that the current annotation should be uploaded to the current Oracle database table. The annotation should be uploaded to a CLOB in an Oracle interMedia object. The name of the server-side object and CLOB attribute follows the ${MANN_UPLOAD_XML} keyword.

Example 7-7 shows a block of code that will upload the current annotation to the comments attribute of the server-side interMedia object videoObj.

Example 7-7 ${MANN_UPLOAD_XML}

${MANN_UPLOAD_XML} videoObj.comments

For more information on Oracle interMedia APIs, see Oracle interMedia User's Guide and Reference.

7.1.3 Saved Files

Once you have written your PL/SQL Upload Template, save it with the suffix .ofm. The default directory that Annotator uses for PL/SQL Upload Templates is <ORACLE_HOME>\ord\Annotator\ofm. To change the default directory, see Section 4.1.7.

See Section 4.2 for information on how to run a PL/SQL Upload Template through the Annotator GUI.

7.1.4 Complete PL/SQL Upload Template Example

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

Example 7-8 PL/SQL Upload Template Sample

DECLARE
   videoObj       ORDSYS.ORDVIDEO;
   ctx            RAW(4000) := NULL;
BEGIN
   INSERT INTO MediaTable VALUES (
      1,
      ORDSYS.ORDVIDEO(
         '${MEDIA_TITLE}',
         ORDSYS.ORDSource(EMPTY_BLOB(),
            NULL,
            '${MEDIA_SOURCE_DIRECTORY}',
            '${MEDIA_SOURCE_FILENAME}',
            NULL,NULL),
         '${MEDIA_SOURCE_FILE_FORMAT}',
         '${MEDIA_SOURCE_MIME_TYPE}',
         EMPTY_CLOB(), NULL, NULL, NULL, NULL, NULL, NULL, '',
         NULL, NULL)
   );

   SELECT M.mediaSource INTO videoObj 
   FROM   MediaTable M
   WHERE  M.MediaId = 1
   FOR UPDATE;

   ${MANN_UPLOAD_SRC} videoObj.source.localData
   ${MANN_UPLOAD_XML} videoObj.comments

   UPDATE MediaTable M SET M.mediaSource = videoObj
   WHERE  M.mediaId = 1;

END;

7.2 Editing Existing PL/SQL Upload Templates

Whether you use the PL/SQL Template Wizard or you write your own PL/SQL Upload Templates, you can edit your PL/SQL Upload Templates using a text editor.

You cannot use the PL/SQL Template Wizard to edit an existing PL/SQL Upload Template; it can create only new PL/SQL Upload Templates.

7Creating PL/SQL Upload Templates