Author: byterock
Date: Wed Mar 19 08:07:58 2008
New Revision: 10945
Modified:
dbd-oracle/branches/scroll/Oracle.pm
dbd-oracle/branches/scroll/dbdimp.c
dbd-oracle/branches/scroll/dbdimp.h
dbd-oracle/branches/scroll/oci8.c
dbd-oracle/branches/scroll/t/51scroll.t
Log:
pod is ready
I also moved prefetch_memory from the db handle struct to the statement handle
strucT
and I have moved the constants out of ora_types and into these
ora_fetch_orient => [ qw( OCI_FETCH_NEXT OCI_FETCH_CURRENT OCI_FETCH_FIRST
OCI_FETCH_LAST OCI_FETCH_PRIOR OCI_FETCH_ABSOLUTE
OCI_FETCH_RELATIVE)],
ora_exe_modes => [ qw(OCI_STMT_SCROLLABLE_READONLY)],
Modified: dbd-oracle/branches/scroll/Oracle.pm
==============================================================================
--- dbd-oracle/branches/scroll/Oracle.pm (original)
+++ dbd-oracle/branches/scroll/Oracle.pm Wed Mar 19 08:07:58 2008
@@ -22,16 +22,18 @@
ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE
ORA_RAW ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_NTY
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
+ SQLT_INT SQLT_FLT ORA_OCI
) ],
ora_session_modes => [ qw( ORA_SYSDBA ORA_SYSOPER ) ],
+ ora_fetch_orient => [ qw( OCI_FETCH_NEXT OCI_FETCH_CURRENT
OCI_FETCH_FIRST
+ OCI_FETCH_LAST OCI_FETCH_PRIOR
OCI_FETCH_ABSOLUTE
+ OCI_FETCH_RELATIVE)],
+ ora_exe_modes => [ qw(OCI_STMT_SCROLLABLE_READONLY)],
);
@EXPORT_OK = qw(OCI_FETCH_NEXT OCI_FETCH_CURRENT OCI_FETCH_FIRST
OCI_FETCH_LAST OCI_FETCH_PRIOR
OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVE ORA_OCI
SQLCS_IMPLICIT SQLCS_NCHAR ora_env_var ora_cygwin_set_env);
#unshift @EXPORT_OK, 'ora_cygwin_set_env' if $^O eq 'cygwin';
- Exporter::export_ok_tags(qw(ora_types ora_session_modes));
+ Exporter::export_ok_tags(qw(ora_types ora_session_modes ora_fetch_orient
ora_exe_modes));
my $Revision = substr(q$Revision: 1.103 $, 10);
@@ -1466,10 +1468,17 @@
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;
+
+This will set the execute mode of the current statement. Presently only one
mode is supported;
OCI_STMT_SCROLLABLE_READONLY = 'scrollable results sets'
+=item ora_prefetch_memory
+
+Sets the memory level for top level rows to be prefetched. Rows up to the
specified top level row
+count C<RowCacheSize> are fetched if it occupies no more than the specified
memory usage limit. The default value is 0,
+which means that memory size is not included in computing the number of rows
to prefetch.
+
=back
=head2 Placeholder Binding Attributes
@@ -2462,37 +2471,44 @@
Set L</ora_check_sql> to 0 in prepare() to enable this behaviour.
=head1 Scrollable Cursors
+
Oracle supports the concept of a 'Scrollable Cursor' which is defined as a
'result set' where
the rows can be fetched either sequentially or non-sequentially. One can fetch
rows forward,
-backwards, from any given postion or the n-th row from the current position in
the 'result set'.
+backwards, from any given position or the n-th row from the current position
in the 'result set'.
-Rows are numbered sequntually starting at one and client-side caching of the
partial of entire result
+Rows are numbered sequentially starting at one and client-side caching of the
partial or entire result set
can improve performance by limiting round trips to the server.
Oracle does not support DML type operations with scrollable cursors so you are
limited
-to simple 'Select' opertions only. As you can not use this functionality if
the LONG
-datatype is part of the select list. However, Lobs, Clobs, and Blobs do work.
+to simple 'Select' operations only. As well you can not use this functionality
with remote
+mapped queries or if the LONG datatype is part of the select list.
-Only use scrollable cursors if you really have a good reason to. They do use
up conciderable
-more server and client resources and have poorer response times than
non-scolling cursors.
+However, Lobs, Clobs, and Blobs do work.
-=haed2 Enableing Scrollable Cursors
+Only use scrollable cursors if you really have a good reason to. They do use
up considerable
+more server and client resources and have poorer response times than
non-scrolling cursors.
-To ebable this functionality you must first import the "Fetch Orientation"
constants by using
+=head2 Enabling Scrollable Cursors
- use DBD::Oracle qw(:ora_types);
+To enable this functionality you must first import the "Fetch Orientation" and
the 'Execution Mode' constants by using
+
+ use DBD::Oracle qw(:ora_fetch_orient,:ora_exe_modes);
-Which will import the following constants
+Which will import the following fetch orientation constants;
OCI_FETCH_CURRENT - gets the current row.
- OCI_FETCH_NEXT - gets the next row from the current postion.
+ OCI_FETCH_NEXT - gets the next row from the current position.
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
+ OCI_FETCH_ABSOLUTE - will fetch a row number in the result set using
absolute positioning
+ OCI_FETCH_RELATIVE - will fetch a row number in the result set using
relative positioning
-Next you will have to tell DBD::Oracle that you will be using scrolling by
setting the ora_exe_mode atribute on the
+and the following Execution Mode constant;
+
+ OCI_STMT_SCROLLABLE_READONLY - Required to make the result set scrollable.
+
+Next you will have to tell DBD::Oracle that you will be using scrolling by
setting the ora_exe_mode attribute on the
statement handle to 'OCI_STMT_SCROLLABLE_READONLY' with the prepare method;
$sth=$dbh->prepare($sql,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
@@ -2502,32 +2518,31 @@
non-scrolling cursor.
=head2 Scrollable Cursor Methods
-The following driver-specific methods let you use scrollable cursors.
+
+The following driver-specific methods are used with scrollable cursors.
=over 4
=item ora_fetch_scroll
- my $value = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
+ my $value = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
Works the same as fetchrow_array method however, one passes in a "Fetch
Orientation" constant and a fetch_offset
value which will then determine the row that will be fetched. It returns the
row 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();
+ my $position = $sth->ora_scroll_position();
-This method returns the current postion in the result set.
+This method returns the current position in the result set.
=head2 Scrollable Cursor Usage
Given a simple code like this:
- use DBD::Oracle qw(:ora_types);
use DBI;
+ use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect($dsn, $dbuser, '');
my $sql = "select id,
first_name,
@@ -2536,40 +2551,137 @@
my $sth=$dbh->prepare($sql,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
$sth->execute();
my $value;
-and one assumes that the number of rows returned is the code snipits below
will illistrate the use of ora_fetch_scroll method.
+
+and one assumes that the number of rows returned from the query 20, the code
snippets below will illustrate the use of ora_fetch_scroll
+method.
=over 4
-=item Fetching the Current Record
+=item Fetching the Current Row
$value = $sth->ora_fetch_scroll(OCI_FETCH_CURRENT,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";
This will get the current row of the result set, which in this case would be
1, print the values for the fields in the row
-and then print the current scroll postion which would still be 1. With this
"Fetch Constant" the value for fetch offset is ignored.
+and then print the current scroll position which would still be 1. With this
fetch constant the value for fetch offset is ignored.
-=item Fetching the Next Record
+=item Fetching the Next Row
- for(my i=0;i<3;i++){
+ for(my i=0;i<=3;i++){
$value = $sth->ora_fetch_scroll(OCI_FETCH_NEXT,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
}
print "current scroll position=".$sth->ora_scroll_position()."\n";
-This snippett will get the next four rows from the result and print them and
then print the postion which will be 5,
-Again the value for fetch offset is ignored for this Fetch Constant".
+This snippet will get the next four rows from the result (2,3,4,5) set and
print them and then print the position which will be 5,
+Again the value for fetch offset is ignored for this fetch constant.
-=item OCI_FETCH_FIRST
+=item Fetching the First Row
$value = $sth->ora_fetch_scroll(OCI_FETCH_FIRST,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";
-This snippett will move the pointed back to the first row and print out its
values again. The position value will be 1
-in this case. Again the value for fetch offset is ignored for this Fetch
Constant".
+This snippet will move the pointer back to the first row (1) and print out its
values again. The position value will be 1
+in this case. Again the value for fetch offset is ignored for this fetch
constant.
+
+=item Fetching the Last Row
+
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_LAST,0);
+ print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
+ print "current scroll position=".$sth->ora_scroll_position()."\n";
+
+This snippet will move the pointer to the last row in the result set (20) and
print out its values. The position value will be 20
+in this case. The value for fetch offset is ignored for this fetch constant.
This is also way to determine the number of rows in
+the record set, however if you result set is large response time can be high.
+
+=item Fetching the Prior Row
+
+ for(my $i=0;$i<=3;i++){
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_PRIOR,0);
+ print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
+ }
+ print "current scroll position=".$sth->ora_scroll_position()."\n";
+
+This snippet will get the four prior rows from the result set (19,18,17,16)
and print them and then print
+the position which will be 16. Again the value for fetch offset is ignored for
this fetch constant.
+
+=item Fetching the 10th Row
+
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,10);
+ print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
+ print "current scroll position=".$sth->ora_scroll_position()."\n";
+
+This snippet will move the pointer to row 10 and print out its values. The
position value will be 10
+in this case. With this fetch constant the fetch offset value is the row
number to fetch.
+
+=item Fetching the 10th to 14th Row
+
+ for(my $i=10;$i<15;i++){
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,$i);
+ print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
+ }
+ print "current scroll position=".$sth->ora_scroll_position()."\n";
+
+In this snippet the OCI_FETCH_ABSOLUTE constant is used with the offset
variable $i to get rows 10,11,12,13, and 14
+from the record set and print them out. The position value will be 14 at the
end of this code.
+
+=item Fetching the 14th to 10th Row
+
+ for(my $i=14;$i>9;i--){
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,$i);
+ print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
+ }
+ print "current scroll position=".$sth->ora_scroll_position()."\n";
+
+In this snippet the OCI_FETCH_ABSOLUTE constant is used with the offset
variable $i to get rows 14,13,12,11, and 10
+from the record set and print them out. The position value will be 10 at the
end of this code.
+
+=item Fetching the 5th Row From the Present position
+
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,5);
+ print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
+ print "current scroll position=".$sth->ora_scroll_position()."\n";
+
+This snippet will jump forward in the record set by the offset value of '5',
from row 10 to row 15, and print out its values.
+The position value will be 15 at this point. With this fetch constant the
fetch offset value is the relative row from the current row to fetch.
+
+=item Fetching the 9th Row Prior From the Present position
+
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,-9);
+ print "id=".$value->[0].", First Name=".$value->[1].", Last
Name=".$value->[2]."\n";
+ print "current scroll position=".$sth->ora_scroll_position()."\n";
+
+This snippet will jump backward in the record set by the offset value of '-9',
from row 15 to row 6 and print out its values.
+The position value will be 6 at this point.
+
+=item Relative Fetching Equivalents
+
+When using OCI_FETCH_RELATIVE with a fetch offset equal to 0 will get the
current row which is the same as a OCI_FETCH_CURRENT fetch.
+When using OCI_FETCH_RELATIVE with a fetch offset equal to 1 will get the next
row which is the same as a OCI_FETCH_NEXT fetch.
+When using OCI_FETCH_RELATIVE with a fetch offset equal to -1 will get the
prior row which is the same as a OCI_FETCH_PRIOR fetch.
+
+=item Use Finish
+
+ $sth->finish();
+
+When using scrollable cursors it is required that you use the $sth->finish()
method when you are done with the cursor as this type of
+cursor has to be explicitly canceled on the server. If you do not do this you
may cause resource problems on your database.
+
+=head2 Prefetching Rows
+
+One can override the DBD::Oracle's default pre-fetch values by using the DBI
database handle attribute C<RowCacheSize> and or the
+Prepare Attribute 'ora_prefetch_memory'. Tweaking these values may yield
improved performance.
+
+ $dbh->{RowCacheSize} = 10;
+
$sth=$dbh->prepare($sql,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY,ora_prefetch_memory=>10000});
+
+In the above example 10 rows will be prefetched up to a maximum of 10000 bytes
of data. A good RowCacheSize value for a scrollable cursor
+is about 20% of expected size of the record set. If the ora_prefetch_memory is
0 or not present then memory size is not included
+in computing the number of rows to prefetch otherwise the number of rows will
be limited to memory size.
+
-This function retrives
=head1 Handling LOBs
=head2 Simple Usage
Modified: dbd-oracle/branches/scroll/dbdimp.c
==============================================================================
--- dbd-oracle/branches/scroll/dbdimp.c (original)
+++ dbd-oracle/branches/scroll/dbdimp.c Wed Mar 19 08:07:58 2008
@@ -906,9 +906,6 @@
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);
}
@@ -960,9 +957,6 @@
else if (kl==12 && strEQ(key, "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);
}
@@ -3410,7 +3404,7 @@
PerlIO_printf(DBIc_LOGPIO(imp_sth), " dbd_st_finish\n");
if (!DBIc_ACTIVE(imp_sth))
- return 1;
+ return 1;
/* Cancel further fetches from this cursor. */
/* We don't close the cursor till DESTROY (dbd_st_destroy). */
@@ -3425,16 +3419,22 @@
}
if (dirty) /* don't walk on the wild side */
- return 1;
+ return 1;
if (!DBIc_ACTIVE(imp_dbh)) /* no longer connected */
- return 1;
+ return 1;
+ /*fetching on a cursor with row =0 will explicitly free any
+ server side resources this is what the next statment does,
+ not sure if we need this for non scrolling cursors they should die on
+ a OER(1403) no records)*/
+
OCIStmtFetch_log_stat(imp_sth->stmhp, imp_sth->errhp, 0,
OCI_FETCH_NEXT,0, status);
+
if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO) {
- oci_error(sth, imp_sth->errhp, status, "Finish OCIStmtFetch");
- return 0;
+ oci_error(sth, imp_sth->errhp, status, "Finish OCIStmtFetch");
+ return 0;
}
return 1;
}
Modified: dbd-oracle/branches/scroll/dbdimp.h
==============================================================================
--- dbd-oracle/branches/scroll/dbdimp.h (original)
+++ dbd-oracle/branches/scroll/dbdimp.h Wed Mar 19 08:07:58 2008
@@ -46,7 +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)
@@ -106,6 +106,7 @@
int fetch_orient;
int fetch_offset;
int fetch_position;
+ int prefetch_memory; /* OCI_PREFETCH_MEMORY*/
};
#define IMP_STH_EXECUTING 0x0001
Modified: dbd-oracle/branches/scroll/oci8.c
==============================================================================
--- dbd-oracle/branches/scroll/oci8.c (original)
+++ dbd-oracle/branches/scroll/oci8.c Wed Mar 19 08:07:58 2008
@@ -277,7 +277,6 @@
dTHX;
D_imp_dbh_from_sth;
sword status = 0;
- ub4 ora_exe_mode = OCI_DEFAULT; /* default is OCI_DEFAULT*/
ub4 oparse_lng = 1; /* auto v6 or v7 as suits db connected
to */
int ora_check_sql = 1; /* to force a describe to check SQL */
IV ora_placeholders = 1; /* find and handle placeholders */
@@ -307,7 +306,8 @@
}
imp_sth->auto_lob = 1;
-
+ imp_sth->exe_mode = OCI_DEFAULT;
+
if (attribs) {
SV **svp;
IV ora_auto_lob = 1;
@@ -317,9 +317,9 @@
imp_sth->auto_lob = (ora_auto_lob) ? 1 : 0;
/* ora_check_sql only works for selects owing to Oracle
behaviour */
DBD_ATTRIB_GET_IV( attribs, "ora_check_sql", 13, svp,
ora_check_sql);
- DBD_ATTRIB_GET_IV( attribs, "ora_exe_mode", 12, svp,
ora_exe_mode);
- imp_sth->exe_mode = ora_exe_mode;
-
+ DBD_ATTRIB_GET_IV( attribs, "ora_exe_mode", 12, svp,
imp_sth->exe_mode);
+ DBD_ATTRIB_GET_IV( attribs, "ora_prefetch_memory", 19, svp,
imp_sth->prefetch_memory);
+
}
@@ -1752,10 +1752,10 @@
else if (SvOK(imp_drh->ora_cache)) imp_sth->cache_rows =
SvIV(imp_drh->ora_cache);
- if (imp_dbh->RowCacheSize || imp_dbh->prefetch_memory){
+ if (imp_dbh->RowCacheSize || imp_sth->prefetch_memory){
/*user set values */
cache_rows =imp_dbh->RowCacheSize;
- cache_mem =imp_dbh->prefetch_memory;
+ cache_mem =imp_sth->prefetch_memory;
} else if (imp_sth->cache_rows >= 0) { /* set cache size by row count
*/
Modified: dbd-oracle/branches/scroll/t/51scroll.t
==============================================================================
--- dbd-oracle/branches/scroll/t/51scroll.t (original)
+++ dbd-oracle/branches/scroll/t/51scroll.t Wed Mar 19 08:07:58 2008
@@ -1,8 +1,8 @@
#!/usr/bin/perl
use strict;
-use Test::More tests => 34;
-use DBD::Oracle qw(:ora_types);
+use Test::More tests => 33;
+use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
use DBI;
unshift @INC ,'t';
@@ -27,7 +27,6 @@
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");
@@ -52,9 +51,8 @@
$sth->execute();
}
-
$sql="select * from ".$table;
-ok($sth=$dbh->prepare($sql,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY}));
+ok($sth=$dbh->prepare($sql,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY,ora_prefetch_memory=>200}));
ok ($sth->execute());
#first loop all the way forward with OCI_FETCH_NEXT
