I am having problems with a stored procedure in an Oracle database.
I just want to call a procedure (which has parameters 50 IN and 2 IN OUT) and get these two parameters OUT.
I try to execute () the associated CallableStatement, but it returns a NullPointerException to me
java.lang.NullPointerException at oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:977) at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1363) ...
Here is the code (I use Hibernate):
Session session = (Session) HibernateUtil.currentSession(); final Transaction transaction = session.beginTransaction(); try { session.doWork(new Work() { public void execute(Connection connection) throws SQLException { try { CallableStatement call = connection.prepareCall("{?,? = call geneva_admin.bksgnvcrmps.createorreturnproduct1nc(" + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + "?,?,?,?,?,?,?,?,?,?,?,?)}"); int i = 1; call.registerOutParameter(i++, Types.INTEGER); call.registerOutParameter(i++, Types.VARCHAR); call.setString(i++, typeofproduct); call.setString(i++, shiptositenumber); call.setString(i++, billtositenumber); call.setString(i++, ordernumber); call.setInt(i++, orderid); ... call.setInt(i++, errcode); call.setString(i++, errmsg); call.execute(); System.out.println("err_code: " + call.getString(1)); System.out.println("err_msg: " + call.getString(2)); call.close(); transaction.commit(); } catch (SQLException e) { throw new SQLException(e); } } }); } catch (Exception e) { e.printStackTrace(); transaction.rollback(); return false; }
If before executing () I set
call.setEscapeProcessing(false);
I have a SQLSyntaxErrorException (ORA-00911: invalid character) exception
could you help me?
Fab
Here is the solution:
The last two parameters of the procedure are: IN OUT. I thought we needed to control them as an OUT parameter:
CallableStatement call = connection.prepareCall("{?, ? = call bksgnvcrm.createorreturnproduct1nc(..., ?, ?)}");
But since they are IN OUT, these parameters must be declared as IN:
CallableStatement call = connection.prepareCall("{call bksgnvcrm.createorreturnproduct1nc(..., ?, ?)}");
Finally, register them as OUT as follows:
call.registerOutParameter(i, Types.INTEGER); call.setInt(i++, errcode); call.registerOutParameter(i, Types.VARCHAR); call.setString(i++, errmsg);
Hope this can help you :)