We are getting inconsistent results from a call to an Oracle database. We have a CallableStatement that returns 4 INTEGERs.
It runs perfectly if I run the Oracle procedure directly from the sql command line and then call it from the java code.
However if I recompile the Oracle stored procedures and execute the Callable Statement (without first running it from sql) then I am getting incorrect results...1,2,1,1 is being returned instead of 1,2,0,3. Are these results being cached somewhere???
We are using Oracle 8.1.7, JDK 1.3.1, and Oracle thin driver 8.1.7.
The code is as follows:
<pre>
private CallableStatement autoLBNumbersNeeded;
private void prepareStatements(){
..............
.............
autoLBNumbersNeeded=dbConnection.prepareCall("{CALL
pkg_scheduler.pr_auto_lb_nos(?,?,?,?)}");
autoLBNumbersNeeded.clearParameters();
autoLBNumbersNeeded.registerOutParameter(1, java.sql.Types.INTEGER);
autoLBNumbersNeeded.registerOutParameter(2, java.sql.Types.INTEGER);
autoLBNumbersNeeded.registerOutParameter(3, java.sql.Types.INTEGER);
autoLBNumbersNeeded.registerOutParameter(4, java.sql.Types.INTEGER);
...........
}
public UserNumber[] doAutoLoadBalancing () throws SQLException {
.............
.............
autoLBNumbersNeeded.clearParameters();
autoLBNumbersNeeded.execute();
mwFF = autoLBNumbersNeeded.getLong(1);
mwHF = autoLBNumbersNeeded.getLong(2);
wmFF = autoLBNumbersNeeded.getLong(3);
wmHF = autoLBNumbersNeeded.getLong(4);
autoLBNumbersNeeded.clearParameters();
...........
}
</pre>
The Oracle procedure is
<pre>
PROCEDURE proc1( p_parm_1 OUT NUMBER, p_parm_2 OUT NUMBER, p_parm_3
OUT NUMBER, p_parm_4 OUT NUMBER)
AS
BEGIN
-- Get Counts
SELECT COUNT(*)
INTO p_parm_1
FROM blah ......
SELECT COUNT(*)
INTO p_parm_2
FROM blah ......
SELECT COUNT(*)
INTO p_parm_3
FROM blah ......
SELECT COUNT(*)
INTO p_parm_4
FROM blah ......
EXCEPTION
blah .......
END proc1;
</pre>
Can anyone help?
Many thanks
Fionnuala