Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    35

    Unanswered: Calling a Oracle Stored Procedure in Java

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

  2. #2
    Join Date
    Mar 2004
    Posts
    9

    Re: Calling a Oracle Stored Procedure in Java

    Hi.

    SP_NEW_USERS must be a FUNCTION, not a STORED PROCEDURE, because you use ? = call PROC_NAME(?,?) format. The first (?) is a result returned by the function.
    If you want to invoke a procedure the format must be: con.prepareCall("{ call SP_NEW_USERS(?,?,?,?)}"), where some of (?) is an out parameter(s). Hence you should change procedure's parameters:

    CREATE OR REPLACE PROCEDURE SP_NEW_USERS(result out NUMBER, currentBeginDate in DATE, currentEndDate in DATE, previousBeginDate in DATE, previousEndDate in DATE) as

    //Your code

    result := ((tmpVar - tmpVar1)/tmpVar) * 100;

    //You don't need tmpVar2 variable, use out parameter "result" instead.
    //Your code


    And then from java:

    try
    {
    CallableStatement cstmt = con.prepareCall("{ call SP_NEW_USERS(?,?,?,?,?) }");
    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(1);
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •