Re: Fetch previous

From: David Fitzjarrell <oratune_at_msn.com>
Date: 14 Apr 2002 08:09:10 -0700
Message-ID: <32d39fb1.0204140709.392db899@posting.google.com>

Well, THAT was short and to the point, wasn't it? And, actually, it wasn't 'to the point' at all, since only OCI and SQLJ support scrollable cursors in 9i.

As an example, for SQLJ the general FETCH syntax for positional iterators is:

#sql { FETCH :iter INTO :x, :y, :z };

This is actually an abbreviated version of the following syntax.

#sql { FETCH NEXT FROM :iter INTO :x, :y, :z };

This suggests the pattern for alternatively moving to the previous, first, or last row in the result set. (Unfortunately, JDBC 2.0--after which the movement methods were modeled--uses previous(), whereas the FETCH syntax, which is patterned after SQL, employs PRIOR. In case you should forget this inconsistency, the SQLJ translator will also accept FETCH PREVIOUS.)
#sql { FETCH PRIOR FROM :iter INTO :x, :y, :z };
#sql { FETCH FIRST FROM :iter INTO :x, :y, :z };
#sql { FETCH LAST FROM :iter INTO :x, :y, :z };

There is also syntax to pass a numeric value for absolute or relative movements, to move to a particular (absolute) row, or to move forward or backward from the current position:

#sql { FETCH ABSOLUTE :n FROM :iter INTO :x, :y, :z };
#sql { FETCH RELATIVE :n FROM :iter INTO :x, :y, :z };

OCI is a bit more complex; let's assume a result set is returned by the SQL query:

SELECT empno, ename FROM emp

and that the table EMP has 14 rows. One usage of scrollable cursors is illustrated in the following code snippet:

...
/* execute the scrollable cursor in the scrollable mode */ OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4)

                                 0,  (CONST OCISnapshot *)
                                 NULL, (OCISnapshot *) NULL,
                                 OCI_STMT_SCROLLABLE_READONLY ); 
                                  

/* Fetches rows with absolute row numbers 6, 7, 8. After this call,  OCI_ATTR_CURRENT_POSITION = 8, OCI_ATTR_ROW_COUNT = 8 */ checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,

                               OCI_FETCH_ABSOLUTE, (sb4) 6,
OCI_DEFAULT);
                               

/* Fetches rows with absolute row numbers 6, 7, 8. After this call,  OCI_ATTR_CURRENT_POSITION = 8, OCI_ATTR_ROW_COUNT = 8 */ checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,

                               OCI_FETCH_RELATIVE, (sb4) -2,
OCI_DEFAULT);
                               

/* Fetches rows with absolute row numbers 14. After this call,  OCI_ATTR_CURRENT_POSITION = 14, OCI_ATTR_ROW_COUNT = 14 */ checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,

                               OCI_FETCH_LAST, (sb4) 0, OCI_DEFAULT);

/* Fetches rows with absolute row number 1. After this call,  OCI_ATTR_CURRENT_POSITION = 1, OCI_ATTR_ROW_COUNT = 14 */ checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,

                               OCI_FETCH_FIRST, (sb4) 0, OCI_DEFAULT);

/* Fetches rows with absolute row numbers 2, 3, 4. After this call,  OCI_ATTR_CURRENT_POSITION = 4, OCI_ATTR_ROW_COUNT = 14 */ checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,

                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT);

/* Fetches rows with absolute row numbers 3,4,5,6,7. After this call,  OCI_ATTR_CURRENT_POSITION = 7, OCI_ATTR_ROW_COUNT = 14. It is assumed the user's define memory is allocated. */ checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 5,

                               OCI_FETCH_PRIOR, (sb4) 0, OCI_DEFAULT);

...
}
checkprint (errhp, status)
{
 ub4 rows_fetched;
 checkerr (errhp, status);
 checkerr(errhp, OCIAttrGet((CONST void *) stmthp, OCI_HTYPE_STMT,

          (void *) &rows_fetched, (uint *) 0,
          OCI_ATTR_ROWS_FETCHED, errhp));

}
...

All of the above code samples came from the 9i documentation. I hope this helps you.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<XFXt8.6183$V44.50_at_rwcrnsc53>...

> yes.
> Jim
> "News" <jjdenboer_at_ncrvnet.nl> wrote in message
> news:a98vv9$51s$1_at_news.solcon.nl...
> > Hi,
> >
> > Does anybody know if it is possible in Oracle 9i to do a fetch previous ?
> >
> > --
> > Regards,
> >
> > Johan den Boer
> > email : j.denboer_at_ns.nl
> >
> >
> >
> >

Received on Sun Apr 14 2002 - 10:09:10 CDT