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

This chapter describes the Oracle Multimedia Code Wizard sample application. The Oracle Multimedia Code Wizard sample application for the PL/SQL Gateway is a media upload and retrieval Web application that uses these Oracle Multimedia image, audio, video, and heterogeneous media object types.

This chapter assumes the following:

  • You are familiar with developing PL/SQL applications using the PL/SQL Gateway.

  • You have installed and configured the Oracle Multimedia Code Wizard sample application.

You can install the Oracle Multimedia Code Wizard sample application from the Oracle Database Examples media, which is available for download from the Oracle Technology Network (OTN). After installing the Oracle Database Examples media, the sample application files and README.txt file are located at:

<ORACLE_HOME>/ord/http/demo/plsgwycw (on Linux and UNIX)

<ORACLE_HOME>\ord\http\demo\plsgwycw (on Windows)

This chapter describes how to run the Code Wizard Photo Album application. See the README.txt file for additional requirements and instructions on installing and configuring this sample application.

Note:

This discussion assumes that the Code Wizard has been installed in the ORDSYS schema.

This chapter includes these sections:

More Sample Applications

See these chapters for more sample applications:

Chapter 3 describes these Photo Album sample Web applications, which use PL/SQL scripts, Java servlet files, and JSP files to demonstrate various ways to upload and retrieve media using Oracle Multimedia object types:

  • Oracle Multimedia PL/SQL Web Toolkit Photo Album application (Section 3.1)

  • Oracle Multimedia Java Servlet Photo Album application (Section 3.2)

  • Oracle Multimedia JSP Photo Album application (Section 3.3)

Chapter 5 describes the Oracle Multimedia Java API sample application. This sample application lets you retrieve, save, play, and delete multimedia data from the Oracle Database sample schemas using Oracle Multimedia Java classes and Oracle Multimedia object types.

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:

4.2.1 Creating a New DAD or Choosing an Existing DAD

To create media upload or retrieval procedures, you must select one or more DADs for use with the Code Wizard. To prevent the unauthorized browsing of schema tables and to prevent the unauthorized creation of media access procedures, you must authorize each DAD using the Code Wizard administration function. Depending on your database and application security requirements, you can create and authorize one or more new DADs specifically for use with the Code Wizard. Or, you can authorize the use of one or more existing DADs.

Oracle recommends that any DAD authorized for use with the Code Wizard employ some form of user authentication mechanism. The simplest approach is to create or use a DAD that uses database authentication. To use this approach, select Basic Authentication Mode and omit the password in the DAD specification. Alternatively, you can use a DAD that specifies an existing application-specific authentication mechanism.

See Also:

Oracle Fusion Middleware Administrator's Guide for Oracle HTTP Server in the Oracle Fusion Middleware Online Documentation Library for more information about configuring DADs

The following example describes how to create a DAD that enables you to create and test media upload and retrieval procedures in the SCOTT schema.

Note:

To test media upload procedures, you must specify the name of a document table in the DAD. When testing an upload procedure, you can choose either the DAD you used to create the procedure or the DAD you used to access the application. You can choose a document table name when you create a DAD, edit a DAD to specify the document table name at a later time, or use an existing DAD that specifies a document table name. This example shows how to specify the document table name when you create the DAD.
  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. Scroll to the DAD Status section on the mod_plsql Services page. 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. Enter MEDIA_UPLOAD_TABLE for the Document Table on the Document, Alias, and Session page. Then, click Apply.

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

4.2.2 Authorizing a DAD

To authorize a DAD for use with the Code Wizard, perform these steps:

  1. Enter the Code Wizard's administration URL into the location bar for your browser. For example:

    http://<host-name>:<port-number>/ordcwadmin
    
  2. Enter the user name and password when prompted by the browser.

  3. Select DAD authorization from the Main menu, as shown in Figure 4-1. Then, click Next.

    Figure 4-1 Main Menu for the Code Wizard

    Description of Figure 4-1 follows
    Description of "Figure 4-1 Main Menu for the Code Wizard "

  4. Enter the name of the DAD you want to authorize along with the user name, as shown in Figure 4-2. Then, click Apply.

    Figure 4-2 Authorize the SCOTTCW DAD

    Description of Figure 4-2 follows
    Description of "Figure 4-2 Authorize the SCOTTCW DAD"

    Note:

    Duplicate DADs are not permitted, and each authorized DAD must indicate which database schema the user is authorized to access with the Code Wizard, using the DAD. Use this same page to delete the authorization for any existing DADs that no longer require the Code Wizard.
  5. Review the updated list of DADs that are authorized to use the Oracle Multimedia Code Wizard, as shown in Figure 4-3. Then, click Done.

    Figure 4-3 List of Authorized DADs

    Description of Figure 4-3 follows
    Description of "Figure 4-3 List of Authorized DADs"

  6. Select Logout from the Main menu to log out (clear HTTP authentication information), then click Next. The log out operation redirects the request to the PL/SQL Gateway built-in logmeoff function.

