C Examples of Data Sources from Stored Procedures

Create the stored procedure Get_Single_CustomerInfo in your Oracle, SQL Server, or DB2 database, using the appropriate commands:

(A) Oracle

CREATE PROCEDURE GET_SINGLE_CUSTOMERINFO
(
   P_ID IN INTEGER,
   P_AGE OUT INTEGER,
   P_OCCUPATION OUT VARCHAR2,
   P_LASTSTATEMENTBALANCE OUT FLOAT
)
AS
BEGIN
   SELECT AGE, OCCUPATION, LASTSTATEMENTBALANCE INTO P_AGE, P_OCCUPATION,            P_LASTSTATEMENTBALANCE 
   FROM CROSSSELLCUSTOMERS     WHERE CROSSSELLCUSTOMERS.ID = P_ID;
END;

(B) SQL Server

CREATE PROCEDURE Get_Single_CustomerInfo 
   @pId INTEGER,
   @pAge INTEGER OUTPUT,
   @pOccupation VARCHAR(20) OUTPUT,
   @pLastStatementBalance FLOAT OUTPUT
AS
   SELECT @pAge = Age,           @pOccupation = Occupation,            @pLastStatementBalance = LastStatementBalance 
   FROM CrossSellCustomers     WHERE Id = @pId;
GO

(C) DB2

CREATE PROCEDURE DB2ADMIN.GET_SINGLE_CUSTOMERINFO
(
   IN P_ID INTEGER,
   OUT P_AGE INTEGER,
   OUT P_OCCUPATION VARCHAR(20),
   OUT P_LASTSTATEMENTBALANCE REAL
)
LANGUAGE SQL
P1: BEGIN
   DECLARE CURSOR_ CURSOR WITH RETURN FOR
      SELECT AGE, OCCUPATION, LASTSTATEMENTBALANCE 
      FROM DB2ADMIN.CROSSSELLCUSTOMERS AS CROSSSELLCUSTOMERS
      WHERE CROSSSELLCUSTOMERS.ID = P_ID;
   OPEN CURSOR_;
   FETCH CURSOR_ INTO P_AGE, P_OCCUPATION, P_LASTSTATEMENTBALANCE;
   CLOSE CURSOR_;
END P1

Create a JDBC data source for the stored procedure in the application server that you are using.

For details of how to create data sources in the application servers, see "Configuring Data Access to Oracle Real-Time Decisions" in Oracle Fusion Middleware Administrator's Guide for Oracle Real-Time Decisions.

In Decision Studio, create the stored procedure data source DS_Single_Customer, by importing the Get_Single_CustomerInfo stored procedure from your database.

For the SQL Server stored procedure, change the direction of the parameters pAge, pOccupation, and pLastStatementBalance from Input/Output to Output.

In Decision Studio, create the entity Ent_Single_Customer, by importing the data source DS_Single_Customer.

Add the attribute Id, of data type Integer.

In the Mapping tab, in the Data Source Input Values area, set the Input Value for the Input Column pId to Id.

Open the Session entity, and add a new attribute cust_sp, setting the data type to Ent_Single_Customer.

Create the stored procedure Get_OneSet_CustomerInfo in your Oracle, SQL Server, or DB2 database, using the appropriate commands:

(A) Oracle

CREATE PROCEDURE GET_ONESET_CUSTOMERINFO
(
   P_CREDITLINEAMOUNT IN INTEGER,
   CURSOR_ IN OUT TYPES.REF_CURSOR
)
AS
BEGIN
   OPEN CURSOR_ FOR
      SELECT * FROM CROSSSELLCUSTOMERS        WHERE CREDITLINEAMOUNT >= P_CREDITLINEAMOUNT;
END;

(B) SQL Server

CREATE PROCEDURE Get_OneSet_CustomerInfo 
   @pCreditLineAmount INTEGER
AS
   SET NOCOUNT ON;
   SELECT * FROM CrossSellCustomers 
   WHERE CreditLineAmount >= @pCreditLineAmount;
GO

(C) DB2

