Importing Oracle Stored Procedure into Cognos Analytics Framework Manager

In this post, I go over steps to set up an Oracle Stored procedure and import them into Framework Manager as Query subjects, so they can be used to write data back into an Oracle DB.

There are many instances where Cognos can be leveraged to write data-back data into Database, while Cognos is not meant to be a data write-back tool. But time to time, you might get a business requirement where the platform can be leveraged to do just that.

There are many examples out there which show you how to get this done using SQL Server database. But, I did not come across any article which uses Oracle DB. The steps are little different for Oracle so decided to post this article.

At one of my previous workplaces, One of our users requested us to provide them with a mechanism with which they could enter and store specific parameter values as favorites. And during report run time, the Corporate Reports should load with their previously saved values as prompt defaults.

So we created a report which contained all the prompts, which the users could execute and make/set their selections. Upon hitting Finish, the Report would call the stored procedure query subject via which users prompt selection gets written into a Prompt values table.

For the second part( i.e., setting the user favorites as default values in standard reports) we leveraged JavaScript in reports to set default values in the Reports. At run time, we read the Prompt values table, then set those values returned as default values in prompts. I will be posting another article with that JavaScript code explaining how to accomplish that part.

Here’s are the setting up an Oracle Stored procedure and import them into Framework Manager as Query subjects

• Create a Table to store your Parameter values

CREATE TABLE USER_FAVORITES_TBL
(
  USERNAME      VARCHAR2(100 BYTE),
  USERID        VARCHAR2(20 BYTE),
  REPORTNAME    VARCHAR2(200 BYTE),
  RETAILER      VARCHAR2(20 BYTE),
  DISTRICT VARCHAR2(20 BYTE),
)

• Create the Stored Procedure

1. Make sure to use Sys_Refcursor as an OUT variable.

CREATE OR REPLACE
PROCEDURE SP_USER_FAVORITES
            (
            USERNAME  	IN varchar2,
            USERID  	IN varchar2 ,
            REPORTNAME 	IN varchar2,
            RETAILER  	IN varchar2, 
            DISTRICT  	IN varchar2, 			
            Rs Out Sys_Refcursor 
            )
AS
--ROW_COUNT NUMBER;
BEGIN
    INSERT INTO USER_FAVORITES_TBL VALUES (USERNAME,USERID,REPORTNAME,RETAILER,DISTRICT);
COMMIT;
    OPEN Rs for SELECT * from <SCHEMA_NAME>.USER_FAVORITES_TBL;
END SP_USER_FAVORITES;
/

• Associate the Stored Procedure with an Oracle Package.

1. This step is essential else you will be scratching your head why it doesn’t work when you import the SP into FM Query Subject.

CREATE OR REPLACE PACKAGE project_USER_FAVORITES 
AS
PROCEDURE SP_USER_FAVORITES 
            (
            USERNAME  IN varchar2,
            USERID  IN varchar2 ,
            REPORTNAME  IN varchar2 ,
            RETAILER  IN varchar2 ,
            DISTRICT IN varchar2,
            Rs Out Sys_Refcursor 
            ); /* SP declaration */
END project_USER_FAVORITES;
/

• Be sure to grant all necessary privileges to the Stored procedure and the Parameter table.

GRANT ALL PRIVILEGES ON <SCHEMA_NAME>.USER_FAVORITES_TBL TO <TO_DESIRED_SCHEMA>;
GRANT ALL PRIVILEGES ON <SCHEMA_NAME>.SP_USER_FAVORITES TO <TO_DESIRED_SCHEMA>;

• Open your FM model and Create a Stored procedure Query Subject.

• 

• Modify the settings of the package data sources

Transaction Access Mode = Read-Write

Transaction Statement Mode = Rollback

as shown in this IBM article. https://www-01.ibm.com/support/docview.wss?uid=swg2190319

Publish the Cognos package and Create a report with Prompts listed in the Stored Procedure and upon execution, the Prompt values will get written into the Parameters table.