4
Storing Data and Creating PL/SQL Procedures

Chapter 3 explained how to retrieve, store, and update multimedia objects using Oracle8i interMedia Clipboard and how to use Oracle8i interMedia Clipboard and Web Agent to generate URLs to retrieve multimedia objects from a database.

In addition to the capabilities offered by Oracle8i interMedia Clipboard, you can store and update multimedia objects from Web applications and you can create your own PL/SQL procedures or modify the ones created by the Clipboard to customize retrieval or storage of multimedia objects. This chapter discusses the following topics:

Storing multimedia objects from Web pages and Web applications using procedures generated by Oracle8i interMedia Clipboard
Creating PL/SQL procedures to customize the retrieval or storage of multimedia objects from Web pages and Web applications
Handling status codes and errors and debugging requests

4.1 Storing Oracle8i interMedia Data from Web Applications

You can create Web pages and Web applications that store new or updated multimedia objects in the database.

To store or update the data in the database, you construct a form in your Web page and, from the form, pass the information to Oracle8i interMedia Web Agent. Oracle8i interMedia Web Agent passes the information to PL/SQL procedures and stores the data in the database.

You can easily generate the PL/SQL procedures using Oracle8i interMedia Clipboard, as explained in Section 3.5, or you can create your own procedures. Section 4.2.1 describes the input and output parameters that you use in the PL/SQL procedures.

When you construct a form to store multimedia objects in the database, note the following requirements:

Use the POST request method. With the POST method, you pass parameters in the request body.
Specify the encoding type as multipart/form-data.
Identify the multimedia object with the variable name ord_content.
In the URL, specify the request mode as mediaput.
Specify the key value of the table row by using the ord_procedure_path variable name.
Use the ord_post_put_call variable name to call a PL/SQL procedure that sets the properties of the data after it is stored in the database. The variable specifies the name of a PL/SQL procedure that performs post-processing.

To pass a SQL statement instead of a PL/SQL procedure, you use the ord_sql variable name. To set the properties of the data after it is stored in the database, you use the ord_post_put_sql variable name.

The following Web page uses a form that asks for the Employee ID and the name of the file containing the employee's picture, and then loads the picture into the database:

This form assumes that the object is stored as a BLOB, not as a BFILE or URL. The following example shows the HTML coding of the form:

<form action="http://nedc.us.ora.com:8007/intermedia/emp_write/mediaput/PUT_EMP_PIC" [1]
      method="post"
      enctype="multipart/form-data">
<input type="hidden" name="ord_post_put_call" value="SET_EMP_PIC"> [2]
Employee ID:           <input type="text" name="ord_procedure_path" length=5> [3]
Employee Picture File: <input type="file" name="ord_content"> [4]
                       <input type="Submit" value="Store Employee Picture Now">
</form>

In the preceding HTML example, the numbered callouts highlight the following:

[1] In the URL, emp_write is the database agent name and PUT_EMP_PIC is the name of the PL/SQL procedure that inserts the multimedia object into the database.

[2] The ord_post_put_call variable name specifies SET_EMP_PIC as the PL/SQL procedure that sets the attributes of the object.

[3]The ord_procedure_path variable name specifies that the value entered as the Employee ID be used as the value of the procedure path of the URL.

[4] The ord_content variable name specifies that the value entered for the Employee Picture File is a file that contains the multimedia object.

Oracle8i interMedia Web Agent passes the information from the form to PL/SQL procedures. The following example shows the PUT_EMP_PIC procedure, which was generated by Oracle8i interMedia Clipboard and which handles objects whether they are stored in the database, in a BFILE, or in a URL:

CREATE OR REPLACE PROCEDURE PUT_EMP_PIC
(   image_file_directory IN VARCHAR2,
    image_file_name IN VARCHAR2,
    image_http_path IN VARCHAR2,
    image_http_name IN VARCHAR2,
    ord_procedure_path IN VARCHAR2,
    ord_content_blob OUT BLOB
    ) 
as
    LocalImage          ORDSYS.ORDImage;
begin

  /* Create an empty object.*/
    localImage := ORDSYS.ORDImage( ORDSYS.ORDSource( empty_blob(),
                                              null, null, null, null, null ),
                                  null, null, null, null, null, null, null );

  /* Process the request based on the location of the new image.*/
    if length( image_file_directory ) > 0 then
      /* Image is stored as a BFILE in a database server directory.
       * Set the local image object to refer to the specified file.
       */
      localImage.clearLocal();
      localImage.setSource( 'FILE',
                            image_file_directory,
                            image_file_name );

    elsif length ( image_http_path ) > 0 then
      /* Image is stored on a Web server.
       * Set the local image to refer to the URL.
       */
      localImage.clearLocal();
      localImage.setSource( 'HTTP',
                            image_http_path,
                            image_http_name );

    else
      /* Image is being uploaded from the client to be stored in the database.
       * Set the flag to indicate the image is to be stored in the object's 
         local-data BLOB.
       */
      localImage.setLocal();
    end if;

    /* Update the image object in the table. If the image is to be stored in
     * the object's local-data BLOB, then return the LOB handle so the Web
     * Agent can store the image in the database.
     */
    if localImage.isLocal() then
        UPDATE EMPLOYEE_PIC I SET I.EMPLOYEE_PIC = localImage 
            WHERE EMP_ID = ord_procedure_path
            RETURN I.PICTURE.Source.localdata INTO ord_content_blob;
    else
        UPDATE EMPLOYEE_PIC I SET I.EMPLOYEE_PIC = localImage 
            WHERE EMP_ID = ord_procedure_path;
    end if;

