Stored Procedure Parameters and Examples
- Becky Weinberg (Unlicensed)
- Pamela Totas (Deactivated)
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.
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 | 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)ASBEGINOPEN gv_refcursor FORSELECTDMART_FUND_ID,ENTITY_ID,EFFECTIVE_DATE,FUND_STATUSFROM DATAMARTDBO.FUND_MASTERWHERE ENTITY_ID = cENTITYAND EFFECTIVE_DATE = dDateAND 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;
Â
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;
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