Getting ResultSet From Cursor Too Slow

All,

I have a method that calls an Oracle Stored Procedure from an Oracle
Package. Everything works fine but the last two lines in my code below
where I get the Cursor from the procedure and cast it to a ResultSet is
a huge bottleneck. Does anyone have any ideas on how to make this
transition faster for large resultSets?

Thanks in advance.

Corey

ResultSet rs = null;
Result r = null;

String sql = "{call SECINQ.GetUsersBasic(?, ?, ?, ?, ?, ?, ?, ?," +
"?, ?, ?, ?, ?, ?, ?, ?," +
"?, ?, ?, ?, ?, ?, ?, ?," +
"?, ?, ?, ?, ?, ?, ?, ?, ?)}";
open();

CallableStatement call = (CallableStatement)con.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.CURSOR);

call.setString(2, name1);
call.setString(3, name2);
call.setString(4, name3);
call.setString(5, name4);

call.setString(6, geid1);
call.setString(7, geid2);
call.setString(8, geid3);
call.setString(9, geid4);

call.setString(10, ssn1);
call.setString(11, ssn2);
call.setString(12, ssn3);
call.setString(13, ssn4);

call.setString(14, userId1.toUpperCase());
call.setString(15, userId2.toUpperCase());
call.setString(16, userId3.toUpperCase());
call.setString(17, userId4.toUpperCase());

call.setString(18, userId1.toLowerCase());
call.setString(19, userId2.toLowerCase());
call.setString(20, userId3.toLowerCase());
call.setString(21, userId4.toLowerCase());

call.setString(22, userId1);
call.setString(23, userId2);
call.setString(24, userId3);
call.setString(25, userId4);

call.setString(26, phone1);
call.setString(27, phone2);
call.setString(28, phone3);
call.setString(29, phone4);

call.setString(30, flName1);
call.setString(31, flName2);
call.setString(32, flName3);
call.setString(33, flName4);

call.setFetchSize(200);

call.execute();

rs = (ResultSet)call.getObject(1);

r = ResultSupport.toResult(rs);

.

Relevant Pages

  • CRecordset and oracle
    ... where I could find some example how to call oracle stored procedure ... wchich return ... resultset like REFCURSOR or something? ...
    (microsoft.public.vc.mfc)
  • remove CURSOR STATEMENT : 1 from printing out
    ... I have created a package the returns a ref cursor. ... type resultset is REF CURSOR; ... rset p_storedivreg_hist.resultset; ...
    (comp.databases.oracle.tools)
  • Oracle with Crystal Reports 11.5.3
    ... I am trying to develop a report to call an oracle stored procedure ... contained within a package and am having major trouble in that crystal ... CREATE OR REPLACE PACKAGE BODY p_Tradereport_NEW ... The problem is when I connect to the database via Crystal I use the ...
    (comp.databases.oracle.server)
  • Re: Transform Data Task and RowCount delivered
    ... Is it possible to store the number of selected rows in a global variable for ... further use in the package? ... the resultset because of performance problems. ...
    (microsoft.public.sqlserver.dts)
  • Re: How i know the size of "resultset"?
    ... oracle. ... i don't know resultset size,so how do i run this oracle's package and get ... but you'll need to walk trhough it to determine the ...
    (microsoft.public.dotnet.framework.adonet)