Java parameter name name (for the result of the Oracle JDBC function)

Question

I am going to call a function and set some parameters by name, for example:

Connection c = null; ResultSet rs = null; String query; PreparedStatement ps; CallableStatement cs = null; try { c = DbUtils.getConnection(); cs = c.prepareCall("{? = call get_proc_name(?, ?) }"); cs.registerOutParameter(1, OracleTypes.VARCHAR); cs.setInt("in_proc_type", ProcTypes.SELECT); cs.setLong("in_table_id", tableId); // here I should use something like cs.registerOutParameter("result", OracleTypes.VARCHAR); cs.execute(); 

Parameters of the PL / SQL function:

 CREATE OR REPLACE FUNCTION get_proc_name ( in_proc_type IN NUMBER, /*1 - insert, 2 - update, 3 - delete, 4 - select*/ in_table_name IN VARCHAR2 := NULL, in_table_id IN NUMBER := NULL, in_table_type_id IN NUMBER := NULL, is_new IN NUMBER := 0 ) RETURN VARCHAR2 

The question is how to register the result as an out parameter, and then get it from oracle in java? I can register I / O parameters by name, because I know their names from the function, but I don’t know how to go get the result of the function, which variable name to use for it.

The manuals describe only in / out usage parameters with procedures, not functions.

Oracle Version: 11.1.0.6.0 Java Version: 1.6.0_14

Answer 1

You register the result of the function as if it were the first parameter. Obviously, this shifts the numbering of the actual parameters.

Your existing line

 cs.registerOutParameter(1, OracleTypes.VARCHAR); 

- that’s all it takes. After the call, get your result as follows:

 String result = cs.getString(1); 

Answer 2

The solution is to use only indices for parameter parameters. Such code works as expected (mix indices and named parameters do not work, so the problem of using a named parameter for the result variable cannot be solved, imho):

  c = DbUtils.getConnection(); cs = c.prepareCall("{? = call get_proc_name(in_proc_type => ?, in_table_id => ?) }"); cs.registerOutParameter(1, java.sql.Types.VARCHAR); cs.setInt(2, ProcTypes.SELECT); cs.setLong(3, tableId); cs.execute(); String procName = cs.getString(1); cs.close(); 

Answer 3

CallableStatement has a bunch of registerXXX methods that accept an index.

The way you record the result. This is the parameter number.

In your case

 cs.registerOutParameter( 1, java.sql.Types.VARCHAR); 

<Speculation>
BTW, because you are using the index for result , you may need to use the setXXX index-oriented methods and provide a complete list of parameters.
</ GT Spec;