IBM Support

How to use Oracle® stored procedure in a Framework Manager Package

How To


Summary

Cognos Framework Manager can use objects like a stored procedure in Oracle® as a query subject. Parameters defined in the stored procedure can be used in Cognos reports through the query subjects defined in the package.

Objective

This article provides an example of how to:

  1. Create a simple stored procedure in Oracle® that takes a parameter and returns result
  2. Create a package in Cognos Framework Manager that uses the stored procedure as a query subject
  3. Create a report in Cognos that uses the parameter defined in the stored procedure through the FM package

Environment

This example was tested on Oracle® 12.0.2, Cognos Analytics, and Framework Manager, Windows®

SQL Developer was used to create Oracle® table and stored procedure

The default System

tablespace
and System user was used to create the Oracle® objects

Steps

Stored procedure in Oracle®:

Connect to an Oracle® database and create a table (Table2 in screen capture)

Create table

Table script:

  CREATE TABLE "SYSTEM"."TABLE2"      (    "ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,       "DESCRIPTION" VARCHAR2(30 CHAR),       "QUANTITY" VARCHAR2(20 BYTE)     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255    NOCOMPRESS LOGGING    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)    TABLESPACE "SYSTEM" ;

Note: The default system user account and 

  tablespace
Is used in this example. User account can vary in different environment

Similarly, create a stored procedure as shown in the following screen capture

Stored Procedure

Stored procedure script:

  create or replace   PROCEDURE        TABLE2SP   (    PARAM1 IN VARCHAR2,    c1 IN OUT SYS_REFCURSOR  ) AS   BEGIN    open c1 for select DESCRIPTION FROM TABLE2 where ID = PARAM1;     END TABLE2SP;

Note: The 

  IN OUT
Parameter c1 of type SYS_REFCURSOR is used to return result set to Framework Manager. This parameter is necessary to run the select statement in the stored procedure, and for the Framework Manager query subject to work in data query mode

Framework Manager

Create a project in Framework Manager

Add a query subject to the model to represent the stored procedure as shown in the following screen

Query subject

If you are prompted to select a data source connection, pick the Oracle® data source connecting to the Oracle® database where the table and the stored procedure were created.

Upon successful authentication of the data source, the Oracle® objects appear. Select the stored procedure that was created earlier as shown in the following screen capture

Stored procedure in FM

After the query subject is created, right-click to open the pop-up menu, and click "Edit definition". The following screen appears

Query subject definition

Here, only the IN parameter can be seen in the definition. Make sure that the query item is in 'Data Query' mode. This parameter is necessary for Framework Manager to retrieve resultset from the SYS_REFCURSOR IN OUT parameter defined in the stored procedure.

Click the ellipsis next to the "value" column and use the following code to define a prompt macro:

#prompt('PARAM1','varchar2')#

You can switch to the 'Test' tab and test the query subject against the stored procedure. If the tests are successful, save the project and publish the package

Cognos Report

Create a report that uses the package that was published from the Framework Manager. The query subject representing the stored procedure in the package appears as shown in the following screen:

FM Package in report

Note: Since the 'Description' column appears in the published package as it was returned as the resultset from the stored procedure. Drag 'Description' into a list in the report

Create a prompt page with a text prompt pass parameter to the stored procedure. In the screen that shows the message to specify prompt parameter, the IN parameter of the stored procedure appears under existing parameters as shown in the following screen

Prompt parameters

Save and run the report

Additional Information

The report specification (Report spec.txt) and the SQL script used in this example is being attached to this document

Report spec.txt

Script.sql

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"11.0.9","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

Cognos Analytics

Document Information

Modified date:
12 August 2020

UID

ibm10729771

Page Feedback