C Examples of Data Sources from Stored Procedures

This appendix shows examples of how to create data sources from Oracle, SQL Server, and DB2, and then how to create entities and session attributes from these data sources.

The examples are based on the CrossSellCustomers table. For details of setting up this table, see the topic "Populating the CrossSell Example Data" in Oracle Fusion Middleware Administrator's Guide for Oracle Real-Time Decisions.

This appendix contains the following topics:

• Section C.1, "Creating a Data Source from Single Result Stored Procedures"

• Section C.2, "Creating a Data Source from Stored Procedures with One Result Set"

• Section C.3, "Creating a Data Source from Stored Procedures with Two Result Sets"

C.1 Creating a Data Source from Single Result Stored Procedures

To create a data source from single result stored procedures:

1. 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
   

2. 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.

3. 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.

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

5. Add the attribute Id, of data type Integer.

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

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

C.2 Creating a Data Source from Stored Procedures with One Result Set

To create a data source from stored procedures with one result set:

1. 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
   

2. 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.

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

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

5. Check Allow multiple rows.

6. 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]

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

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

   This will limit results to around 30 rows.

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

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

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

C.3 Creating a Data Source from Stored Procedures with Two Result Sets

To create a data source from stored procedures with two result sets:

1. 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
   

2. 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.

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

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

5. Check Allow multiple rows.

6. 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]

7. Repeat steps 4 through 6 for the second result set.

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

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

   This will limit results to around 30 rows.

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

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

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