Author: byterock
Date: Thu Mar 20 12:39:55 2008
New Revision: 10965
Modified:
dbd-oracle/trunk/Oracle.pm
Log:
changes to the pod from TIM B. Dealing with scrollable cursors
Modified: dbd-oracle/trunk/Oracle.pm
==============================================================================
--- dbd-oracle/trunk/Oracle.pm (original)
+++ dbd-oracle/trunk/Oracle.pm Thu Mar 20 12:39:55 2008
@@ -1465,7 +1465,7 @@
This will set the execute mode of the current statement. Presently only one
mode is supported;
- OCI_STMT_SCROLLABLE_READONLY = 'scrollable results sets'
+ OCI_STMT_SCROLLABLE_READONLY - 'scrollable results sets'
=item ora_prefetch_memory
@@ -1496,11 +1496,15 @@
Potentially useful values when DBD::Oracle was built using OCI 7 and later:
ORA_VARCHAR2, ORA_STRING, ORA_LONG, ORA_RAW, ORA_LONGRAW,
- ORA_CHAR, ORA_MLSLABEL, ORA_RSET
+ ORA_CHAR, ORA_MLSLABEL, ORA_RSET
Additional values when DBD::Oracle was built using OCI 8 and later:
ORA_CLOB, ORA_BLOB, ORA_NTY, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE
+
+Additional values when DBD::Oracle was built using OCI 10.2 and later:
+
+ SQLT_CHR, SQLT_BIN
See L</Binding Cursors> for the correct way to use ORA_RSET.
@@ -2486,7 +2490,7 @@
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);
+ use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes);
Which will import the following fetch orientation constants;
@@ -2519,7 +2523,7 @@
=item ora_fetch_scroll
- my $value = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
+ @ary = $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.
@@ -2527,7 +2531,7 @@
=item ora_scroll_position
- my $position = $sth->ora_scroll_position();
+ $position = $sth->ora_scroll_position();
This method returns the current position in the result set.
@@ -2734,77 +2738,93 @@
=head2 Support for Remote Lobs;
-The data interface for Persistent LOBs also supports remote LOBs (access over
a dblink) however,
+The data interface for Persistent LOBs also supports remote LOBs (access over
a dblink).
+Given a database called lob_test that has a link defined like this
+
+ create database link link_test connect to test_lobs identified by tester
using 'lob_test';
+
+to a remote database called test_lobs, the following code will work;
+
+ $dbh = DBI->connect('dbi:Oracle:','[EMAIL PROTECTED]','test');
+ $dbh->{LongReadLen} = 2*1024*1024; #2 meg
+ $sth=$dbh->prepare($sql,{ora_pers_lob=>1});
+ $sql='select p_id,lob_1,lob_2,blob_2 from [EMAIL PROTECTED]';
+ $sth=$dbh->prepare($sql,{ora_pers_lob=>1,ora_check_sql=>0});
+ $sth->execute();
+ while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
+ print "p_id=".$p_id."\n";
+ print "clob1=".$clob1."\n";
+ print "clob2=".$clob2."\n";
+ print "blob1=".$blob2."\n";
+ print "blob2=".$blob2."\n";
+ }
+
+Below are the limitations of Remote Lobs;
=over 4
=item Queries involving more than one database are not supported;
+so the following return an error:
+
+ SELECT t1.lobcol, a2.lobcol FROM t1, [EMAIL PROTECTED] a2 WHERE
+ LENGTH(t1.lobcol) = LENGTH(a2.lobcol);
+
+or
+
+ SELECT t1.lobcol FROM [EMAIL PROTECTED]
+ UNION ALL
+ SELECT t2.lobcol FROM [EMAIL PROTECTED];
+
=item DDL commands are not supported;
so the following returns an error:
- CREATE VIEW v AS SELECT lob_col FROM [EMAIL PROTECTED];
-
-=item Only binds and defines for data going into remote persistent LOBs are
supported so that parameter passing in PL/SQL where CHAR data is bound or
defined for remote LOBs is not allowed.
+ CREATE VIEW v AS SELECT lob_col FROM [EMAIL PROTECTED];
+
+=item Only binds and defines for data going into remote persistent LOBs are
supported So that parameter passing in PL/SQL where CHAR data is bound or
defined for remote LOBs is not allowed .
These statements all produce errors:
SELECT foo() FROM [EMAIL PROTECTED];
-
+
SELECT foo()@dbs INTO char_val FROM DUAL;
-
+
SELECT XMLType().getclobval FROM [EMAIL PROTECTED];
=item If the remote object is a view such as
+so the following would not work:
+
CREATE VIEW v AS SELECT foo() FROM ...
/* The local database then tries to get the CLOB data and has an error */
SELECT * FROM [EMAIL PROTECTED];
- RETURNING INTO does not support implicit conversions between CHAR and CLOB.
-
-=item PL/SQL parameter passing is not allowed where the actual argument is a
LOB type and the remote argument is a VARCHAR2, NVARCHAR2, CHAR, NCHAR, or RAW.
-
-Given a database called lob_test that has a link defined like this
-
- create database link link_test connect to test_lobs identified by tester
using 'lob_test';
+=item PL/SQL parameter passing is not allowed where the actual argument is a
LOB type and the remote argument is one of VARCHAR2, NVARCHAR2, CHAR, NCHAR, or
RAW.
-to a remote database called test_lobs the following code will work;
-
- $dbh = DBI->connect('dbi:Oracle:','[EMAIL PROTECTED]','test');
- $dbh->{LongReadLen} = 2*1024*1024; #2 meg
- $sth=$dbh->prepare($sql,{ora_pers_lob=>1});
- $sql='select p_id,lob_1,lob_2,blob_2 from [EMAIL PROTECTED]';
- $sth=$dbh->prepare($sql,{ora_pers_lob=>1,ora_check_sql=>0});
- $sth->execute();
- while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "clob1=".$clob1."\n";
- print "clob2=".$clob2."\n";
- print "blob1=".$blob2."\n";
- print "blob2=".$blob2."\n";
- }
+=item RETURNING INTO does not support implicit conversions between CHAR and
CLOB.
+
+so the following returns an error:
-=head 2 Caviats
+ SELECT t1.lobcol as test, a2.lobcol FROM t1, [EMAIL PROTECTED] a2 RETURNING
test
+
+=head2 Caviats
-So far this is relitivly new tecknology and this is a first attmept to
incorporate it into DBD::ORCALE so
+So far this is relatively new technology and this is a first attempt to
incorporate it into DBD::ORCALE so
please report any problems you may have with it.
-As well there seems to be some issues with retreiving LOBs that have been
truncated, weather this is an Oracle version/patch issue or
-a DBI/DBD::Oracle issue has yet to be determined. If you encouter "ORA-24345 A
Truncation or null fetch error occurred" erorr try
+As well there seems to be some issues with retrieving LOBs that have been
truncated, weather this is an Oracle version/patch issue or
+a DBI/DBD::Oracle issue has yet to be determined. If you encounter "ORA-24345
A Truncation or null fetch error occurred" error try
increasing the size of LongReadLen to see if it fixes the problems.
-Not all of the inteface has beem implemented yet, the following are not
supported yet;
-
-=over 4
+Not all of the interface has been implemented yet, the following are not
supported yet;
=item Piecewise, and callback binds for INSERT and UPDATE operations.
-and
-
=item Array binds for INSERT and UPDATE operations.
+=item Raw and LONG RAW datatypes.
+
=head1 Handling LOBs
=head2 Simple Usage
