4 Oracle Multimedia Code Wizard Sample Application for the PL/SQL Gateway

4.1 Running the Code Wizard Sample Application

To use the Code Wizard sample application to create and test media access procedures, you must perform these steps:

1. Create a new database access descriptor (DAD) or choose an existing DAD for use with the Code Wizard.

2. Authorize use of the DAD using the Code Wizard's administration function.

3. Create and test media upload and retrieval procedures.

The following sections describe these steps and other related topics in more detail.

4.2 Description of the Code Wizard Sample Application

The Oracle Multimedia Code Wizard sample application lets you create PL/SQL stored procedures for the PL/SQL Gateway to upload and retrieve media data (images, audio, video, and general media) stored in a database using these Oracle Multimedia object types and their respective methods:

• ORDImage

• ORDAudio

• ORDVideo

• ORDDoc

The Code Wizard guides you through a series of self-explanatory steps to create either a media retrieval procedure or a media upload procedure. You can create and compile standalone media access procedures. Or, you can create the source of media access procedures for inclusion in a PL/SQL package. Finally, after creating media access procedures, you can customize them to meet your specific application requirements.

These processes are similar to how the Oracle Multimedia PL/SQL Web Toolkit Photo Album application uses the insert_new_photo procedure as the image upload procedure, and the deliver_media procedure as the image retrieval procedure (see Section 3.1).

The following subsections describe how to use the Code Wizard application:

• Creating a New DAD or Choosing an Existing DAD

• Authorizing a DAD

• Creating and Testing Media Upload and Retrieval Procedures

• Creating a Media Upload Procedure

• Creating a Media Retrieval Procedure

• Using the PL/SQL Gateway Document Table

• How Time Zone Information Is Used to Support Browser Caching

CREATE TABLE document-table-name
  ( name           VARCHAR2(256) UNIQUE NOT NULL,
    mime_type      VARCHAR2(128),
    doc_size       NUMBER,
    dad_charset    VARCHAR2(128),
    last_updated   DATE,
    content_type   VARCHAR2(128),
    blob_content   BLOB )
-- 
-- store BLOBs as SecureFile LOBs
-- 
LOB(blob_content)  STORE AS SECUREFILE;

4.3 Sample Session 1: Using Images

The following sample session uses the SCOTT schema to demonstrate the creation of image media upload and retrieval procedures. To use a different schema, substitute a different schema name and password. Or, if you have changed the password for the SCOTT schema, use your new password.

This sample session assumes the Oracle Multimedia Code Wizard has been installed in the ORDSYS schema.

Perform these steps:

Step 1   Create a table to store images for the application by starting SQL*Plus and connecting to the SCOTT (or other) schema in the database.

For example:

sqlplus SCOTT [@<connect_identifer>]

Enter password: password

SQL> CREATE TABLE cw_images_table( id NUMBER PRIMARY KEY,
                                  description VARCHAR2(30) NOT NULL,
                                  location VARCHAR2(30),
                                  image ORDSYS.ORDIMAGE )
--
-- store media as SecureFile LOBs
--
LOB(image.source.localdata)  STORE AS SECUREFILE;

Step 2   Create the SCOTTCW DAD to be used to create the procedures.

1. Set your Web browser to the Oracle HTTP Server Home page. Select PL/SQL Properties in the Administration page to open the mod_plsql Services page.

2. On the mod_plsql Services page, scroll to the DAD Status section. Then, click Create to open the DAD Type page.

3. Select the DAD type to be General. Then, click Next to open the Database Connection page.

4. Enter /scottcw in the DAD Name field. Enter SCOTT for the database account, and leave the password blank. Enter the connection information in the Database Connectivity Information section. Enter ORDCWPKG.MENU in the Default page field, and leave the other fields blank. Then, click Next to open the Document, Alias, and Session page.

5. On the Document, Alias, and Session page, enter MEDIA_UPLOAD_TABLE for the Document Table. Then, click Apply.

6. Restart Oracle HTTP Server for the changes to take effect.

Step 3   Authorize the use of the SCOTTCW DAD and SCOTT schema with the Code Wizard.

1. Enter the Code Wizard's administration URL into your browser's location bar, then enter the ORDSYS user name and password when prompted by the browser, for example:

   http://<hostname>:<port-number>/ordcwadmin
   

2. Select the DAD authorization function from the Code Wizard's Main menu and click Next. Enter the name of the demonstration DAD, SCOTTCW, and the user name SCOTT, then click Apply. Click Done when the confirmation window is displayed.

Step 4   Change DADs to the SCOTTCW DAD.

1. Click Change DAD from the Code Wizard's Main menu.

2. Click Change to SCOTTCW, if it is not already selected, then click Next.

3. Enter the user name SCOTT and the password for the user SCOTT when prompted for the user name and password, then click OK.

   The Main menu now displays the current DAD as SCOTTCW and the current schema as SCOTT.

Step 5   Create and test the media upload procedure.

Click Create media upload procedure from the Main menu, then click Next.

1. Select the database table and procedure type.

   1. Click the CW_IMAGES_TABLE database table.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the PL/SQL document upload table.

   If there are no document tables in the SCOTT schema, the Code Wizard displays a message indicating this situation. In this case, accept the default table name provided, CW_SAMPLE_UPLOAD_TABLE, then click Next.

   If there are existing document tables, but the CW_SAMPLE_UPLOAD_TABLE is not among them, click Create new document table, accept the default table name provided, CW_SAMPLE_UPLOAD_TABLE, then click Next.

   If the CW_SAMPLE_UPLOAD_TABLE document table already exists, ensure that the Use existing document table and the CW_SAMPLE_UPLOAD_TABLE options are selected. Click Next.