See Also:

Oracle Fusion Middleware User's Guide for mod_plsql in the Oracle Fusion Middleware Online Documentation Library

4.2.3 Creating and Testing Media Upload and Retrieval Procedures

After you have completed the setup tasks (as described in Section 4.2.1, Section 4.2.2, and the README.txt file), you are ready to run this application.

To start the Code Wizard, follow these steps:

  1. Enter the appropriate URL into the address field of your Web browser.

    For example:

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

    or

    http://<hostname>:<port-number>/mediadad/ordcwpkg.menu

  2. Enter the user name and password when prompted by the browser. The Main menu page of the Oracle Multimedia Code Wizard for the PL/SQL Gateway is displayed, as shown in Figure 4-4.

    Figure 4-4 Use the SCOTTCW DAD

    Description of Figure 4-4 follows
    Description of "Figure 4-4 Use the SCOTTCW DAD"

  3. If the DAD is configured specifically for use with the Code Wizard, enter the DAD name. To use another DAD, enter the DAD name along with the Code Wizard package name and Main menu procedure name (ORDCWPKG.MENU) after the DAD name.

  4. After logging in, you can log out (clear HTTP authentication information) at any time by selecting Logout from the Main menu, then clicking Next. The logout operation redirects the request to the PL/SQL Gateway's built-in logmeoff function.

    See Also:

    Oracle Fusion Middleware User's Guide for mod_plsql in the Oracle Fusion Middleware Online Documentation Library

To create a media upload procedure (see Section 4.2.4) or a media retrieval procedure (see Section 4.2.5), select the appropriate option from the Main menu page, then click Next. The Code Wizard then guides you through a series of self-explanatory steps to create the procedure.

If you create a standalone media upload or retrieval procedure, you will have the opportunity to view the contents of the procedure and test it. Section 4.3 and Section 4.4, respectively, include sample sessions that demonstrate how to create and test a media upload procedure and a media retrieval procedure.

4.2.4 Creating a Media Upload Procedure

To create a media upload procedure using the Oracle Multimedia Code Wizard for the PL/SQL Gateway, perform these steps:

  1. Select Create media upload procedure from the Main menu page, as shown in Figure 4-5. Then, click Next.

    Figure 4-5 Create a Media Upload Procedure

    Description of Figure 4-5 follows
    Description of "Figure 4-5 Create a Media Upload Procedure"

  2. Select PHOTOS and Standalone procedure from Step 1: Select database table and procedure type, as shown in Figure 4-6. Then, click Next.

    Figure 4-6 Media Upload Step 1: Select Database Table and Procedure Type

    Description of Figure 4-6 follows
    Description of "Figure 4-6 Media Upload Step 1: Select Database Table and Procedure Type"

  3. Select Use existing document table from Step 2: Select PL/SQL Gateway document upload table, as shown in Figure 4-7, because the SCOTTCW DAD is configured to use this document table. Then, click Next.

    Figure 4-7 Media Upload Step 2: Select PL/SQL Gateway Document Upload Table

    Description of Figure 4-7 follows
    Description of "Figure 4-7 Media Upload Step 2: Select PL/SQL Gateway Document Upload Table"

  4. Check PHOTO (ORDIMAGE), select ID (Primary key), and select Conditional insert or update from Step 3: Select data access and media column(s), as shown in Figure 4-8. Then, click Next.

    Figure 4-8 Media Upload Step 3: Select Data Access and Media Column(s)

    Description of Figure 4-8 follows
    Description of "Figure 4-8 Media Upload Step 3: Select Data Access and Media Column(s)"

  5. Check DESCRIPTION, accept the default procedure name, UPLOAD_PHOTOS_PHOTO, and select Create procedure in the database from Step 4: Select additional columns and procedure name, as shown in Figure 4-9. Then, click Next.

    Figure 4-9 Media Upload Step 4: Select Additional Columns and Procedure Name

    Description of Figure 4-9 follows
    Description of "Figure 4-9 Media Upload Step 4: Select Additional Columns and Procedure Name"

  6. Review the options you selected from Step 5: Review selected options, as shown in Figure 4-10. If the options selected are correct, click Finish.

    Figure 4-10 Media Upload Step 5: Review Selected Options

    Description of Figure 4-10 follows
    Description of "Figure 4-10 Media Upload Step 5: Review Selected Options"

  7. The message Procedure created successfully: UPLOAD_PHOTOS_PHOTO is displayed on the Compile procedure and review generated source page, as shown in Figure 4-11.

    Figure 4-11 Compiled Upload Procedure with Success Message

    Description of Figure 4-11 follows
    Description of "Figure 4-11 Compiled Upload Procedure with Success Message"

    To review the compiled PL/SQL source code in another window, click View (see Example 4-1 for a copy of the generated upload procedure). Assuming you have configured the SCOTTCW DAD and specified MEDIA_UPLOAD_TABLE as the document table, in the DAD: field, the DAD name scottcw is displayed by default.

    To test the PL/SQL procedure created, click Test.

    The Oracle Multimedia Code Wizard: Template Upload Form is displayed in another window.

  8. Enter the value 1 in the ID field on the Oracle Multimedia Code Wizard: Template Upload Form window. Click Browse... to find and select the image you want to upload in the PHOTO field, and enter a brief description of the image to be uploaded in the DESCRIPTION field, as shown in Figure 4-12. Then, click Upload media.

    Figure 4-12 Template Upload Form for the Code Wizard

    Description of Figure 4-12 follows
    Description of "Figure 4-12 Template Upload Form for the Code Wizard"

    The image is uploaded into the table row, and this message is displayed:

    Media uploaded successfully.

  9. Return to the Compile procedure and review generated source page. If you are finished testing, click Done to return to the Main menu page.