end;

The following example shows the SET_EMP_PIC procedure, which sets the attributes of the object:

CREATE OR REPLACE PROCEDURE SET_EMP_PIC
  (ord_procedure_path in varchar2
   ) 
as
    localImage  ordsys.ordimage;
    thisRowID   urowid;
begin
    /* Retrieve the image object from the database into a local object so
     * we can set the properties.
     */
    SELECT PICTURE,ROWID INTO localImage, thisRowID
        FROM EMPLOYEE_PIC WHERE EMP_ID = ord_procedure_path FOR UPDATE;

    /* Set the properties if the source type is local or FILE. If 
     * the source type is HTTP, then the properties cannot be set.
     * Update the image object in the table when done.
     */
    if localImage.isLocal() or upper( localImage.getSourceType() ) = 'FILE'
then
        localImage.SetProperties();
        UPDATE EMPLOYEE_PIC SET PICTURE = localImage 
          WHERE ROWID = thisRowID;
    end if;
end;

Section 4.2.3 describes these procedures in more detail.

4.2 Creating PL/SQL Procedures

In addition to using the PL/SQL procedures created by Oracle8i interMedia Clipboard, you can create your own procedures or modify the ones created by the Clipboard.

To store or update multimedia objects in a database, you create a procedure that inserts the object into the database and a procedure that sets the attributes of the object, as Section 4.2.2 describes.

To store or update multimedia objects stored as BFILEs or URLs, you create a procedure that sets the object to refer to a BFILE or URL. For BFILEs, you also create a procedure that sets the attributes of the object. Section 4.2.3 describes these procedures.

To retrieve multimedia objects from a database, you create a procedure that selects the object from the table, as Section 4.2.4 describes.

To retrieve multimedia objects stored as BFILEs or URLs, you create a procedure that determines where the object is stored and retrieves the object, as Section 4.2.3 describes.

Before you create the procedures, you must understand the parameters you use in the procedures. Section 4.2.1 describes those parameters.

Note:
Oracle8i interMedia Web Agent caches metadata information for the argument list of all PL/SQL procedures used for mediaput or mediaget requests. The use of the cache avoids the overhead of reading the information from the database for every request. Therefore, if you modify the argument list of a PL/SQL procedure, you must reload the Web Agent using the Online Administration form. Reloading the Web Agent reinitializes the metadata cache.

4.2.1 Supported Parameters

In the PL/SQL procedures supported by Oracle8i interMedia Clipboard and Web Agent, Oracle8i interMedia Web Agent uses certain parameter names to indicate specific values or handles.

You use the following parameter names to specify the parameters:

HTTP_STATUS: Output parameter. Optional.

Specifies the completion status of a request using the HTTP status codes. For a list of the status codes, see Section 4.3.1.
ORD_CONTENT_TYPE: Input parameter for mediaget requests; output parameter for mediaput requests. Required for mediaget requests.

Specifies the MIME type of the multimedia data. For example, it can specify image/jpeg or audio/wav.
ORD_CONTENT_LENGTH: Output parameter. Optional.

Specifies the length of the multimedia data. Although this parameter is optional, use it for content data types other than VARCHAR2. It avoids an extra round-trip between Oracle8i interMedia Web Agent and the database, thereby reducing the time required to retrieve the data.
One or more of the following output parameters. Required, unless you are storing objects only in BFILEs or HTTP server URLs.
- ORD_CONTENT_BLOB: Specifies the LOB locator for multimedia data with the BLOB data type.
- ORD_CONTENT_CLOB: Specifies the LOB locator for multimedia data with the CLOB data type.
- ORD_CONTENT_BFILE: Specifies the BFILE locator for multimedia data stored in external binary files.
- ORD_CONTENT_VARCHAR2: Specifies the actual data for multimedia data with a data type of VARCHAR2.
ORD_PROCEDURE_PATH: Input parameter. Required unless you pass the information using query string or POST data variables.

Contains path information. In particular, it contains the path information that follows the PL/SQL procedure name in a URL.
ORD_HTML_REDIRECT: Output parameter. Optional.

Specifies the URL that the browser should use to obtain the requested information. Use this parameter to redirect the results of mediaput requests. For more information on using this parameter, see Section 4.3.2.
HTTP_REDIRECT: Output parameter. Optional.

Specifies the URL that the browser should use to obtain the requested information. Use this parameter to redirect the results of mediaget requests. For example, if an employee photograph does not exist, you can use this parameter to return the URL of an image file that contains the message "Employee picture is not available." If you do not use this parameter, the request returns the content of the multimedia object or an error message. For more information on using this parameter, see Section 4.3.2.
HTTP_LAST_MODIFIED: Output parameter. Optional.

Specifies the date that the object was last modified. The value is used to set the Last-Modified HTTP response header field.
HTTP_CACHE_CONTROL: Output parameter. Optional.

Specifies whether or not the object is stored in cache. The value is used to set the Cache-Control HTTP request header field. To specify that the object is not stored in cache, set the value to no-cache. This parameter is useful when you are transforming objects, such as scaling images, and you want to ensure that you are not viewing objects from cache.

All other parameter names in a PL/SQL procedure are treated as input parameters and must match a variable in one of the following categories:

Request header fields

