Author Topic: Using Stored Procedure on Framework Manager?  (Read 103 times)

0 Members and 1 Guest are viewing this topic.

Offline rpmolecule

  • Full Member
  • *
  • Posts: 224
  • Karma: +1/-0
  • Seize the Day!
    • View Profile
Using Stored Procedure on Framework Manager?
« on: August 21, 2014, 03:28:02 AM »
Has anyone of you ever experienced using stored procedure on FM? How does it work?

Techronnati | where technology never sleeps

Using Stored Procedure on Framework Manager?
« on: August 21, 2014, 03:28:02 AM »

Mountain View

Offline Transformer10

  • Full Member
  • *
  • Posts: 104
  • Karma: +0/-0
    • View Profile
Re: Using Stored Procedure on Framework Manager?
« Reply #1 on: August 21, 2014, 03:32:53 AM »
You need to import it.

In order to allow an Oracle Stored Procedure to pass a result set back to Cognos 8 for further reporting, the Stored Procedure must be part of an Oracle package and use ref cursors to return the result set.
Below is an example of an Oracle Package including a Stored Procedure that would be imported successfully as a Stored Procedure Query Subject into Framework Manager. There are comments in italics included to explain the structure.

create or replace package project_pk as /* Creates Package Header*/
type project_type is record( /* A record declaration is used to */
c1 projects.projectid %TYPE, /* provide a definition of a record */
c2 projects.projecttype %TYPE); /* that can be used by other variables*/
type project_type1 is ref cursor return project_type; /* Variable declaration */
procedure project_sp (tproj IN numeric, result1 in out project_type1); /* SP declaration */
end;
/
create or replace package body project_pk as /* Name of package body must be same as header */
procedure project_sp (tproj IN numeric, result1 in out project_type1) is /* SP Definition */
begin
open result1 for
select projects.projectid, projects.projecttype
from projects
where projects.projecttype=tproj;
end;
end;
/

 

Related Topics

  Subject / Started by Replies Last post
0 Replies
134 Views
Last post August 15, 2008, 07:14:21 AM
by h2obubbler
0 Replies
132 Views
Last post September 15, 2008, 01:59:25 AM
by MoneyRepublic
0 Replies
1586 Views
Last post November 27, 2008, 02:04:38 AM
by MrSpecialist
1 Replies
92 Views
Last post August 07, 2014, 01:02:15 AM
by rpmolecule
0 Replies
26 Views
Last post September 16, 2014, 01:20:36 AM
by TDelight

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.