3. Select the data access and media columns.

   1. Click IMAGE (ORDIMAGE).

   2. Click ID (Primary key).

   3. Click Conditional insert or update.

   4. Click Next.

4. Select additional columns and procedure names.

   1. Ensure that DESCRIPTION checkmarked because this column has a NOT NULL constraint. (The LOCATION column is not checkmarked by default as there are no constraints on this column.)

   2. Accept the procedure name provided, UPLOAD_CW_IMAGES_TABLE_IMAGE.

   3. Click Create procedure in the database.

   4. Click Next.

5. Review the following selected procedure creation options that are displayed:

   Procedure type:        Standalone
   Table name:            CW_IMAGES_TABLE
   Media column(s):       IMAGE (ORDIMAGE)
   Key column:            ID
   Additional column(s):  DESCRIPTION
   Table access mode:     Conditional update or insert
   Procedure name:        UPLOAD_CW_IMAGES_TABLE_IMAGE
   Function:              Create procedure in the database
   

   Click Finish.

6. Compile the procedure and review the generated source information.

   The Code Wizard displays this message:

   Procedure created successfully: UPLOAD_CW_IMAGES_TABLE_IMAGE
   

   1. At the option Click to display generated source:, click View to view the generated source in another window. A copy of the generated source is shown at the end of Step 5, substep 6g.

   2. Close the window after looking at the generated source.

   3. Accept the DAD: name provided, SCOTTCW, then click Test to produce another window that displays a template file upload form that you can use to test the generated procedure.

   4. To customize the template file upload form, select Save As... from your browser's File menu to save the HTML source for editing.

   5. To test the template upload form, enter this information:

      • For the ID: column, enter the number 1 as the row's primary key.

      • For the IMAGE column, click Browse... and choose an image file to upload to the database.

      • For the DESCRIPTION column, enter a brief description of the image.

      • Click Upload media.

      The Code Wizard displays a template completion window with the heading Oracle Multimedia Code Wizard: Template Upload Procedure, and, if the procedure is successful, the message: Media uploaded successfully.

   6. Close the window.

   7. Click Done on the Compile procedure and review generated source window to return to the Main menu of the Code Wizard.

   Example 4-1 shows a copy of the generated image upload procedure:

   Example 4-1 Image Upload Procedure Generated in Sample Session 1

   CREATE OR REPLACE PROCEDURE UPLOAD_CW_IMAGES_TABLE_IMAGE
     ( in_ID IN VARCHAR2,
       in_IMAGE IN VARCHAR2 DEFAULT NULL,
       in_DESCRIPTION IN VARCHAR2 DEFAULT NULL )
   AS
     local_IMAGE ORDSYS.ORDIMAGE := ORDSYS.ORDIMAGE.init();
     local_ID CW_IMAGES_TABLE.ID%TYPE := NULL;
     upload_size     INTEGER;
     upload_mimetype VARCHAR2( 128 );
     upload_blob     BLOB;
   BEGIN
     --
     -- Update the existing row.
     --
     UPDATE CW_IMAGES_TABLE mtbl
       SET mtbl.IMAGE = local_IMAGE,
           mtbl.DESCRIPTION = in_DESCRIPTION
       WHERE mtbl.ID = in_ID
       RETURN mtbl.ID INTO local_ID;
     --
     -- Conditionally insert a new row if no existing row is updated.
     --
     IF local_ID IS NULL
     THEN
       --
       -- Insert the new row into the table.
       --
       INSERT INTO CW_IMAGES_TABLE ( ID, IMAGE, DESCRIPTION )
         VALUES ( in_ID, local_IMAGE, in_DESCRIPTION );
     END IF;
     --
     -- Select Oracle Multimedia object(s) for update.
     --
     SELECT mtbl.IMAGE INTO local_IMAGE
       FROM CW_IMAGES_TABLE mtbl WHERE mtbl.ID = in_ID FOR UPDATE;
     --
     -- Store media data for the column in_IMAGE.
     --
     IF in_IMAGE IS NOT NULL
     THEN
       SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
              upload_size, upload_mimetype, upload_blob
         FROM CW_IMAGE_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
       IF upload_size > 0
       THEN
         dbms_lob.copy( local_IMAGE.source.localData, 
                        upload_blob, 
                        upload_size );
         local_IMAGE.setLocal();
         BEGIN
           local_IMAGE.setProperties();
         EXCEPTION
           WHEN OTHERS THEN
             local_IMAGE.contentLength := upload_size;
             local_IMAGE.mimeType := upload_mimetype;
         END;
       END IF;
       DELETE FROM CW_IMAGE_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
     END IF;
     --
     -- Update Oracle Multimedia objects in the table.
     --
     UPDATE CW_IMAGES_TABLE mtbl
       SET mtbl.IMAGE = local_IMAGE
       WHERE mtbl.ID = in_ID;
     --
     -- Display the template completion message.
     --
     htp.print( '<html>' );
     htp.print( '<title>Oracle Multimedia Code Wizard: Template Upload
   Procedure</title>' );
     htp.print( '<body>' );
     htp.print( '<h2> Oracle Multimedia Code Wizard:
   Template Upload Procedure</h2>' );
     htp.print( 'Media uploaded successfully.' );
     htp.print( '</body>' );
     htp.print( '</html>' );
   END UPLOAD_CW_IMAGES_TABLE_IMAGE;
   

   The image upload procedure shown in Example 4-1 declares these input parameters and variables:

   1. In the declaration section, the procedure declares three input parameters: in_ID, in_IMAGE, and in_DESCRIPTION, then initializes the latter two to NULL.

   2. In the subprogram section, the following variables are declared:

      • The variable local_IMAGE is assigned the data type ORDSYS.ORDIMAGE and initialized with an empty BLOB using the ORDIMAGE.init( ) method.

      • The variable local_ID takes the same data type as the ID column in the table CW_IMAGES_TABLE and is initialized to NULL.

      • Three additional variables are declared upload_size, upload_mimetype, and upload_blob, which are later given values from comparable column names doc_size, mime_type, and blob_content from the document table CW_IMAGE_UPLOAD_TABLE, using a SELECT statement in preparation for copying the content of the image BLOB data to the ORDSYS.ORDIMAGE.source.localData attribute.

   Within the outer BEGIN...END executable statement section, the following operations are executed:

   1. Update the existing row in the table CW_IMAGES_TABLE for the IMAGE and DESCRIPTION columns and return the value of local_ID where the value of the ID column is the value of the in_ID input parameter.

   2. If the value returned of local_ID is NULL, conditionally insert a new row into the table CW_IMAGES_TABLE and initialize the instance of the ORDImage object type in the image column with an empty BLOB.

   3. Select the ORDImage object column IMAGE in the table CW_IMAGES_TABLE for update where the value of the ID column is the value of the in_ID input parameter.

   4. Select a row for the doc_size, mime_type, and blob_content columns from the document table and pass the values to the upload_size, upload_mimetype, and upload_blob variables where the value of the document table Name column is the value of the in_IMAGE input parameter.

   5. Perform a DBMS_LOB copy of the BLOB data from the table CW_IMAGE_UPLOAD_TABLE into the ORDSYS.ORDIMAGE.source.localData attribute, then call the setLocal( ) method to indicate that the image data is stored locally in the BLOB, and ORDImage methods are to look for corresponding data in the source.localData attribute.

   6. In the inner executable block, call the ORDImage setProperties( ) method to read the image data to get the values of the object attributes and store them in the image object attributes for the ORDImage object.

   7. If the setProperties( ) call fails, catch the exception and call the contentLength( ) method to get the size of the image and call the mimeType( ) method to get the MIME type of the image.

   8. Delete the row of data from the document table CW_IMAGE_UPLOAD_TABLE that was copied to the row in the table CW_IMAGES_TABLE where the value of the Name column is the value of the in_IMAGE input parameter.

   9. Update the ORDImage object IMAGE column in the table CW_IMAGES_TABLE with the content of the variable local_IMAGE where the value of the ID column is the value of the in_ID input parameter.

   10. Display a completion message on the HTML page to indicate that the media uploaded successfully using the htp.print function from the PL/SQL Web Toolkit.

Step 6   Create and test a media retrieval.

Select Create media retrieval procedure from the Main menu, then click Next.

1. Select the database table and procedure type.

   1. Click CW_IMAGES_TABLE.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the media column and key column.

   1. Click IMAGE (ORDIMAGE).

   2. Click ID (Primary key).

   3. Click Next.

3. Select the procedure name and parameter name.

   1. Accept the procedure name provided, GET_CW_IMAGES_TABLE_IMAGE.

   2. Accept the parameter name provided, MEDIA_ID.

   3. Click Create procedure in the database.

   4. Click Next.

4. Review the following selected procedure creation options:

5. Procedure type:        Standalone
   Table name:            CW_IMAGES_TABLE
   Media column(s):       IMAGE (ORDIMAGE)
   Key column:            ID
   Procedure name:        GET_CW_IMAGES_TABLE_IMAGE
   Parameter Name:        MEDIA_ID
   Function:              Create procedure in the database
   

   Click Next.

6. Compile the procedure and review the generated source.

   The Code Wizard displays this message:

   Procedure created successfully: GET_CW_IMAGES_TABLE_IMAGE
   

   1. Click View to view the generated source in another window. Close the window after looking at the generated source. A copy of the generated source is shown at the end of Step 6, substep 5e.

   2. Review the URL format used to retrieve images using the GET_CW_IMAGES_TABLE_IMAGE procedure.

   3. Enter the number 1 as the Key parameter, then click Test to test the procedure by retrieving the image uploaded previously.

      The retrieved image is displayed in another window.

   4. Close the window.

   5. Click Done to return to the Main menu.

   Example 4-2 shows a copy of the generated image retrieval procedure:

   Example 4-2 Image Retrieval Procedure Generated in Sample Session 1

   CREATE OR REPLACE PROCEDURE GET_CW_IMAGES_TABLE_IMAGE (
    MEDIA_ID IN VARCHAR2 )
   AS
     localObject ORDSYS.ORDIMAGE;
     localBlob  BLOB;
     localBfile BFILE;
     httpStatus NUMBER;
     lastModDate VARCHAR2(256);
   BEGIN
     --
     -- Retrieve the object from the database into a local object.
     --
     BEGIN
       SELECT mtbl.IMAGE INTO localObject FROM CW_IMAGES_TABLE mtbl
         WHERE mtbl.ID = MEDIA_ID;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         ordplsgwyutil.resource_not_found( 'MEDIA_ID', MEDIA_ID );
         RETURN;
     END;
   
     --
     -- Check the update time if the browser sent an If-Modified-Since header.
     --
     IF ordplsgwyutil.cache_is_valid( localObject.getUpdateTime() )
     THEN
       owa_util.status_line( ordplsgwyutil.http_status_not_modified );
       RETURN;
     END IF;
   
     --
     -- Figure out where the image is.
     --
     IF localObject.isLocal() THEN
       --
       -- Data is stored locally in the localData BLOB attribute.
       --
       localBlob := localObject.getContent();
       owa_util.mime_header( localObject.getMimeType(), FALSE );
       ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
       owa_util.http_header_close();
       IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN
         wpg_docload.download_file( localBlob );
       END IF;
     ELSIF UPPER( localObject.getSourceType() ) = 'FILE' THEN
   
       --
       -- Data is stored as a file from which ORDSource creates 
       -- a BFILE.
       --
       localBfile  := localObject.getBFILE();
       owa_util.mime_header( localObject.getMimeType(), FALSE );
       ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
       owa_util.http_header_close();
       IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN
         wpg_docload.download_file( localBfile );
       END IF;
   
     ELSIF UPPER( localObject.getSourceType() ) = 'HTTP' THEN    --
       -- The image is referenced as an HTTP entity, so we have to 
       -- redirect the client to the URL that ORDSource provides.
       --
       owa_util.redirect_url( localObject.getSource() );
     ELSE
       --
       -- The image is stored in an application-specific data
       -- source type for which no default action is available.
       --
       NULL;
     END IF;
   END GET_CW_IMAGES_TABLE_IMAGE;
   

   The image retrieval procedure shown in Example 4-2 declares these input parameters and variables:

   1. In the declaration section, the procedure declares one input parameter: MEDIA_ID.

   2. In the subprogram section, the following variables are declared:

      • The variable localObject is assigned the data type ORDSYS.ORDIMAGE.

      • The variable localBlob is a BLOB data type, the variable localBfile is a BFILE data type, httpStatus is a NUMBER, and lastModDate is a VARCHAR2 with a maximum size of 256 characters.

   Within the outer BEGIN...END executable statement section, the following operations are executed:

   1. Select the ORDImage object column IMAGE in the table CW_IMAGES_TABLE where the value of the ID column is the value of the MEDIA_ID input parameter.

   2. In the inner executable block, when no data is found, raise an exception and call the resource_not_found function of the PL/SQL Gateway and get the value of the MEDIA_ID input parameter.

   3. Check the update time if the browser sent an If-Modified-Since header by calling the getUpdateTime( ) method passed into the cache_is_valid function of the PL/SQL Gateway.

   4. If the cache is valid, send an HTTP status code to the client using the PL/SQL Web Toolkit owa_util package status_line procedure passing in the call to the http_status_not_modified function.

   5. Determine where the image data is stored; call the ORDImage isLocal( ) method, which returns a Boolean expression of true if the image data is stored locally in the BLOB, then get the handle to the local BLOB.

      • If the value is true, assign the variable localBlob the ORDImage getContent( ) method to get the handle of the local BLOB containing the image data.

      • Call the ORDImage getMimeType( ) method to determine the image's MIME type and pass this to the owa_util.mime_header procedure and keep the HTTP header open.

      • Call the ORDImage getUpdateTime( ) method to get the time the image was last modified and pass this to the ordplsgwyutil.set_last_modified procedure.

      • Close the HTTP header by calling the owa_util.http_header_close( ) procedure.

      • Call the owa_util.get_cgi_env procedure and if the value of the request method is not HEAD, then use the wpg_docload.download_file procedure to pass in the value of localBlob that contains the LOB locator of the BLOB containing the image data to download the image from the database.

   6. If the ORDImage isLocal( ) method returns false, call the ORDImage getSourceType( ) method to determine if the value is FILE; if so, then the image data is stored as an external file on the local file system. Then, get the LOB locator of the BFILE containing the image data.

      • Assign the variable localBfile the ORDImage getBfile( ) method to get the LOB locator of the BFILE containing the image data.

      • Call the ORDImage getMimeType( ) method to determine the image's MIME type and pass this to the owa_util.mime_header procedure and keep the HTTP header open.

      • Call the ORDImage getUpdateTime( ) method to get the time the image was last modified and pass this to the ordplsgwyutil.set_last_modified procedure.

      • Close the HTTP header by calling the owa_util.http_header_close() procedure.

      • Call the owa_util.get_cgi_env procedure and if the value of the request method is not HEAD, then use the wpg_docload.download_file procedure to pass in the value of localBfile that contains the LOB locator of the BFILE containing the image data to download the image from the file.

   7. If the ORDImage isLocal( ) method returns false, call the ORDImage getSourceType( ) method to determine if the value is HTTP; if so, then the image data is stored at an HTTP URL location, which then redirects the client to the URL that ORDSource provides using the owa_util.redirect_url procedure.

   8. If the ORDImage isLocal( ) method returns false, call the ORDImage getSourceType( ) method to determine if the value is FILE or HTTP; if it is neither, then the image is stored in an application-specific data source type that is not recognized or supported by Oracle Multimedia.

4.4 Sample Session 2: Using Multiple Object Columns

The following sample session uses the SCOTT schema to demonstrate the creation of a multiple media (multiple Oracle Multimedia object columns) upload procedure and a single media retrieval procedure. To use a different schema, substitute a different schema name and password. Or, if you have changed the password for the SCOTT schema, use your new password.

This sample session assumes the Oracle Multimedia Code Wizard has been installed.

Perform these steps:

Step 1   Create a table to store audio for the application by starting SQL*Plus and connecting to the SCOTT (or other) schema in the database.

For example:

sqlplus SCOTT [@<connect_identifer>]

Enter password: password

SQL> CREATE TABLE cw_media_table( id NUMBER PRIMARY KEY,
                                 description VARCHAR2(30) NOT NULL,
                                 location VARCHAR2(30),
                                 image ORDSYS.ORDIMAGE,
                                 thumb ORDSYS.ORDIMAGE,
                                 audio ORDSYS.ORDAUDIO,
                                 video ORDSYS.ORDVIDEO,
                                 media ORDSYS.ORDDOC )
--
-- store media as SecureFile LOBs
--
LOB(image.source.localdata) STORE AS SECUREFILE
LOB(thumb.source.localdata) STORE AS SECUREFILE
LOB(audio.source.localdata) STORE AS SECUREFILE
LOB(video.source.localdata) STORE AS SECUREFILE
LOB(media.source.localdata) STORE AS SECUREFILE;

Step 2   Use the SCOTTCW DAD you created in Step 2. Then, authorize the use of it in Step 3.

If you have not created the SCOTTCW DAD and authorized the use of this DAD, perform Steps 2 and 3 in this section, then continue to next step that follows in this section.

Step 3   Change DADs to the SCOTTCW DAD.

1. Enter the Code Wizard's administration URL into your browser's location bar, then enter the ORDSYS user name and password when prompted by the browser, for example:

   http://<hostname>:<port-number>/ordcwadmin
   

2. Click Change DAD from the Code Wizard's Main menu.

3. Click Change to SCOTTCW, if it is not already selected, then click Next.

4. Enter the user name SCOTT and the password for the user SCOTT when prompted for the user name and password, then press OK.

   The Main menu now displays the current DAD as SCOTTCW and the current schema as SCOTT.

Step 4   Create and test the media upload procedure.

Click Create media upload procedure from the Main menu, then click Next.

1. Select the database table and procedure Type.

   1. Click CW_MEDIA_TABLE.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the PL/SQL document upload table.

   If there are no document tables in the SCOTT schema, the Code Wizard displays a message indicating this situation. In this case, accept the default table name provided, CW_MEDIA_UPLOAD_TABLE, then click Next.

   If there are existing document tables, but the table CW_MEDIA_UPLOAD_TABLE is not among them, click Create new document table, accept the default table name provided, CW_MEDIA_UPLOAD_TABLE, then click Next.

   If the CW_MEDIA_UPLOAD_TABLE document table already exists, select Use existing document table and CW_MEDIA_UPLOAD_TABLE, then click Next.

3. Select the data access and media columns.

   1. Ensure that IMAGE (ORDIMAGE), THUMB (ORDIMAGE), AUDIO (ORDAUDIO), VIDEO (ORDVIDEO), and MEDIA (ORDDOC) are all checkmarked.

   2. Click ID (Primary key).

   3. Click Conditional insert or update.

   4. Click Next.

4. Select additional columns and procedure names.

   1. Ensure that DESCRIPTION is checkmarked because this column has a NOT NULL constraint. (The LOCATION column is not checkmarked by default as there are no constraints on this column.)

   2. Accept the procedure name provided, UPLOAD_CW_MEDIA_TABLE_IMAGE.

   3. Click Create procedure in the database.

   4. Click Next.

5. Review the following selected procedure creation options that are displayed:

   Procedure type:        Standalone
   Table name:            CW_MEDIA_TABLE
   Media column(s):       IMAGE (ORDIMAGE)
                          THUMB (ORDIMAGE)
                          AUDIO (ORDAUDIO)
                          VIDEO (ORDVIDEO)
                          MEDIA (ORDDOC)
   Key column:            ID
   Additional column(s):  DESCRIPTION
   Table access mode:     Conditional update or insert
   Procedure name:        UPLOAD_CW_MEDIA_TABLE_IMAGE
   Function:              Create procedure in the database
   

   Click Finish.

6. Compile the procedure and review the generated source information.

   The Code Wizard displays this message:

   Procedure created successfully: UPLOAD_CW_MEDIA_TABLE_IMAGE
   

   1. At the option Click to display generated source:, click View to view the generated source in another window. A copy of the generated source is shown at the end of Step 4, substep 6g.

   2. Close the window after looking at the generated source.

   3. Accept the DAD: name provided, SCOTTCW, then click Test to display in another window a template file upload form that you can use to test the generated procedure.

   4. To customize the template file upload form, select Save As... from your browser's File menu to save the HTML source for editing.

   5. To test the template upload form, enter the following information:

      • For the ID: column, enter the number 1 as the row's primary key.

      • For each Oracle Multimedia object column, click Browse... and choose the appropriate media to upload to each column of the table. You can choose one or more or all columns to test.

      • For the DESCRIPTION column, enter a brief description of the media.

      • Click Upload media.

      The Code Wizard displays a template completion window with the heading Oracle Multimedia Code Wizard: Template Upload Procedure, and, if the procedure is successful, the message: Media uploaded successfully.

   6. Close the window.

   7. Click Done on the Compile procedure and review generated source window to return to the Main menu of the Code Wizard.

   Example 4-3 shows a copy of the generated multiple media upload procedure:

   Example 4-3 Multiple Media Upload Procedure Generated in Sample Session 2

   CREATE OR REPLACE PROCEDURE UPLOAD_CW_MEDIA_TABLE_IMAGE
     ( in_ID IN VARCHAR2,
       in_IMAGE IN VARCHAR2 DEFAULT NULL,
       in_THUMB IN VARCHAR2 DEFAULT NULL,
       in_AUDIO IN VARCHAR2 DEFAULT NULL,
       in_VIDEO IN VARCHAR2 DEFAULT NULL,
       in_MEDIA IN VARCHAR2 DEFAULT NULL,
       in_DESCRIPTION IN VARCHAR2 DEFAULT NULL )
   AS
     local_IMAGE ORDSYS.ORDIMAGE := ORDSYS.ORDIMAGE.init();
     local_THUMB ORDSYS.ORDIMAGE := ORDSYS.ORDIMAGE.init();
     local_AUDIO ORDSYS.ORDAUDIO := ORDSYS.ORDAUDIO.init();
     local_AUDIO_ctx RAW( 64 );
     local_VIDEO ORDSYS.ORDVIDEO := ORDSYS.ORDVIDEO.init();
     local_VIDEO_ctx RAW( 64 );
     local_MEDIA ORDSYS.ORDDOC := ORDSYS.ORDDOC.init();
     local_MEDIA_ctx RAW( 64 );
     local_ID CW_MEDIA_TABLE.ID%TYPE := NULL;
     upload_size     INTEGER;
     upload_mimetype VARCHAR2( 128 );
     upload_blob     BLOB;
   BEGIN
     --
     -- Update the existing row.
     --
     UPDATE CW_MEDIA_TABLE mtbl
       SET mtbl.IMAGE = local_IMAGE,
           mtbl.THUMB = local_THUMB,
           mtbl.AUDIO = local_AUDIO,
           mtbl.VIDEO = local_VIDEO,
           mtbl.MEDIA = local_MEDIA,
           mtbl.DESCRIPTION = in_DESCRIPTION
       WHERE mtbl.ID = in_ID
       RETURN mtbl.ID INTO local_ID;
     --
     -- Conditionally insert a new row if no existing row is updated.
     --
     IF local_ID IS NULL
     THEN
       --
       -- Insert a new row into the table.
       --
       INSERT INTO CW_MEDIA_TABLE ( ID, IMAGE, THUMB, AUDIO, VIDEO, MEDIA,
    DESCRIPTION )
         VALUES ( in_ID, local_IMAGE, local_THUMB, local_AUDIO, 
                  local_VIDEO, local_MEDIA, in_DESCRIPTION );
     END IF;
     --
     -- Select Oracle Multimedia object(s) for update.
     --
     SELECT mtbl.IMAGE, mtbl.THUMB, mtbl.AUDIO, mtbl.VIDEO, mtbl.MEDIA INTO
    local_IMAGE, local_THUMB, local_AUDIO, local_VIDEO, local_MEDIA
       FROM CW_MEDIA_TABLE mtbl WHERE mtbl.ID = in_ID FOR UPDATE;
     --
     -- Store media data for the column in_IMAGE.
     --
     IF in_IMAGE IS NOT NULL
     THEN
       SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
              upload_size, upload_mimetype, upload_blob
         FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
       IF upload_size > 0
       THEN
         dbms_lob.copy( local_IMAGE.source.localData, 
                        upload_blob, 
                        upload_size );
         local_IMAGE.setLocal();
         BEGIN
           local_IMAGE.setProperties();
         EXCEPTION
           WHEN OTHERS THEN
             local_IMAGE.contentLength := upload_size;
             local_IMAGE.mimeType := upload_mimetype;
         END;
       END IF;
       DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
     END IF;
     --
     -- Store media data for the column in_THUMB.
     --
     IF in_THUMB IS NOT NULL
     THEN
       SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
              upload_size, upload_mimetype, upload_blob
         FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_THUMB;
       IF upload_size > 0
       THEN
         dbms_lob.copy( local_THUMB.source.localData, 
                        upload_blob, 
                        upload_size );
         local_THUMB.setLocal();
         BEGIN
           local_THUMB.setProperties();
         EXCEPTION
           WHEN OTHERS THEN
             local_THUMB.contentLength := upload_size;
             local_THUMB.mimeType := upload_mimetype;
         END;
       END IF;
       DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_THUMB;
     END IF;
     --
     -- Store media data for the column in_AUDIO.
     --
     IF in_AUDIO IS NOT NULL
     THEN
       SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
              upload_size, upload_mimetype, upload_blob
         FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_AUDIO;
       IF upload_size > 0
       THEN
         dbms_lob.copy( local_AUDIO.source.localData, 
                        upload_blob, 
                        upload_size );
         local_AUDIO.setLocal();
         BEGIN
           local_AUDIO.setProperties(local_AUDIO_ctx);
         EXCEPTION
           WHEN OTHERS THEN
             local_AUDIO.mimeType := upload_mimetype;
         END;
       END IF;
       DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_AUDIO;
     END IF;
     --
     -- Store media data for the column in_VIDEO.
     --
     IF in_VIDEO IS NOT NULL
     THEN
       SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
              upload_size, upload_mimetype, upload_blob
         FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_VIDEO;
       IF upload_size > 0
       THEN
         dbms_lob.copy( local_VIDEO.source.localData, 
                        upload_blob, 
                        upload_size );
         local_VIDEO.setLocal();
         BEGIN
           local_VIDEO.setProperties(local_VIDEO_ctx);
         EXCEPTION
           WHEN OTHERS THEN
             local_VIDEO.mimeType := upload_mimetype;
         END;
       END IF;
       DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_VIDEO;
     END IF;
     --
     -- Store media data for the column in_MEDIA.
     --
     IF in_MEDIA IS NOT NULL
     THEN
       SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
              upload_size, upload_mimetype, upload_blob
         FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_MEDIA;
       IF upload_size > 0
       THEN
         dbms_lob.copy( local_MEDIA.source.localData, 
                        upload_blob, 
                        upload_size );
         local_MEDIA.setLocal();
         BEGIN
           local_MEDIA.setProperties(local_MEDIA_ctx, FALSE);
         EXCEPTION
           WHEN OTHERS THEN
             local_MEDIA.contentLength := upload_size;
             local_MEDIA.mimeType := upload_mimetype;
         END;
       END IF;
       DELETE FROM MEDIA_UPLOAD_TABLE dtbl WHERE dtbl.name = in_MEDIA;
     END IF;
     --
     -- Update Oracle Multimedia objects in the table.
     --
     UPDATE CW_MEDIA_TABLE mtbl
       SET mtbl.IMAGE = local_IMAGE,
           mtbl.THUMB = local_THUMB,
           mtbl.AUDIO = local_AUDIO,
           mtbl.VIDEO = local_VIDEO,
           mtbl.MEDIA = local_MEDIA
       WHERE mtbl.ID = in_ID;
     --
     -- Display the template completion message.
     --
     htp.print( '<html>' );
     htp.print( '<title>Oracle Multimedia Code Wizard: Template Upload
    Procedure</title>' );
     htp.print( '<body>' );
     htp.print( '<h2> Oracle Multimedia Code Wizard:
    Template Upload Procedure</h2>' );
     htp.print( 'Media uploaded successfully.' );
     htp.print( '</body>' );
     htp.print( '</html>' );
   
   END UPLOAD_CW_MEDIA_TABLE_IMAGE;
   

   The multiple media upload procedure shown in Example 4-3 declares these input parameters and variables:

   1. In the declaration section, the procedure declares seven input parameters: in_ID, in_IMAGE, in_THUMB, in_AUDIO, in_VIDEO, in_MEDIA, and in_DESCRIPTION, then initializes the last six to NULL.

   2. In the subprogram section, the following variables are declared:

      • The variables local_IMAGE and local_THUMB are assigned the data type ORDSYS.ORDIMAGE and initialized with an empty BLOB using the ORDIMAGE.init( ) method.

      • The variable local_AUDIO is assigned the data type ORDSYS.ORDAUDIO and initialized with an empty BLOB using the ORDAUDIO.init( ) method. Also a context variable local_AUDIO_ctx is assigned the data type RAW(64).

      • The variable local_VIDEO is assigned the data type ORDSYS.ORDVIDEO and initialized with an empty BLOB using the ORDVIDEO.init( ) method. Also, a context variable local_VIDEO_ctx is assigned the data type RAW(64).

      • The variable local_MEDIA is assigned the data type ORDSYS.ORDDOC and initialized with an empty BLOB using the ORDDOC.init( ) method. Also, a context variable local_MEDIA_ctx is assigned the data type RAW(64).

      • The variable local_ID takes the same data type as the ID column in the table CW_MEDIA_TABLE and is initialized to NULL.

      • Three additional variables are declared upload_size, upload_mimetype, and upload_blob, which are later given values from comparable column names doc_size, mime_type, and blob_content from the document table MEDIA_UPLOAD_TABLE using a SELECT statement. This is all in preparation for copying the content of the image, thumb, audio, video, and media BLOB data to the respective ORDSYS.ORDIMAGE.source.localData, ORDSYS.ORDIMAGE.source.localData, ORDSYS.ORDAUDIO.source.localData, ORDSYS.ORDVIDEO.source.localData, and ORDSYS.ORDDOC.source.localData attributes.

   Within the outer BEGIN...END executable statement section, the following operations are executed:

   1. Update the existing row in the table CW_MEDIA_TABLE for the IMAGE, THUMB, AUDIO, VIDEO, MEDIA, and DESCRIPTION columns and return the value of local_ID where the value of the ID column is the value of the in_ID input parameter.

   2. If the value returned of local_ID is NULL, conditionally insert a new row into the table CW_MEDIA_TABLE and initialize the instance of the ORDImage object type in the IMAGE column with an empty BLOB, the instance of the ORDImage object type in the THUMB column with an empty BLOB, the instance of the ORDAudio object type in the AUDIO column with an empty BLOB, the instance of the ORDVideo object type in the VIDEO column with an empty BLOB, and the instance of the ORDDoc object type in the MEDIA column with an empty BLOB.

   3. Select the ORDImage object column IMAGE, ORDImage object column THUMB, ORDAudio object column AUDIO, ORDVideo object column VIDEO, and ORDDoc object column MEDIA in the table CW_MEDIA_TABLE for update where the value of the ID column is the value of the in_ID input parameter.

   4. Select a row for the doc_size, mime_type, and blob_content columns from the document table and pass the values to the upload_size, upload_mimetype, and upload_blob variables where the value of the Name column is the value of one of these input parameters: in_IMAGE; in_THUMB; in_AUDIO; in_VIDEO; or in_MEDIA.

   5. Perform a DBMS LOB copy of the BLOB data from the table MEDIA_UPLOAD_TABLE into the ORDSYS.ORDIMAGE.source.localData, ORDSYS.ORDIMAGE.source.localData, ORDSYS.ORDAUDIO.source.localData, ORDSYS.ORDVIDEO.source.localData, and ORDSYS.ORDDoc.source.localData attribute, then call the setLocal( ) method to indicate that the image, audio, and video data are stored locally in the BLOB, and ORDImage, ORDAudio, ORDVideo, and ORDDoc methods are to look for corresponding data in the source.localData attribute.

   6. In the inner executable block, call the respective ORDImage, ORDAudio, ORDVideo, and ORDDoc setProperties( ) method to read the image, audio, and video data to get the values of the object attributes and store them in the image, audio, video, and media object attributes for the ORDImage, ORDAudio, ORDVideo, and ORDDoc objects.

   7. If the setProperties( ) call fails, catch the exception and call the contentLength( ) method to get the size of the media data and call the mimeType( ) method to get the MIME type of the media data.

   8. Delete the row of data from the document table MEDIA_UPLOAD_TABLE hat was copied to the row in the table CW_MEDIA_TABLE where the value of the Name column is the value of the respective in_IMAGE, in_THUMB, in_AUDIO, in_VIDEO, and in_MEDIA input parameter.

   9. Update the ORDImage object IMAGE column, the ORDImage object THUMB column, the ORDAudio object AUDIO column, the ORDVideo object VIDEO column, and the ORDDoc object MEDIA column in the table CW_MEDIA_TABLE with the content of the variables local_IMAGE, local_THUMB, local_AUDIO, local_VIDEO, and local_MEDIA respectively, where the value of the ID column is the value of the in_ID input parameter.

   10. Display a completion message on the HTML page to indicate that the media uploaded successfully using the htp.print function from the PL/SQL Web Toolkit.

Step 5   Create and test a media retrieval.

Select Create media retrieval procedure from the Main menu, then click Next.

1. Select the database table and procedure type.

   1. Click CW_MEDIA_TABLE.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the media column and key column.

   1. Ensure that one the following object columns is checkmarked. For example, if you loaded media data into the media column in Step 4, substep 6e, then select the MEDIA (ORDDOC) column.

   2. Click ID (Primary key).

   3. Click Next.

3. Select the procedure name and parameter name.

   1. Accept the procedure name provided, GET_CW_MEDIA_TABLE_IMAGE.

   2. Accept the parameter name provided, MEDIA_ID.

   3. Click Create procedure in the database.

   4. Click Next.

4. Review the following selected procedure creation options:

5. Procedure type:        Standalone
   Table name:            CW_MEDIA_TABLE
   Key column:            ID
   Media column:          IMAGE (ORDDOC)
   Procedure name:        GET_CW_MEDIA_TABLE_IMAGE
   Parameter name:        MEDIA_ID
   Function:              Create procedure in the database
   

   Click Finish.

6. Compile the procedure and review the generated source.

   The Code Wizard displays this message:

   Procedure created successfully: GET_CW_MEDIA_TABLE_IMAGE
   

   1. Click View to view the generated source in another window. Close the window after looking at the generated source. A copy of the generated source is shown at the end of this step.

   2. Review the URL format used to retrieve images using the GET_CW_MEDIA_TABLE_IMAGE procedure.

   3. Enter the number 1 as the Key parameter, then click Test to test the procedure by retrieving the image uploaded previously.

   4. The retrieved image is displayed in another window.

   5. Close the window.

   6. Click Done to return to the Main menu.

   Note:

   A generated media retrieval script, unlike the multiple media upload script shown at the end of Step 4, handles only the type of media data designed for that Oracle Multimedia object type. To retrieve media data stored in other Oracle Multimedia object types, generate a retrieval script for each desired media data type and add it to your PL/SQL package.

   Example 4-4 shows a copy of the generated media retrieval procedure:

   Example 4-4 Media Retrieval Procedure Generated in Sample Session 2

   CREATE OR REPLACE PROCEDURE GET_CW_MEDIA_TABLE_MEDIA ( MEDIA_ID
    IN VARCHAR2 )
   AS
     localObject ORDSYS.ORDDOC;
     localBlob  BLOB;
     localBfile BFILE;
     httpStatus NUMBER;
     lastModDate VARCHAR2(256);
   
   BEGIN
     --
     -- Retrieve the object from the database into a local object.
     --
     BEGIN
       SELECT mtbl.MEDIA INTO localObject FROM CW_MEDIA_TABLE mtbl 
         WHERE mtbl.ID = MEDIA_ID;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         ordplsgwyutil.resource_not_found( 'MEDIA_ID', MEDIA_ID );
         RETURN;
     END;
     --  -- Check the update time if the browser sent an If-Modified-Since header.
     --
     IF ordplsgwyutil.cache_is_valid( localObject.getUpdateTime() )
     THEN
       owa_util.status_line( ordplsgwyutil.http_status_not_modified );
       RETURN;
     END IF;
     --
     -- Figure out where the image is.
     --
     IF localObject.isLocal() THEN
       --
       -- Data is stored locally in the localData BLOB attribute.
       --
       localBlob := localObject.getContent();
       owa_util.mime_header( localObject.getMimeType(), FALSE );
       ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
       owa_util.http_header_close();
       IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN
         wpg_docload.download_file( localBlob );
       END IF;
   
     ELSIF UPPER( localObject.getSourceType() ) = 'FILE' THEN
       --
       -- Data is stored as a file from which ORDSource creates 
       -- a BFILE.
       --
       localBfile  := localObject.getBFILE();
       owa_util.mime_header( localObject.getMimeType(), FALSE );
       ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
       owa_util.http_header_close();
       IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN
         wpg_docload.download_file( localBfile );
       END IF;
   
     ELSIF UPPER( localObject.getSourceType() ) = 'HTTP' THEN
       --
       -- The image is referenced as an HTTP entity, so we have to 
       -- redirect the client to the URL that ORDSource provides.
       --
       owa_util.redirect_url( localObject.getSource() );
     ELSE
       --
       -- The image is stored in an application-specific data
       -- source type for which no default action is available.
       --
       NULL;
     END IF;
   END GET_CW_MEDIA_TABLE_MEDIA;
   

   For a description of the media retrieval procedure shown in Example 4-4, see the description that follows Example 4-2 (in Section 4.3). The only difference between these two retrieval procedures is the type of object that is retrieved. Example 4-2 uses an ORDImage object type; Example 4-4 uses an ORDDoc object type.

4.5 Known Restrictions of the Oracle Multimedia Code Wizard

The following restrictions are known for the Oracle Multimedia Code Wizard:

• Tables with composite primary keys are not supported.

  To use a table with a composite primary key, create an upload or download procedure, then edit the generated source to support all the primary key columns. For example, for a media retrieval procedure, this might involve adding an additional parameter, then specifying that parameter in the where clause of the SELECT statement.

• User object types containing embedded Oracle Multimedia object types are not recognized by the Oracle Multimedia Code Wizard.