Skip Headers
Oracle® Multimedia User's Guide
11g Release 1 (11.1)
Part Number B28415-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
View PDF

4 Oracle Multimedia Code Wizard Sample Application

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 the Oracle Multimedia image, audio, video, and heterogeneous media object types.

This chapter assumes the following:

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)

The following subsections describe 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.See Chapter 5 for a description of the Oracle Multimedia IMExample 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.

See Chapter 3 for descriptions of the following Photo Album sample applications:

This suite of Web applications use PL/SQL scripts, Java servlet files, JSP files, and ASP/VBScript files to demonstrate various ways to upload and retrieve media using Oracle Multimedia object types.

4.1 Using 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 Oracle Multimedia object types, ORDImage, ORDAudio, ORDVideo, and ORDDoc, and their respective methods. The Code Wizard guides you through a series of self-explanatory steps to create either a media retrieval or a media upload procedure. You can either create and compile standalone media access procedures, or you can create the source of media access procedures for inclusion in a PL/SQL package. This is similar to how the photo album application (see Section 3.1) uses the insert_new_photo procedure as the image upload procedure and the deliver_media procedure as the image retrieval procedure in the photo_album PL/SQL package. Finally, once created, you can customize the media access procedures as necessary to meet specific application requirements.

The Oracle Multimedia Code Wizard sample application can be installed from the Oracle Database Examples media, available for download from OTN. The application is installed in the following directory:

<ORACLE_HOME>/ord/http/demo/plsgwycw

The README.txt file in this directory contains complete instructions on installing and configuring the Code Wizard sample application. The following discussion assumes that the Code Wizard has been installed into the ORDSYS schema.

To use the Code Wizard to create and test media upload and retrieval procedures, you must do the following 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.

This section describes these and other related topics in more detail.

4.1.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's administration function. Depending on your database and application security requirements, you may choose to create and authorize one or more new DADs specifically for use with the Code Wizard, or you may choose to authorize the use of one or more existing DADs.

Oracle recommends that any DAD authorized for use with the Code Wizard should use 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 may choose to use a DAD that specifies an existing application-specific authentication mechanism. For more information about configuring DADs, see Oracle HTTP Server Administrator's Guide in the Oracle Application Server Online Documentation Library.The following example describes how to create a DAD to create and test media upload and retrieval procedures in the SCOTT schema.

Note:

To test media upload procedures, the name of a document table must be specified in the DAD. When testing an upload procedure, you may choose the DAD you use to create the procedure, or you may use the DAD used to access the application. You may 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 already specifies a document table name. This example illustrates specifying 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. This opens the mod_plsql Services page.

  2. Scroll to the DAD Status section on the mod_plsql Services page. Click Create. This opens the DAD Type page.

  3. Select the DAD type to be "General". Click Next. This opens the Database Connection page.

  4. Enter "/scottw" 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. In the Default page field, enter ORDCWPKG.MENU. Leave the other fields blank. Click Next. This opens the Document, Alias, and Session page.

  5. Enter MEDIA_UPLOAD_TABLE for the Document Table on the Document, Alias, and Session page. Click Apply.

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

4.1.2 Authorizing a DAD

To authorize a DAD for use with the Code Wizard, do the following steps:

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

    http://<host-name>:<port-number>/ordcwadmin

  2. Enter the ORDSYS 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 interMedia Code Wizard for the PL/SQL Gateway

    Description of Figure 4-1 follows
    Description of "Figure 4-1 Main Menu for the interMedia Code Wizard for the PL/SQL Gateway"

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

    Figure 4-2 Authorizing the SCOTTCW DAD

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

    Note:

    Duplicate DADs are not allowed, 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 need to use 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 Next.

    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's built-in logmeoff function. For more information, see Oracle HTTP Server mod_plsql User's Guide.

4.1.3 Creating and Testing Media Upload and Retrieval Procedures

To start the Code Wizard, enter the appropriate URL into your browser's location bar, for example:

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

or

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

Then, 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 Using the SCOTTCW DAD

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