You can pass the value of fields in the request header by prefixing the field name with HTTP_. For example, to pass the value of the If-Modified-Since request header field to a PL/SQL procedure, specify an input parameter named HTTP_IF_MODIFIED_SINCE. Request header fields are defined by the HTTP protocol specification.
Query string variables or POST data variables

You can pass the values of query string or POST data variables to a PL/SQL procedure by specifying the name of the variable as a parameter name.

4.2.2 Creating PL/SQL Procedures to Store Multimedia Objects

To store or update multimedia objects in a database, you create PL/SQL procedures that process mediaput requests. Procedures that process mediaput requests can use any of the following parameters:

One or more of the following output parameters to store the object in the database, if the data is to be stored in a BLOB or a CLOB:
- ORD_CONTENT_BLOB
- ORD_CONTENT_CLOB
Note that to store or update objects in BFILEs or URLs, these parameters must be NULL. See Section 4.2.3 for information about storing or updating objects in BFILEs or URLs.
ORD_PROCEDURE_PATH as an input parameter
HTTP_STATUS
HTTP_REDIRECT
ORD_HTML_REDIRECT
ORD_CONTENT_TYPE

The mediaput request passes path information, such as a key value for the row, into the ORD_PROCEDURE_PATH parameter in the PL/SQL procedure. You use the information to locate the object in the database.

Before you update the multimedia object in the database, you must ensure that the database column is empty. You can use the EMPTY_BLOB or EMPTY_CLOB function to set the column to empty. (Note that NULL is not the same as empty.)

The following example shows a simple PL/SQL procedure that stores an audio object in a BLOB:

CREATE OR REPLACE PROCEDURE PUT_SOUNDS
(
    ord_procedure_path IN VARCHAR2,
    ord_content_type IN VARCHAR2,
    ord_content_blob OUT BLOB
) AS 
BEGIN
    
    UPDATE SOUNDS T
      SET T.SOUND = ORDSYS.ORDAUDIO ( null,ORDSYS.ORDSource(empty_blob(),
                                  null,null,null,null,null),
                                  null,null,null,null,null,null,null,null,null )
      WHERE T.SOUND_ID = ord_procedure_path AND ROWNUM = 1;

SELECT T.SOUND.GetContent()
    INTO ord_content_blob
       FROM SOUNDS T
       WHERE T.SOUND_ID = ord_procedure_path AND ROWNUM = 1
       FOR UPDATE;

END;

As the preceding example shows, you use the ORD_PROCEDURE_PATH parameter, which the request passes using the ord_procedure_path variable name, to locate the object. You use the EMPTY_BLOB function to set the object to empty.

You use the GetContent method to return the LOB locator to Oracle8i interMedia Web Agent. Using the LOB locator returned by the procedure, Oracle8i interMedia Web Agent stores the multimedia object, identified by the ord_content variable, in the database.

After storing the updated multimedia object, you must set the attributes of the object, using a separate PL/SQL procedure. The following procedure uses the SetMimeType method to set the MIME type of an audio object:

CREATE OR REPLACE PROCEDURE SET_SOUNDS
(
    ord_procedure_path IN VARCHAR2,
    ord_content_type IN VARCHAR2
 ) 
as var_snd ORDSYS.ORDAudio;
BEGIN
    /* Select the object.*/
    SELECT T.SOUND INTO var_snd
        FROM SOUNDS T
        WHERE T.SOUND_ID = ord_procedure_path;

    /* Set the MIME type. */
    var_snd.SetMimeType(ord_content_type );
    
/* Update the row with the processed object.*/
    UPDATE SOUNDS T
        SET T.SOUND = var_snd
        WHERE T.SOUND_ID = ord_procedure_path;

END;

If you set the properties, such as the MIME type, of a multimedia object when you store the object initially, the properties do not need to be determined each time the object is retrieved.

When you use the ORDImage or ORDVir object types, you can use the SetProperties method to set the properties, such as the MIME type, height, width, and image type, of the object. When you use the ORDAudio or ORDVideo object types, you must set the MIME type using the SetMimeType method.

The following example shows the coding for the simple form that uses the PUT_SOUNDS and SET_SOUNDS procedures to store ORDAudio objects:

<form action="http://nedc.us.ora.com:80/intermedia/aud_wr/mediaput/PUT_SOUNDS"
      method="post"
      enctype="multipart/form-data" >
<input type="hidden" name="ord_post_put_call" value="SET_SOUNDS">
<BR>sound ID: 
<input type="text" name="ord_procedure_path" length=5>
<BR>Sound File:
<input type="file" name="ord_content">
<BR><input type="Submit" value="Store Sound Now">
</form>

4.2.3 Creating PL/SQL Procedures to Store or Update BFILEs or URLs

Storing or updating multimedia objects using external files (BFILEs) or URLs is somewhat different than storing multimedia objects directly in a database. You cannot use the ORD_CONTENT_BLOB or ORD_CONTENT_CLOB parameters to store multimedia data that is stored BFILEs or in HTTP server URLs. You must specify application-specific parameters to specify information about the location of the object and set the object attributes to indicate the source location. Then, you can use a mediaput request to store or update the data.

If both of the following conditions are true, the Web Agent assumes that the location is a BFILE or an HTTP server URL:

The ord_content variable is not present or the length of the content in the ord_content variable is 0.
The status of the ORD_CONTENT_BLOB or ORD_CONTENT_CLOB parameter is NULL.

