Stored Procedure Parameters and Examples

Stored Procedure Parameters

The following section describes the options available for stored procedure parameters. For each parameter, specify the following:

Description. Name for the parameter. This value is displayed to the user by Portal.
Type. Type values are Code, Column Value, Date, Entity, Number, Security, String or Issuer in the Maintain Options/Parameters window.
Order. The order in which the parameters are shown.
Default Value. Default value for this parameter, so that parameters are pre-populated.
Single/Multiple. Determines if the parameter can have single values or multiple values.
Show/Hide. Show or hide the parameter.

On this page

In this example, three parameters display. You enter the values as shown in the following table.

Description	Type	Order	Default Value	Single/Multiple	Show/Hide
Fund	Entity	1		Multiple	Show
From Date	Date	2		Single	Multiple
To Date	Date	3		Single	Show

Next, enter values for the parameters that you have configured.

The Eagle Portal Administrator executes the stored procedure with the parameter values and displays the Columns returned by the stored procedure.

For each Column, specify the following:

Description. Description of the column.
XML Tag. XML Tag used to generate the XML. Required by Portal and also used if this is called through the API.
Type. Type values are Integer, Float, String, and Date.

You can specify if the previous details must be mapped by index or by name. If the name is specified, the information is mapped by column name. This example creates the output displayed in the following table.

Index	Name	Description	XML Tag	Type	Show/Hide
1	ENTITY_ID	Fund	FUND_ID	String	Show
2	EFFECTIVE_DATE	Effective	EFF_DATE	Date	Show
3	MARKET _VALUE	Market Value	MKT_VAL	Number	Show

Create and Customize Queries

The ACCESS SQL QUERY type allows you to create and customize queries with data from any database and display that data in a way that is most meaningful to Portal users. Since this report type is a stored procedure, you must first create the stored procedure in the database before configuring the query in Portal Query Explorer.

This section includes an example of a stored procedure in:

Oracle name portal _sql_ demo _proc
Oracle named portal_view_multi_example_ora
Oracle named portal_view_example_ora
SQL named portal_view_example_SQL
SQL named portal_view_multi_example_SQL

Examples of Stored Procedures in Oracle

portal_sql_demo_proc

The following example is an Oracle procedure where you are not creating a package.

The reason for using an Oracle package with an ACCESS SQL QUERY was for versions of Oracle prior to Oracle 9i where the SYS_REFCURSOR did not exist and had to be defined as part of the package. Versions of Oracle prior to 9 were last officially supported prior to Eagle v9.0. Therefore, you are not required to create an Oracle stored procedure using a package.

The following example shows how to create or replace the procedure PORTAL_SQL_DEMO_PROC.

/****************************************/

/* PORTAL_SQL_DEMO_PROC - */

/****************************************/