CREATE PROCEDURE DB2ADMIN.GET_ONESET_CUSTOMERINFO
(
   IN P_CREDITLINEAMOUNT INTEGER
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
   DECLARE CURSOR_ CURSOR WITH RETURN FOR
      SELECT * FROM DB2ADMIN.CROSSSELLCUSTOMERS AS CROSSSELLCUSTOMERS
      WHERE CROSSSELLCUSTOMERS.CREDITLINEAMOUNT >= P_CREDITLINEAMOUNT;
   OPEN CURSOR_;
END P1

Create a JDBC data source for the stored procedure in the application server that you are using.

For details of how to create data sources in the application servers, see "Configuring Data Access to Oracle Real-Time Decisions" in Oracle Fusion Middleware Administrator's Guide for Oracle Real-Time Decisions.

In Decision Studio, create the stored procedure data source DS_OneSet_Customer, by importing the Get_OneSet_CustomerInfo stored procedure from your database.

In the Results Set Details section, add a result set.

Check Allow multiple rows.

For the SQL Server stored procedure, add the following column names exactly as shown with the given data types:

• Age [Integer]

• Occupation [String]

• LastStatementBalance [Double]

For the Oracle and DB2 stored procedures, add the following column names exactly as shown with the given data types:

• AGE [Integer]

• OCCUPATION [String]

• LASTSTATEMENTBALANCE [Double]

In Decision Studio, create the entity Ent_OneSet_Customer, by importing the data source DS_OneSet_Customer.

Add the attribute CreditLineAmount, of data type Integer, and set its default value to 50000.

This will limit results to around 30 rows.

Check the Array column for the attributes Age, Occupation, and LastStatementBalance.

In the Mapping tab, in the Data Source Input Values area, set the Input Value for the Input Column pCreditLineAmount to CreditLineAmount.

Open the Session entity, and add a new attribute cust_oneset_sp, setting the data type to Ent_OneSet_Customer.

Create the stored procedure Get_TwoSets_CustomerInfo in your Oracle, SQL Server, or DB2 database, using the appropriate commands:

(A) Oracle

CREATE PROCEDURE GET_TWOSETS_CUSTOMERINFO
(
   P_CREDITLINEAMOUNT IN INTEGER,
   CURSOR1_ IN OUT TYPES.REF_CURSOR,
   CURSOR2_ IN OUT TYPES.REF_CURSOR 
)
AS
BEGIN
   OPEN CURSOR1_ FOR
      SELECT * FROM CROSSSELLCUSTOMERS 
      WHERE CREDITLINEAMOUNT >= P_CREDITLINEAMOUNT;
    
  OPEN CURSOR2_ FOR
      SELECT * FROM CROSSSELLCUSTOMERS 
       WHERE CARDTYPE = 'Platinum' AND CREDITLINEAMOUNT >= P_CREDITLINEAMOUNT;    
END;

(B) SQL Server

CREATE PROCEDURE Get_TwoSets_CustomerInfo 
   @pCreditLineAmount INTEGER
AS
   SET NOCOUNT ON;
   SELECT * FROM CrossSellCustomers 
   WHERE CreditLineAmount >= @pCreditLineAmount;
   SELECT * FROM CrossSellCustomers 
   WHERE CreditLineAmount >= @pCreditLineAmount AND CardType = 'Platinum';
GO

(C) DB2

CREATE PROCEDURE DB2ADMIN.GET_TWOSETS_CUSTOMERINFO
(
   IN P_CREDITLINEAMOUNT INTEGER
)
DYNAMIC RESULT SETS 2
LANGUAGE SQL
P1: BEGIN
   DECLARE CURSOR1_ CURSOR WITH RETURN FOR
      SELECT * FROM DB2ADMIN.CROSSSELLCUSTOMERS AS CROSSSELLCUSTOMERS
      WHERE CROSSSELLCUSTOMERS.CREDITLINEAMOUNT >= P_CREDITLINEAMOUNT;
 
  DECLARE CURSOR2_ CURSOR WITH RETURN FOR
     SELECT * FROM DB2ADMIN.CROSSSELLCUSTOMERS AS CROSSSELLCUSTOMERS
      WHERE CROSSSELLCUSTOMERS.CREDITLINEAMOUNT >= P_CREDITLINEAMOUNT 
      AND CROSSSELLCUSTOMERS.CARDTYPE = 'Platinum';
 
   OPEN CURSOR1_;
   OPEN CURSOR2_;
END P1

Create a JDBC data source for the stored procedure in the application server that you are using.

For details of how to create data sources in the application servers, see "Configuring Data Access to Oracle Real-Time Decisions" in Oracle Fusion Middleware Administrator's Guide for Oracle Real-Time Decisions.

In Decision Studio, create the stored procedure data source DS_TwoSets_Customer, by importing the Get_TwoSets_CustomerInfo stored procedure from your database.

In the Results Set Details section, add a result set.

Check Allow multiple rows.

For the SQL Server stored procedure, add the following column names exactly as shown with the given data types:

• Age [Integer]

• Occupation [String]

• LastStatementBalance [Double]

For the Oracle and DB2 stored procedures, add the following column names exactly as shown with the given data types:

• AGE [Integer]

• OCCUPATION [String]

• LASTSTATEMENTBALANCE [Double]

Repeat steps 4 through 6 for the second result set.

In Decision Studio, create the entity Ent_TwoSets_Customer, by importing the data source DS_TwoSets_Customer.

Add the attribute CreditLineAmount, of data type Integer, and set its default value to 50000.

This will limit results to around 30 rows.

Check the Array column for the attributes Age, Occupation, and LastStatementBalance.

In the Mapping tab, in the Data Source Input Values area, set the Input Value for the Input Column pCreditLineAmount to CreditLineAmount.

Open the Session entity, and add a new attribute cust_twosets_sp, setting the data type to Ent_TwoSets_Customer.