Hi
Mike,
Thanks
for the prompt response.
If
we run the PL/SQL via SQL Plus, we don't experience the same delay
(there are about a million records in the database)
As
far as the database is concerned, we don't have much insight into the
configuration. Can you please provide me a list of possible database
parameters that we need to take a look at?
Please
note that even though there are a few thousand rows we always fetch
only 10 records. In other words, the cursor should have only 10 records
at any point in time.
I
debugged the code and here's the behavior I see. Do you see a similar
pattern?
1.
Procedure executes
2.
control gets transferred to RefCursorHandler (for OUT parameter of
ORACLECURSOR jdbcType). The following statement gets called
this.result = (ResultSet)arg0.getObject();
3.
next, ResultGetterImpl getObject() gets called. The following statement
within that method gets executed
return
rs.getObject(index);
4.
next, CallableStatementResultSet getObject(int columnIndex) gets called.
5.
next, oracle.jdbc.driver.OracleCallableStatement gets called (At
this point it takes a long time) and I can see NetSocket
statements.
Does
it mean the statement is making a call to the database again? Assuming
it is fetching data from the cursor, there should be only 10 records in
the cursor and it shouldn't be taking that long. Do you think it is
looping thru thousands of records again?
Thanks,
Satish
Satish,
I am also using oracle ref cursors and do not see the delay you are
experiencing.
Has something possibly changed in your database and the delay you see
is really in the pl/sql?
Regards,
Mike Fagan
Rao, Satish wrote:
Is anyone facing a similar issue. Please suggest.
I am using ORACLECURSOR in
my sqlmap and using the following TypeHandler code.
Here’s the sqlmap entry
<parameter
property="offerRS" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT"
typeHandler="RefCursorHandler"/>
And here's the handler code
public
class RefCursorHandler implements TypeHandlerCallback {
public Object getResult(ResultGetter arg0) throws SQLException {
this.result = (ResultSet)arg0.getObject();
return
this.result;
}
The statement (ResultSet)arg0.getObject(); takes a long time to
return (even for 2 rows).
Do you
see any issue with the code?
I
debugged this and eventually found that the above statement calls the
following method within CallableStatementResultSet
public Object getObject(int columnIndex) throws SQLException {
return cs.getObject(columnIndex);
}
This in turn calls
OracleCallableStatement. This part takes a long time to return.
I am using thin driver.
Please suggest. I need to
get this resolved ASAP.
Thanks,
Satish
Yes, It is talking to oracle and I beleive it is now actually executing
the cursor reference it returned to you.