Getting output parameters of a procedure-CodePudding

I have a stored procedure with plenty of parameters, I need to call this procedure from Java. All of procedure's input parameters have defaults, like this:

PROCEDURE create_entity
(
    p_id number deafult 1,
    p_code number default 1,
    p_uid number default 1,

    --......here comes about 50 of similar parameters with defaults set

    p_name out varchar2,
    p_location out number
)

My goal is to call this procedure via JDBC without specifying any input arguments, and fetch p_name, p_location output parameter values. In this case, do I need to specify all of 50 input parameters, even though I'm fine with using their default values?

CallableStatement stmt = connection.prepareCall("CALL create_entity(?, ?, ?, ?, ?, ?, ? ......)")

Is there any way to omit specifying those '?' in a call statement? How can I get just the output parameters values in an easy way?

CodePudding user response：

You can either use the => notation to specify which parameters you are passing, and use positional notation:

stmt = connection.prepareCall("CALL create_entity(p_name => ?, p_location => ?)");
stmt.registerOutParameter(1, ...);
stmt.registerOutParameter(2, ...);
...
stmt.execute();
stmt.getString(1);
stmt.getInt(2);

Or use named binding:

stmt = connection.prepareCall("CALL create_entity(?, ?)");
stmt.registerOutParameter("P_NAME", ...);
stmt.registerOutParameter("P_LOCATION", ...);
...
stmt.execute();
stmt.getString("P_NAME");
stmt.getInt("P_LOCATION");

But don't mix them (as the question linked in a comment shows, that won't work); and your driver and version might affect what is allowed.

If you want to override any of the input parameters then you can add those to the argument list, and bind values for them, while leaving the rest to use their defaults.