How-To Document

Integrating Oracle Application Express (formerly HTML DB) and Oracle Portal with the OmniPortlet:
Linking from an OmniPortlet to an Application Express Page


Date: 15-NOV-2003

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

  • How to add a link to an OmniPortlet which will link back to an Oracle HTML DB report

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 link from an OmniPortlet that will "drill" back to an HTML DB Report.

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

  • Create a public application in HTML DB (See How to make an HTML DB Application Public for details)
  • On a new page with no other regions, create a new Region
  • Select Report and click Next
  • Select SQL Report and click Next
  • Give it any title you wish and click Next
  • For the SQL Query, enter:
    SELECT a.order_id, d.cust_first_name || ' ' || d.cust_last_name cust_name, c.product_name, a.unit_price, a.quantity, (a.unit_price * a.quantity) amt 
                  FROM demo_order_items a, demo_orders b, demo_product_info c, demo_customers d 
                  WHERE a.order_id = b.order_id
                  AND a.product_id = c.product_id
                  AND b.customer_id = d.customer_id
                  AND b.order_id IN (SELECT order_id FROM demo_order_items WHERE product_id = :PRODUCT_ID)
    

    Note: The demo_order_items, demo_orders, demo_product_info & demo_customers 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.

  • Save your changes by clicking on Apply Changes.
  • Click on Create in the Items region of the page.
  • Give the item the name PRODUCT_ID, and set Display As to Hidden. Click on Next, 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.
  • If you run this page, the report should not return any data, as the variable PRODUCT_ID is null, and thus will cause the query to return no rows. When we call this report from the OmniPortlet in Oracle Portal, we will pass the value for Product ID to PRODUCT_ID, thus causing the report to act as a drill-down from Oracle Portal to HTML DB.

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.
  • Edit the OmniPortlet where your chart is created it, and select the Layout tab.
  • In the Chart Drill down section, set the Action to Hyperlink, and enter the following into the URL:

    http://myserver.com/pls/htmldb/f?p=100:1:::::PRODUCT_ID:##ProductId##

  • Be sure to replace 100 with your application ID, 1 with your page number in HTML DB where the above report is, http:// myserver.com with the name and port of your HTML DB server, and /pls/htmldb/ with the path to your DAD. Also, ##ProductId## must be the exact case-sensitive name which you defined in the HTML DB XML report you created in the Create a Chart How To.
  • Click on OK to save your changes.
  • Now, when you view the chart, you should be able to click on each of its bars or slices, and drill to the orders where that product were sold on. See Figure 1 for an example.

  
Figure 1: An example of a drill-down HTML DB report, which is a result of clicking on any bar in the Oracle Portal OmniPortlet

Additional Resources

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