Hi. To start with I am an Oracle DBA, I know little to nothing about ColdFusion. I am supporting an application using ColdFusion MX7. They call one of our stored procedures which dynamically builds a SQL statement and returns to them via a RefCursor. The problem we are having is that the query could possibly return hundreds of thousands, or maybe a million or more rows. It's necessary to do the query processing on the DB server as the processing involves complex joins, group by, sorting, etc. processing. From a pl/sql point of view, after opening a cursor you must FETCH the rows from the Cursor, using program logic to determine when to continue and when to stop fetching(threshhold hit or end of cursor), then close the cursor. The Coldfusion developers claim they have no idea how to do this. I have supported other applications with the ability to do this (Java, Delphi). They claim that once they open the cursor it automatically fetches all rows until the end of the cursor, then closes it. Since they had issues with too much data crashing the Coldfusion server (out of memory) they have code in the pls/sql procedure that builds the dynamic sql statment for the ref cursor, that only fetches 100 rows at a time for display, using a (rownum BETWEEN 1 and 100) where clause delimiter. When the user clicks page 2, they call the stored procedure again and use (rownum BETWEEN 101 and 200), click page 4 and another call using (rownum BETWEEN 301 and 400). Unfortunately, oracle must process everything all over again (group by, sort, etc.) just to return the next 100 rows. For the larger queries that take up to 90 seconds to compute the results, this hit happens for every page (100 rows a page) that is selected. I am in utter shock that they are processing data like this. Could someone please let me know if its possible to programatically fetch records from the ref cursor, fetching rows as needed, say 100 at a time, then closing the cursor after the user is finished browsing the data and exits. If possible, does anyone have a code sample I could give them. Thanks.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279158 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