The following PL/SQL procedure lets users store images regardless of whether the location is in the database, in BFILEs, or in HTTP server URLs. The procedure stores the image by writing to a LOB, or by setting the object to refer to a BFILE or to an HTTP location using a URL, based on the information passed to the procedure.

CREATE OR REPLACE PROCEDURE PUT_EMP_PIC
(   image_file_directory IN VARCHAR2,
    image_file_name IN VARCHAR2,
    image_http_path IN VARCHAR2,
    image_http_name IN VARCHAR2,
    ord_procedure_path IN VARCHAR2,
    ord_content_blob OUT BLOB
    ) 
as
    LocalImage          ORDSYS.ORDImage;
begin

  /* Create an empty object.*/
    localImage := ORDSYS.ORDImage( ORDSYS.ORDSource( empty_blob(),
                                              null, null, null, null, null ),
                                  null, null, null, null, null, null, null );

  /* Process the request based on the location of the new image.*/
    if length( image_file_directory ) > 0 then
      /* Image is stored as a BFILE in a database server directory.
       * Set the local image object to refer to the specified file.
       */
      localImage.clearLocal();
      localImage.setSource( 'FILE',
                            image_file_directory,
                            image_file_name );

    elsif length ( image_http_path ) > 0 then
      /* Image is stored on a Web server.
       * Set the local image to refer to the URL.
       */
      localImage.clearLocal();
      localImage.setSource( 'HTTP',
                            image_http_path,
                            image_http_name );

    else
      /* Image is being uploaded from the client to be stored in the database.
       * Set the flag to indicate the image is to be stored in the object's 
         local-data BLOB.
       */
      localImage.setLocal();
    end if;

    /* Update the image object in the table. If the image is to be stored in
     * the object's local-data BLOB, then return the LOB handle so the Web
     * Agent can store the image in the database.
     */
    if localImage.isLocal() then
        UPDATE EMPLOYEE_PIC I SET I.EMPLOYEE_PIC = localImage 
            WHERE EMP_ID = ord_procedure_path
            RETURN I.PICTURE.Source.localdata INTO ord_content_blob;
    else
        UPDATE EMPLOYEE_PIC I SET I.EMPLOYEE_PIC = localImage 
            WHERE EMP_ID = ord_procedure_path;
    end if;

end;

When you store an object as a BFILE, you must pass the following to the SetSource method:

The keyword FILE as the source type of the object.
The BFILE directory as the source location. The BFILE directory specifies a logical alias name for a physical directory on the server's file system under which the file to be accessed is located. You use the CREATE DIRECTORY command to map the alias to the physical directory.
The file name as the source name.

When you store an object as a URL, you must pass the following to the SetSource method:

The keyword HTTP as the source type of the object.
The path of the URL as the source location.
The file name as the source name.

After you call the PUT_EMP_PIC procedure, you call a procedure, SET_EMP_PIC, that sets the properties of the object. However, note that you cannot set the properties of an object stored as a URL.

The following example shows a PL/SQL procedure that sets the properties of the objects:

CREATE OR REPLACE PROCEDURE SET_EMP_PIC
  (ord_procedure_path in varchar2
   ) 
as
    localImage  ordsys.ordimage;
    thisRowID   urowid;
begin
    /* Retrieve the image object from the database into a local object so
     * we can set the properties.
     */
    SELECT PICTURE,ROWID INTO localImage, thisRowID
        FROM EMPLOYEE_PIC WHERE EMP_ID = ord_procedure_path FOR UPDATE;

    /* Set the properties if the source type is local or FILE. If 
     * the source type is HTTP, then the properties cannot be set.
     * Update the image object in the table when done.
     */
    if localImage.isLocal() or upper( localImage.getSourceType() ) = 'FILE'
then
        localImage.SetProperties();
        UPDATE EMPLOYEE_PIC SET PICTURE = localImage 
          WHERE ROWID = thisRowID;
    end if;
end;

The following example shows the form that calls the preceding PL/SQL procedures:

<form 
  action="http://nedc.us.ora.com/intermedia/sample_upload/mediaput/put_emp_pic"
  method=post  
  enctype="multipart/form-data">
<p>
Image ID: <input type="text" name="ord_procedure_path" length="10">
<p>
Store Image in a BLOB

<p>Image file: <input type="file" name="ord_content">

<p>Store Image as BFILE
<p> Image file directory:
 <input type="text" name="image_file_directory", size="32" length="32">
<p> Image file name: 
<input type="text" name="image_file_name", size="32" length="32">

<p>Store Image as HTTP URL
<p>Image URL path:
 <input type="text" name="image_http_path", size="32" length="128">
<p>Image file name:
<input type="text" name="image_http_name", size="32" length="32">

<input type="hidden" name="ord_post_put_call" value="set_emp_pic">
<p>
<input type="submit" value="Store Image Now" >
</FORM>

Storing an audio or video object as a BFILE or URL is similar to storing an image as a BFILE or URL. However, the ORDAudio or ORDVideo object types do not set the MIME type. Depending on the location of the object, you must take specific actions:

For BFILEs, you must explicitly set the MIME type by passing it to a parameter. In the following extract of an example that stores ORDAudio objects, the MIME type is passed to the parameter SOUND_FILE_MIME_TYPE from the client:

if length( sound_file_directory ) > 0 then
      localSound.clearLocal();
      localSound.setSource( 'FILE',
                            sound_file_directory,
                            sound_file_name );
      localSound.setMimeType( sound_file_mime_type );

