CODEWITHSUNDEEP
javaoracle-databasestored-proceduresjdbc
Mike
Mike

Reputation: 7914

Stored Procedure Out param through Java

I've below stored proc:

CREATE OR REPLACE PROCEDURE "demo"."run_demo"(v_sql IN VARCHAR2, return_code OUT number)
AS

i number;

BEGIN
return_code := 0;
execute immediate v_sql;
i := sql%rowcount;

    IF (i<1)
    THEN return_code := 1;
END IF;

EXCEPTION  
    WHEN OTHERS THEN
    return_code := SQLCODE;
END;

I'm trying to call it from Java as shown below:

public static void main(String[] args) {
    try {
        Class.forName("oracle.jdbc.OracleDriver");      
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@10.10.10.10:1521:demo", "demo_app", "demo");

        CallableStatement cs = conn.prepareCall("{call demo.run_demo(?)}");
        cs.registerOutParameter(1, Types.INTEGER);
        cs.setString(1, "update demo.users set locale=''english'' where user_id = 2");

        cs.execute();

        System.out.println("out="+cs.getInt(1));
    } catch (Exception e) {         
        e.printStackTrace();
    }
}

But its returning error

"Parameter Type Conflict".

Can any one tell me what's wrong?

Also, how can I return additional return_string in stored proc which would contain oracle error message (may be short description of error message)?

Thanks!

Upvotes: 1

Views: 13662

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

The question @Alex van den Hoogen referred to is very similar, but is using a function rather than a procedure, which seems to have confused you slightly. The gist is the same; you aren't passing enough parameters in the Java call - you're setting and getting the same positional parameter, number 1. You need to do something like:

CallableStatement cs = conn.prepareCall("{call demo.run_demo(?,?)}");
cs.setString(1, "update demo.users set locale=''english'' where user_id = 2");
cs.registerOutParameter(2, Types.INTEGER);

cs.execute();

System.out.println("out="+cs.getInt(2));

So in the prepareCall, ?,? instead of just ?; and 2 instead of 1 in registerOutParameter and getInt.

Or with the updated procedure from your later question, getting the error message too:

CallableStatement cs = conn.prepareCall("{call demo.run_demo(?,?,?)}");
cs.setString(1, "update demo.users set locale=''english'' where user_id = 2");
cs.registerOutParameter(2, Types.INTEGER);
cs.registerOutParameter(3, Types.VARCHAR);

cs.execute();

System.out.println("out="+cs.getInt(2) + ":" + cs.getString(3));

Upvotes: 2

Related Questions