Hi All,
I have my bean fetches data from the connection pooling.
DataSource ds = (DataSource) ic.lookup( "java:comp/env/myDB" );
Connection con = ds.getConnection();
PreparedStatement prepStmt =
con.prepareStatement( "select * from mytable where id = ? order by
col1" );
prepStmt.setString( 1, id );
ResultSet rs = prepStmt.executeQuery();
while ( rs.next() ) {
// store data record by record
}
So when you look at network traffic, for (n) number of records cursor is
making (n) number of database trips. In situation of thousands of records,
the performance hit is there. So to minimize traffic, I wrote a PL/SQL to
return %ROWTYPE to caller bean like this:
Connection con = ds.getConnection();
CallableStatement call =
con.prepareCall( "{ ? = call java_refcursor.list(?) }" ); //
PL/SQL function name qualified with package name
call.registerOutParameter( 1, OracleTypes.CURSOR );
call.setString( 2, "value" );
call.execute();
ResultSet rs = (ResultSet) call.getObject(1);
while ( rs.next() ) {
// fetch data
}
My pl/sql function is returning... type rc is ref cursor return
mytable%ROWTYPE
My primary objective is to call pl/sql function, get an object in single
traffic and unwrap the object in my bean without further trips to database.
BUT what call.getObject(1) returns is a reference cursor of the one defined
in my pl/sql function, so when I do rs.next(), it still doing network
traffic (i think???).
My question is: is there a way to get an object from database and isolate
the population from it? I thought of Pro C* with type (desc table).
Any idea would be greatly appreciated.
-David
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