For objects to be stored in the database, you set the MIME type using the value that is provided implicitly by a browser when a sound file is stored, as shown in the following extract of an example that stores ORDAudio objects:
```
    localSound.setLocal();
    localSound.setMimeType( ord_content_type );
```
For URLs, the MIME type is retrieved by the client when the URL is retrieved. Therefore, you do not need to set the MIME type.

Section 4.2.5 shows how to retrieve an object regardless of whether it is stored in the database as a BLOB or CLOB, a BFILE, or a URL.

4.2.4 Creating PL/SQL Procedures to Retrieve Multimedia Objects

To retrieve multimedia objects, you create PL/SQL procedures that process mediaget requests. Procedures that process mediaget requests must use the following parameters:

ORD_CONTENT_TYPE as an output parameter
One or more of the following output parameters:
- ORD_CONTENT_BLOB
- ORD_CONTENT_CLOB
- ORD_CONTENT_BFILE
- ORD_CONTENT_VARCHAR2
The procedure must return exactly one non-NULL multimedia content locator or value. If it does, Oracle8i interMedia Web Agent returns an HTTP status code of 200 (OK) by default.
ORD_PROCEDURE_PATH as an input parameter

In addition to these parameters, mediaget requests can use any of the other parameters listed in Section 4.2.1.

The mediaget request passes path information, such as the key value for the row, into the ORD_PROCEDURE_PATH parameter. The PL/SQL procedure returns the MIME type of the multimedia object in the ORD_CONTENT_TYPE parameter, the content length in the ORD_CONTENT_LENGTH parameter if specified, and the LOB locator or value in the ORD_CONTENT_BLOB, ORD_CONTENT_CLOB, ORD_CONTENT_BFILE, or ORD_CONTENT_VARCHAR2 parameter.

The following example shows a simple PL/SQL procedure that retrieves an audio object stored in a BLOB from the table SOUNDS:

CREATE OR REPLACE PROCEDURE GET_SOUNDS
(
    ord_procedure_path IN VARCHAR2,
    http_if_modified_since IN VARCHAR2,
    http_status OUT VARCHAR2,
    http_last_modified OUT VARCHAR2,
    ord_content_type OUT VARCHAR2,
    ord_content_length OUT NUMBER,
    ord_content_blob OUT BLOB
   ) as   db_mod_date date;
begin
  /* Get the content, content type, content length, and last-modified date from
   * the object. */
    SELECT
        T.SOUND.GetContent(),
        T.SOUND.GetMimeType(),
        T.SOUND.GetContentLength(),
        T.SOUND.GetUpdateTime()
    INTO
        ord_content_blob,
        ord_content_type,
        ord_content_length,
        db_mod_date
    FROM SOUNDS T
    WHERE T.SOUND_ID = ord_procedure_path AND ROWNUM = 1;

    /* If the content type is null, set it to unknown. */
    IF ord_content_type IS NULL
    THEN
        ord_content_type := 'audio/x-unknown';
    END IF;

    /* Determine the HTTP status and last-modified date. */
    http_status := Ordwebutl.cache_status(db_mod_date, 
                                 http_if_modified_since, http_last_modified );
END;

As the preceding procedure shows, you can retrieve the MIME type, content length, content, and last-modified date by using the following methods supported by the Oracle8i interMedia object types. These methods are supported for the ORDImage, ORDAudio, ORDVideo, and ORDVir object types.

You use the GetMimeType method to return the MIME type. Then, you read the MIME type into the ORD_CONTENT_TYPE parameter.
You use the GetContentLength method to return the length. Then, you read the length into the ORD_CONTENT_LENGTH parameter.
You use the GetContent method to return the content. Then, you read the content LOB locator into one of the LOB locator parameters, such as ORD_CONTENT_BLOB. Alternatively, you can read the content value into the ORD_CONTENT_VARCHAR2 parameter.
You use the GetUpdateTime method to return the last-modified date. Then, you read the date into the DB_MOD_DATE parameter.

If you are using object types other than ORDImage, ORDAudio, ORDVideo, or ORDVir, see Section 4.2.6.

To retrieve the cache status, you use the PL/SQL package ORDWEBUTL. For information on cache status and using this package, see Section 4.2.7.

For information about handling the flow of the application and handling errors, see Section 4.3.

4.2.5 Creating PL/SQL Procedures to Retrieve BFILEs or URLs

Retrieving multimedia objects from BFILEs or URLs is somewhat different than retrieving multimedia objects directly from a database. The following example retrieves an image regardless of its location, and redirects the output if the object is stored in a URL:

CREATE OR REPLACE PROCEDURE GET_EMP_PIC
  (
    ord_procedure_path in varchar2,
    http_if_modified_since in varchar2,
    http_status out varchar2,
    http_last_modified out varchar2,
    http_redirect out varchar2,
    ord_content_type out varchar2,
    ord_content_length out number,
    ord_content_blob out blob,
    ord_content_bfile out bfile
    )
as
    localImage    ordsys.ordimage;

