How to run a PLSQL function with return and in out parameter using java in DB2?

Question

I created a PLSQL function in DB2 and tried to execute it using java. But I am receiving an error code of -440 stating no matching stored procedure is found. Please see the code snippet below.

Java Code

    Connection dbConnection = null;
    CallableStatement callableStatement = null;

    try {
        dbConnection = getDBConnection();
        dbConnection.setAutoCommit(false);


        String getDBUSERByUserIdSql = "{? = call saju_func(?,?)}";

        callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
        callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
        callableStatement.registerOutParameter(3, java.sql.Types.INTEGER);

        callableStatement.setInt(2, 10);
        callableStatement.setInt(3, 20);


        // execute getDBUSERByUserId store procedure
        callableStatement.executeUpdate();

        int sum = callableStatement.getInt(1);
        System.out.println(sum);
        dbConnection.commit();


    } catch (SQLException e) {
        dbConnection.rollback();
        System.out.println(e.getMessage());

    } finally {

        if (callableStatement != null) {
            callableStatement.close();
        }
        if (dbConnection != null) {
            dbConnection.close();
        }
    }
}

PLSQL Function

CREATE OR REPLACE FUNCTION saju_func (
     s_num1     IN      NUMBER,
     s_num2     IN OUT      NUMBER )
RETURN NUMBER
IS
BEGIN
    s_num2:= (s_num1+s_num2);
    RETURN s_num2;
END saju_func;
/

I have tried a similar procedure (same code without return) and is working fine.

mustaccio · Accepted Answer · 2013-05-29 11:51:22Z

2

I don't believe you can CALL a function in DB2. CALL applies to stored procedures only. You can either rewrite your function as a stored procedure or execute a PL/SQL block in which the function is invoked (examples here).

answered May 29, 2013 at 11:51

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Rotteveel · Accepted Answer · 2013-05-29 08:33:03Z

1

Your procedure doesn't return anything (it has an IN OUT parameter instead), so use {call saju_func(?,?)} instead and adjust the indices of your parameters to:

callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);

callableStatement.setInt(1, 10);
callableStatement.setInt(2, 20);

UPDATE Based on your updated code, you probably need to remove

callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);

The reason: a return value is generally NOT considered to be an OUT parameter, so it shouldn't be registered as such.

NOTE: I don't use DB2 myself, my answer is based on my general understanding and expectations of JDBC.

edited May 29, 2013 at 8:33

answered May 29, 2013 at 7:59

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

2 Comments

Saju Over a year ago

By mistake I added the procedure instead of function. COde is updated. Please help to run this function, The procedure I shared earlier was working fine the way suggested. But problem is in calling function.

Mark Rotteveel Over a year ago

@Saju See my update, I can't run this for you as I don't use DB2 myself.

Tilak Sharma · Accepted Answer · 2015-07-30 11:49:10Z

0

You can call a DB2 function in JDBC program like:

String csSql = "{? = call abc.dbo.GetLength(?)}"

CallableStatement cStmt = conn.prepareCall(sql);
cStmt.registerOutParameter(1, Types.INTEGER);
cStmt.setString(2, "abcd");

boolean returnValue = cStmt.execute();
int length = cStmt.getInt(1);
System.out.println(length);

The above code would print 4

answered Jul 30, 2015 at 11:49

Tilak Sharma

111 silver badge3 bronze badges

Collectives™ on Stack Overflow

How to run a PLSQL function with return and in out parameter using java in DB2?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related