Calling an Oracle Stored Procedure in JAVA

[dvknn]

Hi,

This is my first sample program to actually call a Oracle Stored Procedure from Java.

I want to return a float value from the stored procedure into the Java Program. How do I do that?

Here's my Java code and the stored procedure.

try
{
CallableStatement cstmt = con.prepareCall("{ ? = call
SP_NEW_USERS.sql(?,?,?,?)}");
cstmt.registerOutParameter(1, Types.FLOAT);
cstmt.setString(2, currentStartDate);
cstmt.setString(3, currentEndDate);
cstmt.setString(4, previousStartDate);
cstmt.setString(5, previousEndDate);
cstmt.execute();
changeFromPreviousMonth = cstmt.getFloat(??????) ------What should I write here??

}


STORED PROCEDURE:

CREATE OR REPLACE PROCEDURE SP_NEW_USERS(currentBeginDate DATE, currentEndDate DATE, previousBeginDate DATE, previousEndDate DATE)

tmpVar NUMBER;
tmpVar1 NUMBER;
tmpVar2 NUMBER;

BEGIN
tmpVar := 0;
select count(user_id) into tmpVar from oradba.up_user where (date_created between currentBeginDate and currentEndDate) and customertype in ('A', 'B');
select count(user_id) into tmpVar1 from oradba.up_user where date_created between previousBeginDate and previousEndDate and customertype in ('A', 'B');
tmpVar2 := ((tmpVar - tmpVar1)/tmpVar) * 100;

DBMS_OUTPUT.PUT_LINE(tmpVar2);
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;
END SP_NEW_USERS;


Thank You for helping me out here..

 

[dsanchez2]

The parameter to getFloat is the index to the parameter. In your case it would be:
[tt]
changeFromPreviousMonth = cstmt.getFloat(1);
[/tt]
Since it is the first parameter. (Note the returned value is counted as a parameter). When counting the parameters to determine the index you must include the IN parameters in your count.

Also you will need to change your stored procedured from a PROCEDURE to a FUNCTION that returns a NUMBER.