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