How-To Document

Integrating Oracle Application Express (formerly HTML DB) and Oracle Portal with the OmniPortlet:
Making an OmniPortlet Parameter Driven


Date: 15-NOV-2003

After completing this How-To, you should be able to understand:

  • How to add a parameter to an Oracle Portal page which will filter the data displayed in an OmniPortlet

Introduction

In Oracle Portal, the OmniPortlet is a powerful mechanism to capture and display data from a variety of external sources. One type of source which the OmniPortlet works with is XML. Since Oracle HTML DB can automatically format any report as an XML document, it is possible to integrate it with Oracle Portal by setting up an OmniPortet.

Software Requirements

Steps Required

In this example, we will create a Simple Parameter Form which will act as a filter for an OmniPortlet.

You must have completed the Create a Chart How To before attempting this one, as this How To will build on the chart that was created.

Steps in Oracle HTML DB

  • Navigate to the page where your HTML DB XML Report that you created in the Create a Chart How To is located
  • Change the SQL so that it now reads:
    SELECT b.product_name, b.product_id, SUM(a.quantity * b.list_price)
           FROM DEMO_ORDER_ITEMS a, DEMO_PRODUCT_INFO b
           WHERE a.product_id = b.product_id
           A
                                              
    ND UPPER(b.product_name) LIKE '%' || UPPER(:PRODUCT_NAME) || '%'
           GROUP BY b.product_name, b.product_id
                                            

    Note: The demo_order_items & demo_product_info tables are installed as part of the Sample Application, in the schema that your workspace is based on. If you have removed this application or the tables, you can reinstall it by clicking on the Home icon, and then Review Demonstration Applications in the Tasks region in HTML DB.

  • Click on the Report Attributes tab, and under the Messages section, clear all text out of the When No Data Found Message.
  • Click on Apply Changes.
  • Click on Create in the Items region of the page.
  • Give the item the name PRODUCT_NAME, and set Display As to Hidden. Click on Next and then click on Create Item. This is the item that will receive the parameter from Oracle Portal and use it as a bind variable in the above SQL statement to limit the results of the report before it is passed over to Portal as an XML Document.

Steps in Oracle Portal

  • Sign on to Oracle Portal as a user with privileges to create pages and assemble content.
  • Navigate to where you created your chart in the How To Create a Chart.
  • Add another portlet to the page by selecting the Add Portlet icon. Navigate and find the Simple Parameter Form, and then click OK once you've added it to the page. You can add it anywhere on the page - it does not have to be in the same region as the OmniPortlet.
  • Click on Define your Simple Parameter Form.
  • You can optionally add header and footer text here, as well as change the title of the portlet. Click on Next.
  • In the Entry Fields section, change the Label for Field 1 to " Product Name" and change the Display As for Field 2 to Hidden. Click on Finish. You do not need to complete the items on the next page.
  • Edit the Page Properties by clicking on the Page: Properties link at the top of the page.
  • Click on the Parameters tab.
  • Under the New Page Parameter section, enter Param1 in the Parameter Name box and click on Add.
  • Click on the triangle next to the OmniPortlet Portlet. Set the value of Param1 to Page Parameter, and then the next value which will appear to Param1.
  • Click on the triangle next to the Sample Parameter Form Portlet. Set the value of Param1 to Page Parameter, and then the next value which will appear to Param1.
  • Click on OK.
  • Edit the Page Properties by clicking on the Page: Properties link at the top of the page.
  • Click on the Events tab.
  • Click on the triangle next to the Sample Parameter Form Portlet. Click on the Submit link.
  • Set the value of When this event is raised: to Go To Page:
  • Click on the LOV icon next to the Go To Page: text box, and find the page where your OmniPortlet and Simple Parameter Form is located and select the Return Object link next to it.
  • Under the Page Input section - which should display under the When this event is raised section - set Param1 = Event Output, and then select Parameter 1 from the next select list which will appear to the right.
  • Click on OK.
  • Edit the default properties of the OmniPortlet by clicking on the Edit Defaults icon.
  • Under the source tab, the value of XML URL similar to this
    http://myserver.com/pls/htmldb/f?p=100:1
    
  • You need to append the following to the XML URL in order to pass the search term back to Oracle HTML DB:
     :::::PRODUCT_NAME:##ProductName##
    
  • So that your new XML URL will look something like this:
    http://myserver.com/pls/htmldb/f?p=100:1:::::PRODUCT_NAME:##Param1##
    
  • Click on OK to save the changes.
  • You can now enter some text into the Product Name text box, click on Submit, and watch the chart change based on your query. Try USB, mouse, or home as search terms to test the form. Results will be similar to those displayed in Figure 1 below.

  
Figure 1: An example of an OmniPortlet chart and a Simple Parameter Form, which is being to used to filter the result set at the HTML DB instance, before the XML document is sent to Oracle Portal

Additional Resources

Discuss this how-to in the Oracle HTML DB Discussion Forum

 
Left Curve
Popular Downloads
Right Curve
Untitled Document