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