begin
    /* Retrieve the image object from the database into a local object.*/
    SELECT PICTURE INTO localImage FROM EMPLOYEE_PIC 
    WHERE EMP_ID = ord_procedure_path;

    /* Determine the status of the client's cache. If the client's cache
     * is up-to-date, then return the object immediately.
     */
    http_status := ordwebutl.cache_status( localImage.getUpdateTime(),
                                           http_if_modified_since,
                                           http_last_modified );
    if http_status = 304 then
        return;
    end if;

    /* Determine the location of the image.*/
    if localImage.isLocal() then
        /* The image is stored locally in the localData BLOB attribute. */
        ord_content_type   := localImage.getMimeType();
        ord_content_length := localImage.getContentLength();
        ord_content_blob   := localImage.getContent();

    elsif upper( localImage.getSourceType() ) = 'FILE' then
        /* The image is stored as a file from which ORDSource creates a BFILE.*/
        ord_content_type   := localImage.getMimeType();
        ord_content_length := localImage.getContentLength();
        ord_content_bfile  := localImage.getBFILE();

    elsif upper( localImage.getSourceType() ) = 'HTTP' then
        /* The image is referenced as an HTTP entity, so redirect
         * the client to the URL provided by ORDSource. */
        http_status        := 302;
        http_redirect      := localImage.getSource();

    else
        /* The image is stored in an application-specific location for
         * which no default action is available.*/
        null;
    end if;
end;

Retrieving ORDAudio and ORDVideo objects that are stored as BFILEs or URLs is similar to retrieving ORDImage objects. However, the ORDVideo object type does not provide a method in the ORDSource type to call the GetBfile method. You must call the GetBfile method directly, as shown in the following example:

elsif upper( localVideo.getSourceType() ) = 'FILE' then
        ord_content_type   := localVideo.getMimeType();
        ord_content_length := localVideo.getContentLength();
        ord_content_bfile  := localVideo.source.getBFILE();

For information on using the HTTP_REDIRECT parameter to redirect output, see Section 4.3.2.

4.2.6 Using Other Object Types

Object types other than ORDImage, ORDAudio, ORDVideo, and ORDVir may not support methods to get the MIME type, last-modified date, or content length, or to set the object to empty. If you are using other object types, note the following:

To convert the content type to a MIME type, you can use the SQL function DECODE, as shown in the following example:

SELECT T.PICTURE.CONTENT,
       DECODE( T.PICTURE.fileFormat,'JFIF','image/jpeg','TIFF','image/tiff',
                                  'GIFF','image/gif', 'image/x-unknown' ),
       LAST_MODIFIED
       INTO CONTENT_BLOB, CONTENT_TYPE, DB_MOD_DATE
       FROM PICTURES T

To retrieve the last-modified date of the object, create a column in the table or an attribute of a user-defined object and compare it with the date of the object in cache.
To retrieve the content length of a multimedia object stored as a BLOB, CLOB, or BFILE, use the GETLENGTH function of the DBMS_LOB package, as shown in the following example:
```
ord_content_length := dbms_lob.getlength( ord_content_blob );
```
To set a multimedia object stored as a BLOB or CLOB to empty before storing or updating the object, use the SQL functions EMPTY_BLOB or EMPTY_CLOB, as shown in the following example:
```
UPDATE PICTURES T
    SET T.PICTURE.CONTENT = empty_blob()
    WHERE T.NAME = ord_procedure_path AND ROWNUM = 1;
```

4.2.7 Checking the Modification Date of an Object

For more efficient retrieval of data, consider taking advantage of caching of the objects by clients, such as Web browsers. Oracle8i interMedia Web Agent provides the ORDWEBUTL package, which contains functions that help you determine the status of the cached objects. See the online Read Me file for information about installing the ORDWEBUTL package.

The ORDWEBUTL package provides the following functions:

HTTP_TO_ORACLE_DATE

Converts a date from the character HTTP format to an internal Oracle date format. It accepts a date with a VARCHAR2 data type and returns a date with the Oracle DATE data type.

The ORDWEBUTL.CACHE_STATUS function uses this function to convert dates, as shown in the following example:
```
cli_ifmod_since_local := http_to_oracle_date( cli_ifmod_since );
```
ORACLE_TO_HTTP_DATE

Converts a date from an internal Oracle format to the character HTTP date format. It accepts a date with the Oracle DATE data type and returns a date with a VARCHAR2 data type.

The ORDWEBUTL.CACHE_STATUS function uses this function to convert dates, as shown in the following example:
```
cli_lastmod_date := oracle_to_http_date( db_lastmod_date );
```
CACHE_STATUS

Determines if an object in the client cache is current. For input, this function accepts the last-modified date of the object in the database and the value of the If-Modified-Since HTTP request header variable, which contains the date of the object in the cache. When you use the Oracle8i interMedia object types, you can retrieve the last-modified date and time by using the GetUpdateTime method. Otherwise, you can use the date from a column or attribute that you create for that purpose. The CACHE_STATUS function compares the two dates to see if the object in client cache is current.

The following example shows how to use the CACHE_STATUS function in a PL/SQL procedure:
```
http_status := ordwebutl.cache_status( db_mod_date,
                                      http_if_modified_since,
                                      http_last_modified );
```
If the object in the database has not been modified since it was cached at the client, the object in cache is considered current. In this case, the CACHE_STATUS function returns the HTTP status code 304 (Not Modified).

If the object in the database has been modified since it was cached (the date of the object in cache is earlier than the date of the object in the database), the object in the cache is considered not current. If the object is not current or if the client did not send an If-Modified-Since HTTP request header (because the object has never been cached), the function returns the HTTP status code 200 (OK) and uses the last-modified date of the object to generate an HTTP date. Oracle8i interMedia Web Agent sends the date as the value of the Last-Modified HTTP response header, as well as the content length and content, to the browser.

If the last-modified date is NULL, the function returns the HTTP status code 200 (OK) and a null value for the Last-Modified HTTP response header, which Oracle8i interMedia Web Agent does not send to the browser.

