Re: Server Side Cursors

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 18 May 2004 22:28:14 -0700
Message-ID: <1084944497.318745@yasure>

Sybrand Bakker wrote:

> On Tue, 18 May 2004 12:46:32 -0500, "Joe"
> <heltibrand_at_NO_SPAMkarpel.com> wrote:
>
>

>>Hi All
>>
>>   I was just reading Patel's post from 5-14.
>>
>>   I'm one of two Forms developers in company of .net / SQL Server
>>developers.  Boss man has a hard time understanding why the SQL Server guys
>>and gals can't populate a grid (or whatever they call it in Microschlock
>>land) with x records at a time.  He asks why my Forms apps can serve up 15
>>rows at a time based on user requests (page down) without having to wait for
>>the entire result set to fill a data set (or whatever...).  All I can tell
>>him is "that's just the way it works!"
>>
>>   Does anyone have an explanation of how Forms (or is it the database?)
>>accomplishes this?
>>
>>Thanks
>>

>
>
> Oracle has a mechanism called 'array fetching'. You don't fetch record
> for record, you don't fetch all records at a time, but you fetch
> (configurable) say 15 records at a time.
> There is a specific call in the Oracle Call Interface to fetch n
> records, to fetch all records you simply repeatedly issue that call
> until it returns the -1403 'No data found' status.
> Forms simply has the user issue each call by pressing a button to
> fetch the next n records.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Here's an example of it in a stored procedure:

CREATE OR REPLACE PROCEDURE nrows_at_a_time ( p_array_size IN PLS_INTEGER DEFAULT 100) IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE; l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
   OPEN c;
   LOOP
     FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

     FORALL i IN 1..l_data.COUNT
     INSERT INTO t2 VALUES l_data(i);

     EXIT WHEN c%NOTFOUND;

   END LOOP;
   CLOSE c;
END nrows_at_a_time;
/

Note the LIMIT clause.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed May 19 2004 - 00:28:14 CDT