×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Cognos Impromptu

How to use Oracle Stored Procedures with Impromptu by DGXGUY
Posted: 12 Jul 01

To use Impromptu to pass a parameter to an Oracle Stored Procedure and have it pass a result set back to Impromptu for further reporting, the Stored Procedure must be part of an Oracle package that use objects as cursors to return the result set. Here is an example of an Oracle Package including a Stored Procedure. There are comments 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;
/

/* To test this (interactively) in Oracle type: */
/* var c1 refcursor; The use of the variable is not needed through Impromptu */
/* execute project_pk.project_sp(4,:c1); Note PackageName.ProcedureName */
/* print c1; this will display the result returned to Impromptu */



In the Stored Procedure template in Impromptu, enter:

call PROJECT_PK.PROJECT_SP(?tproj? IN)

In Oracle, the name of the stored procedure must be upper case. The ?tproj? can be defined as a type in or report etc. prompt and provides a project type numeric value.

Note: there is no prompt for the 'result1' IN OUT parameter of the Stored Procedure.


Back to Cognos solutions FAQ Index
Back to Cognos solutions Forum


My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close