Author: byterock
Date: Tue Mar 18 11:57:50 2008
New Revision: 10940
Added:
dbd-oracle/branches/scroll/t/51scroll.t
Modified:
dbd-oracle/branches/scroll/Changes
dbd-oracle/branches/scroll/Oracle.pm
dbd-oracle/branches/scroll/Oracle.xs
dbd-oracle/branches/scroll/dbdimp.c
dbd-oracle/branches/scroll/dbdimp.h
dbd-oracle/branches/scroll/oci8.c
Log:
almost complete patch now. Did change ora_fetch_scroll to a two param function
rather that an atrib type function. Test is included and the pod is almost
complete
Modified: dbd-oracle/branches/scroll/Changes
==============================================================================
--- dbd-oracle/branches/scroll/Changes (original)
+++ dbd-oracle/branches/scroll/Changes Tue Mar 18 11:57:50 2008
@@ -1,4 +1,5 @@
=head1 Changes in DBD-Oracle 1.21(svn rev xxxx)
+ Changed the max size of cache_rows to a ub4 rather than a ub2
Added support for Lobs in select of OCI Embedded Objects from John Scoles
with a big thankyou to Paul Weiss
Fixed for embedded object in object from Paul Weiss
Added support for direct insert of large XML data into XMLType fields from
Hendrik Fuss
Modified: dbd-oracle/branches/scroll/Oracle.pm
==============================================================================
--- dbd-oracle/branches/scroll/Oracle.pm (original)
+++ dbd-oracle/branches/scroll/Oracle.pm Tue Mar 18 11:57:50 2008
@@ -24,7 +24,7 @@
ORA_CLOB ORA_BLOB ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE
SQLT_INT SQLT_FLT OCI_STMT_SCROLLABLE_READONLY OCI_FETCH_NEXT
OCI_FETCH_CURRENT OCI_FETCH_FIRST OCI_FETCH_LAST OCI_FETCH_PRIOR
- OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVE ORA_OCI
+ OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVE ORA_OCI
) ],
ora_session_modes => [ qw( ORA_SYSDBA ORA_SYSOPER ) ],
);
@@ -939,8 +939,8 @@
ora_parse_lang => undef,
ora_placeholders => undef,
ora_auto_lob => undef,
- ora_check_sql => undef
- };
+ ora_check_sql => undef,
+ };
}
}
@@ -1221,7 +1221,6 @@
bind type for ORA_NUMBER_TABLE. See notes about ORA_NUMBER_TABLE elsewhere
in this document
-
=item ORA_OCI
Oracle doesn't provide a formal API for determining the exact version
@@ -1466,6 +1465,11 @@
See L</Prepare postponed till execute> for more information.
+=item ora_exe_mode
+This will set the execute mode of the current statement. Presently only one
mode is suppored;
+
+ OCI_STMT_SCROLLABLE_READONLY = 'scrollable results sets'
+
=back
=head2 Placeholder Binding Attributes
@@ -2457,6 +2461,56 @@
Set L</ora_check_sql> to 0 in prepare() to enable this behaviour.
+=head1 Scrollable Cursors
+A 'Scrollable Cursor' provides support for farward and backward access into
the 'result set'
+from a given postion, using either aboslute or relative row numbers offset
into the 'result set'.
+
+To use this functionality you must first import the "Fetch Options" constants
by using
+
+ use DBD::Oracle qw(:ora_types);
+
+This will import the follow constants
+
+ OCI_FETCH_CURRENT - gets the current row.
+ OCI_FETCH_NEXT - gets the next row from the current postion.
+ OCI_FETCH_FIRST - gets the first row in the 'result set'
+ OCI_FETCH_LAST - gets the last row in the 'result set'
+ OCI_FETCH_PRIOR - positions the result set on the previous row from the
current row in the 'result set'
+ OCI_FETCH_ABSOLUTE - will fetch a row number in the result set using
absolute postioning
+ OCI_FETCH_RELATIVE - will fetch a row number in the result set using
relative postioning
+
+Next you will have to tell DBD::Oracle that you will be using scrolling by
setting the ora_exe_mode atribute on the
+statement handle to 'OCI_STMT_SCROLLABLE_READONLY' with the prepare method
like this
+
+ $sth=$dbh->prepare($sql,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
+
+When the stament is executed you will then be able to use ora_fetch_scroll
method to get your results or any of the other fetch commends.
+
+=head2 Scrollable Cursor Methods
+The following driver-specific methods let you use scrollable cursors.
+
+=over 4
+
+=item ora_fetch_scroll
+
+ my $value = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset});
+
+Works the same as fetchrow_array however one passes in 'fetch_orient' constant
and a fetch_offset value which determin which record will be fetched it then
+returns it as a list containing the field values. Null fields are returned as
undef values in the list.
+
+=over 4
+
+=item ora_scroll_position
+
+ my $position = $sth->ora_scroll_position();
+
+This function returns the current postion in the result set.
+
+=head2 Scrollable Cursor Examples
+
+
+
+This function retrives
=head1 Handling LOBs
=head2 Simple Usage
@@ -2474,9 +2528,9 @@
To correct for this you must use an SQL UPDATE statement to reset the
LOB column to a non-NULL (or empty) value with an SQL like this;
- UPDATE lob_example
- SET bindata=EMPTY_BLOB()
- WHERE bindata IS NULL.
+ UPDATE lob_example
+ SET bindata=EMPTY_BLOB()
+ WHERE bindata IS NULL.
When fetching LOBs they are, by default, made to look just like LONGs and
are subject to the LongReadLen and LongTruncOk attributes. Note that
Modified: dbd-oracle/branches/scroll/Oracle.xs
==============================================================================
--- dbd-oracle/branches/scroll/Oracle.xs (original)
+++ dbd-oracle/branches/scroll/Oracle.xs Tue Mar 18 11:57:50 2008
@@ -33,7 +33,7 @@
SQLCS_NCHAR = SQLCS_NCHAR
SQLT_INT = SQLT_INT
SQLT_FLT = SQLT_FLT
- OCI_BATCH_MODE = 0x01
+ OCI_BATCH_MODE = 0x01
OCI_EXACT_FETCH = 0x02
OCI_KEEP_FETCH_STATE = 0x04
OCI_DESCRIBE_ONLY = 0x10
@@ -107,19 +107,22 @@
}
void
-ora_fetch_scroll(sth,attribs)
+ora_fetch_scroll(sth,fetch_orient,fetch_offset)
SV * sth
- SV * attribs
+ IV fetch_orient
+ IV fetch_offset
PREINIT:
D_imp_sth(sth);
CODE:
{
AV *av;
- SV **svp;
- int fetch_orient = OCI_FETCH_NEXT;
+ /* SV **svp;
+ if (fetch_orient){
+ fetch_orient = OCI_FETCH_NEXT;
+ }
sb4 fetch_offset = 0;
DBD_ATTRIB_GET_IV( attribs, "fetch_orient",12, svp, fetch_orient);
- DBD_ATTRIB_GET_IV( attribs, "fetch_offset",12, svp, fetch_offset);
+ DBD_ATTRIB_GET_IV( attribs, "fetch_offset",12, svp, fetch_offset);*/
imp_sth->fetch_orient=fetch_orient;
imp_sth->fetch_offset=fetch_offset;
av = dbd_st_fetch(sth,imp_sth);
Modified: dbd-oracle/branches/scroll/dbdimp.c
==============================================================================
--- dbd-oracle/branches/scroll/dbdimp.c (original)
+++ dbd-oracle/branches/scroll/dbdimp.c Tue Mar 18 11:57:50 2008
@@ -906,6 +906,9 @@
if (kl==10 && strEQ(key, "AutoCommit")) {
DBIc_set(imp_dbh,DBIcf_AutoCommit, on);
}
+ else if (kl==19 && strEQ(key, "ora_prefetch_memory")) {
+ imp_dbh->prefetch_memory = SvIV(valuesv);
+ }
else if (kl==12 && strEQ(key, "RowCacheSize")) {
imp_dbh->RowCacheSize = SvIV(valuesv);
}
@@ -955,30 +958,35 @@
retsv = boolSV(DBIc_has(imp_dbh,DBIcf_AutoCommit));
}
else if (kl==12 && strEQ(key, "RowCacheSize")) {
- retsv = newSViv(imp_dbh->RowCacheSize);
+ retsv = newSViv(imp_dbh->RowCacheSize);
+ }
+ else if (kl==19 && strEQ(key, "ora_prefetch_memory")) {
+ retsv = newSViv(imp_dbh->prefetch_memory);
}
else if (kl==22 && strEQ(key, "ora_max_nested_cursors")) {
- retsv = newSViv(imp_dbh->max_nested_cursors);
+ retsv = newSViv(imp_dbh->max_nested_cursors);
}
else if (kl==11 && strEQ(key, "ora_ph_type")) {
- retsv = newSViv(imp_dbh->ph_type);
+ retsv = newSViv(imp_dbh->ph_type);
}
else if (kl==13 && strEQ(key, "ora_ph_csform")) {
- retsv = newSViv(imp_dbh->ph_csform);
+ retsv = newSViv(imp_dbh->ph_csform);
}
else if (kl==22 && strEQ(key, "ora_parse_error_offset")) {
retsv = newSViv(imp_dbh->parse_error_offset);
}
if (!retsv)
- return Nullsv;
+ return Nullsv;
if (cacheit) { /* cache for next time (via DBI quick_FETCH) */
- SV **svp = hv_fetch((HV*)SvRV(dbh), key, kl, 1);
- sv_free(*svp);
- *svp = retsv;
- (void)SvREFCNT_inc(retsv); /* so sv_2mortal won't free it */
+ SV **svp = hv_fetch((HV*)SvRV(dbh), key, kl, 1);
+ sv_free(*svp);
+ *svp = retsv;
+ (void)SvREFCNT_inc(retsv); /* so sv_2mortal won't free it
*/
}
+
if (retsv == &sv_yes || retsv == &sv_no)
- return retsv; /* no need to mortalize yes or no */
+ return retsv; /* no need to mortalize yes or no */
+
return sv_2mortal(retsv);
}
Modified: dbd-oracle/branches/scroll/dbdimp.h
==============================================================================
--- dbd-oracle/branches/scroll/dbdimp.h (original)
+++ dbd-oracle/branches/scroll/dbdimp.h Tue Mar 18 11:57:50 2008
@@ -46,6 +46,7 @@
int parse_error_offset; /* position in statement of last error */
int max_nested_cursors; /* limit on cached nested cursors per stmt */
int array_chunk_size; /* the max size for an array bind */
+ int prefetch_memory; /* OCI_PREFETCH_MEMORY*/
};
#define DBH_DUP_OFF sizeof(dbih_dbc_t)
Modified: dbd-oracle/branches/scroll/oci8.c
==============================================================================
--- dbd-oracle/branches/scroll/oci8.c (original)
+++ dbd-oracle/branches/scroll/oci8.c Tue Mar 18 11:57:50 2008
@@ -33,6 +33,7 @@
imp_drh->ora_trunc = perl_get_sv("Oraperl::ora_trunc", GV_ADDMULTI);
imp_drh->ora_cache = perl_get_sv("Oraperl::ora_cache", GV_ADDMULTI);
imp_drh->ora_cache_o = perl_get_sv("Oraperl::ora_cache_o", GV_ADDMULTI);
+
}
@@ -111,6 +112,27 @@
return SvPV(sv,na);
}
+char *
+oci_fetch_options(ub4 fetchtype)
+{
+ dTHX;
+ SV *sv;
+ switch (fetchtype) {
+ /* fetch options */
+ case OCI_FETCH_CURRENT: return "OCI_FETCH_CURRENT";
+ case OCI_FETCH_NEXT: return "OCI_FETCH_NEXT";
+ case OCI_FETCH_FIRST: return "OCI_FETCH_FIRST";
+ case OCI_FETCH_LAST: return "OCI_FETCH_LAST";
+ case OCI_FETCH_PRIOR: return "OCI_FETCH_PRIOR";
+ case OCI_FETCH_ABSOLUTE: return "OCI_FETCH_ABSOLUTE";
+ case OCI_FETCH_RELATIVE: return "OCI_FETCH_RELATIVE";
+ }
+ sv = sv_2mortal(newSViv((IV)fetchtype));
+ return SvPV(sv,na);
+}
+
+
+
static sb4
oci_error_get(OCIError *errhp, sword status, char *what, SV *errstr, int debug)
@@ -1271,30 +1293,31 @@
{
dTHX;
if (has_longs) /* override/disable caching */
- cache_rows = 1; /* else read_blob can't work */
+ cache_rows = 1; /* else read_blob can't work
*/
else
- if (cache_rows == 0) { /* automatically size the cache */
+ if (cache_rows == 0) { /* automatically size the cache
*/
- /* Oracle packets on ethernet have max size of around 1460. */
- /* We'll aim to fill our row cache with around 10 per go. */
- /* Using 10 means any 'runt' packets will have less impact. */
- int txfr_size = 10 * 1460; /* desired transfer/cache size */
-
- /* Use guessed average on-the-wire row width calculated above & */
- /* add in overhead of 5 bytes per field plus 8 bytes per row. */
- /* The n*5+8 was determined by studying SQL*Net v2 packets. */
- /* It could probably benefit from a more detailed analysis. */
- est_width += num_fields*5 + 8;
-
- cache_rows = txfr_size / est_width; /* (maybe 1 or 0) */
-
- /* To ensure good performance with large rows (near or larger */
- /* than our target transfer size) we set a minimum cache size. */
- if (cache_rows < 6) /* is cache a 'useful' size? */
- cache_rows = (cache_rows > 0) ? 6 : 4;
- }
- if (cache_rows > 32767) /* keep within Oracle's limits */
- cache_rows = 32767;
+ /* Oracle packets on ethernet have max size of around
1460. */
+ /* We'll aim to fill our row cache with around 10 per
go. */
+ /* Using 10 means any 'runt' packets will have less
impact. */
+ int txfr_size = 10 * 1460; /* desired
transfer/cache size */
+
+ /* Use guessed average on-the-wire row width calculated
above & */
+ /* add in overhead of 5 bytes per field plus 8 bytes
per row. */
+ /* The n*5+8 was determined by studying SQL*Net v2
packets. */
+ /* It could probably benefit from a more detailed
analysis. */
+ est_width += num_fields*5 + 8;
+
+ cache_rows = txfr_size / est_width; /* (maybe
1 or 0) */
+
+ /* To ensure good performance with large rows (near or
larger */
+ /* than our target transfer size) we set a minimum
cache size. */
+ if (cache_rows < 6) /* is cache a 'useful' size?
*/
+ cache_rows = (cache_rows > 0) ? 6 : 4;
+ }
+
+ if (cache_rows > 10000000) /* keep within Oracle's limits */
+ cache_rows = 10000000; /* seems it was ub2 at one time now ub4
this number is arbitary on my part*/
return cache_rows;
}
@@ -1704,6 +1727,8 @@
+
+
static int /* --- Setup the row cache for this sth --- */
sth_set_row_cache(SV *h, imp_sth_t *imp_sth, int max_cache_rows, int
num_fields, int has_longs)
{
@@ -1711,57 +1736,75 @@
D_imp_dbh_from_sth;
D_imp_drh_from_dbh;
int num_errors = 0;
- ub4 cache_mem, cache_rows;
+ ub4 cache_mem=0;
+ ub4 cache_rows=10000;/* set high so memory is the limit */
sword status;
- /* number of rows to cache */
+ /* reworked this is little so the user can set up his own cache
+ basically if rowcachesize or prefetch_mem is set it uses those values
+ otherwise it does it itself
+ no sure what happens in the last case but I lwft it in for now
+ Also I think in later version of OCI this call does not really do
anything
+ */
+
+ /* number of rows to cache if using oraperl */
if (SvOK(imp_drh->ora_cache_o)) imp_sth->cache_rows =
SvIV(imp_drh->ora_cache_o);
else if (SvOK(imp_drh->ora_cache)) imp_sth->cache_rows =
SvIV(imp_drh->ora_cache);
- else imp_sth->cache_rows =
imp_dbh->RowCacheSize;
-
- if (imp_sth->cache_rows >= 0) { /* set cache size by row count */
- /* imp_sth->est_width needs to be set */
- cache_mem = 0; /* so memory isn't the limit */
- cache_rows = calc_cache_rows(imp_sth->cache_rows,
- (int)num_fields, imp_sth->est_width, has_longs);
- if (max_cache_rows && cache_rows > (unsigned long) max_cache_rows)
- cache_rows = max_cache_rows;
- imp_sth->cache_rows = cache_rows; /* record updated value */
-
- OCIAttrSet_log_stat(imp_sth->stmhp, OCI_HTYPE_STMT,
- &cache_mem, sizeof(cache_mem), OCI_ATTR_PREFETCH_MEMORY,
- imp_sth->errhp, status);
- OCIAttrSet_log_stat(imp_sth->stmhp, OCI_HTYPE_STMT,
- &cache_rows, sizeof(cache_rows), OCI_ATTR_PREFETCH_ROWS,
- imp_sth->errhp, status);
- if (status != OCI_SUCCESS) {
- oci_error(h, imp_sth->errhp, status, "OCIAttrSet
OCI_ATTR_PREFETCH_ROWS");
- ++num_errors;
- }
+
+
+ if (imp_dbh->RowCacheSize || imp_dbh->prefetch_memory){
+ /*user set values */
+ cache_rows =imp_dbh->RowCacheSize;
+ cache_mem =imp_dbh->prefetch_memory;
+
+ } else if (imp_sth->cache_rows >= 0) { /* set cache size by row count
*/
+
+ /* imp_sth->est_width needs to be set */
+ cache_mem = 0; /* so memory isn't the limit */
+
+ cache_rows =
calc_cache_rows(imp_sth->cache_rows,(int)num_fields, imp_sth->est_width,
has_longs);
+
+ if (max_cache_rows && cache_rows > (unsigned long)
max_cache_rows)
+ cache_rows = max_cache_rows;
+
+ imp_sth->cache_rows = cache_rows; /* record updated value
*/
+
}
else { /* set cache size by memory */
- cache_mem = -imp_sth->cache_rows; /* cache_mem always +ve here */
- cache_rows = 100000; /* set high so memory is the limit */
- if (max_cache_rows && cache_rows > (unsigned long) max_cache_rows) {
- cache_rows = max_cache_rows;
- imp_sth->cache_rows = cache_rows; /* record updated value only if
max_cache_rows */
+ /* not sure if we ever reach this*/
+ cache_mem = -imp_sth->cache_rows; /* cache_mem always +ve here
*/
+ if (max_cache_rows && cache_rows > (unsigned long)
max_cache_rows) {
+ cache_rows = max_cache_rows;
+ imp_sth->cache_rows = cache_rows; /* record updated value
only if max_cache_rows */
+ }
+
+ }
+
+
+ OCIAttrSet_log_stat(imp_sth->stmhp, OCI_HTYPE_STMT,
+ &cache_mem, sizeof(cache_mem),
OCI_ATTR_PREFETCH_MEMORY,
+ imp_sth->errhp, status);
+
+ if (status != OCI_SUCCESS) {
+ oci_error(h, imp_sth->errhp, status,
+ "OCIAttrSet
OCI_ATTR_PREFETCH_ROWS/OCI_ATTR_PREFETCH_MEMORY");
+ ++num_errors;
}
+
OCIAttrSet_log_stat(imp_sth->stmhp, OCI_HTYPE_STMT,
- &cache_rows, sizeof(cache_rows), OCI_ATTR_PREFETCH_ROWS,
- imp_sth->errhp, status);
- OCIAttrSet_log_stat(imp_sth->stmhp, OCI_HTYPE_STMT,
- &cache_mem, sizeof(cache_mem), OCI_ATTR_PREFETCH_MEMORY,
- imp_sth->errhp, status);
- if (status != OCI_SUCCESS) {
- oci_error(h, imp_sth->errhp, status,
- "OCIAttrSet OCI_ATTR_PREFETCH_ROWS/OCI_ATTR_PREFETCH_MEMORY");
+ &cache_rows, sizeof(cache_rows),
OCI_ATTR_PREFETCH_ROWS,
+ imp_sth->errhp, status);
+
+ if (status != OCI_SUCCESS) {
+ oci_error(h, imp_sth->errhp, status, "OCIAttrSet
OCI_ATTR_PREFETCH_ROWS");
++num_errors;
}
- }
+
if (DBIS->debug >= 3)
- PerlIO_printf(DBILOGFP,
+ PerlIO_printf(DBILOGFP,
" row cache OCI_ATTR_PREFETCH_ROWS %lu, OCI_ATTR_PREFETCH_MEMORY
%lu\n",
(unsigned long) (cache_rows), (unsigned long) (cache_mem));
+
return num_errors;
}
@@ -2237,9 +2280,12 @@
fbh->disize += 1; /* allow for null terminator */
/* dbsize can be zero for 'select NULL ...' */
+
imp_sth->t_dbsize += fbh->dbsize;
+
if (!avg_width)
avg_width = fbh->dbsize;
+
est_width += avg_width;
if (DBIS->debug >= 2)
@@ -2249,9 +2295,8 @@
imp_sth->est_width = est_width;
sth_set_row_cache(h, imp_sth,
- (nested_cursors) ? imp_dbh->max_nested_cursors / nested_cursors : 0,
- (int)num_fields, has_longs
- );
+ (nested_cursors) ? imp_dbh->max_nested_cursors / nested_cursors
: 0,
+ (int)num_fields, has_longs );
/* Initialise cache counters */
imp_sth->in_cache = 0;
@@ -2380,11 +2425,18 @@
}
if (imp_sth->fetch_orient) {
if (imp_sth->exe_mode!=OCI_STMT_SCROLLABLE_READONLY)
- croak ("attempt to use a scrollable cursor
without first setting ora_exe_mode to OCI_STMT_SCROLLABLE_READONLY\n") ;
+ croak ("attempt to use a scrollable cursor
without first setting ora_exe_mode to OCI_STMT_SCROLLABLE_READONLY\n") ;
+
+ if (DBIS->debug >= 4)
+ PerlIO_printf(DBILOGFP," Scrolling Fetch,
postion before fetch=%d, Orientation = %s , Fetchoffset =%d\n",
+
imp_sth->fetch_position,oci_fetch_options(imp_sth->fetch_orient),imp_sth->fetch_offset);
OCIStmtFetch_log_stat(imp_sth->stmhp, imp_sth->errhp,1,
imp_sth->fetch_orient,imp_sth->fetch_offset, status);
OCIAttrGet_stmhp_stat(imp_sth,
&imp_sth->fetch_position, 0, OCI_ATTR_CURRENT_POSITION, status);
+ if (DBIS->debug >= 4)
+ PerlIO_printf(DBILOGFP," Scrolling Fetch,
postion after fetch=%d\n",imp_sth->fetch_position);
+
} else {
OCIStmtFetch_log_stat(imp_sth->stmhp, imp_sth->errhp,1,
(ub2)OCI_FETCH_NEXT, 0, status);
Added: dbd-oracle/branches/scroll/t/51scroll.t
==============================================================================
--- (empty file)
+++ dbd-oracle/branches/scroll/t/51scroll.t Tue Mar 18 11:57:50 2008
@@ -0,0 +1,123 @@
+#!/usr/bin/perl
+
+use strict;
+use Test::More tests => 33;
+use DBD::Oracle qw(:ora_types);
+use DBI;
+
+unshift @INC ,'t';
+require 'nchar_test_lib.pl';
+
+## ----------------------------------------------------------------------------
+## 51scroll.t
+## By John Scoles, The Pythian Group
+## ----------------------------------------------------------------------------
+## Just a few checks to see if one can use a scrolling cursor
+## Nothing fancy.
+## ----------------------------------------------------------------------------
+
+BEGIN {
+ use_ok('DBI');
+}
+
+# create a database handle
+my $dsn = oracle_test_dsn();
+my $dbuser = $ENV{ORACLE_USERID} || 'scott/tiger';
+my $dbh = DBI->connect($dsn, $dbuser, '', { RaiseError=>1,
+ AutoCommit=>1,
+ PrintError => 0 });
+ok ($dbh->{RowCacheSize} = 10);
+ok ($dbh->{ora_prefetch_memory} = 200);
+
+# check that our db handle is good
+isa_ok($dbh, "DBI::db");
+
+my $table = table();
+
+
+$dbh->do(qq{
+ CREATE TABLE $table (
+ id INTEGER )
+ });
+
+
+my ($sql, $sth,$value);
+my $i=0;
+$sql = "INSERT INTO ".$table." VALUES (?)";
+
+$sth =$dbh-> prepare($sql);
+
+for ($i=1;$i<=10;$i++){
+ $sth-> bind_param(1, $i);
+ $sth->execute();
+}
+
+
+$sql="select * from ".$table;
+$sth=$dbh->prepare($sql,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
+ok ($sth->execute());
+
+#first loop all the way forward with OCI_FETCH_NEXT
+for($i=1;$i<=10;$i++){
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_NEXT,0);
+ cmp_ok($value->[0], '==', $i, '... we should get the next record');
+}
+
+
+$value = $sth->ora_fetch_scroll(OCI_FETCH_CURRENT,0);
+cmp_ok($value->[0], '==', 10, '... we should get the 10th record');
+
+#now loop all the way back
+for($i=1;$i<=9;$i++){
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_PRIOR,0);
+ cmp_ok($value->[0], '==', 10-$i, '... we should get the prior record');
+}
+
+#now +4 records relative from the present position of 0;
+
+$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,4);
+cmp_ok($value->[0], '==', 5, '... we should get the 5th record');
+
+#now +2 records relative from the present position of 4;
+
+$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,2);
+cmp_ok($value->[0], '==', 7, '... we should get the 7th record');
+
+#now -3 records relative from the present position of 6;
+
+$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,-3);
+
+cmp_ok($value->[0], '==', 4, '... we should get the 4th record');
+
+#now get the 9th record from the start
+$value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,9);
+
+cmp_ok($value->[0], '==', 9, '... we should get the 9th record');
+
+#now get the last record
+
+$value = $sth->ora_fetch_scroll(OCI_FETCH_LAST,0);
+
+cmp_ok($value->[0], '==', 10, '... we should get the 10th record');
+
+#now get the ora_scroll_position
+
+cmp_ok($sth->ora_scroll_position(), '==', 10, '... we should get the 10 for
the ora_scroll_position');
+
+#now back to the first
+
+$value = $sth->ora_fetch_scroll(OCI_FETCH_FIRST,0);
+cmp_ok($value->[0], '==', 1, '... we should get the 1st record');
+
+#check the ora_scroll_position one more time
+
+cmp_ok($sth->ora_scroll_position(), '==', 1, '... we should get the 1 for the
ora_scroll_position');
+
+$sth->finish();
+drop_table($dbh);
+
+
+$dbh->disconnect;
+
+1;
+
