"Parameter type conflict" when calling Java Stored Procedure within another Java Stored Procedure

Question

Here's the problem (sorry for the bad english):

i'm working with JDeveloper and Oracle10g, and i have a Java Stored Procedure that is calling another JSP like the code:

int sd = 0;

try {
  CallableStatement clstAddRel = conn.prepareCall(" {call FC_RJS_INCLUIR_RELACAO_PRODCAT(?,?)} ");
  clstAddRel.registerOutParameter(1, Types.INTEGER);
  clstAddRel.setString(1, Integer.toString(id_produto_interno));
  clstAddRel.setString(2, ac[i].toString());
  clstAddRel.execute();

  sd = clstAddRel.getInt(1);
} catch(SQLException e) {
  String sqlTeste3 = "insert into ateste values (SQ_ATESTE.nextval, ?)";

   PreparedStatement pstTeste3 = conn.prepareStatement(sqlTeste3);
   pstTeste3.setString(1,"erro: "+e.getMessage()+ ac[i]);
   pstTeste3.execute();
   pstTeste3.close();
}

I'm recording the error in a table called ATESTE because this JavaSP is a procedure and not a function, I've to manipulate DML inside.

So, the error message I'm getting is: 'parameter type conflict'...

the function "FC_RJS_INCLUIR_RELACAO_PRODCAT" it's a Java Stored Procedure too, it's already exported to Oracle, and returns an int variable, and i have to read this to decide which webservice i will call from this JavaSP.

I have already tried the OracleTyep.NUMBER in the registerOutParameter.

Anyone knows what i'm doing wrong?

Answer 1

It looks like you are missing a parameter in your call. You register an Integer output parameter, and then you set 2 string parameters. I'm presuming your procedure FC_RJS_INCLUIR_RELACAO_PRODCAT returns an integer value. If so your code should look more like this:

CallableStatement clstAddRel = conn.prepareCall(" { ? = call FC_RJS_INCLUIR_RELACAO_PRODCAT(?,?)} ");
clstAddRel.registerOutParameter(1, Types.INTEGER);
clstAddRel.setString(2, Integer.toString(id_produto_interno));
clstAddRel.setString(3, ac[i].toString());
clstAddRel.execute();