All HTTP dates are expressed as Greenwich Mean Time (GMT). If your Web server is located in another time zone, you can use the following ORDWEBUTL package constants to specify the Web server time zone:

DBMS_SERVER_TIMEZONE

The Oracle server recognizes many time zones. If your server is located in one of these time zones, you can use the DBMS_SERVER_TIMEZONE constant to specify it. The following example sets the time zone to Eastern Standard Time (US and Canada):
```
dbms_server_timezone constant varchar2(3) := 'EST';
dbms_server_gmtdiff constant number := NULL;
```
When you use this constant, set the DBMS_SERVER_GMTDIFF constant to NULL. See Oracle8i SQL Reference for a list of supported time zones and the text strings used to specify them.
DBMS_SERVER_GMTDIFF

If your server is located in a time zone not listed, you can use the DBMS_SERVER_GMTDIFF constant to specify the time zone as the offset, in hours, from GMT. If the time zone is ahead of GMT, specify a positive number; if it is behind GMT, specify a negative number. The following example sets the time zone for Brisbane, Australia:
```
dbms_server_gmtdiff constant number := -10;
dbms_server_timezone constant varchar2(3) := NULL;
```
When you use this constant, set the DBMS_SERVER_TIMEZONE constant to NULL.

4.2.8 Understanding Differences from the PL/SQL Cartridge

Oracle8i interMedia Web Agent is specifically designed to efficiently retrieve and store multimedia objects. As such, it does not provide all the features of the PL/SQL cartridge, which is a component of Oracle Application Server. However, when you use the Web Agent, you can use Web servers other than Oracle Application Server and you can retrieve and store multimedia objects quickly.

Some differences between the Web Agent and the PL/SQL cartridge are:

The PL/SQL cartridge provides a toolkit for dynamically creating HTML pages.
The PL/SQL cartridge supports procedure overloading.
The PL/SQL cartridge supports default values in PL/SQL argument declarations. If no query-string or POST data variable name matches an argument name, the Web Agent sets the argument to NULL. The PL/SQL cartridge uses the default value.
The Web Agent ignores extraneous query string or POST data variables. The PL/SQL cartridge returns an error.
With the Web Agent, you can use Web servers other than Oracle Application Server. The PL/SQL cartridge works only with Oracle Application Server.

4.3 Handling Application Flow

When you use Oracle8i interMedia Web Agent to retrieve, store, and update data, Oracle8i interMedia Web Agent returns the status of the request using HTTP status codes. You can use the status code to help you control the flow of the application.

4.3.1 Understanding HTTP Status Codes

When Oracle8i interMedia Web Agent processes requests, it returns the status of the request using the HTTP status codes shown in Table 4-1.

Table 4-1 HTTP Status Codes

Status Code	Status Message	Meaning
200	OK	Success. For mediaget requests, Oracle8i interMedia Web Agent returns the multimedia object, the status, and other header information to the client. For mediaput requests, it returns the status and a success message.
302	Redirect	The response to the request has been redirected. Oracle8i interMedia Web Agent returns a URL.
304	Not Modified	The object has not been modified since it was last stored in the client cache. Oracle8i interMedia Web Agent does not return the multimedia object to the client. It returns only the status and other header information.
400	Bad Request	The request does not conform to a valid format or does not contain sufficient information. Oracle8i interMedia Web Agent returns the status and an error message.
401	Unauthorized	You must supply correct authentication information to access the requested information. Oracle8i interMedia Web Agent returns the status and an error message.
403	Forbidden	You are not authorized to access the requested information. Oracle8i interMedia Web Agent returns the status and an error message.
404	Not Found	The information you requested was not found. Oracle8i interMedia Web Agent returns the status and an error message.
405	Method Not Allowed	The server cannot process your request using the specified HTTP method. Oracle8i interMedia Web Agent returns the status and an error message.
500	Internal Server Error	An unexpected error was encountered while processing your request. Oracle8i interMedia Web Agent returns the status and an error message.
503	Service Unavailable	The server is unable to process your request at this time. Oracle8i interMedia Web Agent returns the status and an error message.

PL/SQL procedures that retrieve data must return exactly one non-NULL multimedia content locator or value. If the content parameters or select list items that the procedure returns are all null or if more than one is not null, Oracle8i interMedia Web Agent returns a status code of 500 and the error message. "An unexpected error was encountered while processing your request."

If a request to retrieve data or store data completes successfully, but does not return a status code, Oracle8i interMedia Web Agent returns a status code of 200. If the request completes explicitly or implicitly with a successful status (2nn) or with a redirect status (3nn), Oracle8i interMedia Web Agent automatically commits the transaction at the end of the request.

If you do not want the transaction to be committed, modify the procedure to roll back the transaction before the request is completed.

If a request completes explicitly with a client error status (HTTP status code 4nn) or with a server error status (HTTP status code 5nn), Oracle8i interMedia Web Agent automatically rolls back the transaction at the end of the request. If you do not want the transaction to be rolled back, modify the procedure to commit the transaction before the request is completed.

4.3.2 Redirecting Output

You may want to redirect the output from a mediaget or mediaput request. For example, you may want to redirect the output in the following situations:

To return an application-specific HTML page or image when an error occurs during a mediaget or mediaput request
To return the URL of an object when the object is stored in an HTTP server-based URL, rather than in the database as a BLOB, CLOB, or external BFILE
To return information, such as summary data, after you store data in the database
To return an HTML page that contains a menu of choices

