Resolution:
Description:
===========
Calling database stored procedure from TIBCO BusinessEvents.

Environment:
==========
All Operating Systems
TIBCO BusinessEvents 4.x , 5.x
Oracle database


Resolution:
===========
Follow the steps outlined below to create a stored procedure in Oracle database and retrieve the results using SYS_REFCURSOR .

Steps:
1). Create a table in the Oracle database.
// Sample Create Statement
create table account (accountId varchar2(5) , firstName varchar2(20));

2). Insert a row in the Oracle database to test the outcome of the stored procedure.
// Sample Insert Statement
insert into account values (1000 , "AB") ;

3). Create a stored procedure in the Oracle database.
// Sample Stored Procedure
create or replace PROCEDURE getAccountsProc(
                P1 IN NUMBER,
                account OUT SYS_REFCURSOR)
is
begin
OPEN account  FOR
SELECT accountId  as Id,
                firstName as FirstName
                FROM account
                ORDER BY accountId;
end;

4). Import the table created in Step 1 as a database concept in BusinessEvents project.
// Import the account table as a database concept
In this example concept ACCOUNT in Concepts.BE_USER7.ACCOUNT is an imported database concept  .

5). Use the sample code below to retrieve information in the BusinessEvents project.
// Sample  BE Code

System.debugOut("Start");
try
{
    Database.setCurrentConnection("/SharedResources/JDBC");
    Object spStmt = Database.sp.initStoredProc("call getAccountsProc(?, ?)");
    Database.sp.setInputParameter(spStmt, 1, 1000);
    Database.sp.setOutputParameterType(spStmt, 2, 6);
    Database.sp.executeStoredProc(spStmt);
    Concepts.BE_USER7.ACCOUNT[] acount = Database.sp.getConceptsAtIndex(spStmt, 1,"/Concepts/BE_USER7/ACCOUNT");
    Database.sp.closeStoredProc(spStmt);
}
catch (Exception ex)
{
    System.debugOut(ex@stackTrace);
}

Calling database stored procedure from TIBCO BusinessEvents.