4.2.5 Creating a Media Retrieval Procedure

To create a media retrieval procedure using the Oracle Multimedia Code Wizard for the PL/SQL Gateway, perform these steps:

  1. Select Create media retrieval procedure from the Main menu page, as shown in Figure 4-13. Then, click Next.

    Figure 4-13 Create a Media Retrieval Procedure

    Description of Figure 4-13 follows
    Description of "Figure 4-13 Create a Media Retrieval Procedure"

  2. Select PHOTOS and Standalone procedure from Step 1: Select database table and procedure type, as shown in Figure 4-14. Then, click Next.

    Figure 4-14 Media Retrieval Step 1: Select Database Table and Procedure Type

    Description of Figure 4-14 follows
    Description of "Figure 4-14 Media Retrieval Step 1: Select Database Table and Procedure Type"

  3. Select PHOTO (ORDIMAGE) and ID (Primary key) from Step 2: Select media column and key column, as shown in Figure 4-15. Then, click Next.

    Figure 4-15 Media Retrieval Step 2: Select Media Column and Key Column

    Description of Figure 4-15 follows
    Description of "Figure 4-15 Media Retrieval Step 2: Select Media Column and Key Column"

  4. Accept the default procedure name, GET_PHOTOS_PHOTO, the default parameter name, MEDIA_ID, and Create procedure in the database from Step 3: Select procedure name and parameter name, as shown in Figure 4-16. Then, click Next.

    Figure 4-16 Media Retrieval Step 3: Select Procedure Name and Parameter Name

    Description of Figure 4-16 follows
    Description of "Figure 4-16 Media Retrieval Step 3: Select Procedure Name and Parameter Name"

  5. Review the options you selected from Step 4: Review Selected Options, as shown in Figure 4-17. If the options selected are correct, click Finish.

    Figure 4-17 Media Retrieval Step 4: Review Selected Options

    Description of Figure 4-17 follows
    Description of "Figure 4-17 Media Retrieval Step 4: Review Selected Options"

  6. The message Procedure created successfully: GET_PHOTOS_PHOTO is displayed in the Compile procedure and review generated source page, as shown in Figure 4-18.

    Figure 4-18 Compiled Retrieval Procedure with Success Message

    Description of Figure 4-18 follows
    Description of "Figure 4-18 Compiled Retrieval Procedure with Success Message"

    To review the compiled PL/SQL source code in another window, click View (see Example 4-2 for a copy of the generated retrieval procedure).

    To test the PL/SQL procedure created, assuming you have an image loaded in the database with an ID value of 1, enter the value 1 for the Key parameter (MEDIA_ID), then click Test.

    The image is retrieved from the table row and displayed in another window.

  7. Click Done to return to the Main menu page.

4.2.6 Using the PL/SQL Gateway Document Table

All files uploaded using the PL/SQL Gateway are stored in a document table. Media upload procedures created by the Code Wizard automatically move uploaded media from the specified document table to the application's table. To avoid transient files from appearing temporarily in a document table used by another application component, use a document table that is not being used to store documents permanently.

