Add Oracle Function into an existing Universe model

[Former Member]

Hi,

How can I add an Oracle Function into my existing universe that already has many tables and dynamic tables in it, tables linked by foreign keys?

This oracle function returns a cursor (recordset). The function returns a set of records from a Country table and code for the function is:

CREATE OR REPLACE FUNCTION TEST_FUNCTION1 (PARAM1 IN VARCHAR2) RETURN SYS_REFCURSOR

AS

  my_cursor SYS_REFCURSOR;

BEGIN

  OPEN my_cursor FOR SELECT * FROM COUNTRY;

  RETURN my_cursor;

END TEST_FUNCTION1;

What would be an efficient way to add this oracle function into my existing Universe model, so I can build Webi reports based on it (to get my report to show list of countries that my function returns)?

Please note that this function above is just a simplified one, and that my real function is more complex, involves applying calculations on the table fileds, and has much longer code. But the concept is the same, the real function returns a sys_refcursor set of records too, so the same solution should be good.

I work with BOE XI 3.1 SP6 , Universe Designer 12.6.2.1782 and Oracle 11.2.

Thank you,

Mbuki

[former_member193452 · Accepted solution]

Hi mbuki,

The Oracle Function is treated as a stored procedure in BI.  Stored Procedures are only supported through a UNV or Universe Designer.  Unfortunately the bad news is that a stored procedure cannot be added to an existing universe.  They must be inserted into a new universe.

To Create a new universe,

You must select the check box Stored Procedure under the connection.

Then below the Table browser select the Stored Procedure you have created in your DB.

There are special supported capabilities for Oracle

only procedures that return result sets as table and cannot return integers, strings or cursors and must always contain a select statement.

The Data Access guide and the Universe designer guide have more information and you can find your version guides HERE: >

http://wiki.scn.sap.com/wiki/x/5INeFw

Jacqueline