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
In the Statement field of the OmniPortlet SQL data source you can now enter: call EMPLOYEE_API.get_employee_for_dept(‘10’)
Enjoy OmniPortlet !