Re: OCI/ PL/SQL - Given a table with a large number of rows - how to implement "get next n rows" and "get prev n rows"

Someone else commented on using the explicit array feature of OCI.

Basically, I believe the solution is to

    open the cursor on the table
    loop

        collect a predetermined number of rows
        do whatever you want to do

OR

    function openCursor()
    function getNRows()
    function closeCursor

    main function()

        openCursor()
        looping...
            getNRows()
            do other stuff
        closeCursor()

This is to use the cursor and your data stores to control access to the data. The major drawback is that you cannot go backwards.

Another alternative is to create (1) a PL/SQL table or (2) an external file with the
same data in it. The first is more dynamic - Oracle does that storage and no external file needs
to be generated.
In both cases you can go backwards and forward, to the front, to the end, or to any positon.
You have basically random access. In both cases, you must first load the table or file prior to
using the data.

Mike Krolewski.

rakesh wrote:

> There is a table with a large number of records (>10000)
> OR a select statement results in number of rows which is so large that
> the client cannot cache all the rows.
>
> The client wants to get rows in batches (say of 100) from the Database and
> also be able to scroll through the result set. i.e. from a given row it
> should be able
> to say "get previous n rows" or "get next n rows".
>
> What is the most optimal way to implement this using OCI or PL/SQL. if any.
>
> Thanks
> rakesh
Received on Sun Jun 28 1998 - 17:32:21 CEST