Skip Headers
Oracle® Fusion Middleware Platform Developer's Guide for Oracle Real-Time Decisions
11g Release 1 (11.1.1)

Part Number E16630-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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:

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.