Re: Ada DB bindings and APQ

From: Brian May (bam_at_snoopy.apana.org.au)
Date: 12/16/04 

Date: Thu, 16 Dec 2004 12:40:31 +1100

>>>>> "Brian" == Brian May <bam@snoopy.apana.org.au> writes:

    Brian> The documentation has an alternative loop structure:

    Brian> while not End_Of_Query(Q) loop Fetch(Q); ... end loop;

I realized, despite the documentation, End_Of_Query is not currently
supported on sequential connections, because it is not known if all
tuples have been fetched until you try to fetch the next one past the
end.

This limitation could be overcome if the previous call to the
"execute" or "fetch" called fetch in advance for the next row, and
stored the results in a temporary holding point. You don't miss out on
anything either, as all the rows will eventually have to be fetched
anyway.

This is better, IMHO, then requiring an exception be the terminating
condition for a loop.

An alternative would be to restructure the loop as:

while true loop
      Fetch(Q);
      exit if No_More_Data(Q);

      ...
end loop;

I don't particular like this approach though, although it would work. 

-- 
Brian May <bam@snoopy.apana.org.au>

Relevant Pages

  • Re: Self Joins and optimization
    ... What I'm interested in exploring is your assertion of the ... non-existence of a set oriented solution to the problem, ... while x.ArrivalTime is null and x%FOUND loop ...
    (comp.databases.theory)
  • Re: Ada DB bindings and APQ
    ... > supported on sequential connections, because it is not known if all ... > tuples have been fetched until you try to fetch the next one past the ... > condition for a loop. ... Fetch would raise an exception (it cannot be ...
    (comp.lang.ada)
  • Re: Bulk Collect without LIMIT
    ... Oracle, with 10g, merged the BULK COLLECT and FETCH to use the same mechanism. ... If you just say OPEN CURSOR ... ... for i in 1..500 loop ...
    (comp.databases.oracle.misc)
  • Re: Calling a SP inside a cursor loop..
    ... every loop iteration of the cursor. ... Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2 ... and If the Fetch stmt is below the begin Stmt, the loop iterations are ... the proper way to program a cursor loop is: ...
    (comp.databases.ms-sqlserver)
  • Re: -1555-ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" t
    ... it happen to fetch something from a big result set in a loop and commit ... Or does it happen that the query runs longer than ... Well, if this script receives ORA-1555 on regular basis, then you ...
    (comp.databases.oracle.server)