Re: cursors on client

In article <3dp10u$ptc_at_raffles.technet.sg>, shchua_at_technet.sg (S H CHUA) writes:
|> I have some questions on the use of cursors in a client-server
|> environment. I am running forms 4 on hp9000(server) and 486 pc(client).
|> Suppose I have a program unit in my Form that uses a cursor as follows:
|>
|> declare
|> cursor C1 is select ...........
|>
|> begin
|> open C1
|> loop
|> exit when C1%NOTFOUND
|> . .......
|> end loop
|> end
|>
|>
|> 1) At which statement does the system retrieve the records which meet the
|> select criteria from the database ( ie at the fetch, open or declare or
|> ....)?

The active set (namely the rows which match the query) is determined at OPEN time. This is where the work of the query is done. Any temporary segments for sorts, etc. are used at this point. The active set is stored in the PGA for the shadow process.

|> 2) Are all records in the cursor selected all together or are the records
|> selected one at a time?

The active set in the PGA consists of the rowids of the selected rows. Each fetch then retrieves from the active set.

|> 3) After retrieving the records from the database on the server, are they
|> placed in
|> the client(pc's memory) or are they manipulated in the server's memory?

The PGA is on the server, so that's where the active set is stored. Thus each fetch requires a network call to the database. You can reduce these calls by having the fetch done in a stored procedure, and returning a PL/SQL table to the client, or by fetching into a host array using the precompilers or OCI.

|>
|> Any help is appreciated.
|>

My pleasure.

|>
|>
|>
Received on Tue Dec 27 1994 - 18:17:50 CET