(

/* Input parameters */
cENTITY char,
dDate date,
gv_refcursor OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN gv_refcursor FOR
SELECT
DMART_FUND_ID,
ENTITY_ID,
EFFECTIVE_DATE,
FUND_STATUS
FROM DATAMARTDBO.FUND_MASTER
WHERE ENTITY_ID = cENTITY
AND EFFECTIVE_DATE = dDate
AND SNAPSHOT_ID = 'DEFAULT';
end PORTAL_SQL_DEMO_PROC;

When you configure an ACCESS SQL QUERY, the Eagle Portal Administrator executes the stored procedure with test values in order to determine the columns that it returns. The stored procedure you have requires certain values in order to execute properly. To get around this, you can add exception handling in your stored procedure, or make it always return the same columns. Either of these actions produces the expected results. For example:

SELECT user_field12

INTO v_entity_id

FROM rulesdbo.entity

WHERE entity_id = iEntityId;

exception

when no_data_found then

v_entity_id:=' ';

end;

As the Eagle Portal Administrator, you can input the test parameters to the ACCESS SQL QUERY. This allows you to change the test values that your store procedure requires to return data.

portal_view_multi_example_ora

CREATE OR REPLACE PACKAGE PACE_MASTERDBO.PORTAL_VIEW_MULTI_PKG IS

/*********************************************************/

/* PORTAL_VIEW_MULTI_PKG - */

/* This package or similar is required to create portal sql views */

/* in Oracle. /

/**********************************************************/

TYPE RefCursor IS REF CURSOR;

PROCEDURE PORTAL_VIEW_MULTI

(

/* Input parameters */

EntityId char,

EffectiveDate date,

/*Output record cursor */

ReturnRecords OUT RefCursor);

END;

CREATE OR REPLACE PACKAGE BODY PACE_MASTERDBO.PORTAL_VIEW_MULTI_PKG IS PROCEDURE PORTAL_VIEW_MULTI

/**********************************************************/

/* PORTAL_VIEW_MULTI - */

/* This Oracle procedure gets data from the Fund Master table*/

/* in Data Mart for the entities and effective date that was selected. */

/**********************************************************/

(

/* Input parameters */

EntityId char,

EffectiveDate date,

/*Output record cursor */

ReturnRecords OUT RefCursor) IS

BEGIN

OPEN ReturnRecords FOR

SELECT

DMART_FUND_ID,

ENTITY_ID,

EFFECTIVE_DATE,

FUND_STATUS

FROM DATAMARTDBO.FUND_MASTER

WHERE ENTITY_ID IN ( SELECT COLUMN_VALUE FROM TABLE( PACE_MASTERDBO.SPLIT ( EntityId, ',') ) )

AND EFFECTIVE_DATE = EffectiveDate;

END;

portal_view_example_ora

CREATE OR REPLACE PACKAGE PACE_MASTERDBO.PORTAL_VIEW_EXAMPLE_PKG IS

/**********************************************************/

/* PORTAL_VIEW_EXAMPLE_PKG - */

/* This package or similar is required to create portal sql views */

/* in Oracle. */

/**********************************************************/

TYPE RefCursor IS REF CURSOR;

PROCEDURE PORTAL_VIEW_EXAMPLE

(

/* Input parameters */

EntityId char,

EffectiveDate date,

/*Output record cursor */

ReturnRecords OUT RefCursor);

END;

CREATE OR REPLACE PACKAGE BODY PACE_MASTERDBO.PORTAL_VIEW_EXAMPLE_PKG IS

PROCEDURE PORTAL_VIEW_EXAMPLE

/***=******************************************************/

/* PORTAL_VIEW_EXAMPLE - */

/* This Oracle procedure gets data from the Fund Master table */

/* in Data Mart for the entity and effective date that was selected. */

/**********************************************************/

(

/* Input parameters */

EntityId char,

EffectiveDate date,

/*Output record cursor */

ReturnRecords OUT RefCursor) IS

BEGIN

OPEN ReturnRecords FOR

SELECT

DMART_FUND_ID,

ENTITY_ID,

EFFECTIVE_DATE,

FUND_STATUS

FROM DATAMARTDBO.FUND_MASTER

WHERE ENTITY_ID = EntityId

AND EFFECTIVE_DATE = EffectiveDate;

END;

Examples of Stored Procedures in SQL

portal_view_example_SQL

CREATE PROCEDURE PORTAL_VIEW_EXAMPLE

/**********************************************************/

/* PORTAL_VIEW_EXAMPLE - */

/* This SQL Server procedure gets data from the Fund Master table */

/* in Data Mart for the entity and effective date that was selected. */

/**********************************************************/

@EntityId AS char(8),

@EffectiveDate AS datetime

AS

BEGIN

SELECT

DMART_FUND_ID,

ENTITY_ID,

EFFECTIVE_DATE,

FUND_STATUS

FROM DATAMART.DBO.FUND_MASTER

WHERE ENTITY_ID = @EntityId

AND EFFECTIVE_DATE = @EffectiveDate;

END

portal_view_multi_example_SQL

CREATE PROCEDURE PORTAL_VIEW_MULTI

/**********************************************************/

/* PORTAL_VIEW_MULTI - */

/* This SQL Server procedure gets data from the Fund Master table */

/* in Data Mart for the entities and effective date that was selected. */

/**********************************************************/

@EntityId AS char(255),

@EffectiveDate AS datetime

AS

BEGIN

SELECT

DMART_FUND_ID,

ENTITY_ID,

EFFECTIVE_DATE,

FUND_STATUS

FROM DATAMART.DBO.FUND_MASTER

WHERE ENTITY_ID IN ( SELECT COLUMN_VALUE FROM PACE_MASTER.DBO.SPLIT ( @EntityId, ',' ) )

AND EFFECTIVE_DATE = @EffectiveDate;

END

Stored Procedure Parameters and Examples