How do I call a stored procedure with fewer parameters?
String foo = "{call myStored(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(foo);
callableStatement.setInt(1, 10);
callableStatement.executeUpdate();
I have a stored procedure with 20 parameters. Can only a few parameters be set? The stored procedure also returns a value. I tried to just do call myStored(?)
and set callableStatement.setInt("colname", 10);
I get a missmatch in the number of parameters ... If the return value is also counted as a parameter, then it is 21?
source to share
You must bind all the parameters that you specify in the instructions. (for each? you need to provide a value with set * or registerOutParameter). But if these parameters have a default value (which is possible in PL / SQL), you do not need to declare them in the statement.
In DB:
FUNCTION get_empName(emp_id NUMBER, emp_name VARCHAR2 DEFAULT 'Something') RETURN VARCHAR2
In Java:
String statement1= "{? = call get_empName(?)}"; // valid statement
String statement2= "{? = call get_empName(?, ?)}"; // valid statement
If you have a function stored (it returns a value) you can write a statement like this
String foo = "{? = call myStored(?)}";
callableStatement = dbConnection.prepareCall(foo);
callableStatement.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
callableStatement.setInt(2, 10);
callableStatement.executeUpdate();
source to share