You can redirect the output by using the HTTP_REDIRECT or the ORD_HTML_REDIRECT parameter. Generally, you use the HTTP_REDIRECT parameter for mediaget requests and the ORD_HTML_REDIRECT parameter for mediaput requests.

Redirecting Output for Mediaget Requests

In some cases, browsers cannot display the error messages returned by Oracle8i interMedia Web Agent. For example, if a request attempts to retrieve an image and display it using the <IMG> tag, the browser can accept only an image as a response to the request. If the request fails because the image is not found, Oracle8i interMedia Web Agent sends a message in the form of an HTML page. Because an HTML page is not a valid image, the browser displays a broken image icon.

To avoid this situation, you can design the PL/SQL procedure to redirect the browser to display the contents of a URL that points to an HTML file that contains an image that graphically displays the text "No photograph available." To redirect the browser, you use the HTTP_REDIRECT parameter in the PL/SQL procedure. The following example shows a code fragment that redirects the browser to display the contents of a specified URL when no data is returned:

    if ord_content_type is null
    then
       HTTP_STATUS := '302';
       HTTP_REDIRECT := 'http://imedia.ora.com/misc_gif/no_photo_avail.gif';
    end if;

You can use redirection when the ORDSource of the object indicates that the object is stored in an HTTP server-based URL rather than in the database as a BLOB or in an external binary file (BFILE). The following extract from a PL/SQL procedure retrieves an image object and redirects the output if the object is stored in a URL:

elsif upper (localImage.GetSourceType() ) = 'HTTP' then
        /* The image is referenced as an HTTP entity, so we have to redirect
         * the client to the URL provided by ORDSource. */
        http_status        := 302;
        http_redirect      := localImage.GetSource();

To review the entire procedure, which retrieves an image regardless of its location, see Section 4.2.5.

Redirecting Output for Mediaput Requests

You can also use redirection when you store data. In most cases, it is more efficient to use the ORD_HTML_REDIRECT parameter rather than the HTTP_REDIRECT parameter when redirecting mediaput requests. The HTTP_REDIRECT parameter usually causes the browser to resend the original request to the new location. The ORD_HTML_REDIRECT parameter eliminates this unnecessary duplication. The ORD_HTML_REDIRECT parameter uses the HTML META tag to redirect the client when the HTTP_STATUS is not specified or is specified as 200.

You can use redirection after successfully executing a request. For example, after a request successfully updates an employee's photograph in the database, the Web Agent returns a success message. You can use redirection to return a page that tells the user that the request was successful and asks if the user wants to add another employee or exit the application.

However, note that if you use redirection from a procedure that stores or updates data, the Web Agent immediately executes the redirection and does not write the data to the database. To successfully store data and use redirection, you store the data in one procedure and then you redirect the output using a post-processing procedure or SQL statement. You call the post-processing procedure by using the ord_post_put_call variable from the form; you call the post-processing SQL statement by using the ord_post_put_sql variable.

The following example shows a code fragment from the post-processing procedure that redirects the browser to display a page that asks if the user wants to continue:

   http_status := '200';
   ORD_HTML_REDIRECT := 'http://imedia.ora.com/forms/continue.html';

You can also use redirection to return information, such as summary information, after a mediaput request completes. For example, after a request successfully adds a movie to the database, you can use redirection to return a list of movie titles.

When you use redirection to handle error conditions resulting from storing or updating data, you can use redirection directly from the put procedure. For example, if a request to store data detects an unauthorized attempt to store data, you can use redirection from the procedure to return a URL of an error page.

4.3.3 Customizing Applications for Storing Multimedia Objects

The mediaput request is designed to store multimedia data from a browser using an HTML form. Each mediaput request results in the status of the request being displayed for the user as an HTML page, as the following examples demonstrate:

Oracle interMedia Web Agent

Your Mediaput request was processed successfully.

Oracle interMedia Web Agent

An error occurred processing your MEDIAGET or MEDIAPUT request

MWM-00740: MEDIAPUT request did not return a non-NULL content handle

However, you may want to store multimedia data using a customized application, perhaps one written in Java or C/C++. In this situation, use the request mode appmediaput to return the error in an easy-to-parse, fixed format. It returns the results of the request using the following format with a content type of text/plain:

status:facility-status: error text

A nonzero status code indicates some form of error condition.

For example, the successful execution of an appmediaput request results in the following message:

0:MWM-00000: normal, successful completion

However, an appmediaput request might return the following message when you try to store an object into a nonexistent row:

740:MWM-00740: MEDIAPUT request did not return a non-NULL content handle

The application can easily parse the status code that precedes the colon to determine how the request completed.

4.3.4 Debugging Mediaget Requests

If you encounter an error condition, you can debug the mediaget request using the ~mediaget request mode. The ~mediaget request mode provides more information about error conditions from mediaget requests.

For example, assume that the following URL returns an HTTP status of 404 (Not Found):

http://web.ora.com:8001/intermedia/empdb_read/mediaget/get_emp_pic/90001

Depending on the Web server you are using, the mediaget request mode returns a message similar to the following:

If you substitute the request mode ~mediaget, the URL returns an HTTP status of 200 (OK) and error messages from Oracle8i interMedia Web Agent in a dynamically generated HTML page:

For information about Oracle8i interMedia Web Agent error messages and actions you can take to correct errors, see Appendix A.

4 Storing Data and Creating PL/SQL Procedures