Wednesday, November 19, 2003

OmniPortlet tip: use stored procedure with the SQL data source

OmniPortlet provides to page designer an easy and powerful way to publish content from different data sources. One of the data source is a SQL data source that allow you to connect to a relational database using JDBC. Obviously you can enter any SQL statement, but you can also consume a REF CURSOT returned by a procedure.

That is really interesting if you want to add business logic to your data,or have to set some specific code before the execution of the query.

To to it you have to create a procedure that has the first parameter the return a ref cursor:
procedure get_employee_for_dept(p_ref_cursor out ref_cursor, p_dept in number);

Here a complete package based on the SCOTT sample schema:

 create or replace package EMPLOYEE_API
  -- create a ref cursor type that will be return to the consumer
  type ref_cursor is ref cursor;

  -- return in the p_ref_cursor the list of employees for the department p_dept
  procedure get_employee_for_dept(p_ref_cursor out ref_cursor, p_dept in number);
 create or replace package body EMPLOYEE_API
  -- return in the p_ref_cursor the list of employees for the department p_dept
  procedure get_employee_for_dept(p_ref_cursor out ref_cursor, p_dept in number)
   -- open the cusor based on the emp table
   OPEN p_ref_cursor FOR
    SELECT * from emp WHERE deptno = p_dept;


In the Statement field of the OmniPortlet SQL data source you can now enter:
call EMPLOYEE_API.get_employee_for_dept('10')

Enjoy OmniPortlet !

Sunday, November 16, 2003

How to configure Web Cache to cache remote portlet content

Some portlet developers have hard time to set up a correct environment to use invalidation based cache with portlets. The summary is often:
"it is working with the PDK Example provided in Oracle 9iAS but every time that I want to do it myself from Oracle JDeveloper or a stand alone OC4J the portlet is not cached !"

This is most of the case due to a bad understanding of the different components that are involved.

When we talk about invalidation based caching for Remote Portlet, we talk about a portlet that caches its content in a Web Cache that is between the Portal Midtier and the Web Provider Midtier. This is a first important point. That also means that you have to register your remote Web provider using a URL that points to this Web Cache.

PPE, Web Cache and Web Provider


So when you are developing a portlet within Oracle JDeveloper or deploying a portlet to an OC4J or an AS and you want to use invalidation based caching you have to put a Web Cache between the PPE and the Application Server that you are using. Let's take an example based on a execution of the Web Provider inside JDeveloper -embedded OC4J-, a URL like: http://dev-machine:port/application/providers.

So to cache the content of the portlets that are running in this OC4J you have to configure Web Cache using Web Cache Manager and:

  1. create an Application Web Server that is the embedded OC4J
  2. create a new Site Definition, this will create a new HTTP port, that you'll use to register your provider.
  3. finally you have to create a "Site to server mapping", where you associate the OC4J to the new HTTP port.

After the bounce of Web Cache you can use the HTTP port on the Web Cache server to access your provider, and if the portlet is correctly configured -see this Oracle article -to support invalidation based caching; its content will be automatically cached.

Note: this tip is not limited to Portlet bu to any application that you want to develop and enhance using invalidation based caching.

Friday, November 14, 2003

Add columns to OmniPortlet

I had some questions about the limitation to 5 columns of the OmniPortlet tabular layout. Here is a tip to add more columns to this layout:

1. backup the current OmniPortlet provider.xml

2. You can not open the file:
and look for the tag <dataField

3. You can add new fields, by copying the existing <dataField> tag and change the value of the <name> and <displayName>

  <!-- Here is a complete example -->
  <dataField class="oracle.webdb.reformlet.definition.DataFieldDefinition">




  <!-- end of the example -->

The same logic could be used to add parameters or events to the OmniPortlet.p>