If the DAD is configured specifically for use with the Code Wizard, simply enter the DAD name. Alternatively, to use another DAD, enter the DAD name together with the Code Wizard package name and Main menu procedure name, ORDCWPKG.MENU after the DAD name.

Once you have logged 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. For more information, see Oracle HTTP Server mod_plsql User's Guide.

To create a media upload procedure (see Section 4.1.4) or a media retrieval procedure (see Section 4.1.5), select the appropriate option from the Main menu, 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 as well as to test it.The image and multimedia sample sessions described in Section 4.2 and Section 4.3 respectively, illustrate how to create and test a media upload procedure and a media retrieval procedure.

4.1.4 Creating a Media Upload Procedure

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

  1. Select Create media upload procedure from the Main menu, 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 CW_IMAGES_TABLE and Standalone procedure from Step 1: Select database table and procedure type, as shown in Figure 4-6. Then, click Next.

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

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

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

    Figure 4-7 Step 2: Select PL/SQL Gateway Document Upload Table (Part 1)

    Description of Figure 4-7 follows
    Description of "Figure 4-7 Step 2: Select PL/SQL Gateway Document Upload Table (Part 1)"

    Figure 4-8 Step 2: Select PL/SQL Gateway Document Upload Table (Part 2)

    Description of Figure 4-8 follows
    Description of "Figure 4-8 Step 2: Select PL/SQL Gateway Document Upload Table (Part 2)"

  4. Check IMAGE (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-9. Then, click Next.

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

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

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

    Figure 4-10 Step 4: Select Additional Columns and Procedure Name

    Description of Figure 4-10 follows
    Description of "Figure 4-10 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-11. If the options selected are correct, click Finish.

    Figure 4-11 Step 5: Review Selected Options

    Description of Figure 4-11 follows
    Description of "Figure 4-11 Step 5: Review Selected Options"

  7. Note the message, Procedure created successfully: UPLOAD_CW_IMAGES_TABLE_IMAGE on the Compile procedure and review generated source window, as shown in Figure 4-12. To review the compiled PL/SQL source code in another window, click View (see Step 5 in Section 4.2 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.

    Figure 4-12 Compile Procedure and Review Generated Source

    Description of Figure 4-12 follows
    Description of "Figure 4-12 Compile Procedure and Review Generated Source"

  8. Enter the value 1 in the ID field on the interMedia Code Wizard: Template Upload Form window, browse for and select the image you want to upload in the IMAGE field, and enter a brief description of the image to be uploaded in the DESCRIPTION field, as shown in Figure 4-13. Then, click Upload media.

    Figure 4-13 Template Upload Form

    Description of Figure 4-13 follows
    Description of "Figure 4-13 Template Upload Form"

  9. The image is uploaded into the table row and a message is displayed, as shown in Figure 4-14.

    Figure 4-14 Template Upload Procedure -- Media Uploaded Successfully Message

    Description of Figure 4-14 follows
    Description of "Figure 4-14 Template Upload Procedure -- Media Uploaded Successfully Message"

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

4.1.5 Creating a Media Retrieval Procedure

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

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

    Figure 4-15 Create a Media Retrieval Procedure

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

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

    Figure 4-16 Step 1: Select Database Table and Procedure Type

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

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

    Figure 4-17 Step 2: Select Media Column and Key Column

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

  4. Accept the default procedure name, GET_CW_IMAGES_TABLE_IMAGES, 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-18. Then, click Next.

    Figure 4-18 Step 3: Select Procedure Name and Parameter Name

    Description of Figure 4-18 follows
    Description of "Figure 4-18 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-19. If the options selected are correct, click Finish.

    Figure 4-19 Step 4: Review Selected Options

    Description of Figure 4-19 follows
    Description of "Figure 4-19 Step 4: Review Selected Options"

  6. Note the message Procedure created successfully: GET_CW_IMAGES_TABLE_IMAGE from the Compile procedure and review generated source window, as shown in Figure 4-20. To review the compiled PL/SQL source code in another window, click View (see Step 6 in Section 4.2 for a copy of the generated retrieval procedure). To test the PL/SQL procedure created, assuming you have an image already 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 is displayed as shown in Figure 4-21. Click Done to return to the Main menu.

    Figure 4-20 Compile Procedure and Review Generated Source

    Description of Figure 4-20 follows
    Description of "Figure 4-20 Compile Procedure and Review Generated Source"

    Figure 4-21 Displayed Image 1981

    Description of Figure 4-21 follows
    Description of "Figure 4-21 Displayed Image 1981"

4.1.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.

Be sure to specify the selected document table in the application's database access descriptor (DAD). If the DAD already 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 will create 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 SecureFile LOBs
-- 
LOB(blob_content)  STORE AS SECUREFILE;

For more information about file upload and document tables, see Oracle HTTP Server mod_plsql User's Guide.

4.1.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, note 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. It is important to note that the Last-Modified header must not be later than the Date header.

After receiving and caching a resource, if a browser needs to 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 once again 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. See the HTTP specification (http://www.w3.org/Protocols/) for more information.

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. See Oracle Database Administrator's Guide for more information about how to set a time zone for a database. See Oracle Database SQL Language Reference for more information about date and time functions.

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 the resulting date conforms to the rule for the Last-Modified date described previously, the time zone information must be specified correctly. See Oracle Database Administrator's Guide for more information about how to set a time zone for a database.

4.2 Sample Session Using Images

The following sample session uses the SCOTT schema with the password tiger to illustrate the creation of image media upload and retrieval procedures. Substitute a different schema name, password, or both if you want to use a different schema or if you have changed the password for the SCOTT schema.

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

Perform the following steps:


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

For example:

sqlplus SCOTT/tiger[@<connect_identifer>]

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. This opens the mod_plsql Services page.

  2. On the mod_plsql Services page, scroll to the DAD Status section. Click Create. This opens the DAD Type page.

  3. Select the DAD type to be "General". Click Next. This opens the Database Connection page.

  4. Enter "/scottw" 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. In the Default page field, enter ORDCWPKG.MENU. Leave the other fields blank. Click Next. This opens the Document, Alias, and Session page.

  5. On the Document, Alias, and Session page, enter MEDIA_UPLOAD_TABLE for the Document Table. 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 tiger 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 the following 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 pull-down 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 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 interMedia 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.

    A copy of the generated image upload procedure is as follows:

    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 interMedia 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 interMedia 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;interMedia Code Wizard: Template Upload
Procedure&lt;/title&gt;' );
  htp.print( '&lt;body&gt;' );
  htp.print( '&lt;h2&gt;&lt;i&gt;inter&lt;/i&gt;Media 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;

    This sample image upload procedure declares the following 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 should 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 the following 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.

    A copy of the generated image retrieval procedure is as follows:

    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;

    This sample image retrieval procedure declares the following 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.3 Sample Session Using Multiple Object Columns

The following sample session uses the SCOTT schema with the password tiger to illustrate the creation of a multimedia upload (multiple Oracle Multimedia object columns) and single media retrieval procedures. Substitute a different schema name, password, or both if you want to use a different schema or if you have changed the password for the SCOTT schema.

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

Perform the following steps:


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

For example:

sqlplus SCOTT/tiger[@<connect_identifer>]

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 SCOTTW DAD you created in Step 2. Then, authorize the use of it in Step 3.

If you have not created the SCOTTW 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 tiger 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 the following 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 pull-down 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 interMedia 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.

    A copy of the generated multimedia upload procedure is as follows:

    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 interMedia 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 interMedia 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;interMedia Code Wizard: Template Upload
 Procedure&lt;/title&gt;' );
  htp.print( '&lt;body&gt;' );
  htp.print( '&lt;h2&gt;&lt;i&gt;inter&lt;/i&gt;Media 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;

    This sample multimedia upload procedure declares the following 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 the following 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 should 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 the following 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.

    A copy of the generated media retrieval procedure is as follows:

    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;

    See the description at the end of the generated image retrieval procedure in Section 4.2, Step 6, after substep 5e. The only difference between these two retrieval procedures is the type of object being retrieved, an ORDImage object type versus an ORDDoc object type.

4.4 Known Restrictions of the Oracle Multimedia Code Wizard

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

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us