How to use Oracle Stored Procedures with Impromptu

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.