Java callableStatement giving error : Attempt to set a parameter name that does not occur in the SQL

Question

I want to execute a Oracle Stored procedure using named parameter from Java CollableStatement. Syntactically all is good by when we execute the application we get SQL Error-

Java Code -

int method1(){
  CallableStatement stmt stmt = connection.prepareCall("{call "+strSQL.toString()+"}");
  sp_copy_my_tree(?,?,?)
  stmt.setInt("src_cd_ekey", 2057);
  stmt.setInt("trg_ef_ekey", 8222);
  stmt.setInt("trg_display_order", 1]);

  returnValue = stmt.executeUpdate(strSQL.toString());
  return returnValue ;
}

Oracle Stored Procedure -

create or replace PROCEDURE  sp_copy_my_tree (src_ab_ekey IN NUMBER DEFAULT NULL, 
src_cd_ekey IN NUMBER DEFAULT NULL, trg_ef_ekey IN NUMBER DEFAULT NULL, 
trg_gh_ekey IN NUMBER DEFAULT NULL, trg_display_order IN NUMBER) 
IS
begin 
  --- Some PL/SQL code ---
END ;

When I execute the above java statement I am getting Exception -

*java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: src_cd_ekey*

Note- I have also tried to pass all the parameters in the same order of procedure, where for other 2 parameters I have passed null. But still getting the same Exception.

Please somebody help us to resolve this issue.

Answer 1

Try this method:

CallableStatement stmt = connection.prepareCall("{call sp_copy_my_tree (?,?,?}");
  stmt.setInt(1, 2057);
  stmt.setInt(2, 8222);
  stmt.setInt(3, 1);

  returnValue = stmt.executeUpdate();
  return returnValue ;
}

See documentation for details: http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#i1008346

Answer 2

This issue came because of two reasons.

1) The Number of parameters declared in Procedure is different than the number of parameter passed from Java procedure call. This is the reason why the aptly given Answer 1, to this thread cannot be used as is. We need to pass all 5 parameters from the procedure call in the proper sequence.

2) Named Parameter in Java is different then Oracle Named Parameter.

In Oracle we can execute procedure by passing only selected parameters, as the parameters passed may not be following the proper sequence so the values can be paired with the parameter name(as key). example -

    EXEC sp_copy_my_tree (src_cd_ekey=>2057, trg_ef_ekey=>8222, trg_display_order=>1);

In Java we cannot emulate this and java named parameter has different meaning. In java statement we can specify the parameter name starting with colon instead of using ? as placeholder. Later while setting the placeholder we can use these parameter names rather then index. Example -

    CallableStatement stmt stmt = connection.prepareCall( "{call sp_copy_my_tree(:src_cd_ekey,:trg_ef_ekey, :trg_display_order)}");

    stmt.setInt("src_cd_ekey", 2057);
    stmt.setInt("trg_ef_ekey", 8222);
    stmt.setInt("trg_display_order", 1]);

But this call by Oracle will be considered as execution request to procedure with first 3 parameters.

Note: I was getting SqlException with stmt.executeUpdate(), so I used stmt.execute(). It might be a problem in my implementation but the latter worked in my case.