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

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

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

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

Perform these steps:

Step 1. Create a table to store images for the application after connecting to a schema with privileges to create a table.

For example:

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

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

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

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

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

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

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

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

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

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

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

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

Step 4. Change DADs to the SCOTTCW DAD.

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

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

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

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

Step 5. Create and test the media upload procedure.

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

1. Select the database table and procedure type.

   1. Click the CW_IMAGES_TABLE database table.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the PL/SQL document upload table.

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

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

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

3. Select the data access and media columns.

   1. Click IMAGE (ORDIMAGE).

   2. Click ID (Primary key).

   3. Click Conditional insert or update.

   4. Click Next.

4. Select additional columns and procedure names.

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

   2. Accept the procedure name provided, UPLOAD_CW_IMAGES_TABLE_IMAGE.

   3. Click Create procedure in the database.

   4. Click Next.

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

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

   Click Finish.

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

   The Code Wizard displays this message:

   Procedure created successfully: UPLOAD_CW_IMAGES_TABLE_IMAGE
   

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

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

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

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

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

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

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

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

      • Click Upload media.

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

   6. Close the window.

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

   A copy of the generated image upload procedure 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 Oracle Multimedia object(s) for update.
     --
     SELECT mtbl.IMAGE INTO local_IMAGE
       FROM CW_IMAGES_TABLE mtbl WHERE mtbl.ID = in_ID FOR UPDATE;
     --
     -- Store media data for the column in_IMAGE.
     --
     IF in_IMAGE IS NOT NULL
     THEN
       SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content INTO
              upload_size, upload_mimetype, upload_blob
         FROM CW_IMAGE_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
       IF upload_size > 0
       THEN
         dbms_lob.copy( local_IMAGE.source.localData, 
                        upload_blob, 
                        upload_size );
         local_IMAGE.setLocal();
         BEGIN
           local_IMAGE.setProperties();
         EXCEPTION
           WHEN OTHERS THEN
             local_IMAGE.contentLength := upload_size;
             local_IMAGE.mimeType := upload_mimetype;
         END;
       END IF;
       DELETE FROM CW_IMAGE_UPLOAD_TABLE dtbl WHERE dtbl.name = in_IMAGE;
     END IF;
     --
     -- Update Oracle Multimedia objects in the table.
     --
     UPDATE CW_IMAGES_TABLE mtbl
       SET mtbl.IMAGE = local_IMAGE
       WHERE mtbl.ID = in_ID;
     --
     -- Display the template completion message.
     --
     htp.print( '<html>' );
     htp.print( '<title>Oracle Multimedia Code Wizard: Template Upload
   Procedure</title>' );
     htp.print( '<body>' );
     htp.print( '<h2> Oracle Multimedia Code Wizard:
   Template Upload Procedure</h2>' );
     htp.print( 'Media uploaded successfully.' );
     htp.print( '</body>' );
     htp.print( '</html>' );
   END UPLOAD_CW_IMAGES_TABLE_IMAGE;
   

   The previous image upload procedure declares these input parameters and variables:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Step 6. Create and test a media retrieval.

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

1. Select the database table and procedure type.

   1. Click CW_IMAGES_TABLE.

   2. Click Standalone procedure.

   3. Click Next.

2. Select the media column and key column.

   1. Click IMAGE (ORDIMAGE).

   2. Click ID (Primary key).

   3. Click Next.

3. Select the procedure name and parameter name.

   1. Accept the procedure name provided, GET_CW_IMAGES_TABLE_IMAGE.

   2. Accept the parameter name provided, MEDIA_ID.

   3. Click Create procedure in the database.

   4. Click Next.

4. Review the following selected procedure creation options:

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

   Click Next.

6. Compile the procedure and review the generated source.

   The Code Wizard displays this message:

   Procedure created successfully: GET_CW_IMAGES_TABLE_IMAGE
   

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

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

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

      The retrieved image is displayed in another window.

   4. Close the window.

   5. Click Done to return to the Main menu.

   A copy of the generated image retrieval procedure 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' ) <> 'HEAD' THEN
         wpg_docload.download_file( localBlob );
       END IF;
     ELSIF UPPER( localObject.getSourceType() ) = 'FILE' THEN
   
       --
       -- Data is stored as a file from which ORDSource creates 
       -- a BFILE.
       --
       localBfile  := localObject.getBFILE();
       owa_util.mime_header( localObject.getMimeType(), FALSE );
       ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
       owa_util.http_header_close();
       IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN
         wpg_docload.download_file( localBfile );
       END IF;
   
     ELSIF UPPER( localObject.getSourceType() ) = 'HTTP' THEN    --
       -- The image is referenced as an HTTP entity, so we have to 
       -- redirect the client to the URL that ORDSource provides.
       --
       owa_util.redirect_url( localObject.getSource() );
     ELSE
       --
       -- The image is stored in an application-specific data
       -- source type for which no default action is available.
       --
       NULL;
     END IF;
   END GET_CW_IMAGES_TABLE_IMAGE;
   

   The image retrieval procedure shown previously declares these input parameters and variables:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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