views:

142

answers:

3

I'm going to call a function, and set some parameters by name, 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();

PL/SQL function parameters are:

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 result as an out parameter, and then get it from oracle to java? I can register in/out parameters by name, because I know theirs names from function, but I don't know how go get function result, what variable name use for it.

Manuals describe only usage in/out params with procedures, not functions.

Oracle version: 11.1.0.6.0 Java version: 1.6.0_14

+1  A: 

CallableStatement has a bunch of registerXXX methods that take index.

That's how you register the result. It is parameter number 1.

In your case,

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

<SPECULATION>
BTW, because you are using index for result, you may need to use index-oriented setXXX methods and provide a full parameter list.
</SPECULATION>

Alexander Pogrebnyak
I got "Invalid column index" on line with cs.registerOutParameter( 0, java.sql.Types.VARCHAR);
Kerb
@Kerb. Sorry, it's 1, I've corrected the answer.
Alexander Pogrebnyak
A: 

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

Your already existing line

cs.registerOutParameter(1, OracleTypes.VARCHAR);

is all it takes. After the call, get your result like this:

String result = cs.getString(1);
ammoQ
I got exception "The number of parameter names does not match the number of registered praremeters" on line with "cs.execute();" .... p.s. I've just forgotten to remove this line when posted the code.
Kerb
IIRC you cannot mix positional and named parameters, you it seems like you have to use positional parameters for the whole statement, i.e. cs.setInt(2, ProcTypes.SELECT); cs.setLong(3, tableId);
ammoQ
A: 

The solution is to use only indexes for settings parameters. Such code works as expected (mixing indexes and named parameters doesn't work; so, the problem of using named parameter for result variable could not 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();
Kerb