Service Virtualization

How to call Store Procedure which returns the ref cursor 

The below code examples are from a Genealogy database that I use for examples. The below code is for a MySQL db, the code may need to be modified for a different database.

If you are returning one cursor, then this code should get you started.

Connection conn = null

CallableStatement stproc_stmt = null;

...

//Make connection

...

//Create CallableStatement

stproc_stmt = conn.prepareCall("{call gendata.getMilitary(?)}");

//set all In/Out parameters
stproc_stmt.setString(1, "Y");

//execute
stproc_stmt.execute();

//Get ResultSet
ResultSet rs = stproc_stmt.getResultSet();

while (rs.next()) {
     System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
} //end while

******************************************************************

If you are returning multiple cursors, then add this code for each additional cursor returned:

******************************************************************

//Get next ResultSet
stproc_stmt.getMoreResults();
rs = stproc_stmt.getResultSet();

while (rs.next()) {
    System.out.println(rs.getString("given_name")+" "+rs.getString("last_name")+" "+rs.getInt("birth_year"));
} //end while

Best Regards, 

Ricky

CA Application Insight Support

Hi Ricky,

Called the StoreProcedure using the JDBC Step       {call PKG_XYZ(?,?,?,?,?)}

I have declared the output parameters in the properties but its not working .

its giving the error message as below.

There was a SQLException: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'PKG_SELECT_REIMB.GETFSAACCOUNTSUMMARY' must be declared
ORA-06550: line 1, column 7:

Can you add the Stored Procedure definition?

Also include the version of Oracle DB and Oracle driver you are using?

Best Regards, 

Ricky

CA Application Insight Support

Hi Riky,

Please find the stored procedure definition as below.

PROCEDURE GETFSAProcedure (C_MEMBER_KEY IN CHAR,
V_BNFT_TYPE_CD IN VARCHAR2,
FSA_ACCT_SUM_REC OUT REF_CURSOR,
N_ERR_NUM OUT NUMBER,
V_ERR_MSG OUT VARCHAR2);

Driver :jdbc:oracle:thin:

Ricky:kindly help me where and how to define store procedure parameters(Cursor,out parameters) in the ca devtest.