Specify the selected document table in the application's database access descriptor (DAD). If the DAD specifies a different document table, create a new DAD for media upload procedures. If you choose to create a new document table, the Code Wizard creates a table with the following format:

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 SecureFiles LOBs
-- 
LOB(blob_content)  STORE AS SECUREFILE;

See Also:

Oracle Fusion Middleware User's Guide for mod_plsql in the Oracle Fusion Middleware Online Documentation Library for more information about file upload and document tables

4.2.7 How Time Zone Information Is Used to Support Browser Caching

User response times are improved and network traffic is reduced if a browser can cache resources received from a Web server and subsequently use those cached resources to satisfy future requests. This section describes at a very high level, how the browser caching mechanism works and how the Code Wizard utility package is used to support that mechanism. When reading this discussion, keep in mind that all HTTP date and time stamps are expressed in Coordinated Universal Time (UTC).

All HTTP responses include a Date header, which indicates the date and time when the response was generated. When a Web server sends a resource in response to a request from a browser, it can also include the Last-Modified HTTP response header, which indicates the date and time when the requested resource was last modified. The Last-Modified header must not be later than the Date header.

After receiving and caching a resource, if a browser must retrieve the same resource again, it sends a request to the Web server with the If-Modified-Since request header specified as the value of the Last-Modified date, which was returned by the application server when the resource was previously retrieved and cached. When the Web server receives the request, it compares the date in the If-Modified-Since request header with the last update time of the resource. Assuming the resource still exists, if the resource has not changed since it was cached by the browser, the Web server responds with an HTTP 304 Not Modified status with no response body, which indicates that the browser can use the resource currently stored in its cache. Assuming again that the resource still exists, if the request does not include an If-Modified-Since header or if the resource has been updated since it was cached by the browser, the Web server responds with an HTTP 200 OK status and sends the resource to the browser.

The ORDImage, ORDAudio, ORDVideo, and ORDDoc objects all possess an updateTime attribute stored as a DATE in the embedded ORDSource object. Although the DATE data type has no support for time zones or daylight savings time, the Oracle9i and later database versions do support time zones and also provide functions for converting a DATE value stored in a database to UTC.

When a response is first returned to a browser, a media retrieval procedure sets the Last-Modified HTTP response header based on the updateTime attribute. If a request for media data includes an If-Modified-Since header, the media retrieval procedure compares the value with the updateTime attribute and returns an appropriate response. If the resource in the browser's cache is still valid, an HTTP 304 Not Modified status is returned with no response body. If the resource has been updated since it was cached by the browser, then an HTTP 200 OK status is returned with the media resource as the response body.Media retrieval procedures created by the Code Wizard call the utility package to convert a DATE value stored in the database to UTC. The utility package uses the time zone information stored with an Oracle9i or later database and the date and time functions to convert database date and time stamps to UTC. To ensure that the resulting date conforms to the rule for the Last-Modified date described previously, the time zone information must be specified correctly.

See Also:

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.

See Also:

Oracle Database Security Guide for more information about creating secure passwords

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 SecureFiles 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 &gt; 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( '&lt;html&gt;' );
      htp.print( '&lt;title&gt;Oracle Multimedia Code Wizard: Template Upload
    Procedure&lt;/title&gt;' );
      htp.print( '&lt;body&gt;' );
      htp.print( '&lt;h2&gt; Oracle Multimedia Code Wizard:
    Template Upload Procedure&lt;/h2&gt;' );
      htp.print( 'Media uploaded successfully.' );
      htp.print( '&lt;/body&gt;' );
      htp.print( '&lt;/html&gt;' );
    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' ) &lt;&gt; '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' ) &lt;&gt; '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.

See Also:

Oracle Database Security Guide for more information about creating secure passwords

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 SecureFiles 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 &gt; 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 &gt; 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 &gt; 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 &gt; 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 &gt; 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( '&lt;html&gt;' );
      htp.print( '&lt;title&gt;Oracle Multimedia Code Wizard: Template Upload
     Procedure&lt;/title&gt;' );
      htp.print( '&lt;body&gt;' );
      htp.print( '&lt;h2&gt; Oracle Multimedia Code Wizard:
     Template Upload Procedure&lt;/h2&gt;' );
      htp.print( 'Media uploaded successfully.' );
      htp.print( '&lt;/body&gt;' );
      htp.print( '&lt;/html&gt;' );
    
    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' ) &lt;&gt; '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' ) &lt;&gt; '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.