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).
:
: 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
: ....)?
: 2) Are all records in the cursor selected all together or are the records
: selected one at a time?
:

Following the contradictory postings, and odd private mail on this one, I've run up Forms (but only 4.0) in client server against Oracle 7.0.16.

I created the following procedure, with a couple of variations, and ran the form with SQL_TRACE on. The results are below.

procedure get_test2 is

    cursor c1 is

            select object_name 
            from   test2 
            where   object_name > chr(0);

    open c1;
    loop

        fetch c1 into v1;
        exit;
/*      exit when c1%notfound */

    end loop;
end;

The table TEST2 holds 600 rows, and is indexed on object_name.

The first set of results shows the work done when the exit is unconditional after the first fetch; the second set of results if when the exit is on NOTFOUND.

call count cpu elapsed disk query current rows ------- ------ ------ --------- ----- ------- ------- -------

Parse        1    0.00      0.00     0      0       0       0
Execute      1    0.00      0.00     0      0       0       0
Fetch        1    0.00      0.00     0      2       0       1



Parse        1   0.00      0.00     0       0       0       0
Execute      1   0.00      0.00     0       0       0       0
Fetch      601   0.00      0.00     0     601       0     600

The work is done piece-meal as rows are fetched, not in bulk as the cursor is opened.

Variation 1: The really interesting one: if there is no FETCH, and the loop just exits, then the cursor is NEVER PARSED !! Does this mean that OPEN now does nothing, or is it just Forms 4 being smart at compile time and only coding for an OPEN when there is a FETCH to follow ? This is completely different from having the same PL/SQL block embedded in (say) a simple SQL*Plus script.

Variation 2: Using a SELECT FOR UPDATE: the work of identifying and locking all records is recorded in the Execute line as the first row is fetched: thereafter each subsequent fetch adds a little extra work to the Fetch line.

-- 
Jonathan Lewis