Tug’s Blog

My journey in Big Data, Hadoop, NoSQL and MapR

OmniPortlet Tip: Use Stored Procedure With the SQL Data Source

| Comments

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 create or replace package EMPLOYEE_API
 as
 -- 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);
 end;
 create or replace package body EMPLOYEE_API
 as
 -- 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)
 as
 begin
  -- open the cusor based on the emp table

  OPEN p_ref_cursor FOR
  SELECT * from emp WHERE deptno = p_dept;
 end;

 end;

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

Enjoy OmniPortlet !

How to Configure Web Cache to Cache Remote Portlet Content

| Comments

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 !”

Add Columns to OmniPortlet

| Comments

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 /OC4J_HOME/applications/portalTools/omniPortlet/WEB-INF/providers/omniPortlet

  2. You can now 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>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<name>Field6</name>
<displayName>Column6</displayName>
<description>Field6</description>
<text>##column##</text>

<alignment>left</alignment>
<displayAs>hidden</displayAs>
<type>linebreak</type>
<font>Arial.3.Plain.None</font>

<color>#000000</color>
<style>none</style>
<styleType>custom</styleType>
</dataField>

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

Portlet With a Vertical Scroll Bar

| Comments

To have a better control of the layout of your Portal Pages, you can use a vertical scroll bar in portlet wihout having to use iFrame technology. CSS provide us lof of power to do it.

  1. Create a style in your portlet (or in the portlet container code):
1
2
3
4
5
6
<style>
.scroll<i>portletId</i> {
  height:200px;
  overflow: auto;
}
</style>
  1. Use this style in a DIV section:
1
2
3
<div class="scrollportletId">
The content of this section will be vertically scrollable.
</div>