[svn:dbd-oracle] r15370 - in dbd-oracle/trunk: . lib/DBD lib/DBD/Oracle lib/DBD/Oracle/Troubleshooting t
[email protected] Fri, 17 Aug 2012 08:35:56 -0700 (PDT)
| Newsgroups | perl.dbd.oracle.changes |
|---|---|
| Message-ID | <[email protected]> |
Author: yanick
Date: Fri Aug 17 08:35:55 2012
New Revision: 15370
Modified:
dbd-oracle/trunk/Changes
dbd-oracle/trunk/LICENSE
dbd-oracle/trunk/META.json
dbd-oracle/trunk/META.yml
dbd-oracle/trunk/README
dbd-oracle/trunk/README.mkdn
dbd-oracle/trunk/lib/DBD/Oracle.pm
dbd-oracle/trunk/lib/DBD/Oracle/GetInfo.pm
dbd-oracle/trunk/lib/DBD/Oracle/Object.pm
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting.pod
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Aix.pod
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Cygwin.pod
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Hpux.pm
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Linux.pod
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Macos.pod
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Sun.pod
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Vms.pod
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Win32.pod
dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Win64.pod
dbd-oracle/trunk/t/000-report-versions.t
dbd-oracle/trunk/t/10general.t
dbd-oracle/trunk/t/34pres_lobs.t
Log:
remove mention of Oraperl in tests (rel v1.50)
Modified: dbd-oracle/trunk/Changes
==============================================================================
--- dbd-oracle/trunk/Changes (original)
+++ dbd-oracle/trunk/Changes Fri Aug 17 08:35:55 2012
@@ -53,6 +53,9 @@
- added a few more simple TAF tests (Martin J. Evans)
+1.50 2012-08-15
+ - RT78965 - Remove Oraperl tests (which were forcing a require on Oraperl)
+
1.48 2012-08-09
- promote 1.47_00 to official release
Modified: dbd-oracle/trunk/LICENSE
==============================================================================
--- dbd-oracle/trunk/LICENSE (original)
+++ dbd-oracle/trunk/LICENSE Fri Aug 17 08:35:55 2012
@@ -18,15 +18,16 @@
The GNU General Public License, Version 1, February 1989
- GNU GENERAL PUBLIC LICENSE
- Version 1, February 1989
+ GNU GENERAL PUBLIC LICENSE
+ Version 1, February 1989
Copyright (C) 1989 Free Software Foundation, Inc.
- 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA
+ 51 Franklin St, Suite 500, Boston, MA 02110-1335 USA
+
Everyone is permitted to copy and distribute verbatim copies
of this license document, but changing it is not allowed.
- Preamble
+ Preamble
The license agreements of most software companies try to keep users
at the mercy of those companies. By contrast, our General Public
@@ -67,7 +68,7 @@
The precise terms and conditions for copying, distribution and
modification follow.
- GNU GENERAL PUBLIC LICENSE
+ GNU GENERAL PUBLIC LICENSE
TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION
0. This License Agreement applies to any program or other work which
@@ -185,7 +186,7 @@
of preserving the free status of all derivatives of our free software and
of promoting the sharing and reuse of software generally.
- NO WARRANTY
+ NO WARRANTY
9. BECAUSE THE PROGRAM IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY
FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN
@@ -207,9 +208,9 @@
PROGRAMS), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.
- END OF TERMS AND CONDITIONS
+ END OF TERMS AND CONDITIONS
- Appendix: How to Apply These Terms to Your New Programs
+ Appendix: How to Apply These Terms to Your New Programs
If you develop a new program, and you want it to be of the greatest
possible use to humanity, the best way to achieve this is to make it
@@ -235,8 +236,9 @@
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
- along with this program; if not, write to the Free Software Foundation,
- Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston MA 02110-1301 USA
+
Also add information on how to contact you by electronic and paper mail.
Modified: dbd-oracle/trunk/META.json
==============================================================================
--- dbd-oracle/trunk/META.json (original)
+++ dbd-oracle/trunk/META.json Fri Aug 17 08:35:55 2012
@@ -7,7 +7,7 @@
"Martin J. Evans <[email protected]>"
],
"dynamic_config" : 0,
- "generated_by" : "Dist::Zilla version 4.300016, CPAN::Meta::Converter version 2.120630",
+ "generated_by" : "Dist::Zilla version 4.300021, CPAN::Meta::Converter version 2.120921",
"license" : [
"perl_5"
],
@@ -40,7 +40,6 @@
"Devel::Peek" : "0",
"Encode" : "0",
"Math::BigInt" : "0",
- "Oraperl" : "0",
"Scalar::Util" : "0",
"Test::More" : "0.94",
"Thread::Semaphore" : "0",
@@ -52,30 +51,18 @@
"provides" : {
"DBD::Oracle" : {
"file" : "lib/DBD/Oracle.pm",
- "version" : "1.49_00"
+ "version" : "1.50"
},
"DBD::Oracle::GetInfo" : {
"file" : "lib/DBD/Oracle/GetInfo.pm",
- "version" : "1.49_00"
+ "version" : "1.50"
},
"DBD::Oracle::Object" : {
"file" : "lib/DBD/Oracle/Object.pm",
- "version" : "1.49_00"
- },
- "DBD::Oracle::db" : {
- "file" : "lib/DBD/Oracle.pm",
- "version" : "1.49_00"
- },
- "DBD::Oracle::dr" : {
- "file" : "lib/DBD/Oracle.pm",
- "version" : "1.49_00"
- },
- "DBD::Oracle::st" : {
- "file" : "lib/DBD/Oracle.pm",
- "version" : "1.49_00"
+ "version" : "1.50"
}
},
- "release_status" : "testing",
+ "release_status" : "stable",
"resources" : {
"bugtracker" : {
"mailto" : "bug-dbd-oracle at rt.cpan.org",
@@ -84,11 +71,11 @@
"homepage" : "http://search.cpan.org/dist/DBD-Oracle/",
"repository" : {
"type" : "git",
- "url" : "git://github.com/yanick/DBD-Oracle.git",
- "web" : "http://github.com/yanick/DBD-Oracle/tree"
+ "url" : "https://github.com/yanick/DBD-Oracle.git",
+ "web" : "https://github.com/yanick/DBD-Oracle"
}
},
- "version" : "1.49_00",
+ "version" : "1.50",
"x_authority" : "cpan:PYTHIAN",
"x_help_wanted" : [
"coder",
Modified: dbd-oracle/trunk/META.yml
==============================================================================
--- dbd-oracle/trunk/META.yml (original)
+++ dbd-oracle/trunk/META.yml Fri Aug 17 08:35:55 2012
@@ -12,7 +12,6 @@
Devel::Peek: 0
Encode: 0
Math::BigInt: 0
- Oraperl: 0
Scalar::Util: 0
Test::More: 0.94
Thread::Semaphore: 0
@@ -21,7 +20,7 @@
configure_requires:
ExtUtils::MakeMaker: 6.30
dynamic_config: 0
-generated_by: 'Dist::Zilla version 4.300016, CPAN::Meta::Converter version 2.120630'
+generated_by: 'Dist::Zilla version 4.300021, CPAN::Meta::Converter version 2.120921'
license: perl
meta-spec:
url: http://module-build.sourceforge.net/META-spec-v1.4.html
@@ -30,22 +29,13 @@
provides:
DBD::Oracle:
file: lib/DBD/Oracle.pm
- version: 1.49_00
+ version: 1.50
DBD::Oracle::GetInfo:
file: lib/DBD/Oracle/GetInfo.pm
- version: 1.49_00
+ version: 1.50
DBD::Oracle::Object:
file: lib/DBD/Oracle/Object.pm
- version: 1.49_00
- DBD::Oracle::db:
- file: lib/DBD/Oracle.pm
- version: 1.49_00
- DBD::Oracle::dr:
- file: lib/DBD/Oracle.pm
- version: 1.49_00
- DBD::Oracle::st:
- file: lib/DBD/Oracle.pm
- version: 1.49_00
+ version: 1.50
requires:
DBI: 0
DynaLoader: 0
@@ -56,8 +46,8 @@
resources:
bugtracker: http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Oracle
homepage: http://search.cpan.org/dist/DBD-Oracle/
- repository: git://github.com/yanick/DBD-Oracle.git
-version: 1.49_00
+ repository: https://github.com/yanick/DBD-Oracle.git
+version: 1.50
x_authority: cpan:PYTHIAN
x_help_wanted:
- coder
Modified: dbd-oracle/trunk/README
==============================================================================
--- dbd-oracle/trunk/README (original)
+++ dbd-oracle/trunk/README Fri Aug 17 08:35:55 2012
@@ -2,7 +2,7 @@
DBD::Oracle - Oracle database driver for the DBI module
VERSION
- version 1.49_00
+ version 1.50
SYNOPSIS
use DBI;
@@ -248,12 +248,16 @@
#set up TAF on the connection
# NOTE since DBD::Oracle uses call_pv you may need to pass a full
# name space as the function e.g., 'main::handle_taf'
- my $dbh = DBI->connect('dbi:Oracle:XE','hr','hr',{ora_taf=>1,ora_taf_sleep=>5,ora_taf_function=>'handle_taf'});
+ # NOTE from 1.49_00 ora_taf_function can accept a code ref as well
+ # as a sub name as it now uses call_sv
+ my $dbh = DBI->connect('dbi:Oracle:XE', 'hr', 'hr',
+ {ora_taf_function => 'main::handle_taf'});
#create the perl TAF event function
sub handle_taf {
- my ($fo_event,$fo_type) = @_;
+ # NOTE from 1.49_00 the $dbh handle was passed to your callback
+ my ($fo_event,$fo_type, $dbh) = @_;
if ($fo_event == OCI_FO_BEGIN){
print " Instance Unavailable Please stand by!! \n";
@@ -276,9 +280,8 @@
print " Failed over user. Resuming services\n";
}
elsif ($fo_event == OCI_FO_ERROR){
- print " Failover error Sleeping...\n";
- # DBD::Oracle will sleep for ora_taf_sleep if you return OCI_FO_RETRY
- # If you want to stop retrying just return 0
+ print " Failover error ...\n";
+ sleep 5; # sleep before having another go
return OCI_FO_RETRY;
}
else {
@@ -367,38 +370,36 @@
variable.
ora_taf
- If your Oracle instance has been configured to use TAF events you can
- enable the TAF callback by setting this option to any *true* value.
-
- NOTE: All the ora_taf* attributes must currently be set in the connect
- method if you want TAF enabled at the moment i.e., after connect you can
- change the callback but you cannot disable TAF.
+ This attribute was removed in 1.49_00 as it was redundant. To enable TAF
+ simply set "ora_taf_function".
ora_taf_function
- The name of the Perl subroutine that will be called from OCI when a TAF
- event occurs. You must supply a perl function to use the callback and it
- will always receive two parameters, the failover event value and the
- failover type. Below is an example of a TAF function
+ If your Oracle instance has been configured to use TAF events you can
+ enable the TAF callback by setting this option.
+
+ The name of the Perl subroutine (or a code ref from 1.49_00) that will
+ be called from OCI when a TAF event occurs. You must supply a perl
+ function to use the callback and it will always receive at least two
+ parameters; the failover event value and the failover type. From 1.49_00
+ the dbh is passed as the third argument. Below is an example of a TAF
+ function
sub taf_event{
- my ($event, $type) = @_;
+ # NOTE from 1.49_00 the $dbh handle is passed to the callback
+ my ($event, $type, $dbh) = @_;
print "My TAF event=$event\n";
print "My TAF type=$type\n";
return;
}
- Note you'll probably have to use the full name space when setting the
- TAF function e.g., 'main::my_taf_function' and not just
- 'my_taf_function'.
+ Note if passing a sub name you will probably have to use the full name
+ space when setting the TAF function e.g., 'main::my_taf_function' and
+ not just 'my_taf_function'.
ora_taf_sleep
- The amount of time in seconds DBD::Oracle will sleep between attempting
- successive failover events when the event is OCI_FO_ERROR and
- OCI_FO_RETRY is returned from the TAF handler.
-
- NOTE: This attribute will be withdrawn in the future so I suggest you
- don't use it and if you want to sleep, add it to your own callback sub.
+ This attribute was removed in 1.49_00 as it was redundant. If you want
+ to sleep between retries simple add a sleep to your callback sub.
ora_session_mode
The ora_session_mode attribute can be used to connect with SYSDBA,
@@ -1007,7 +1008,7 @@
or BLOB column in most circumstances. If false, fetching retrieves
the Oracle "LOB Locator" of the CLOB or BLOB value.
- See "LOBs and LONGs" for more details.
+ See "LOBS AND LONGS" for more details.
See also the LOB tests in 05dbi.t of Oracle::OCI for examples of how
to use LOB Locators.
@@ -1043,7 +1044,7 @@
OCI_STMT_SCROLLABLE_READONLY - make result set scrollable
- See "Scrollable Cursors" for more details.
+ See "SCROLLABLE CURSORS" for more details.
ora_prefetch_rows
Sets the number of rows to be prefetched. If it is not set, then the
@@ -1399,8 +1400,8 @@
$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. The Oracle® Call Interface Programmer's Guide, suggests
- a good row cache value for a scrollable cursor is about 20% of expected
+ bytes of data. The Oracle® Call Interface Programmer's Guide, suggests a
+ good row cache value for a scrollable cursor is about 20% of expected
size of the record set.
The prefetch settings tell the DBD::Oracle to grab x rows (or x-bytes)
@@ -1547,7 +1548,7 @@
See "Binding Cursors" for the correct way to use ORA_RSET.
- See "LOBs and LONGs" for how to use ORA_CLOB and ORA_BLOB.
+ See "LOBS AND LONGS" for how to use ORA_CLOB and ORA_BLOB.
See "SYS.DBMS_SQL datatypes" for ORA_VARCHAR2_TABLE,
ORA_NUMBER_TABLE.
@@ -1555,7 +1556,7 @@
See "Data Interface for Persistent LOBs" for the correct way to use
SQLT_CHR and SQLT_BIN.
- See "Other Data Types" for more information.
+ See "OTHER DATA TYPES" for more information.
See also "Placeholders and Bind Values" in DBI.
@@ -4241,3 +4242,11 @@
This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.
+POD ERRORS
+ Hey! The above document had some coding errors, which are explained
+ below:
+
+ Around line 2655:
+ Non-ASCII character seen before =encoding in 'Oracle®'. Assuming
+ UTF-8
+
Modified: dbd-oracle/trunk/README.mkdn
==============================================================================
--- dbd-oracle/trunk/README.mkdn (original)
+++ dbd-oracle/trunk/README.mkdn Fri Aug 17 08:35:55 2012
@@ -4,20 +4,20 @@
# VERSION
-version 1.49_00
+version 1.50
# SYNOPSIS
- use DBI;
+ use DBI;
- $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);
+ $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);
- $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $passwd);
+ $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $passwd);
- # See the DBI module documentation for full details
+ # See the DBI module documentation for full details
- # for some advanced uses you may need Oracle type values:
- use DBD::Oracle qw(:ora_types);
+ # for some advanced uses you may need Oracle type values:
+ use DBD::Oracle qw(:ora_types);
# DESCRIPTION
@@ -30,66 +30,62 @@
# CONSTANTS
-- :ora_session_modes
-
-ORA_SYSDBA ORA_SYSOPER ORA_SYSASM
-
-- :ora_types
+- :ora\_session\_modes
- ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
- ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB
- ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI
- SQLT_CHR SQLT_BIN
+ORA\_SYSDBA ORA\_SYSOPER ORA\_SYSASM
-- SQLCS_IMPLICIT
+- :ora\_types
-- SQLCS_NCHAR
+ ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
+ ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB
+ ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI
+ SQLT_CHR SQLT_BIN
+- SQLCS\_IMPLICIT
+- SQLCS\_NCHAR
-SQLCS_IMPLICIT and SQLCS_NCHAR are _character set form_ values.
+SQLCS\_IMPLICIT and SQLCS\_NCHAR are _character set form_ values.
See notes about Unicode elsewhere in this document.
-- SQLT_INT
-
-- SQLT_FLT
+- SQLT\_INT
+- SQLT\_FLT
These types are used only internally, and may be specified as internal
-bind type for ORA_NUMBER_TABLE. See notes about ORA_NUMBER_TABLE elsewhere
+bind type for ORA\_NUMBER\_TABLE. See notes about ORA\_NUMBER\_TABLE elsewhere
in this document
-- ORA_OCI
+- ORA\_OCI
Oracle doesn't provide a formal API for determining the exact version
number of the OCI client library used, so DBD::Oracle has to go digging
-(and sometimes has to more or less guess). The ORA_OCI constant
+(and sometimes has to more or less guess). The ORA\_OCI constant
holds the result of that process.
-In string context ORA_OCI returns the full "A.B.C.D" version string.
+In string context ORA\_OCI returns the full "A.B.C.D" version string.
-In numeric context ORA_OCI returns the major.minor version number
+In numeric context ORA\_OCI returns the major.minor version number
(8.1, 9.2, 10.0 etc). But note that version numbers are not actually
floating point and so if Oracle ever makes a release that has a two
digit minor version, such as `9.10` it will have a lower numeric
value than the preceding `9.9` release. So use with care.
-The contents and format of ORA_OCI are subject to change (it may,
+The contents and format of ORA\_OCI are subject to change (it may,
for example, become a _version object_ in later releases).
I recommend that you avoid checking for exact values.
-- :ora_fetch_orient
+- :ora\_fetch\_orient
- OCI_FETCH_CURRENT OCI_FETCH_NEXT OCI_FETCH_FIRST OCI_FETCH_LAST
- OCI_FETCH_PRIOR OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVE
+ OCI_FETCH_CURRENT OCI_FETCH_NEXT OCI_FETCH_FIRST OCI_FETCH_LAST
+ OCI_FETCH_PRIOR OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVE
These constants are used to set the orientation of a fetch on a scrollable cursor.
-- :ora_exe_modes
-
- OCI_STMT_SCROLLABLE_READONLY
+- :ora\_exe\_modes
-- :ora_fail_over
+ OCI_STMT_SCROLLABLE_READONLY
+- :ora\_fail\_over
- OCI_FO_END OCI_FO_ABORT OCI_FO_REAUTH OCI_FO_BEGIN OCI_FO_ERROR
- OCI_FO_NONE OCI_FO_SESSION OCI_FO_SELECT OCI_FO_TXNAL OCI_FO_RETRY
+ OCI_FO_END OCI_FO_ABORT OCI_FO_REAUTH OCI_FO_BEGIN OCI_FO_ERROR
+ OCI_FO_NONE OCI_FO_SESSION OCI_FO_SELECT OCI_FO_TXNAL OCI_FO_RETRY
# DBI CLASS METHODS
@@ -112,30 +108,30 @@
- 3
To connect without TNSNAMES.ORA file, you can use an EZCONNECT url, of the form:
-//host[:port][/service_name]
+//host\[:port\]\[/service\_name\]
If port name is not specified, 1521 is the default. If service name is not specified, the hostname will be used as a service name.
The following examples show several ways a connection can be created:
- $dbh = DBI->connect('dbi:Oracle:DB','username','password');
+ $dbh = DBI->connect('dbi:Oracle:DB','username','password');
- $dbh = DBI->connect('dbi:Oracle:DB','username/password','');
+ $dbh = DBI->connect('dbi:Oracle:DB','username/password','');
- $dbh = DBI->connect('dbi:Oracle:','username@DB','password');
+ $dbh = DBI->connect('dbi:Oracle:','username@DB','password');
- $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=DB;port=1521', 'scott/tiger', '');
+ $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=DB;port=1521', 'scott/tiger', '');
- $dbh = DBI->connect("dbi:Oracle://myhost:1522/ORCL",'username', 'password');
+ $dbh = DBI->connect("dbi:Oracle://myhost:1522/ORCL",'username', 'password');
### OS authentication
To connect to a local database with a user which has been set up to
authenticate via the OS ("ALTER USER username IDENTIFIED EXTERNALLY"):
- $dbh = DBI->connect('dbi:Oracle:','/','');
+ $dbh = DBI->connect('dbi:Oracle:','/','');
-Note the lack of a connection name (use the ORACLE_SID environment
+Note the lack of a connection name (use the ORACLE\_SID environment
variable). If an explicit SID is used you will probably get an ORA-01004 error.
That only works for local databases. (Authentication to remote Oracle
@@ -144,15 +140,15 @@
### Oracle Environment Variables
-To use DBD::ORACLE to connect to an Oracle database, ORACLE_HOME environment variable should be set correctly.
-In general, the value used should match the version of Oracle that was used to build DBD::Oracle. If using dynamic linking then ORACLE_HOME should match the version of Oracle that will be used to load in the Oracle client libraries (via LD_LIBRARY_PATH, ldconfig, or similar on Unix).
+To use DBD::ORACLE to connect to an Oracle database, ORACLE\_HOME environment variable should be set correctly.
+In general, the value used should match the version of Oracle that was used to build DBD::Oracle. If using dynamic linking then ORACLE\_HOME should match the version of Oracle that will be used to load in the Oracle client libraries (via LD\_LIBRARY\_PATH, ldconfig, or similar on Unix).
-Oracle can use two environment variables to specify default connections: ORACLE_SID and TWO_TASK.
+Oracle can use two environment variables to specify default connections: ORACLE\_SID and TWO\_TASK.
To use them, specify either a local SID or service name, or a service name that is specified in the TNSNAMES.ORA file.
-Note that if you have *both* local and remote databases, and you have ORACLE_SID *and* TWO_TASK set, and you don't specify a fully
-qualified connect string on the command line, TWO_TASK takes precedence over ORACLE_SID (i.e. you get connected to remote system).
+Note that if you have \*both\* local and remote databases, and you have ORACLE\_SID \*and\* TWO\_TASK set, and you don't specify a fully
+qualified connect string on the command line, TWO\_TASK takes precedence over ORACLE\_SID (i.e. you get connected to remote system).
It is highly recommended not to rely on environment variables and to always explicitly specify the SID in the connection string. This can prevent serious mistakes such as dropping a schema in the wrong database, and generally makes debugging and troubleshooting easier.
@@ -160,7 +156,7 @@
### Timezones
-If the query is run through SQL*Net (mostly queries that are executed on remote servers), Oracle will return the time zone based on the setting of the UNIX environment variable "TZ" for the user who started the listener.
+If the query is run through SQL\*Net (mostly queries that are executed on remote servers), Oracle will return the time zone based on the setting of the UNIX environment variable "TZ" for the user who started the listener.
If the query is run locally, Oracle will return the time zone based on the "TZ" environment variable setting of the user running
the query.
@@ -173,29 +169,29 @@
DBD::Oracle supports DRCP (Database Resident Connection Pool) so
if you have an 11.2 database and DRCP is enabled you can direct
all of your connections to it by adding ':POOLED' to the SID or
-setting a connection attribute of ora_drcp, or set the SERVER=POOLED
+setting a connection attribute of ora\_drcp, or set the SERVER=POOLED
when using a TNSENTRY style connection or even by setting an
-environment variable ORA_DRCP. All of which are demonstrated below;
+environment variable ORA\_DRCP. All of which are demonstrated below;
- $dbh = DBI->connect('dbi:Oracle:DB:POOLED','username','password')
+ $dbh = DBI->connect('dbi:Oracle:DB:POOLED','username','password')
- $dbh = DBI->connect('dbi:Oracle:','username@DB:POOLED','password')
+ $dbh = DBI->connect('dbi:Oracle:','username@DB:POOLED','password')
- $dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1})
+ $dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1})
- $dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1,
- ora_drcp_class=>'my_app',
- ora_drcp_min =>10})
+ $dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1,
+ ora_drcp_class=>'my_app',
+ ora_drcp_min =>10})
- $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521;SERVER=POOLED', 'scott/tiger', '')
+ $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521;SERVER=POOLED', 'scott/tiger', '')
- $dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
- (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
- (CONNECT_DATA=(SID=ORCL)(SERVER=POOLED)))}, "")
+ $dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
+ (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
+ (CONNECT_DATA=(SID=ORCL)(SERVER=POOLED)))}, "")
- if the ORA_DRCP environment variable is set then just this
+ if the ORA_DRCP environment variable is set then just this
- $dbh = DBI->connect('dbi:Oracle:DB','username','password')
+ $dbh = DBI->connect('dbi:Oracle:DB','username','password')
You can find a white paper on setting up DRCP and its advantages at [http://www.oracle.com/technetwork/articles/oracledrcp11g-1-133381.pdf](http://www.oracle.com/technetwork/articles/oracledrcp11g-1-133381.pdf).
@@ -217,7 +213,7 @@
callback. You can test your instance to see if you can use TAF
callback with
- $dbh->ora_can_taf();
+ $dbh->ora_can_taf();
If you try to set up a callback without it being enabled DBD::Oracle
will croak.
@@ -230,13 +226,13 @@
example:
The TNS entry for the instance has had the following added to the
-CONNECT_DATA section
+CONNECT\_DATA section
- (FAILOVER_MODE=
- (TYPE=select)
- (METHOD=basic)
- (RETRIES=10)
- (DELAY=10))
+ (FAILOVER_MODE=
+ (TYPE=select)
+ (METHOD=basic)
+ (RETRIES=10)
+ (DELAY=10))
You will also have to create your own perl function that will be
called from the client. You can name it anything you want and it will
@@ -245,19 +241,23 @@
error and the OCI client will sleep for the specified seconds before it
attempts another event.
- use DBD::Oracle(qw(:ora_fail_over));
- #import the ora fail over constants
+ use DBD::Oracle(qw(:ora_fail_over));
+ #import the ora fail over constants
- #set up TAF on the connection
- # NOTE since DBD::Oracle uses call_pv you may need to pass a full
- # name space as the function e.g., 'main::handle_taf'
- my $dbh = DBI->connect('dbi:Oracle:XE','hr','hr',{ora_taf=>1,ora_taf_sleep=>5,ora_taf_function=>'handle_taf'});
-
- #create the perl TAF event function
-
- sub handle_taf {
- my ($fo_event,$fo_type) = @_;
- if ($fo_event == OCI_FO_BEGIN){
+ #set up TAF on the connection
+ # NOTE since DBD::Oracle uses call_pv you may need to pass a full
+ # name space as the function e.g., 'main::handle_taf'
+ # NOTE from 1.49_00 ora_taf_function can accept a code ref as well
+ # as a sub name as it now uses call_sv
+ my $dbh = DBI->connect('dbi:Oracle:XE', 'hr', 'hr',
+ {ora_taf_function => 'main::handle_taf'});
+
+ #create the perl TAF event function
+
+ sub handle_taf {
+ # NOTE from 1.49_00 the $dbh handle was passed to your callback
+ my ($fo_event,$fo_type, $dbh) = @_;
+ if ($fo_event == OCI_FO_BEGIN){
print " Instance Unavailable Please stand by!! \n";
printf(" Your TAF type is %s \n",
@@ -279,36 +279,35 @@
print " Failed over user. Resuming services\n";
}
elsif ($fo_event == OCI_FO_ERROR){
- print " Failover error Sleeping...\n";
- # DBD::Oracle will sleep for ora_taf_sleep if you return OCI_FO_RETRY
- # If you want to stop retrying just return 0
+ print " Failover error ...\n";
+ sleep 5; # sleep before having another go
return OCI_FO_RETRY;
}
else {
printf(" Bad Failover Event: %d.\n", $fo_event);
+ }
+ return 0;
}
- return 0;
- }
The TAF types are as follows
- OCI_FO_SESSION indicates the user has requested only session failover.
- OCI_FO_SELECT indicates the user has requested select failover.
- OCI_FO_NONE indicates the user has not requested a failover type.
- OCI_FO_TXNAL indicates the user has requested a transaction failover.
+ OCI_FO_SESSION indicates the user has requested only session failover.
+ OCI_FO_SELECT indicates the user has requested select failover.
+ OCI_FO_NONE indicates the user has not requested a failover type.
+ OCI_FO_TXNAL indicates the user has requested a transaction failover.
The TAF events are as follows
- OCI_FO_BEGIN indicates that failover has detected a lost connection and failover is starting.
- OCI_FO_END indicates successful completion of failover.
- OCI_FO_ABORT indicates that failover was unsuccessful, and there is no option of retrying.
- OCI_FO_ERROR also indicates that failover was unsuccessful, but it gives the application the opportunity to handle the error and retry failover.
- OCI_FO_REAUTH indicates that you have multiple authentication handles and failover has occurred after the original authentication. It indicates that a user handle has been re-authenticated. To find out which, the application checks the OCI_ATTR_SESSION attribute of the service context handle (which is the first parameter).
+ OCI_FO_BEGIN indicates that failover has detected a lost connection and failover is starting.
+ OCI_FO_END indicates successful completion of failover.
+ OCI_FO_ABORT indicates that failover was unsuccessful, and there is no option of retrying.
+ OCI_FO_ERROR also indicates that failover was unsuccessful, but it gives the application the opportunity to handle the error and retry failover.
+ OCI_FO_REAUTH indicates that you have multiple authentication handles and failover has occurred after the original authentication. It indicates that a user handle has been re-authenticated. To find out which, the application checks the OCI_ATTR_SESSION attribute of the service context handle (which is the first parameter).
### Connect Attributes
-#### ora_ncs_buff_mtpl
+#### ora\_ncs\_buff\_mtpl
You can customize the size of the buffer when selecting LOBs with
the built-in AUTO Lob. The default value is 4 which is probably
@@ -320,27 +319,27 @@
environment variable in which case it sets the value at the connect
stage.
-#### ora_drcp
+#### ora\_drcp
For Oracle 11.2 or greater.
Set to _1_ to enable DRCP. Can also be set via the `ORA_DRCP` environment variable.
-#### ora_drcp_class
+#### ora\_drcp\_class
-If you are using DRCP, you can set a CONNECTION_CLASS for your pools
+If you are using DRCP, you can set a CONNECTION\_CLASS for your pools
as well. As sessions from a DRCP cannot be shared by users, you can
use this setting to identify the same user across different
applications. OCI will ensure that sessions belonging to a 'class' are
not shared outside the class'.
-The values for ora_drcp_class cannot contain a '*' and must be less
+The values for ora\_drcp\_class cannot contain a '\*' and must be less
than 1024 characters.
This value can be also be specified with the `ORA_DRCP_CLASS`
environment variable.
-#### ora_drcp_min
+#### ora\_drcp\_min
This optional value specifies the minimum number of sessions that are
initially opened. New sessions are only opened after this value has
@@ -354,140 +353,137 @@
This value can also be specified with the `ORA_DRCP_MIN` environment
variable.
-#### ora_drcp_max
+#### ora\_drcp\_max
This optional value specifies the maximum number of sessions that can
be open at one time. Once reached no more sessions can be opened
until one becomes free. The default value is 40 and any value above 1
-is valid. You should not set this value lower than ora_drcp_min as
+is valid. You should not set this value lower than ora\_drcp\_min as
that will just waste resources.
This value can also be specified with the `ORA_DRCP_MAX` environment
variable.
-#### ora_drcp_incr
+#### ora\_drcp\_incr
This optional value specifies the next increment for sessions to be
started if the current number of sessions are less than
-ora_drcp_max. The default value is 2 and any value above 0 is
-valid as long as the value of ora_drcp_min + ora_drcp_incr is not
-greater than ora_drcp_max.
+ora\_drcp\_max. The default value is 2 and any value above 0 is
+valid as long as the value of ora\_drcp\_min + ora\_drcp\_incr is not
+greater than ora\_drcp\_max.
This value can also be specified with the `ORA_DRCP_INCR` environment
variable.
-#### ora_taf
+#### ora\_taf
-If your Oracle instance has been configured to use TAF events you can
-enable the TAF callback by setting this option to any _true_ value.
-
-NOTE: All the ora_taf* attributes must currently be set in the connect
-method if you want TAF enabled at the moment i.e., after connect you
-can change the callback but you cannot disable TAF.
-
-#### ora_taf_function
+This attribute was removed in 1.49\_00 as it was redundant. To
+enable TAF simply set ["ora\_taf\_function"](#ora\_taf\_function).
-The name of the Perl subroutine that will be called from OCI when a
-TAF event occurs. You must supply a perl function to use the callback
-and it will always receive two parameters, the failover event value
-and the failover type. Below is an example of a TAF function
+#### ora\_taf\_function
- sub taf_event{
- my ($event, $type) = @_;
-
- print "My TAF event=$event\n";
- print "My TAF type=$type\n";
- return;
- }
+If your Oracle instance has been configured to use TAF events you can
+enable the TAF callback by setting this option.
-Note you'll probably have to use the full name space when setting the
-TAF function e.g., 'main::my_taf_function' and not just
-'my_taf_function'.
+The name of the Perl subroutine (or a code ref from 1.49\_00) that will
+be called from OCI when a TAF event occurs. You must supply a perl
+function to use the callback and it will always receive at least two
+parameters; the failover event value and the failover type. From
+1.49\_00 the dbh is passed as the third argument. Below is an example
+of a TAF function
+
+ sub taf_event{
+ # NOTE from 1.49_00 the $dbh handle is passed to the callback
+ my ($event, $type, $dbh) = @_;
+
+ print "My TAF event=$event\n";
+ print "My TAF type=$type\n";
+ return;
+ }
-#### ora_taf_sleep
+Note if passing a sub name you will probably have to use the full name
+space when setting the TAF function e.g., 'main::my\_taf\_function' and
+not just 'my\_taf\_function'.
-The amount of time in seconds DBD::Oracle will sleep between attempting
-successive failover events when the event is OCI_FO_ERROR and OCI_FO_RETRY
-is returned from the TAF handler.
+#### ora\_taf\_sleep
-NOTE: This attribute will be withdrawn in the future so I suggest you
-don't use it and if you want to sleep, add it to your own callback
-sub.
+This attribute was removed in 1.49\_00 as it was redundant. If you want
+to sleep between retries simple add a sleep to your callback sub.
-#### ora_session_mode
+#### ora\_session\_mode
-The ora_session_mode attribute can be used to connect with SYSDBA,
-SYSOPER and ORA_SYSASM authorization.
-The ORA_SYSDBA, ORA_SYSOPER and ORA_SYSASM constants can be imported using
+The ora\_session\_mode attribute can be used to connect with SYSDBA,
+SYSOPER and ORA\_SYSASM authorization.
+The ORA\_SYSDBA, ORA\_SYSOPER and ORA\_SYSASM constants can be imported using
- use DBD::Oracle qw(:ora_session_modes);
+ use DBD::Oracle qw(:ora_session_modes);
-This is one case where setting ORACLE_SID may be useful since
-connecting as SYSDBA or SYSOPER via SQL*Net is frequently disabled
+This is one case where setting ORACLE\_SID may be useful since
+connecting as SYSDBA or SYSOPER via SQL\*Net is frequently disabled
for security reasons.
Example:
- $dsn = "dbi:Oracle:"; # no dbname here
- $ENV{ORACLE_SID} = "orcl"; # set ORACLE_SID as needed
- delete $ENV{TWO_TASK}; # make sure TWO_TASK isn't set
+ $dsn = "dbi:Oracle:"; # no dbname here
+ $ENV{ORACLE_SID} = "orcl"; # set ORACLE_SID as needed
+ delete $ENV{TWO_TASK}; # make sure TWO_TASK isn't set
- $dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA });
+ $dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA });
It has been reported that this only works if `$dsn` does not contain
-a SID so that Oracle then uses the value of ORACLE_SID (not
-TWO_TASK) environment variable to connect to a local instance. Also
+a SID so that Oracle then uses the value of ORACLE\_SID (not
+TWO\_TASK) environment variable to connect to a local instance. Also
the username and password should be empty, and the user executing the
script needs to be part of the dba group or osdba group.
-#### ora_oratab_orahome
+#### ora\_oratab\_orahome
-Passing a true value for the ora_oratab_orahome attribute will make
+Passing a true value for the ora\_oratab\_orahome attribute will make
DBD::Oracle change `$ENV{ORACLE_HOME}` to make the Oracle home directory
that specified in the `/etc/oratab` file _if_ the database to connect to
is specified as a SID that exists in the oratab file, and DBD::Oracle was
built to use the Oracle 7 OCI API (not Oracle 8+).
-#### ora_module_name
+#### ora\_module\_name
After connecting to the database the value of this attribute is passed
-to the SET_MODULE() function in the `DBMS_APPLICATION_INFO` PL/SQL
+to the SET\_MODULE() function in the `DBMS_APPLICATION_INFO` PL/SQL
package. This can be used to identify the application to the DBA for
monitoring and performance tuning purposes. For example:
- my $dbh = DBI->connect($dsn, $user, $passwd, { ora_module_name => $0 });
+ my $dbh = DBI->connect($dsn, $user, $passwd, { ora_module_name => $0 });
- $dbh->{ora_module_name} = $y;
+ $dbh->{ora_module_name} = $y;
The maximum size is 48 bytes.
NOTE: You will need an Oracle client 10.1 or later to use this.
-#### ora_driver_name
+#### ora\_driver\_name
For 11g and later you can now set the name of the driver layer using OCI.
Perl, Perl5, ApachePerl so on. Names starting with "ORA" are reserved. You
can enter up to 8 characters. If none is enter then this will default to
DBDOxxxx where xxxx is the current version number. This value can be
-retrieved on the server side using V$SESSION_CONNECT_INFO or
-GV$SESSION_CONNECT_INFO
+retrieved on the server side using V$SESSION\_CONNECT\_INFO or
+GV$SESSION\_CONNECT\_INFO
- my $dbh = DBI->connect($dsn, $user, $passwd, { ora_driver_name => 'ModPerl_1' });
+ my $dbh = DBI->connect($dsn, $user, $passwd, { ora_driver_name => 'ModPerl_1' });
- $dbh->{ora_driver_name} = $q;
+ $dbh->{ora_driver_name} = $q;
-#### ora_client_info
+#### ora\_client\_info
Allows you to add any value (up to 64 bytes) to your session and it can be
retrieved on the server side from the `V$SESSION`a view.
- my $dbh = DBI->connect($dsn, $user, $passwd, { ora_client_info => 'Remote2' });
+ my $dbh = DBI->connect($dsn, $user, $passwd, { ora_client_info => 'Remote2' });
- $dbh->{ora_client_info} = "Remote2";
+ $dbh->{ora_client_info} = "Remote2";
NOTE: You will need an Oracle client 10.1 or later to use this.
-#### ora_client_identifier
+#### ora\_client\_identifier
Allows you to specify the user identifier in the session handle.
@@ -498,93 +494,93 @@
':'. This value can be retrieved on the server side using `V$SESSION`
view.
- my $dbh = DBI->connect($dsn, $user, $passwd, { ora_client_identifier => $some_web_user });
+ my $dbh = DBI->connect($dsn, $user, $passwd, { ora_client_identifier => $some_web_user });
- $dbh->{ora_client_identifier} = $local_user;
+ $dbh->{ora_client_identifier} = $local_user;
-#### ora_action
+#### ora\_action
Allows you to specify any string up to 32 bytes which may be retrieved
on the server side using `V$SESSION` view.
- my $dbh = DBI->connect($dsn, $user, $passwd, { ora_action => "Login"});
+ my $dbh = DBI->connect($dsn, $user, $passwd, { ora_action => "Login"});
- $dbh->{ora_action} = "New Long Query 22";
+ $dbh->{ora_action} = "New Long Query 22";
NOTE: You will need an Oracle client 10.1 or later to use this.
-#### ora_dbh_share
+#### ora\_dbh\_share
Requires at least Perl 5.8.0 compiled with ithreads.
Allows you to share
database connections between threads. The first connect will make the
-connection, all following calls to connect with the same ora_dbh_share
+connection, all following calls to connect with the same ora\_dbh\_share
attribute will use the same database connection. The value must be a
reference to a already shared scalar which is initialized to an empty
string.
- our $orashr : shared = '' ;
+ our $orashr : shared = '' ;
- $dbh = DBI->connect ($dsn, $user, $passwd, {ora_dbh_share => \$orashr}) ;
+ $dbh = DBI->connect ($dsn, $user, $passwd, {ora_dbh_share => \$orashr}) ;
-#### ora_envhp
+#### ora\_envhp
The first time a connection is made a new OCI 'environment' is
created by DBD::Oracle and stored in the driver handle.
Subsequent connects reuse (share) that same OCI environment
by default.
-The ora_envhp attribute can be used to disable the reuse of the OCI
+The ora\_envhp attribute can be used to disable the reuse of the OCI
environment from a previous connect. If the value is `0` then
a new OCI environment is allocated and used for this connection.
The OCI environment holds information about the client side context,
such as the local NLS environment. By altering `%ENV` and setting
-ora_envhp to 0 you can create connections with different NLS
+ora\_envhp to 0 you can create connections with different NLS
settings. This is most useful for testing.
-#### ora_charset, ora_ncharset
+#### ora\_charset, ora\_ncharset
For oracle versions >= 9.2 you can specify the client charset and
-ncharset with the ora_charset and ora_ncharset attributes. You
+ncharset with the ora\_charset and ora\_ncharset attributes. You
still need to pass `ora_envhp = 0` for all but the first connect.
These attributes override the settings from environment variables.
- $dbh = DBI->connect ($dsn, $user, $passwd,
- {ora_charset => 'AL32UTF8'});
+ $dbh = DBI->connect ($dsn, $user, $passwd,
+ {ora_charset => 'AL32UTF8'});
-#### ora_verbose
+#### ora\_verbose
Use this value to enable DBD::Oracle only tracing. Simply either set
-the ora_verbose attribute on the connect() method to the trace level
+the ora\_verbose attribute on the connect() method to the trace level
you desire like this
- my $dbh = DBI->connect($dsn, "", "", {ora_verbose=>6});
+ my $dbh = DBI->connect($dsn, "", "", {ora_verbose=>6});
or set it directly on the DB handle like this;
- $dbh->{ora_verbose} =6;
+ $dbh->{ora_verbose} =6;
In both cases the DBD::Oracle trace level is set to 6, which is the highest
level tracing most of the calls to OCI.
-NOTE: In future versions of DBD::Oracle ora_verbose will be changed so
+NOTE: In future versions of DBD::Oracle ora\_verbose will be changed so
that it is simply a switch to turn DBI's DBD tracing on or off. A
true value will turn it on and a false value will turn it off. DBI's
-"DBD" tracing was not available when ora_verbose was created and
-ora_verbose adds an additional test to every trace test.
+"DBD" tracing was not available when ora\_verbose was created and
+ora\_verbose adds an additional test to every trace test.
-#### ora_oci_success_warn
+#### ora\_oci\_success\_warn
Use this value to print otherwise silent OCI warnings that may happen
when an execute or fetch returns "Success With Info" or when you want
to tune RowCaching and LOB Reads
- $dbh->{ora_oci_success_warn} = 1;
+ $dbh->{ora_oci_success_warn} = 1;
-#### ora_objects
+#### ora\_objects
Use this value to enable extended embedded oracle objects mode. In extended:
@@ -596,55 +592,55 @@
Determine object type for each instance. All object attributes are returned (not only super-type's attributes).
- $dbh->{ora_objects} = 1;
+ $dbh->{ora_objects} = 1;
-#### ora_ph_type
+#### ora\_ph\_type
The default placeholder datatype for the database session.
-The `TYPE` or [ora_type](#pod_ora_type) attributes to L<DBI/bind_param> and
-L<DBI/bind_param_inout> override the datatype for individual placeholders.
+The `TYPE` or ["ora\_type"](#ora\_type) attributes to ["bind\_param" in DBI](http://search.cpan.org/perldoc?DBI#bind\_param) and
+["bind\_param\_inout" in DBI](http://search.cpan.org/perldoc?DBI#bind\_param\_inout) override the datatype for individual placeholders.
The most frequent reason for using this attribute is to permit trailing spaces
in values passed by placeholders.
Constants for the values allowed for this attribute can be imported using
- use DBD::Oracle qw(:ora_types);
+ use DBD::Oracle qw(:ora_types);
Only the following values are permitted for this attribute.
-- ORA_VARCHAR2
+- ORA\_VARCHAR2
-Oracle clients using OCI 8 will strip trailing spaces and allow embedded \0 bytes.
-Oracle clients using OCI 9.2 do not strip trailing spaces and allow embedded \0 bytes.
+Oracle clients using OCI 8 will strip trailing spaces and allow embedded \\0 bytes.
+Oracle clients using OCI 9.2 do not strip trailing spaces and allow embedded \\0 bytes.
This is the normal default placeholder type.
-- ORA_STRING
+- ORA\_STRING
-Do not strip trailing spaces and end the string at the first \0.
+Do not strip trailing spaces and end the string at the first \\0.
-- ORA_CHAR
+- ORA\_CHAR
-Do not strip trailing spaces and allow embedded \0.
+Do not strip trailing spaces and allow embedded \\0.
Force 'blank-padded comparison semantics'.
For example:
- use DBD::Oracle qw(:ora_types);
+ use DBD::Oracle qw(:ora_types);
- $SQL="select username from all_users where username = ?";
- #username is a char(8)
- $sth=$dbh->prepare($SQL)";
- $sth->bind_param(1,'bloggs',{ ora_type => ORA_CHAR});
+ $SQL="select username from all_users where username = ?";
+ #username is a char(8)
+ $sth=$dbh->prepare($SQL)";
+ $sth->bind_param(1,'bloggs',{ ora_type => ORA_CHAR});
Will pad bloggs out to 8 characters and return the username.
-#### ora_parse_error_offset
+#### ora\_parse\_error\_offset
If the previous error was from a failed `prepare` due to a syntax error,
this attribute gives the offset into the `Statement` attribute where the
error was found.
-#### ora_array_chunk_size
+#### ora\_array\_chunk\_size
Due to OCI limitations, DBD::Oracle needs to buffer up rows of
bind values in its `execute_for_fetch` implementation. This attribute
@@ -658,7 +654,7 @@
Note that this attribute also applies to `execute_array`, since that
method is implemented using `execute_for_fetch`.
-#### ora_connect_with_default_signals
+#### ora\_connect\_with\_default\_signals
Sometimes the Oracle client seems to change some of the signal
handlers of the process during the connect phase. For instance, some
@@ -671,36 +667,36 @@
For example:
- $dbh = DBI->connect ($dsn, $user, $passwd,
- {ora_connect_with_default_signals => [ 'INT' ] });
+ $dbh = DBI->connect ($dsn, $user, $passwd,
+ {ora_connect_with_default_signals => [ 'INT' ] });
NOTE disabling the signal handlers the OCI library sets up may affect
functionality in the OCI library.
-NOTE If you are using connect_cached then the above example will lead
+NOTE If you are using connect\_cached then the above example will lead
to DBI thinking each connection is different as an anonymous array reference
-is being used. To avoid this when using connect_cached you are advised
+is being used. To avoid this when using connect\_cached you are advised
to use:
- my @ora_default_signals = (...);
- $dbh = DBI->connect($dsn, $user, $passwd,
- {ora_connect_with_default_signals => \@ora_default_signals});
+ my @ora_default_signals = (...);
+ $dbh = DBI->connect($dsn, $user, $passwd,
+ {ora_connect_with_default_signals => \@ora_default_signals});
In more recent Perl versions you could possibly make use of new state
variables.
-## __connect_cached__
+## __connect\_cached__
Implemented by DBI, no driver-specific impact.
-Please note that connect_cached as not been tested with DRCP.
+Please note that connect\_cached as not been tested with DRCP.
-## __data_sources__
+## __data\_sources__
- @data_sources = DBI->data_sources('Oracle');
- @data_sources = $dbh->data_sources();
+ @data_sources = DBI->data_sources('Oracle');
+ @data_sources = $dbh->data_sources();
-Returns a list of available databases. You will have to set either the 'ORACLE_HOME' or
-'TNS_ADMIN' environment value to retrieve this list. It will read these values from
+Returns a list of available databases. You will have to set either the 'ORACLE\_HOME' or
+'TNS\_ADMIN' environment value to retrieve this list. It will read these values from
TNSNAMES.ORA file entries.
# METHODS COMMON TO ALL HANDLES
@@ -712,19 +708,19 @@
## __err__
- $rv = $h->err;
+ $rv = $h->err;
Returns the error code from the last method called.
## __errstr__
- $str = $h->errstr;
+ $str = $h->errstr;
Returns the last error that was reported by Oracle. Starting with "ORA-00000" code followed by the error message.
## __state__
- $str = $h->state;
+ $str = $h->state;
Oracle hasn't supported SQLSTATE since the early versions OCI. It will return empty when the command succeeds and
'S1000' (General Error) for all other errors.
@@ -736,11 +732,11 @@
Implemented by DBI, no driver-specific impact.
-## __trace_msg__
+## __trace\_msg__
Implemented by DBI, no driver-specific impact.
-## __parse_trace_flag__ and __parse_trace_flags__
+## __parse\_trace\_flag__ and __parse\_trace\_flags__
Implemented by DBI, no driver-specific impact.
@@ -751,15 +747,15 @@
## __Private database handle functions__
Some of these functions are called through the method func()
-which is described in the DBI documentation. Any function that begins with ora_
+which is described in the DBI documentation. Any function that begins with ora\_
can be called directly.
-## __plsql_errstr__
+## __plsql\_errstr__
This function returns a string which describes the errors
from the most recent PL/SQL function, procedure, package,
or package body compile in a format similar to the output
-of the SQL*Plus command 'show errors'.
+of the SQL\*Plus command 'show errors'.
The function returns undef if the error string could not
be retrieved due to a database error.
@@ -784,70 +780,70 @@
die $msg if $msg;
}
-## __dbms_output_enable / dbms_output_put / dbms_output_get__
+## __dbms\_output\_enable / dbms\_output\_put / dbms\_output\_get__
-These functions use the PL/SQL DBMS_OUTPUT package to store and
-retrieve text using the DBMS_OUTPUT buffer. Text stored in this buffer
-by dbms_output_put or any PL/SQL block can be retrieved by
-dbms_output_get or any PL/SQL block connected to the same database
+These functions use the PL/SQL DBMS\_OUTPUT package to store and
+retrieve text using the DBMS\_OUTPUT buffer. Text stored in this buffer
+by dbms\_output\_put or any PL/SQL block can be retrieved by
+dbms\_output\_get or any PL/SQL block connected to the same database
session.
-Stored text is not available until after dbms_output_put or the PL/SQL
+Stored text is not available until after dbms\_output\_put or the PL/SQL
block that saved it completes its execution. This means you __CAN NOT__
use these functions to monitor long running PL/SQL procedures.
Example 1:
- # Enable DBMS_OUTPUT and set the buffer size
- $dbh->{RaiseError} = 1;
- $dbh->func( 1000000, 'dbms_output_enable' );
+ # Enable DBMS_OUTPUT and set the buffer size
+ $dbh->{RaiseError} = 1;
+ $dbh->func( 1000000, 'dbms_output_enable' );
- # Put text in the buffer . . .
- $dbh->func( @text, 'dbms_output_put' );
+ # Put text in the buffer . . .
+ $dbh->func( @text, 'dbms_output_put' );
- # . . . and retrieve it later
- @text = $dbh->func( 'dbms_output_get' );
+ # . . . and retrieve it later
+ @text = $dbh->func( 'dbms_output_get' );
Example 2:
- $dbh->{RaiseError} = 1;
- $sth = $dbh->prepare(q{
- DECLARE tmp VARCHAR2(50);
- BEGIN
- SELECT SYSDATE INTO tmp FROM DUAL;
- dbms_output.put_line('The date is '||tmp);
- END;
- });
- $sth->execute;
+ $dbh->{RaiseError} = 1;
+ $sth = $dbh->prepare(q{
+ DECLARE tmp VARCHAR2(50);
+ BEGIN
+ SELECT SYSDATE INTO tmp FROM DUAL;
+ dbms_output.put_line('The date is '||tmp);
+ END;
+ });
+ $sth->execute;
- # retrieve the string
- $date_string = $dbh->func( 'dbms_output_get' );
+ # retrieve the string
+ $date_string = $dbh->func( 'dbms_output_get' );
-## __dbms_output_enable ( [ buffer_size ] )__
+## __dbms\_output\_enable ( \[ buffer\_size \] )__
-This function calls DBMS_OUTPUT.ENABLE to enable calls to package
-DBMS_OUTPUT procedures GET, GET_LINE, PUT, and PUT_LINE. Calls to
-these procedures are ignored unless DBMS_OUTPUT.ENABLE is called
+This function calls DBMS\_OUTPUT.ENABLE to enable calls to package
+DBMS\_OUTPUT procedures GET, GET\_LINE, PUT, and PUT\_LINE. Calls to
+these procedures are ignored unless DBMS\_OUTPUT.ENABLE is called
first.
-The buffer_size is the maximum amount of text that can be saved in the
-buffer and must be between 2000 and 1,000,000. If buffer_size is not
+The buffer\_size is the maximum amount of text that can be saved in the
+buffer and must be between 2000 and 1,000,000. If buffer\_size is not
given, the default is 20,000 bytes.
-## __dbms_output_put ( [ @lines ] )__
+## __dbms\_output\_put ( \[ @lines \] )__
-This function calls DBMS_OUTPUT.PUT_LINE to add lines to the buffer.
+This function calls DBMS\_OUTPUT.PUT\_LINE to add lines to the buffer.
If all lines were saved successfully the function returns 1. Depending
on the context, an empty list or undef is returned for failure.
-If any line causes buffer_size to be exceeded, a buffer overflow error
+If any line causes buffer\_size to be exceeded, a buffer overflow error
is raised and the function call fails. Some of the text might be in
the buffer.
-## __dbms_output_get__
+## __dbms\_output\_get__
-This function calls DBMS_OUTPUT.GET_LINE to retrieve lines of text from
+This function calls DBMS\_OUTPUT.GET\_LINE to retrieve lines of text from
the buffer.
In an array context, all complete lines are removed from the buffer and
@@ -857,19 +853,19 @@
In a scalar context, the first complete line is removed from the buffer
and returned. If there are no complete lines, undef is returned.
-Any text in the buffer after a call to DBMS_OUTPUT.GET_LINE or
-DBMS_OUTPUT.GET is discarded by the next call to DBMS_OUTPUT.PUT_LINE,
-DBMS_OUTPUT.PUT, or DBMS_OUTPUT.NEW_LINE.
+Any text in the buffer after a call to DBMS\_OUTPUT.GET\_LINE or
+DBMS\_OUTPUT.GET is discarded by the next call to DBMS\_OUTPUT.PUT\_LINE,
+DBMS\_OUTPUT.PUT, or DBMS\_OUTPUT.NEW\_LINE.
## __reauthenticate ( $username, $password )__
Starts a new session against the current database using the credentials
supplied.
-## __private_attribute_info__
+## __private\_attribute\_info__
- $hashref = $dbh->private_attribute_info();
- $hashref = $sth->private_attribute_info();
+ $hashref = $dbh->private_attribute_info();
+ $hashref = $sth->private_attribute_info();
Returns a hash of all private attributes used by DBD::Oracle, for either
a database or a statement handle. Currently, all the hash values are undef.
@@ -889,7 +885,7 @@
## __PrintError__ (boolean, inherited)
Forces database errors to also generate warnings, which can then be filtered with methods such as
-locally redefining _$SIG{__WARN__}_ or using modules such as `CGI::Carp`. This attribute is on
+locally redefining _$SIG{\_\_WARN\_\_}_ or using modules such as `CGI::Carp`. This attribute is on
by default.
## __ShowErrorStatement__ (boolean, inherited)
@@ -904,15 +900,15 @@
## __Executed__ (boolean, read-only)
-Indicates if a handle has been executed. For database handles, this value is true after the [do](#pod_do) method has been called, or
-when one of the child statement handles has issued an [execute](#pod_execute). Issuing a [commit](#pod_commit) or [rollback](#pod_rollback) always resets the
-attribute to false for database handles. For statement handles, any call to [execute](#pod_execute) or its variants will flip the value to
+Indicates if a handle has been executed. For database handles, this value is true after the ["do"](#do) method has been called, or
+when one of the child statement handles has issued an ["execute"](#execute). Issuing a ["commit"](#commit) or ["rollback"](#rollback) always resets the
+attribute to false for database handles. For statement handles, any call to ["execute"](#execute) or its variants will flip the value to
true for the lifetime of the statement handle.
## __TraceLevel__ (integer, inherited)
-Sets the trace level, similar to the [trace](#pod_trace) method. See the sections on
-[trace](#pod_trace) and [parse_trace_flag](#pod_parse_trace_flag) for more details.
+Sets the trace level, similar to the ["trace"](#trace) method. See the sections on
+["trace"](#trace) and ["parse\_trace\_flag"](#parse\_trace\_flag) for more details.
## __Active__ (boolean, read-only)
@@ -933,7 +929,7 @@
## __CachedKids__ (hash ref)
Returns a hashref of handles. If called on a database handle, returns all statement handles created by use of the
-`prepare_cached` method. If called on a driver handle, returns all database handles created by the [connect_cached](#pod_connect_cached)
+`prepare_cached` method. If called on a driver handle, returns all database handles created by the ["connect\_cached"](#connect\_cached)
method.
## __ChildHandles__ (array ref)
@@ -1008,10 +1004,10 @@
# ORACLE-SPECIFIC DATABASE HANDLE METHODS
-## __ora_can_unicode ( [ $refresh ] )__
+## __ora\_can\_unicode ( \[ $refresh \] )__
Returns a number indicating whether either of the database character sets
-is a Unicode encoding. Calls ora_nls_parameters() and passes the optional
+is a Unicode encoding. Calls ora\_nls\_parameters() and passes the optional
$refresh parameter to it.
0 = Neither character set is a Unicode encoding.
@@ -1022,37 +1018,37 @@
3 = Both character sets are Unicode encodings.
-## __ora_can_taf__
+## __ora\_can\_taf__
Returns true if the current connection supports TAF events. False if otherise.
-## __ora_nls_parameters ( [ $refresh ] )__
+## __ora\_nls\_parameters ( \[ $refresh \] )__
Returns a hash reference containing the current NLS parameters, as given
-by the v$nls_parameters view. The values fetched are cached between calls.
+by the v$nls\_parameters view. The values fetched are cached between calls.
To cause the latest values to be fetched, pass a true value to the function.
# DATABASE HANDLE METHODS
-## __selectall_arrayref__
+## __selectall\_arrayref__
- $ary_ref = $dbh->selectall_arrayref($sql);
- $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
- $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
+ $ary_ref = $dbh->selectall_arrayref($sql);
+ $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
+ $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
Returns a reference to an array containing the rows returned by preparing and executing the SQL string.
See the DBI documentation for full details.
-## __selectall_hashref__
+## __selectall\_hashref__
- $hash_ref = $dbh->selectall_hashref($sql, $key_field);
+ $hash_ref = $dbh->selectall_hashref($sql, $key_field);
Returns a reference to a hash containing the rows returned by preparing and executing the SQL string.
See the DBI documentation for full details.
-## __selectcol_arrayref__
+## __selectcol\_arrayref__
- $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
+ $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
Returns a reference to an array containing the first column
from each rows returned by preparing and executing the SQL string. It is possible to specify exactly
@@ -1060,86 +1056,83 @@
## __prepare__
- $sth = $dbh->prepare($statement, \%attr);
+ $sth = $dbh->prepare($statement, \%attr);
Prepares a statement for later execution by the database engine and returns a reference to a statement handle object.
### __Prepare Attributes__
These attributes may be used in the `\%attr` parameter of the
-L<DBI/prepare> database handle method.
+["prepare" in DBI](http://search.cpan.org/perldoc?DBI#prepare) database handle method.
-- ora_placeholders
+- ora\_placeholders
Set to false to disable processing of placeholders. Used mainly for loading a
PL/SQL package that has been _wrapped_ with Oracle's `wrap` utility.
-- ora_auto_lob
+- ora\_auto\_lob
If true (the default), fetching retrieves the contents of the CLOB or
BLOB column in most circumstances. If false, fetching retrieves the
Oracle "LOB Locator" of the CLOB or BLOB value.
-See [LOBs and LONGs](#pod_LOBs and LONGs) for more details.
+See ["LOBS AND LONGS"](#LOBS AND LONGS) for more details.
See also the LOB tests in 05dbi.t of Oracle::OCI for examples
of how to use LOB Locators.
-- ora_pers_lob
+- ora\_pers\_lob
-If true the [Simple Fetch for CLOBs and BLOBs](#pod_Simple Fetch for CLOBs and BLOBs) method for the [Data Interface for Persistent LOBs](#pod_Data Interface for Persistent LOBs) will be
-used for LOBs rather than the default method [Data Interface for LOB Locators](#pod_Data Interface for LOB Locators).
+If true the ["Simple Fetch for CLOBs and BLOBs"](#Simple Fetch for CLOBs and BLOBs) method for the ["Data Interface for Persistent LOBs"](#Data Interface for Persistent LOBs) will be
+used for LOBs rather than the default method ["Data Interface for LOB Locators"](#Data Interface for LOB Locators).
-- ora_clbk_lob
+- ora\_clbk\_lob
-If true the [Piecewise Fetch with Callback](#pod_Piecewise Fetch with Callback) method for the L</Data
-Interface for Persistent LOBs> will be used for LOBs.
+If true the ["Piecewise Fetch with Callback"](#Piecewise Fetch with Callback) method for the ["Data Interface for Persistent LOBs"](#Data Interface for Persistent LOBs) will be used for LOBs.
-- ora_piece_lob
+- ora\_piece\_lob
-If true the [Piecewise Fetch with Polling](#pod_Piecewise Fetch with Polling) method for the L</Data
-Interface for Persistent LOBs> will be used for LOBs.
+If true the ["Piecewise Fetch with Polling"](#Piecewise Fetch with Polling) method for the ["Data Interface for Persistent LOBs"](#Data Interface for Persistent LOBs) will be used for LOBs.
-- ora_piece_size
+- ora\_piece\_size
-This is the max piece size for the [Piecewise Fetch with Callback](#pod_Piecewise Fetch with Callback)
-and [Piecewise Fetch with Polling](#pod_Piecewise Fetch with Polling) methods, in chars for CLOBS, and
+This is the max piece size for the ["Piecewise Fetch with Callback"](#Piecewise Fetch with Callback)
+and ["Piecewise Fetch with Polling"](#Piecewise Fetch with Polling) methods, in chars for CLOBS, and
bytes for BLOBS.
-- ora_check_sql
+- ora\_check\_sql
If 1 (default), force SELECT statements to be described in prepare().
If 0, allow SELECT statements to defer describe until execute().
-See [Prepare Postponed Till Execute](#pod_Prepare Postponed Till Execute) for more information.
+See ["Prepare Postponed Till Execute"](#Prepare Postponed Till Execute) for more information.
-- ora_exe_mode
+- ora\_exe\_mode
This will set the execute mode of the current statement. Presently
only one mode is supported;
- OCI_STMT_SCROLLABLE_READONLY - make result set scrollable
+ OCI_STMT_SCROLLABLE_READONLY - make result set scrollable
-See [Scrollable Cursors](#pod_Scrollable Cursors) for more details.
+See ["SCROLLABLE CURSORS"](#SCROLLABLE CURSORS) for more details.
-- ora_prefetch_rows
+- ora\_prefetch\_rows
Sets the number of rows to be prefetched. If it is not set, then the
-default value is 1. See [Row Prefetching](#pod_Row Prefetching) for more details.
+default value is 1. See ["Row Prefetching"](#Row Prefetching) for more details.
-- ora_prefetch_memory
+- ora\_prefetch\_memory
Sets the memory level for rows to be prefetched. The application then
-fetches as many rows as will fit into that much memory. See L</Row
-Prefetching> for more details.
+fetches as many rows as will fit into that much memory. See ["Row Prefetching"](#Row Prefetching) for more details.
-- ora_row_cache_off
+- ora\_row\_cache\_off
By default DBD::Oracle will use a row cache when fetching to cut down
the number of round trips to the server. If you do not want to use an
array fetch set this value to any value other than 0;
-See [Row Prefetching](#pod_Row Prefetching) for more details.
+See ["Row Prefetching"](#Row Prefetching) for more details.
### __Placeholders__
@@ -1150,18 +1143,18 @@
represented by a single question mark character. This is the method
recommended by the DBI and is the most portable. Each question
mark is internally replaced by a "dollar sign number" in the order in
-which they appear in the query (important when using [bind_param](#pod_bind_param)).
+which they appear in the query (important when using ["bind\_param"](#bind\_param)).
The second type of placeholder is "named parameters" in the format
":foo" which is the one Oracle prefers.
- $dbh->{RaiseError} = 1; # save having to check each method call
- $sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE :name");
- $sth->bind_param(':name', "John%");
- $sth->execute;
- DBI::dump_results($sth);
+ $dbh->{RaiseError} = 1; # save having to check each method call
+ $sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE :name");
+ $sth->bind_param(':name', "John%");
+ $sth->execute;
+ DBI::dump_results($sth);
-Note when calling bind_param with named parameters you must include
+Note when calling bind\_param with named parameters you must include
the leading colon. The advantage of this placeholder type is that you
can use the same placeholder more than once in the same SQL statement
but you only need to bind it once.
@@ -1169,55 +1162,55 @@
The last placeholder type is a variation of the two above where you
name each placeholder :N (where N is a number). Like the named
placeholders above you can use the same placeholder multiple times in
-the SQL but when you call bind_param you only need to pass the N
-(e.g., for :1 you use bind_param(1,...) and not bind_param(':1',...).
+the SQL but when you call bind\_param you only need to pass the N
+(e.g., for :1 you use bind\_param(1,...) and not bind\_param(':1',...).
The different types of placeholders cannot be mixed within a statement, but you may
use different ones for each statement handle you have. This is confusing at best, so
stick to one style within your program.
-## __prepare_cached__
+## __prepare\_cached__
- $sth = $dbh->prepare_cached($statement, \%attr);
+ $sth = $dbh->prepare_cached($statement, \%attr);
Implemented by DBI, no driver-specific impact. This method is most useful
if the same query is used over and over as it will cut down round trips to the server.
## __do__
- $rv = $dbh->do($statement);
- $rv = $dbh->do($statement, \%attr);
- $rv = $dbh->do($statement, \%attr, @bind_values);
+ $rv = $dbh->do($statement);
+ $rv = $dbh->do($statement, \%attr);
+ $rv = $dbh->do($statement, \%attr, @bind_values);
Prepare and execute a single statement. Returns the number of rows affected if the
query was successful, returns undef if an error occurred, and returns -1 if the
number of rows is unknown or not available. Note that this method will return __0E0__ instead
of 0 for 'no rows were affected', in order to always return a true value if no error occurred.
-## __last_insert_id__
+## __last\_insert\_id__
-Oracle does not implement auto_increment of serial type columns it uses predefined
+Oracle does not implement auto\_increment of serial type columns it uses predefined
sequences where the id numbers are either selected before insert, at insert time with a trigger,
or as part of the query.
Below is an example of you to use the latter with the SQL returning clause to get the ID number back
-on insert with the bind_param_inout method.
+on insert with the bind\_param\_inout method.
.
- $dbh->do('CREATE SEQUENCE lii_seq START 1');
- $dbh->do(q{CREATE TABLE lii (
- foobar INTEGER NOT NULL UNIQUE,
- baz VARCHAR)});
- $SQL = "INSERT INTO lii (foobar,baz) VALUES (lii_seq.nextval,'XX') returning foobar into :p_new_id";";
- $sth = $dbh->prepare($SQL);
- my $p_new_id='-1';
- $sth->bind_param_inout(":p_new_id",\$p_new_id,38);
- $sth->execute();
- $db->commit();
+ $dbh->do('CREATE SEQUENCE lii_seq START 1');
+ $dbh->do(q{CREATE TABLE lii (
+ foobar INTEGER NOT NULL UNIQUE,
+ baz VARCHAR)});
+ $SQL = "INSERT INTO lii (foobar,baz) VALUES (lii_seq.nextval,'XX') returning foobar into :p_new_id";";
+ $sth = $dbh->prepare($SQL);
+ my $p_new_id='-1';
+ $sth->bind_param_inout(":p_new_id",\$p_new_id,38);
+ $sth->execute();
+ $db->commit();
## __commit__
- $rv = $dbh->commit;
+ $rv = $dbh->commit;
Issues a COMMIT to the server, indicating that the current transaction is finished and that
all changes made will be visible to other processes. If AutoCommit is enabled, then
@@ -1225,21 +1218,21 @@
## __rollback__
- $rv = $dbh->rollback;
+ $rv = $dbh->rollback;
Issues a ROLLBACK to the server, which discards any changes made in the current transaction. If AutoCommit
is enabled, then a warning is given and no ROLLBACK is issued. Returns true on success, and
false on error.
-## __begin_work__
+## __begin\_work__
-This method turns on transactions until the next call to [commit](#pod_commit) or [rollback](#pod_rollback), if [AutoCommit](#pod_AutoCommit) is
-currently enabled. If it is not enabled, calling begin_work will issue an error. Note that the
-transaction will not actually begin until the first statement after begin_work is called.
+This method turns on transactions until the next call to ["commit"](#commit) or ["rollback"](#rollback), if ["AutoCommit"](#AutoCommit) is
+currently enabled. If it is not enabled, calling begin\_work will issue an error. Note that the
+transaction will not actually begin until the first statement after begin\_work is called.
## __disconnect__
- $rv = $dbh->disconnect;
+ $rv = $dbh->disconnect;
Disconnects from the Oracle database. Any uncommitted changes will be rolled back upon disconnection. It's
good policy to always explicitly call commit or rollback at some point before disconnecting, rather than
@@ -1251,42 +1244,42 @@
## __ping__
- $rv = $dbh->ping;
+ $rv = $dbh->ping;
This `ping` method is used to check the validity of a database handle. The value returned is
either 0, indicating that the connection is no longer valid, or 1, indicating the connection is valid.
This function does 1 round trip to the Oracle Server.
-## __get_info()__
+## __get\_info()__
- $value = $dbh->get_info($info_type);
+ $value = $dbh->get_info($info_type);
DBD::Oracle supports `get_info()`, but (currently) only a few info types.
-## __table_info()__
+## __table\_info()__
DBD::Oracle supports attributes for `table_info()`.
In Oracle, the concept of _user_ and _schema_ is (currently) the
same. Because database objects are owned by an user, the owner names
in the data dictionary views correspond to schema names.
-Oracle does not support catalogues so TABLE_CAT is ignored as
+Oracle does not support catalogues so TABLE\_CAT is ignored as
selection criterion.
-Search patterns are supported for TABLE_SCHEM and TABLE_NAME.
+Search patterns are supported for TABLE\_SCHEM and TABLE\_NAME.
-TABLE_TYPE may contain a comma-separated list of table types.
+TABLE\_TYPE may contain a comma-separated list of table types.
The following table types are supported:
- TABLE
- VIEW
- SYNONYM
- SEQUENCE
+ TABLE
+ VIEW
+ SYNONYM
+ SEQUENCE
-The result set is ordered by TABLE_TYPE, TABLE_SCHEM, TABLE_NAME.
+The result set is ordered by TABLE\_TYPE, TABLE\_SCHEM, TABLE\_NAME.
The special enumerations of catalogues, schemas and table types are
-supported. However, TABLE_CAT is always NULL.
+supported. However, TABLE\_CAT is always NULL.
An identifier is passed _as is_, i.e. as the user provides or
Oracle returns it.
@@ -1300,124 +1293,124 @@
`table_info()` has no special quote handling, neither adds nor
removes quotes.
-## __primary_key_info()__
+## __primary\_key\_info()__
-Oracle does not support catalogues so TABLE_CAT is ignored as
+Oracle does not support catalogues so TABLE\_CAT is ignored as
selection criterion.
-The TABLE_CAT field of a fetched row is always NULL (undef).
-See [table_info()](#pod_table_info()) for more detailed information.
+The TABLE\_CAT field of a fetched row is always NULL (undef).
+See ["table\_info()"](#table\_info()) for more detailed information.
If the primary key constraint was created without an identifier,
-PK_NAME contains a system generated name with the form SYS_Cn.
+PK\_NAME contains a system generated name with the form SYS\_Cn.
-The result set is ordered by TABLE_SCHEM, TABLE_NAME, KEY_SEQ.
+The result set is ordered by TABLE\_SCHEM, TABLE\_NAME, KEY\_SEQ.
An identifier is passed _as is_, i.e. as the user provides or
Oracle returns it.
-See [table_info()](#pod_table_info()) for more detailed information.
+See ["table\_info()"](#table\_info()) for more detailed information.
-## __foreign_key_info()__
+## __foreign\_key\_info()__
This method (currently) supports the extended behaviour of SQL/CLI, i.e. the
result set contains foreign keys that refer to primary __and__ alternate keys.
-The field UNIQUE_OR_PRIMARY distinguishes these keys.
+The field UNIQUE\_OR\_PRIMARY distinguishes these keys.
Oracle does not support catalogues, so `$pk_catalog` and `$fk_catalog` are
ignored as selection criteria (in the new style interface).
-The UK_TABLE_CAT and FK_TABLE_CAT fields of a fetched row are always
+The UK\_TABLE\_CAT and FK\_TABLE\_CAT fields of a fetched row are always
NULL (undef).
-See [table_info()](#pod_table_info()) for more detailed information.
+See ["table\_info()"](#table\_info()) for more detailed information.
If the primary or foreign key constraints were created without an identifier,
-UK_NAME or FK_NAME contains a system generated name with the form SYS_Cn.
+UK\_NAME or FK\_NAME contains a system generated name with the form SYS\_Cn.
-The UPDATE_RULE field is always 3 ('NO ACTION'), because Oracle (currently)
+The UPDATE\_RULE field is always 3 ('NO ACTION'), because Oracle (currently)
does not support other actions.
-The DELETE_RULE field may contain wrong values. This is a known Bug (#1271663)
+The DELETE\_RULE field may contain wrong values. This is a known Bug (\#1271663)
in Oracle's data dictionary views. Currently (as of 8.1.7), 'RESTRICT' and
'SET DEFAULT' are not supported, 'CASCADE' is mapped correctly and all other
actions (incl. 'SET NULL') appear as 'NO ACTION'.
The DEFERABILITY field is always NULL, because this columns is
-not present in the ALL_CONSTRAINTS view of older Oracle releases.
+not present in the ALL\_CONSTRAINTS view of older Oracle releases.
-The result set is ordered by UK_TABLE_SCHEM, UK_TABLE_NAME, FK_TABLE_SCHEM,
-FK_TABLE_NAME, ORDINAL_POSITION.
+The result set is ordered by UK\_TABLE\_SCHEM, UK\_TABLE\_NAME, FK\_TABLE\_SCHEM,
+FK\_TABLE\_NAME, ORDINAL\_POSITION.
An identifier is passed _as is_, i.e. as the user provides or
Oracle returns it.
-See [table_info()](#pod_table_info()) for more detailed information.
+See ["table\_info()"](#table\_info()) for more detailed information.
-## __column_info()__
+## __column\_info()__
-Oracle does not support catalogues so TABLE_CAT is ignored as
+Oracle does not support catalogues so TABLE\_CAT is ignored as
selection criterion.
-The TABLE_CAT field of a fetched row is always NULL (undef).
-See [table_info()](#pod_table_info()) for more detailed information.
+The TABLE\_CAT field of a fetched row is always NULL (undef).
+See ["table\_info()"](#table\_info()) for more detailed information.
-The CHAR_OCTET_LENGTH field is (currently) always NULL (undef).
+The CHAR\_OCTET\_LENGTH field is (currently) always NULL (undef).
-Don't rely on the values of the BUFFER_LENGTH field!
+Don't rely on the values of the BUFFER\_LENGTH field!
Especially the length of FLOATs may be wrong.
Datatype codes for non-standard types are subject to change.
-Attention! The DATA_DEFAULT (COLUMN_DEF) column is of type LONG so you
+Attention! The DATA\_DEFAULT (COLUMN\_DEF) column is of type LONG so you
may have to set LongReadLen on the connection handle before calling
-column_info if you have a large default column. After DBD::Oracle 1.40
-LongReadLen is set automatically to 1Mb when calling column_info and
+column\_info if you have a large default column. After DBD::Oracle 1.40
+LongReadLen is set automatically to 1Mb when calling column\_info and
reset aftwerwards.
-The result set is ordered by TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION.
+The result set is ordered by TABLE\_SCHEM, TABLE\_NAME, ORDINAL\_POSITION.
An identifier is passed _as is_, i.e. as the user provides or
Oracle returns it.
-See [table_info()](#pod_table_info()) for more detailed information.
+See ["table\_info()"](#table\_info()) for more detailed information.
It is possible with Oracle to make the names of the various DB objects (table,column,index etc)
case sensitive.
- alter table bloggind add ("Bla_BLA" NUMBER)
+ alter table bloggind add ("Bla_BLA" NUMBER)
-So in the example the exact case "Bla_BLA" must be used to get it info on the column. While this
+So in the example the exact case "Bla\_BLA" must be used to get it info on the column. While this
- alter table bloggind add (Bla_BLA NUMBER)
+ alter table bloggind add (Bla_BLA NUMBER)
any case can be used to get info on the column.
-## __selectrow_array__
+## __selectrow\_array__
- @row_ary = $dbh->selectrow_array($sql);
- @row_ary = $dbh->selectrow_array($sql, \%attr);
- @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
+ @row_ary = $dbh->selectrow_array($sql);
+ @row_ary = $dbh->selectrow_array($sql, \%attr);
+ @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
Returns an array of row information after preparing and executing the provided SQL string. The rows are returned
-by calling [fetchrow_array](#pod_fetchrow_array). The string can also be a statement handle generated by a previous prepare. Note that
+by calling ["fetchrow\_array"](#fetchrow\_array). The string can also be a statement handle generated by a previous prepare. Note that
only the first row of data is returned. If called in a scalar context, only the first column of the first row is
returned. Because this is not portable, it is not recommended that you use this method in that way.
-## __selectrow_arrayref__
+## __selectrow\_arrayref__
- $ary_ref = $dbh->selectrow_arrayref($statement);
- $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
- $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
+ $ary_ref = $dbh->selectrow_arrayref($statement);
+ $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
+ $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
-Exactly the same as [selectrow_array](#pod_selectrow_array), except that it returns a reference to an array, by internal use of
-the [fetchrow_arrayref](#pod_fetchrow_arrayref) method.
+Exactly the same as ["selectrow\_array"](#selectrow\_array), except that it returns a reference to an array, by internal use of
+the ["fetchrow\_arrayref"](#fetchrow\_arrayref) method.
-## __selectrow_hashref__
+## __selectrow\_hashref__
- $hash_ref = $dbh->selectrow_hashref($sql);
- $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
- $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
+ $hash_ref = $dbh->selectrow_hashref($sql);
+ $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
+ $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
-Exactly the same as [selectrow_array](#pod_selectrow_array), except that it returns a reference to an hash, by internal use of
-the [fetchrow_hashref](#pod_fetchrow_hashref) method.
+Exactly the same as ["selectrow\_array"](#selectrow\_array), except that it returns a reference to an hash, by internal use of
+the ["fetchrow\_hashref"](#fetchrow\_hashref) method.
## __clone__
- $other_dbh = $dbh->clone();
+ $other_dbh = $dbh->clone();
Creates a copy of the database handle by connecting with the same parameters as the original
handle, then trying to merge the attributes. See the DBI documentation for complete usage.
@@ -1428,11 +1421,11 @@
Supported by DBD::Oracle as proposed by DBI.The default of AutoCommit is on, but this may change
in the future, so it is highly recommended that you explicitly set it when
-calling [connect](#pod_connect).
+calling ["connect"](#connect).
## __ReadOnly__ (boolean)
- $dbh->{ReadOnly} = 1;
+ $dbh->{ReadOnly} = 1;
Specifies if the current database connection should be in read-only mode or not.
@@ -1456,7 +1449,7 @@
Holds the handle of the parent driver. The only recommended use for this is to find the name
of the driver using:
- $dbh->{Driver}->{Name}
+ $dbh->{Driver}->{Name}
## __RowCacheSize__
@@ -1485,46 +1478,46 @@
### Row Prefetching
Row prefetching occurs on the server side and uses the DBI database handle attribute `RowCacheSize` and or the
-Prepare Attribute 'ora_prefetch_memory'. Tweaking these values may yield improved performance.
+Prepare Attribute 'ora\_prefetch\_memory'. Tweaking these values may yield improved performance.
- $dbh->{RowCacheSize} = 100;
- $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY,ora_prefetch_memory=>10000});
+ $dbh->{RowCacheSize} = 100;
+ $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. The Oracle® Call Interface Programmer's Guide,
suggests a good row cache value for a scrollable cursor is about 20% of expected size of the record set.
The prefetch settings tell the DBD::Oracle to grab x rows (or x-bytes) when it needs to get new rows. This happens on the first
-fetch that sets the current_positon to any value other than 0. In the above example if we do a OCI_FETCH_FIRST the first 10 rows are
+fetch that sets the current\_positon to any value other than 0. In the above example if we do a OCI\_FETCH\_FIRST the first 10 rows are
loaded into the buffer and DBD::Oracle will not have to go back to the server for more rows. When record 11 is fetched DBD::Oracle
fetches and returns this row and the next 9 rows are loaded into the buffer. In this case if you fetch backwards from 10 to 1
no server round trips are made.
With large record sets it is best not to attempt to go to the last record as this may take some time, A large buffer size might even slow down
-the fetch. If you must get the number of rows in a large record set you might try using an few large OCI_FETCH_ABSOLUTEs and then an OCI_FETCH_LAST,
-this might save some time. So if you had a record set of 10000 rows and you set the buffer to 5000 and did a OCI_FETCH_LAST one would fetch the first 5000 rows into the buffer then the next 5000 rows.
+the fetch. If you must get the number of rows in a large record set you might try using an few large OCI\_FETCH\_ABSOLUTEs and then an OCI\_FETCH\_LAST,
+this might save some time. So if you had a record set of 10000 rows and you set the buffer to 5000 and did a OCI\_FETCH\_LAST one would fetch the first 5000 rows into the buffer then the next 5000 rows.
If one requires only the first few rows there is no need to set a large prefetch value.
-If the ora_prefetch_memory less than 1 or not present then memory size is not included in computing the
+If the ora\_prefetch\_memory less than 1 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. Likewise if the RowCacheSize is less than 1 it
is not included in the computing of the prefetch rows.
# ORACLE-SPECIFIC STATEMENT HANDLE METHODS
-## __ora_stmt_type__
+## __ora\_stmt\_type__
Returns the OCI Statement Type number for the SQL of a statement handle.
-## __ora_stmt_type_name__
+## __ora\_stmt\_type\_name__
Returns the OCI Statement Type name for the SQL of a statement handle.
# DBI STATEMENT HANDLE OBJECT METHODS
-## __bind_param__
+## __bind\_param__
- $rv = $sth->bind_param($param_num, $bind_value);
- $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
- $rv = $sth->bind_param($param_num, $bind_value, \%attr);
+ $rv = $sth->bind_param($param_num, $bind_value);
+ $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
+ $rv = $sth->bind_param($param_num, $bind_value, \%attr);
Allows the user to bind a value and/or a data type to a placeholder.
@@ -1540,26 +1533,26 @@
use one of the values provided by DBI or by DBD::Pg. To use a SQL value,
modify your "use DBI" statement at the top of your script as follows:
- use DBI qw(:sql_types);
+ use DBI qw(:sql_types);
This will import some constants into your script. You can plug those
-directly into the [bind_param](#pod_bind_param) call. Some common ones that you will
+directly into the ["bind\_param"](#bind\_param) call. Some common ones that you will
encounter are:
- SQL_INTEGER
+ SQL_INTEGER
To use Oracle SQL data types, import the list of values like this:
- use DBD::Pg qw(:ora_types);
+ use DBD::Pg qw(:ora_types);
You can then set the data types by setting the value of the `ora_type`
-key in the hash passed to [bind_param](#pod_bind_param).
+key in the hash passed to ["bind\_param"](#bind\_param).
The current list of Oracle data types exported is:
- ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
- ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB
- ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI
- SQLT_CHR SQLT_BIN
+ ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
+ ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB
+ ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI
+ SQLT_CHR SQLT_BIN
Data types are "sticky," in that once a data type is set to a certain placeholder,
it will remain for that placeholder, unless it is explicitly set to something
@@ -1569,42 +1562,42 @@
Examples:
- use DBI qw(:sql_types);
- use DBD::Pg qw(:ora_types);
+ use DBI qw(:sql_types);
+ use DBD::Pg qw(:ora_types);
- $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
- $sth = $dbh->prepare($SQL);
+ $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
+ $sth = $dbh->prepare($SQL);
- ## Both arguments below are bound to placeholders as "varchar"
- $sth->execute(123, "Merk");
+ ## Both arguments below are bound to placeholders as "varchar"
+ $sth->execute(123, "Merk");
- ## Reset the datatype for the first placeholder to an integer
- $sth->bind_param(1, undef, SQL_INTEGER);
+ ## Reset the datatype for the first placeholder to an integer
+ $sth->bind_param(1, undef, SQL_INTEGER);
- ## The "undef" bound above is not used, since we supply params to execute
- $sth->execute(123, "Merk");
+ ## The "undef" bound above is not used, since we supply params to execute
+ $sth->execute(123, "Merk");
- ## Set the first placeholder's value and data type
- $sth->bind_param(1, 234, { pg_type => ORA_NUMBER });
+ ## Set the first placeholder's value and data type
+ $sth->bind_param(1, 234, { pg_type => ORA_NUMBER });
- ## Set the second placeholder's value and data type.
- ## We don't send a third argument, so the default "varchar" is used
- $sth->bind_param('$2', "Zool");
+ ## Set the second placeholder's value and data type.
+ ## We don't send a third argument, so the default "varchar" is used
+ $sth->bind_param('$2', "Zool");
- ## We realize that the wrong data type was set above, so we change it:
- $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
+ ## We realize that the wrong data type was set above, so we change it:
+ $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
- ## We also got the wrong value, so we change that as well.
- ## Because the data type is sticky, we don't need to change it
- $sth->bind_param(1, 567);
+ ## We also got the wrong value, so we change that as well.
+ ## Because the data type is sticky, we don't need to change it
+ $sth->bind_param(1, 567);
- ## This executes the statement with 567 (integer) and "Zool" (varchar)
- $sth->execute();
+ ## This executes the statement with 567 (integer) and "Zool" (varchar)
+ $sth->execute();
These attributes may be used in the `\%attr` parameter of the
-L<DBI/bind_param> or L<DBI/bind_param_inout> statement handle methods.
+["bind\_param" in DBI](http://search.cpan.org/perldoc?DBI#bind\_param) or ["bind\_param\_inout" in DBI](http://search.cpan.org/perldoc?DBI#bind\_param\_inout) statement handle methods.
-- ora_type
+- ora\_type
Specify the placeholder's datatype using an Oracle datatype.
A fatal error is raised if `ora_type` and the DBI `TYPE` attribute
@@ -1613,61 +1606,61 @@
DBD::Oracle and will cause a fatal error if used.
Constants for the Oracle datatypes may be imported using
- use DBD::Oracle qw(:ora_types);
+ use DBD::Oracle qw(:ora_types);
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_VARCHAR2, ORA_STRING, ORA_LONG, ORA_RAW, ORA_LONGRAW,
+ ORA_CHAR, ORA_MLSLABEL, ORA_RSET
Additional values when DBD::Oracle was built using OCI 8 and later:
- ORA_CLOB, ORA_BLOB, ORA_XMLTYPE, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE
+ ORA_CLOB, ORA_BLOB, ORA_XMLTYPE, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE
Additional values when DBD::Oracle was built using OCI 9.2 and later:
- SQLT_CHR, SQLT_BIN
+ SQLT_CHR, SQLT_BIN
-See [Binding Cursors](#pod_Binding Cursors) for the correct way to use ORA_RSET.
+See ["Binding Cursors"](#Binding Cursors) for the correct way to use ORA\_RSET.
-See [LOBs and LONGs](#pod_LOBs and LONGs) for how to use ORA_CLOB and ORA_BLOB.
+See ["LOBS AND LONGS"](#LOBS AND LONGS) for how to use ORA\_CLOB and ORA\_BLOB.
-See [SYS.DBMS_SQL datatypes](#pod_SYS.DBMS_SQL datatypes) for ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE.
+See ["SYS.DBMS\_SQL datatypes"](#SYS.DBMS\_SQL datatypes) for ORA\_VARCHAR2\_TABLE, ORA\_NUMBER\_TABLE.
-See [Data Interface for Persistent LOBs](#pod_Data Interface for Persistent LOBs) for the correct way to use SQLT_CHR and SQLT_BIN.
+See ["Data Interface for Persistent LOBs"](#Data Interface for Persistent LOBs) for the correct way to use SQLT\_CHR and SQLT\_BIN.
-See [Other Data Types](#pod_Other Data Types) for more information.
+See ["OTHER DATA TYPES"](#OTHER DATA TYPES) for more information.
-See also L<DBI/Placeholders and Bind Values>.
+See also ["Placeholders and Bind Values" in DBI](http://search.cpan.org/perldoc?DBI#Placeholders and Bind Values).
-- ora_csform
+- ora\_csform
-Specify the OCI_ATTR_CHARSET_FORM for the bind value. Valid values
-are SQLCS_IMPLICIT (1) and SQLCS_NCHAR (2). Both those constants can
+Specify the OCI\_ATTR\_CHARSET\_FORM for the bind value. Valid values
+are SQLCS\_IMPLICIT (1) and SQLCS\_NCHAR (2). Both those constants can
be imported from the DBD::Oracle module. Rarely needed.
-- ora_csid
+- ora\_csid
-Specify the _integer_ OCI_ATTR_CHARSET_ID for the bind value.
+Specify the _integer_ OCI\_ATTR\_CHARSET\_ID for the bind value.
Character set names can't be used currently.
-- ora_maxdata_size
+- ora\_maxdata\_size
-Specify the integer OCI_ATTR_MAXDATA_SIZE for the bind value.
+Specify the integer OCI\_ATTR\_MAXDATA\_SIZE for the bind value.
May be needed if a character set conversion from client to server
causes the data to use more space and so fail with a truncation error.
-- ora_maxarray_numentries
+- ora\_maxarray\_numentries
Specify the maximum number of array entries to allocate. Used with
-ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE. Define the maximum number of
+ORA\_VARCHAR2\_TABLE, ORA\_NUMBER\_TABLE. Define the maximum number of
array entries Oracle can pass back to you in OUT variable of type
TABLE OF ... .
-- ora_internal_type
+- ora\_internal\_type
Specify internal data representation. Currently is supported only for
-ORA_NUMBER_TABLE.
+ORA\_NUMBER\_TABLE.
### Optimizing Results
@@ -1677,7 +1670,7 @@
prior to the execution of the statement unless the application requests
information about the results (such as $sth->{NAME}). This reduces
communication with the server and increases performance (reducing the
-number of PARSE_CALLS inside the server).
+number of PARSE\_CALLS inside the server).
However, it also means that SQL errors are not detected until
`execute()` (or $sth->{NAME} etc) is called instead of when
@@ -1685,13 +1678,13 @@
use of $sth->{NAME} or a similar attribute and the describe fails then
_an exception is thrown_ even if `RaiseError` is false!
-Set [ora_check_sql](#pod_ora_check_sql) to 0 in prepare() to enable this behaviour.
+Set ["ora\_check\_sql"](#ora\_check\_sql) to 0 in prepare() to enable this behaviour.
-## __bind_param_inout__
+## __bind\_param\_inout__
- $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
+ $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
-DBD::Oracle fully supports bind_param_inout below are some uses for this method.
+DBD::Oracle fully supports bind\_param\_inout below are some uses for this method.
### __Returning A Value from an INSERT__
@@ -1700,50 +1693,47 @@
single-pass insertion of values with re-used sequence values
(avoiding a separate "select seq.nextval from dual" step).
- $sth = $dbh->prepare(qq{
- INSERT INTO foo (id, bar)
- VALUES (foo_id_seq.nextval, :bar)
- RETURNING id INTO :id
- });
- $sth->bind_param(":bar", 42);
- $sth->bind_param_inout(":id", \my $new_id, 99);
- $sth->execute;
- print "The id of the new record is $new_id\n";
+ $sth = $dbh->prepare(qq{
+ INSERT INTO foo (id, bar)
+ VALUES (foo_id_seq.nextval, :bar)
+ RETURNING id INTO :id
+ });
+ $sth->bind_param(":bar", 42);
+ $sth->bind_param_inout(":id", \my $new_id, 99);
+ $sth->execute;
+ print "The id of the new record is $new_id\n";
If you have many columns to bind you can use code like this:
- @params = (... column values for record to be inserted ...);
- $sth->bind_param($_, $params[$_-1]) for (1..@params);
- $sth->bind_param_inout(@params+1, \my $new_id, 99);
- $sth->execute;
+ @params = (... column values for record to be inserted ...);
+ $sth->bind_param($_, $params[$_-1]) for (1..@params);
+ $sth->bind_param_inout(@params+1, \my $new_id, 99);
+ $sth->execute;
If you have many rows to insert you can take advantage of Oracle's built in execute array feature
with code like this:
- my @in_values=('1',2,'3','4',5,'6',7,'8',9,'10');
- my @out_values;
- my @status;
- my $sth = $dbh->prepare(qq{
- INSERT INTO foo (id, bar)
- VALUES (foo_id_seq.nextval, ?)
- RETURNING id INTO ?
- });
- $sth->bind_param_array(1,\@in_values);
- $sth->bind_param_inout_array(2,\@out_values,0,{ora_type => ORA_VARCHAR2});
- $sth->execute_array({ArrayTupleStatus=>\@status}) or die "error inserting";
- foreach my $id (@out_values){
+ my @in_values=('1',2,'3','4',5,'6',7,'8',9,'10');
+ my @out_values;
+ my @status;
+ my $sth = $dbh->prepare(qq{
+ INSERT INTO foo (id, bar)
+ VALUES (foo_id_seq.nextval, ?)
+ RETURNING id INTO ?
+ });
+ $sth->bind_param_array(1,\@in_values);
+ $sth->bind_param_inout_array(2,\@out_values,0,{ora_type => ORA_VARCHAR2});
+ $sth->execute_array({ArrayTupleStatus=>\@status}) or die "error inserting";
+ foreach my $id (@out_values){
print 'returned id='.$id.'\n';
- }
+ }
-Which will return all the ids into @out_values.
+Which will return all the ids into @out\_values.
- __Note:__
-
- This will only work for numbered (?) placeholders,
-
-- The third parameter of bind_param_inout_array, (0 in the example), "maxlen" is required by DBI but not used by DBD::Oracle
-
-- The "ora_type" attribute is not needed but only ORA_VARCHAR2 will work.
+- The third parameter of bind\_param\_inout\_array, (0 in the example), "maxlen" is required by DBI but not used by DBD::Oracle
+- The "ora\_type" attribute is not needed but only ORA\_VARCHAR2 will work.
### Returning A Recordset
@@ -1755,90 +1745,90 @@
For example, assume you have the existing PL/SQL Package :
- CREATE OR REPLACE PACKAGE Array_Example AS
+ CREATE OR REPLACE PACKAGE Array_Example AS
+ --
+ TYPE tRec IS RECORD (
+ Col1 NUMBER,
+ Col2 VARCHAR2 (10),
+ Col3 DATE) ;
+ --
+ TYPE taRec IS TABLE OF tRec INDEX BY BINARY_INTEGER ;
+ --
+ FUNCTION Array_Func RETURN taRec ;
+ --
+ END Array_Example ;
+
+ CREATE OR REPLACE PACKAGE BODY Array_Example AS
--
- TYPE tRec IS RECORD (
- Col1 NUMBER,
- Col2 VARCHAR2 (10),
- Col3 DATE) ;
+ FUNCTION Array_Func RETURN taRec AS
--
- TYPE taRec IS TABLE OF tRec INDEX BY BINARY_INTEGER ;
+ l_Ret taRec ;
--
- FUNCTION Array_Func RETURN taRec ;
+ BEGIN
+ FOR i IN 1 .. 5 LOOP
+ l_Ret (i).Col1 := i ;
+ l_Ret (i).Col2 := 'Row : ' || i ;
+ l_Ret (i).Col3 := TRUNC (SYSDATE) + i ;
+ END LOOP ;
+ RETURN l_Ret ;
+ END ;
--
- END Array_Example ;
-
- CREATE OR REPLACE PACKAGE BODY Array_Example AS
- --
- FUNCTION Array_Func RETURN taRec AS
- --
- l_Ret taRec ;
- --
- BEGIN
- FOR i IN 1 .. 5 LOOP
- l_Ret (i).Col1 := i ;
- l_Ret (i).Col2 := 'Row : ' || i ;
- l_Ret (i).Col3 := TRUNC (SYSDATE) + i ;
- END LOOP ;
- RETURN l_Ret ;
- END ;
- --
- END Array_Example ;
- /
+ END Array_Example ;
+ /
Currently, there is no way to directly call the function
-Array_Example.Array_Func from DBI. However, by making the following relatively
+Array\_Example.Array\_Func from DBI. However, by making the following relatively
painless additions, its not only possible, but extremely efficient.
First, you need to create database object types that correspond to the record
and table types in the package. From the above example, these would be :
- CREATE OR REPLACE TYPE tArray_Example__taRec
- AS OBJECT (
- Col1 NUMBER,
- Col2 VARCHAR2 (10),
- Col3 DATE
- ) ;
+ CREATE OR REPLACE TYPE tArray_Example__taRec
+ AS OBJECT (
+ Col1 NUMBER,
+ Col2 VARCHAR2 (10),
+ Col3 DATE
+ ) ;
- CREATE OR REPLACE TYPE taArray_Example__taRec
- AS TABLE OF tArray_Example__taRec ;
+ CREATE OR REPLACE TYPE taArray_Example__taRec
+ AS TABLE OF tArray_Example__taRec ;
Now, assuming the existing function needs to remain unchanged (it is probably
being called from other PL/SQL code), we need to add a new function to the
package. Here's the new package specification and body :
- CREATE OR REPLACE PACKAGE Array_Example AS
- --
- TYPE tRec IS RECORD (
+ CREATE OR REPLACE PACKAGE Array_Example AS
+ --
+ TYPE tRec IS RECORD (
Col1 NUMBER,
Col2 VARCHAR2 (10),
Col3 DATE) ;
- --
- TYPE taRec IS TABLE OF tRec INDEX BY BINARY_INTEGER ;
- --
- FUNCTION Array_Func RETURN taRec ;
- FUNCTION Array_Func_DBI RETURN taArray_Example__taRec PIPELINED ;
- --
- END Array_Example ;
+ --
+ TYPE taRec IS TABLE OF tRec INDEX BY BINARY_INTEGER ;
+ --
+ FUNCTION Array_Func RETURN taRec ;
+ FUNCTION Array_Func_DBI RETURN taArray_Example__taRec PIPELINED ;
+ --
+ END Array_Example ;
- CREATE OR REPLACE PACKAGE BODY Array_Example AS
- --
- FUNCTION Array_Func RETURN taRec AS
- l_Ret taRec ;
- BEGIN
- FOR i IN 1 .. 5 LOOP
+ CREATE OR REPLACE PACKAGE BODY Array_Example AS
+ --
+ FUNCTION Array_Func RETURN taRec AS
+ l_Ret taRec ;
+ BEGIN
+ FOR i IN 1 .. 5 LOOP
l_Ret (i).Col1 := i ;
l_Ret (i).Col2 := 'Row : ' || i ;
l_Ret (i).Col3 := TRUNC (SYSDATE) + i ;
- END LOOP ;
- RETURN l_Ret ;
- END ;
+ END LOOP ;
+ RETURN l_Ret ;
+ END ;
- FUNCTION Array_Func_DBI RETURN taArray_Example__taRec PIPELINED AS
- l_Set taRec ;
- BEGIN
- l_Set := Array_Func ;
- FOR i IN l_Set.FIRST .. l_Set.LAST LOOP
+ FUNCTION Array_Func_DBI RETURN taArray_Example__taRec PIPELINED AS
+ l_Set taRec ;
+ BEGIN
+ l_Set := Array_Func ;
+ FOR i IN l_Set.FIRST .. l_Set.LAST LOOP
PIPE ROW (
tArray_Example__taRec (
l_Set (i).Col1,
@@ -1846,26 +1836,26 @@
l_Set (i).Col3
)
) ;
- END LOOP ;
- RETURN ;
- END ;
- --
- END Array_Example ;
+ END LOOP ;
+ RETURN ;
+ END ;
+ --
+ END Array_Example ;
As you can see, the new function is very simple. Now, it is a simple matter
of calling the function as a straight-forward SELECT from your DBI code. From
the above example, the code would look something like this :
- my $sth = $dbh->prepare('SELECT * FROM TABLE(Array_Example.Array_Func_DBI)');
- $sth->execute;
- while ( my ($col1, $col2, $col3) = $sth->fetchrow_array {
- ...
- }
+ my $sth = $dbh->prepare('SELECT * FROM TABLE(Array_Example.Array_Func_DBI)');
+ $sth->execute;
+ while ( my ($col1, $col2, $col3) = $sth->fetchrow_array {
+ ...
+ }
-### __SYS.DBMS_SQL datatypes__
+### __SYS.DBMS\_SQL datatypes__
-DBD::Oracle has built-in support for __SYS.DBMS_SQL.VARCHAR2_TABLE__
-and __SYS.DBMS_SQL.NUMBER_TABLE__ datatypes. The simple example is here:
+DBD::Oracle has built-in support for __SYS.DBMS\_SQL.VARCHAR2\_TABLE__
+and __SYS.DBMS\_SQL.NUMBER\_TABLE__ datatypes. The simple example is here:
my $statement='
DECLARE
@@ -1893,43 +1883,42 @@
"\tarr=",Data::Dumper::Dumper(\@arr),"\n";
- __Note:__
-
-- Take careful note that we use '\\@arr' here because the 'bind_param_inout'
+- Take careful note that we use '\\\\@arr' here because the 'bind\_param\_inout'
will only take a reference to a scalar.
-### __ORA_VARCHAR2_TABLE__
+### __ORA\_VARCHAR2\_TABLE__
-SYS.DBMS_SQL.VARCHAR2_TABLE object is always bound to array reference.
-( in bind_param() and bind_param_inout() ). When you bind array, you need
+SYS.DBMS\_SQL.VARCHAR2\_TABLE object is always bound to array reference.
+( in bind\_param() and bind\_param\_inout() ). When you bind array, you need
to specify full buffer size for OUT data. So, there are two parameters:
-_max_len_ (specified as 3rd argument of bind_param_inout() ),
-and _ora_maxarray_numentries_. They define maximum array entry length and
+_max\_len_ (specified as 3rd argument of bind\_param\_inout() ),
+and _ora\_maxarray\_numentries_. They define maximum array entry length and
maximum rows, that can be passed to Oracle and back to you. In this
example we send array with 1 element with length=3, but allocate space for 100
Oracle array entries with maximum length 10 of each. So, you can get no more
than 100 array entries with length <= 10.
-If you set _max_len_ to zero, maximum array entry length is calculated
-as maximum length of entry of array bound. If 0 < _max_len_ < length( $some_element ),
+If you set _max\_len_ to zero, maximum array entry length is calculated
+as maximum length of entry of array bound. If 0 < _max\_len_ < length( $some\_element ),
truncation occur.
-If you set _ora_maxarray_numentries_ to zero, current (at bind time) bound
-array length is used as maximum. If 0 < _ora_maxarray_numentries_ < scalar(@array),
+If you set _ora\_maxarray\_numentries_ to zero, current (at bind time) bound
+array length is used as maximum. If 0 < _ora\_maxarray\_numentries_ < scalar(@array),
not all array entries are bound.
-### __ORA_NUMBER_TABLE__
+### __ORA\_NUMBER\_TABLE__
-SYS.DBMS_SQL.NUMBER_TABLE object handling is much alike ORA_VARCHAR2_TABLE.
+SYS.DBMS\_SQL.NUMBER\_TABLE object handling is much alike ORA\_VARCHAR2\_TABLE.
The main difference is internal data representation. Currently 2 types of
bind is allowed : as C-integer, or as C-double type. To select one of them,
-you may specify additional bind parameter _ora_internal_type_ as either
-__SQLT_INT__ or __SQLT_FLT__ for C-integer and C-double types.
+you may specify additional bind parameter _ora\_internal\_type_ as either
+__SQLT\_INT__ or __SQLT\_FLT__ for C-integer and C-double types.
Integer size is architecture-specific and is usually 32 or 64 bit.
Double is standard IEEE 754 type.
-_ora_internal_type_ defaults to double (SQLT_FLT).
+_ora\_internal\_type_ defaults to double (SQLT\_FLT).
-_max_len_ is ignored for OCI_NUMBER_TABLE.
+_max\_len_ is ignored for OCI\_NUMBER\_TABLE.
Currently, you cannot bind full native Oracle NUMBER(38). If you really need,
send request to dbi-dev list.
@@ -1986,7 +1975,7 @@
'-2'
];
-If you change bind type to __SQLT_INT__, like:
+If you change bind type to __SQLT\_INT__, like:
ora_internal_type => SQLT_INT
@@ -2001,135 +1990,135 @@
-2
];
-## __bind_param_inout_array__
+## __bind\_param\_inout\_array__
-DBD::Oracle supports this undocumented feature of DBI. See [Returning A Value from an INSERT](#pod_Returning A Value from an INSERT) for an example.
+DBD::Oracle supports this undocumented feature of DBI. See ["Returning A Value from an INSERT"](#Returning A Value from an INSERT) for an example.
-## __bind_param_array__
+## __bind\_param\_array__
- $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
- $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
- $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
+ $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
+ $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
+ $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
Binds an array of values to a placeholder, so that each is used in turn by a call
-to the [execute_array](#pod_execute_array) method.
+to the ["execute\_array"](#execute\_array) method.
## __execute__
- $rv = $sth->execute(@bind_values);
+ $rv = $sth->execute(@bind_values);
Perform whatever processing is necessary to execute the prepared statement.
-## __execute_array__
+## __execute\_array__
- $tuples = $sth->execute_array() or die $sth->errstr;
- $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
- $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
+ $tuples = $sth->execute_array() or die $sth->errstr;
+ $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
+ $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
- ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
- ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
+ ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
+ ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
Execute a prepared statement once for each item in a passed-in hashref, or items that
-were previously bound via the [bind_param_array](#pod_bind_param_array) method. See the DBI documentation
+were previously bound via the ["bind\_param\_array"](#bind\_param\_array) method. See the DBI documentation
for more details.
DBD::Oracle takes full advantage of OCI's array interface so inserts and updates using this interface will run very
quickly.
-## __execute_for_fetch__
+## __execute\_for\_fetch__
- $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
- $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
+ $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
+ $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
- ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
- ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
+ ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
+ ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
-Used internally by the [execute_array](#pod_execute_array) method, and rarely used directly. See the
+Used internally by the ["execute\_array"](#execute\_array) method, and rarely used directly. See the
DBI documentation for more details.
-## __fetchrow_arrayref__
+## __fetchrow\_arrayref__
- $ary_ref = $sth->fetchrow_arrayref;
+ $ary_ref = $sth->fetchrow_arrayref;
Fetches the next row of data from the statement handle, and returns a reference to an array
holding the column values. Any columns that are NULL are returned as undef within the array.
If there are no more rows or if an error occurs, the this method return undef. You should
-check `$sth->err` afterwards (or use the [RaiseError](#pod_RaiseError) attribute) to discover if the undef returned
+check `$sth->err` afterwards (or use the ["RaiseError"](#RaiseError) attribute) to discover if the undef returned
was due to an error.
Note that the same array reference is returned for each fetch, so don't store the reference and
then use it after a later fetch. Also, the elements of the array are also reused for each row,
-so take care if you want to take a reference to an element. See also [bind_columns](#pod_bind_columns).
+so take care if you want to take a reference to an element. See also ["bind\_columns"](#bind\_columns).
-## __fetchrow_array__
+## __fetchrow\_array__
- @ary = $sth->fetchrow_array;
+ @ary = $sth->fetchrow_array;
-Similar to the [fetchrow_arrayref](#pod_fetchrow_arrayref) method, but returns a list of column information rather than
+Similar to the ["fetchrow\_arrayref"](#fetchrow\_arrayref) method, but returns a list of column information rather than
a reference to a list. Do not use this in a scalar context.
-## __fetchrow_hashref__
+## __fetchrow\_hashref__
- $hash_ref = $sth->fetchrow_hashref;
- $hash_ref = $sth->fetchrow_hashref($name);
+ $hash_ref = $sth->fetchrow_hashref;
+ $hash_ref = $sth->fetchrow_hashref($name);
Fetches the next row of data and returns a hashref containing the name of the columns as the keys
and the data itself as the values. Any NULL value is returned as as undef value.
If there are no more rows or if an error occurs, the this method return undef. You should
-check `$sth->err` afterwards (or use the [RaiseError](#pod_RaiseError) attribute) to discover if the undef returned
+check `$sth->err` afterwards (or use the ["RaiseError"](#RaiseError) attribute) to discover if the undef returned
was due to an error.
The optional `$name` argument should be either `NAME`, `NAME_lc` or `NAME_uc`, and indicates
what sort of transformation to make to the keys in the hash. By default Oracle uses upper case.
-## __fetchall_arrayref__
+## __fetchall\_arrayref__
- $tbl_ary_ref = $sth->fetchall_arrayref();
- $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
- $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
+ $tbl_ary_ref = $sth->fetchall_arrayref();
+ $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
+ $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
Returns a reference to an array of arrays that contains all the remaining rows to be fetched from the
statement handle. If there are no more rows, an empty arrayref will be returned. If an error occurs,
the data read in so far will be returned. Because of this, you should always check `$sth->err` after
-calling this method, unless [RaiseError](#pod_RaiseError) has been enabled.
+calling this method, unless ["RaiseError"](#RaiseError) has been enabled.
-If `$slice` is an array reference, fetchall_arrayref uses the [fetchrow_arrayref](#pod_fetchrow_arrayref) method to fetch each
+If `$slice` is an array reference, fetchall\_arrayref uses the ["fetchrow\_arrayref"](#fetchrow\_arrayref) method to fetch each
row as an array ref. If the `$slice` array is not empty then it is used as a slice to select individual
columns by perl array index number (starting at 0, unlike column and parameter numbers which start at 1).
-With no parameters, or if $slice is undefined, fetchall_arrayref acts as if passed an empty array ref.
+With no parameters, or if $slice is undefined, fetchall\_arrayref acts as if passed an empty array ref.
-If `$slice` is a hash reference, fetchall_arrayref uses [fetchrow_hashref](#pod_fetchrow_hashref) to fetch each row as a hash reference.
+If `$slice` is a hash reference, fetchall\_arrayref uses ["fetchrow\_hashref"](#fetchrow\_hashref) to fetch each row as a hash reference.
See the DBI documentation for a complete discussion.
-## __fetchall_hashref__
+## __fetchall\_hashref__
- $hash_ref = $sth->fetchall_hashref( $key_field );
+ $hash_ref = $sth->fetchall_hashref( $key_field );
Returns a hashref containing all rows to be fetched from the statement handle. See the DBI documentation for
a full discussion.
## __finish__
- $rv = $sth->finish;
+ $rv = $sth->finish;
Indicates to DBI that you are finished with the statement handle and are not going to use it again. Only needed
when you have not fetched all the possible rows.
## __rows__
- $rv = $sth->rows;
+ $rv = $sth->rows;
Returns the number of rows affected for updates, deletes and inserts and -1 for selects.
-## __bind_col__
+## __bind\_col__
- $rv = $sth->bind_col($column_number, \$var_to_bind);
- $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
- $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
+ $rv = $sth->bind_col($column_number, \$var_to_bind);
+ $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
+ $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
Binds a Perl variable and/or some attributes to an output column of a SELECT statement.
Column numbers count up from 1. You do not need to bind output columns in order to fetch data.
@@ -2142,15 +2131,15 @@
See the DBI documentation for a discussion of the optional parameters `\%attr` and `$bind_type`
-## __bind_columns__
+## __bind\_columns__
- $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
+ $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
-Calls the [bind_col](#pod_bind_col) method for each column in the SELECT statement, using the supplied list.
+Calls the ["bind\_col"](#bind\_col) method for each column in the SELECT statement, using the supplied list.
-## __dump_results__
+## __dump\_results__
- $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
+ $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
Fetches all the rows from the statement handle, calls `DBI::neat_list` for each row, and
prints the results to `$fh` (which defaults to `STDOUT`). Rows are separated by `$lsep` (which defaults
@@ -2158,19 +2147,19 @@
how wide the output can be, and defaults to 35.
This method is designed as a handy utility for prototyping and testing queries. Since it uses
-"neat_list" to format and edit the string for reading by humans, it is not recommended
+"neat\_list" to format and edit the string for reading by humans, it is not recommended
for data transfer applications.
# STATEMENT HANDLE ATTRIBUTES
-## __NUM_OF_FIELDS__ (integer, read-only)
+## __NUM\_OF\_FIELDS__ (integer, read-only)
Returns the number of columns returned by the current statement. A number will only be returned for
SELECT statements for INSERT,
UPDATE, and DELETE statements which contain a RETURNING clause.
This method returns undef if called before `execute()`.
-## __NUM_OF_PARAMS__ (integer, read-only)
+## __NUM\_OF\_PARAMS__ (integer, read-only)
Returns the number of placeholders in the current statement.
@@ -2181,25 +2170,25 @@
INSERT, UPDATE, and DELETE statements which contain a RETURNING clause.
This method returns undef if called before `execute()`.
-## __NAME_lc__ (arrayref, read-only)
+## __NAME\_lc__ (arrayref, read-only)
The same as the `NAME` attribute, except that all column names are forced to lower case.
-## __NAME_uc__ (arrayref, read-only)
+## __NAME\_uc__ (arrayref, read-only)
The same as the `NAME` attribute, except that all column names are forced to upper case.
-## __NAME_hash__ (hashref, read-only)
+## __NAME\_hash__ (hashref, read-only)
Similar to the `NAME` attribute, but returns a hashref of column names instead of an arrayref. The names of the columns
are the keys of the hash, and the values represent the order in which the columns are returned, starting at 0.
This method returns undef if called before `execute()`.
-## __NAME_lc_hash__ (hashref, read-only)
+## __NAME\_lc\_hash__ (hashref, read-only)
The same as the `NAME_hash` attribute, except that all column names are forced to lower case.
-## __NAME_uc_hash__ (hashref, read-only)
+## __NAME\_uc\_hash__ (hashref, read-only)
The same as the `NAME_hash` attribute, except that all column names are forced to lower case.
@@ -2240,17 +2229,17 @@
be the numbers, without the dollar signs. If the "question mark" type is used, integer numbers will be returned,
starting at one and increasing for every placeholder.
-If this method is called before [execute](#pod_execute), the literal values passed in are returned. If called after
-[execute](#pod_execute), then the quoted versions of the values are returned.
+If this method is called before ["execute"](#execute), the literal values passed in are returned. If called after
+["execute"](#execute), then the quoted versions of the values are returned.
## __ParamTypes__ (hash ref, read-only)
Returns a reference to a hash containing the type names currently bound to placeholders. The keys
are the same as returned by the ParamValues method. The values are hashrefs containing a single key value
-pair, in which the key is either 'TYPE' if the type has a generic SQL equivalent, and 'pg_type' if the type can
+pair, in which the key is either 'TYPE' if the type has a generic SQL equivalent, and 'pg\_type' if the type can
only be expressed by a Postgres type. The value is the internal number corresponding to the type originally
passed in. (Placeholders that have not yet been bound will return undef as the value). This allows the output of
-ParamTypes to be passed back to the [bind_param](#pod_bind_param) method.
+ParamTypes to be passed back to the ["bind\_param"](#bind\_param) method.
## __Statement__ (string, read-only)
@@ -2284,14 +2273,14 @@
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);
-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;
+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});
+ $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
-When the statement is executed you will then be able to use 'ora_fetch_scroll' method to get a row
+When the statement is executed you will then be able to use 'ora\_fetch\_scroll' method to get a row
or you can still use any of the other fetch methods but with a poorer response time than if you used a
non-scrolling cursor. As well scrollable cursors are compatible with any applicable bind methods.
@@ -2299,199 +2288,199 @@
The following driver-specific methods are used with scrollable cursors.
-- ora_scroll_position
+- ora\_scroll\_position
- $position = $sth->ora_scroll_position();
+ $position = $sth->ora_scroll_position();
This method returns the current position (row number) attribute of the result set. Prior to the first fetch this value is 0. This is the only time
this value will be 0 after the first fetch the value will be set, so you can use this value to test if any rows have been fetched.
The minimum value will always be 1 after the first fetch. The maximum value will always be the total number of rows in the record set.
-- ora_fetch_scroll
+- ora\_fetch\_scroll
- $ary_ref = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
+ $ary_ref = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
-Works the same as `fetchrow_arrayref`, excepts one passes in a 'Fetch Orientation' constant and a fetch_offset
+Works the same as `fetchrow_arrayref`, excepts 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.
The valid orientation constant and fetch offset values combination are detailed below
- OCI_FETCH_CURRENT, fetches the current row, the fetch offset value is ignored.
- OCI_FETCH_NEXT, fetches the next row from the current position, the fetch offset value
- is ignored.
- OCI_FETCH_FIRST, fetches the first row, the fetch offset value is ignored.
- OCI_FETCH_LAST, fetches the last row, the fetch offset value is ignored.
- OCI_FETCH_PRIOR, fetches the previous row from the current position, the fetch offset
- value is ignored.
-
- OCI_FETCH_ABSOLUTE, fetches the row that is specified by the fetch offset value.
-
- OCI_FETCH_ABSOLUTE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_FIRST.
- OCI_FETCH_ABSOLUTE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
-
- OCI_FETCH_RELATIVE, fetches the row relative from the current position as specified by the
- fetch offset value.
-
- OCI_FETCH_RELATIVE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
- OCI_FETCH_RELATIVE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_NEXT.
- OCI_FETCH_RELATIVE, and a fetch offset value of -1 is equivalent to a OCI_FETCH_PRIOR.
-
-The effect that a ora_fetch_scroll method call has on the current_positon attribute is detailed below.
-
- OCI_FETCH_CURRENT, has no effect on the current_positon attribute.
- OCI_FETCH_NEXT, increments current_positon attribute by 1
- OCI_FETCH_NEXT, when at the last row in the record set does not change current_positon
- attribute, it is equivalent to a OCI_FETCH_CURRENT
- OCI_FETCH_FIRST, sets the current_positon attribute to 1.
- OCI_FETCH_LAST, sets the current_positon attribute to the total number of rows in the
- record set.
- OCI_FETCH_PRIOR, decrements current_positon attribute by 1.
- OCI_FETCH_PRIOR, when at the first row in the record set does not change current_positon
- attribute, it is equivalent to a OCI_FETCH_CURRENT.
-
- OCI_FETCH_ABSOLUTE, sets the current_positon attribute to the fetch offset value.
- OCI_FETCH_ABSOLUTE, and a fetch offset value that is less than 1 does not change
- current_positon attribute, it is equivalent to a OCI_FETCH_CURRENT.
- OCI_FETCH_ABSOLUTE, and a fetch offset value that is greater than the number of records in
- the record set, does not change current_positon attribute, it is
- equivalent to a OCI_FETCH_CURRENT.
- OCI_FETCH_RELATIVE, sets the current_positon attribute to (current_positon attribute +
- fetch offset value).
- OCI_FETCH_RELATIVE, and a fetch offset value that makes the current position less than 1,
- does not change fetch offset value so it is equivalent to a OCI_FETCH_CURRENT.
- OCI_FETCH_RELATIVE, and a fetch offset value that makes it greater than the number of records
- in the record set, does not change fetch offset value so it is equivalent
- to a OCI_FETCH_CURRENT.
-
-The effects of the differing orientation constants on the first fetch (current_postion attribute at 0) are as follows.
-
- OCI_FETCH_CURRENT, dose not fetch a row or change the current_positon attribute.
- OCI_FETCH_FIRST, fetches row 1 and sets the current_positon attribute to 1.
- OCI_FETCH_LAST, fetches the last row in the record set and sets the current_positon
- attribute to the total number of rows in the record set.
- OCI_FETCH_NEXT, equivalent to a OCI_FETCH_FIRST.
- OCI_FETCH_PRIOR, equivalent to a OCI_FETCH_CURRENT.
-
- OCI_FETCH_ABSOLUTE, and a fetch offset value that is less than 1 is equivalent to a
- OCI_FETCH_CURRENT.
- OCI_FETCH_ABSOLUTE, and a fetch offset value that is greater than the number of
- records in the record set is equivalent to a OCI_FETCH_CURRENT.
- OCI_FETCH_RELATIVE, and a fetch offset value that is less than 1 is equivalent
- to a OCI_FETCH_CURRENT.
- OCI_FETCH_RELATIVE, and a fetch offset value that makes it greater than the number
- of records in the record set, is equivalent to a OCI_FETCH_CURRENT.
+ OCI_FETCH_CURRENT, fetches the current row, the fetch offset value is ignored.
+ OCI_FETCH_NEXT, fetches the next row from the current position, the fetch offset value
+ is ignored.
+ OCI_FETCH_FIRST, fetches the first row, the fetch offset value is ignored.
+ OCI_FETCH_LAST, fetches the last row, the fetch offset value is ignored.
+ OCI_FETCH_PRIOR, fetches the previous row from the current position, the fetch offset
+ value is ignored.
+
+ OCI_FETCH_ABSOLUTE, fetches the row that is specified by the fetch offset value.
+
+ OCI_FETCH_ABSOLUTE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_FIRST.
+ OCI_FETCH_ABSOLUTE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
+
+ OCI_FETCH_RELATIVE, fetches the row relative from the current position as specified by the
+ fetch offset value.
+
+ OCI_FETCH_RELATIVE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
+ OCI_FETCH_RELATIVE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_NEXT.
+ OCI_FETCH_RELATIVE, and a fetch offset value of -1 is equivalent to a OCI_FETCH_PRIOR.
+
+The effect that a ora\_fetch\_scroll method call has on the current\_positon attribute is detailed below.
+
+ OCI_FETCH_CURRENT, has no effect on the current_positon attribute.
+ OCI_FETCH_NEXT, increments current_positon attribute by 1
+ OCI_FETCH_NEXT, when at the last row in the record set does not change current_positon
+ attribute, it is equivalent to a OCI_FETCH_CURRENT
+ OCI_FETCH_FIRST, sets the current_positon attribute to 1.
+ OCI_FETCH_LAST, sets the current_positon attribute to the total number of rows in the
+ record set.
+ OCI_FETCH_PRIOR, decrements current_positon attribute by 1.
+ OCI_FETCH_PRIOR, when at the first row in the record set does not change current_positon
+ attribute, it is equivalent to a OCI_FETCH_CURRENT.
+
+ OCI_FETCH_ABSOLUTE, sets the current_positon attribute to the fetch offset value.
+ OCI_FETCH_ABSOLUTE, and a fetch offset value that is less than 1 does not change
+ current_positon attribute, it is equivalent to a OCI_FETCH_CURRENT.
+ OCI_FETCH_ABSOLUTE, and a fetch offset value that is greater than the number of records in
+ the record set, does not change current_positon attribute, it is
+ equivalent to a OCI_FETCH_CURRENT.
+ OCI_FETCH_RELATIVE, sets the current_positon attribute to (current_positon attribute +
+ fetch offset value).
+ OCI_FETCH_RELATIVE, and a fetch offset value that makes the current position less than 1,
+ does not change fetch offset value so it is equivalent to a OCI_FETCH_CURRENT.
+ OCI_FETCH_RELATIVE, and a fetch offset value that makes it greater than the number of records
+ in the record set, does not change fetch offset value so it is equivalent
+ to a OCI_FETCH_CURRENT.
+
+The effects of the differing orientation constants on the first fetch (current\_postion attribute at 0) are as follows.
+
+ OCI_FETCH_CURRENT, dose not fetch a row or change the current_positon attribute.
+ OCI_FETCH_FIRST, fetches row 1 and sets the current_positon attribute to 1.
+ OCI_FETCH_LAST, fetches the last row in the record set and sets the current_positon
+ attribute to the total number of rows in the record set.
+ OCI_FETCH_NEXT, equivalent to a OCI_FETCH_FIRST.
+ OCI_FETCH_PRIOR, equivalent to a OCI_FETCH_CURRENT.
+
+ OCI_FETCH_ABSOLUTE, and a fetch offset value that is less than 1 is equivalent to a
+ OCI_FETCH_CURRENT.
+ OCI_FETCH_ABSOLUTE, and a fetch offset value that is greater than the number of
+ records in the record set is equivalent to a OCI_FETCH_CURRENT.
+ OCI_FETCH_RELATIVE, and a fetch offset value that is less than 1 is equivalent
+ to a OCI_FETCH_CURRENT.
+ OCI_FETCH_RELATIVE, and a fetch offset value that makes it greater than the number
+ of records in the record set, is equivalent to a OCI_FETCH_CURRENT.
## Scrollable Cursor Usage
Given a simple code like this:
- use DBI;
- use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
- my $dbh = DBI->connect($dsn, $dbuser, '');
- my $SQL = "select id,
- first_name,
- last_name
- from employee";
- my $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
- $sth->execute();
- my $value;
+ use DBI;
+ use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
+ my $dbh = DBI->connect($dsn, $dbuser, '');
+ my $SQL = "select id,
+ first_name,
+ last_name
+ from employee";
+ 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 from the query is 20, the code snippets below will illustrate the use of ora_fetch_scroll
+and one assumes that the number of rows returned from the query is 20, the code snippets below will illustrate the use of ora\_fetch\_scroll
method;
- 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";
+ $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";
-The current_positon attribute to will be 20 after this snippet. This is also a way to get the number of rows in the record set, however,
+The current\_positon attribute to will be 20 after this snippet. This is also a way to get the number of rows in the record set, however,
if the record set is large this could take some time.
- 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";
+ $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";
-The current_positon attribute will still be 20 after this snippet.
+The current\_positon attribute will still be 20 after this snippet.
- 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";
+ $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";
-The current_positon attribute will be 1 after this snippet.
+The current\_positon attribute will be 1 after this snippet.
- Fetching the Next Row
- 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";
+ 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";
-The current_positon attribute will be 5 after this snippet.
+The current\_positon attribute will be 5 after this snippet.
- 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";
+ 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";
-The current_positon attribute will be 1 after this snippet.
+The current\_positon attribute will be 1 after this snippet.
- 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";
+ $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";
-The current_positon attribute will be 10 after this snippet.
+The current\_positon attribute will be 10 after this snippet.
- 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";
+ 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";
-The current_positon attribute will be 14 after this snippet.
+The current\_positon attribute will be 14 after this snippet.
- 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";
+ 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";
-The current_positon attribute will be 10 after this snippet.
+The current\_positon attribute will be 10 after this snippet.
- 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";
+ $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";
-The current_positon attribute will be 15 after this snippet.
+The current\_positon attribute will be 15 after this snippet.
- 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";
+ $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";
-The current_positon attribute will be 6 after this snippet.
+The current\_positon attribute will be 6 after this snippet.
- Use Finish
- $sth->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 cancelled on the server. If you do not do this you may cause resource problems on your database.
@@ -2505,16 +2494,16 @@
DBD::Oracle now offers three interfaces to LOB and LONG data,
-- [Data Interface for Persistent LOBs](#pod_Data Interface for Persistent LOBs)
+- ["Data Interface for Persistent LOBs"](#Data Interface for Persistent LOBs)
With this interface DBD::Oracle handles your data directly utilizing regular OCI calls, Oracle itself takes care of the LOB Locator operations in the case of
BLOBs and CLOBs treating them exactly as if they were the same as the legacy LONG or LONG RAW types.
-- [Data Interface for LOB Locators](#pod_Data Interface for LOB Locators)
+- ["Data Interface for LOB Locators"](#Data Interface for LOB Locators)
With this interface DBD::Oracle handles your data utilizing LOB Locator OCI calls so it only works with CLOB and BLOB datatypes. With this interface DBD::Oracle takes care of the LOB Locator operations for you.
-- [LOB Locator Method Interface](#pod_LOB Locator Method Interface)
+- ["LOB Locator Method Interface"](#LOB Locator Method Interface)
This allows the user direct access to the LOB Locator methods, so you have to take case of the LOB Locator operations yourself.
@@ -2528,15 +2517,15 @@
The table cell is created, but the cell holds no locator or value.
If your LOB field is in this state then there is no LOB Locator that DBD::Oracle can work so if your encounter a
- DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr
+ DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr
error when working with a LOB.
-You can correct this by using an SQL UPDATE statement to reset the LOB column to a non-NULL (or empty LOB) value with either EMPTY_BLOB or EMPTY_CLOB as in this example;
+You can correct this by using an SQL UPDATE statement to reset the LOB column to a non-NULL (or empty LOB) value with either EMPTY\_BLOB or EMPTY\_CLOB as in this example;
- UPDATE lob_example
- SET bindata=EMPTY_BLOB()
- WHERE bindata IS NULL.
+ UPDATE lob_example
+ SET bindata=EMPTY_BLOB()
+ WHERE bindata IS NULL.
- Empty
@@ -2563,64 +2552,64 @@
For example give this table;
- CREATE TABLE test_long (
- id NUMBER,
+ CREATE TABLE test_long (
+ id NUMBER,
long1 long)
this code;
- $dbh->{LongReadLen} = 2*1024*1024; #2 meg
- $SQL='select p_id,long1 from test_long';
- $sth=$dbh->prepare($SQL);
- $sth->execute();
- while (my ( $p_id,$long )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "long=".$long."\n";
- }
+ $dbh->{LongReadLen} = 2*1024*1024; #2 meg
+ $SQL='select p_id,long1 from test_long';
+ $sth=$dbh->prepare($SQL);
+ $sth->execute();
+ while (my ( $p_id,$long )=$sth->fetchrow()){
+ print "p_id=".$p_id."\n";
+ print "long=".$long."\n";
+ }
Will select out all of the long1 fields in the table as long as they are all under 2MB in length. A value in long1 longer than this will throw an error. Adding this line;
- $dbh->{LongTruncOk}=1;
+ $dbh->{LongTruncOk}=1;
before the execute will return all the long1 fields but they will be truncated at 2MBs.
-### Using ora_ncs_buff_mtpl
+### Using ora\_ncs\_buff\_mtpl
When getting CLOBs and NCLOBs in or out of Oracle, the Server will translate from the Server's NCharSet to the
Client's. If they happen to be the same or at least compatible then all of these actions are a 1 char to 1 char bases.
-Thus if you set your LongReadLen buffer to 10_000_000 you will get up to 10_000_000 char.
+Thus if you set your LongReadLen buffer to 10\_000\_000 you will get up to 10\_000\_000 char.
However if the Server has to translate from one NCharSet to another it will use bytes for conversion. The buffer
-value is set to 4 * LONG_READ_LEN which was very wasteful as you might only be asking for 10_000_000 bytes
-but you were actually using 40_000_000 bytes of buffer under the hood. You would still get 10_000_000 bytes
+value is set to 4 \* LONG\_READ\_LEN which was very wasteful as you might only be asking for 10\_000\_000 bytes
+but you were actually using 40\_000\_000 bytes of buffer under the hood. You would still get 10\_000\_000 bytes
(maybe less characters though) but you are using allot more memory that you need.
-You can now customize the size of the buffer by setting the 'ora_ncs_buff_mtpl' either on the connection or statement handle. You can
-also set this as 'ORA_DBD_NCS_BUFFER' OS environment variable so you will have to go back and change all your code if you are getting into trouble.
+You can now customize the size of the buffer by setting the 'ora\_ncs\_buff\_mtpl' either on the connection or statement handle. You can
+also set this as 'ORA\_DBD\_NCS\_BUFFER' OS environment variable so you will have to go back and change all your code if you are getting into trouble.
The default value is still set to 4 for backward compatibility. You can lower this value and thus increase the amount of data you can retrieve. If the
-ora_ncs_buff_mtpl is too small DBD::Oracle will throw and error telling you to increase this buffer by one.
+ora\_ncs\_buff\_mtpl is too small DBD::Oracle will throw and error telling you to increase this buffer by one.
If the error is not captured then you may get at some random point later on, usually at a finish() or disconnect() or even a fetch() this error;
- ORA-03127: no new operations allowed until the active operation ends
+ ORA-03127: no new operations allowed until the active operation ends
This is one of the more obscure ORA errors (have some fun and report it to Meta-Link they will scratch their heads for hours)
-If you get this, simply increment the ora_ncs_buff_mtpl by one until it goes away.
+If you get this, simply increment the ora\_ncs\_buff\_mtpl by one until it goes away.
This should greatly increase your ability to select very large CLOBs or NCLOBs, by freeing up a large block of memory.
-You can tune this value by setting ora_oci_success_warn which will display the following
+You can tune this value by setting ora\_oci\_success\_warn which will display the following
- OCILobRead field 2 of 3 SUCCESS: csform 1 (SQLCS_IMPLICIT), LOBlen 10240(characters), LongReadLen
- 20(characters), BufLen 80(characters), Got 28(characters)
+ OCILobRead field 2 of 3 SUCCESS: csform 1 (SQLCS_IMPLICIT), LOBlen 10240(characters), LongReadLen
+ 20(characters), BufLen 80(characters), Got 28(characters)
-In the case above the query Got 28 characters (well really only 20 characters of 28 bytes) so we could use ora_ncs_buff_mtpl=>2 (20*2=40) thus saving 40bytes of memory.
+In the case above the query Got 28 characters (well really only 20 characters of 28 bytes) so we could use ora\_ncs\_buff\_mtpl=>2 (20\*2=40) thus saving 40bytes of memory.
### Simple Fetch for CLOBs and BLOBs
-To use this interface for CLOBs and LOBs datatypes set the 'ora_pers_lob' attribute of the statement handle to '1' with the prepare method, as well
+To use this interface for CLOBs and LOBs datatypes set the 'ora\_pers\_lob' attribute of the statement handle to '1' with the prepare method, as well
set the database handle's 'LongReadLen' attribute to a value that will be the larger than the expected size of the LOB. If the size of the LOB exceeds
the 'LongReadLen' DBD::Oracle will return a 'ORA-24345: A Truncation' error. To stop this set the database handle's 'LongTruncOk' attribute to '1'.
The maximum value of 'LongReadLen' seems to be dependent on the physical memory limits of the box that Oracle is running on in the same way that LONGs and LONG RAWs are.
@@ -2632,155 +2621,155 @@
For example give this table;
- CREATE TABLE test_lob (id NUMBER,
- clob1 CLOB,
- clob2 CLOB,
- blob1 BLOB,
- blob2 BLOB)
+ CREATE TABLE test_lob (id NUMBER,
+ clob1 CLOB,
+ clob2 CLOB,
+ blob1 BLOB,
+ blob2 BLOB)
this code;
- $dbh->{LongReadLen} = 2*1024*1024; #2 meg
- $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
- $sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
- $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";
- }
+ $dbh->{LongReadLen} = 2*1024*1024; #2 meg
+ $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
+ $sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
+ $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";
+ }
Will select out all of the LOBs in the table as long as they are all under 2MB in length. Longer lobs will throw an error. Adding this line;
- $dbh->{LongTruncOk}=1;
+ $dbh->{LongTruncOk}=1;
before the execute will return all the lobs but they will be truncated at 2MBs.
### Piecewise Fetch with Callback
With a piecewise callback fetch DBD::Oracle sets up a function that will 'callback' to the DB during the fetch and gets your LOB (LONG, LONG RAW, CLOB, BLOB) piece by piece.
-To use this interface set the 'ora_clbk_lob' attribute of the statement handle to '1' with the prepare method. Next set the 'ora_piece_size' to the size of the piece that
+To use this interface set the 'ora\_clbk\_lob' attribute of the statement handle to '1' with the prepare method. Next set the 'ora\_piece\_size' to the size of the piece that
you want to return on the callback. Finally set the database handle's 'LongReadLen' attribute to a value that will be the larger than the expected
-size of the LOB. Like the [Simple Fetch for LONGs and LONG RAWs](#pod_Simple Fetch for LONGs and LONG RAWs) and [Simple Fetch for CLOBs and BLOBs](#pod_Simple Fetch for CLOBs and BLOBs) the if the size of the LOB exceeds the is 'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB
-or set the 'LongReadLen' to a higher value. With this interface the value of 'ora_piece_size' seems to be constrained by the same memory limit as found on
-the Simple Fetch interface. If you encounter an 'ORA-01062' error try setting the value of 'ora_piece_size' to a smaller value. The value for 'LongReadLen' is
+size of the LOB. Like the ["Simple Fetch for LONGs and LONG RAWs"](#Simple Fetch for LONGs and LONG RAWs) and ["Simple Fetch for CLOBs and BLOBs"](#Simple Fetch for CLOBs and BLOBs) the if the size of the LOB exceeds the is 'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB
+or set the 'LongReadLen' to a higher value. With this interface the value of 'ora\_piece\_size' seems to be constrained by the same memory limit as found on
+the Simple Fetch interface. If you encounter an 'ORA-01062' error try setting the value of 'ora\_piece\_size' to a smaller value. The value for 'LongReadLen' is
dependent on the version and settings of the Oracle DB you are using. In theory it ranges from 8GBs
in 9iR1 up to 128 terabytes with 11g but you will also be limited by the physical memory of your PERL instance.
Using the table from the last example this code;
- $dbh->{LongReadLen} = 20*1024*1024; #20 meg
- $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
- $sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
- $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";
- }
+ $dbh->{LongReadLen} = 20*1024*1024; #20 meg
+ $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
+ $sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
+ $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";
+ }
-Will select out all of the LOBs in the table as long as they are all under 20MB in length. If the LOB is longer than 5MB (ora_piece_size) DBD::Oracle will fetch it in at least 2 pieces to a
-maximum of 4 pieces (4*5MB=20MB). Like the Simple Fetch examples Lobs longer than 20MB will throw an error.
+Will select out all of the LOBs in the table as long as they are all under 20MB in length. If the LOB is longer than 5MB (ora\_piece\_size) DBD::Oracle will fetch it in at least 2 pieces to a
+maximum of 4 pieces (4\*5MB=20MB). Like the Simple Fetch examples Lobs longer than 20MB will throw an error.
Using the table from the first example (LONG) this code;
- $dbh->{LongReadLen} = 20*1024*1024; #2 meg
- $SQL='select p_id,long1 from test_long';
- $sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
- $sth->execute();
- while (my ( $p_id,$long )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "long=".$long."\n";
- }
+ $dbh->{LongReadLen} = 20*1024*1024; #2 meg
+ $SQL='select p_id,long1 from test_long';
+ $sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
+ $sth->execute();
+ while (my ( $p_id,$long )=$sth->fetchrow()){
+ print "p_id=".$p_id."\n";
+ print "long=".$long."\n";
+ }
-Will select all of the long1 fields from table as long as they are is under 20MB in length. If the long1 filed is longer than 5MB (ora_piece_size) DBD::Oracle will fetch it in at least 2 pieces to a
-maximum of 4 pieces (4*5MB=20MB). Like the other examples long1 fields longer than 20MB will throw an error.
+Will select all of the long1 fields from table as long as they are is under 20MB in length. If the long1 filed is longer than 5MB (ora\_piece\_size) DBD::Oracle will fetch it in at least 2 pieces to a
+maximum of 4 pieces (4\*5MB=20MB). Like the other examples long1 fields longer than 20MB will throw an error.
#### Piecewise Fetch with Polling
-With a polling piecewise fetch DBD::Oracle iterates (Polls) over the LOB during the fetch getting your LOB (LONG, LONG RAW, CLOB, BLOB) piece by piece. To use this interface set the 'ora_piece_lob'
-attribute of the statement handle to '1' with the prepare method. Next set the 'ora_piece_size' to the size of the piece that
+With a polling piecewise fetch DBD::Oracle iterates (Polls) over the LOB during the fetch getting your LOB (LONG, LONG RAW, CLOB, BLOB) piece by piece. To use this interface set the 'ora\_piece\_lob'
+attribute of the statement handle to '1' with the prepare method. Next set the 'ora\_piece\_size' to the size of the piece that
you want to return on the callback. Finally set the database handle's 'LongReadLen' attribute to a value that will be the larger than the expected
-size of the LOB. Like the [Piecewise Fetch with Callback](#pod_Piecewise Fetch with Callback) and Simple Fetches if the size of the LOB exceeds the is 'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB
-or set the 'LongReadLen' to a higher value. With this interface the value of 'ora_piece_size' seems to be constrained by the same memory limit as found on
-the [Piecewise Fetch with Callback](#pod_Piecewise Fetch with Callback).
+size of the LOB. Like the ["Piecewise Fetch with Callback"](#Piecewise Fetch with Callback) and Simple Fetches if the size of the LOB exceeds the is 'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB
+or set the 'LongReadLen' to a higher value. With this interface the value of 'ora\_piece\_size' seems to be constrained by the same memory limit as found on
+the ["Piecewise Fetch with Callback"](#Piecewise Fetch with Callback).
Using the table from the example above this code;
- $dbh->{LongReadLen} = 20*1024*1024; #20 meg
- $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
- $sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
- $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";
- }
+ $dbh->{LongReadLen} = 20*1024*1024; #20 meg
+ $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
+ $sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
+ $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";
+ }
-Will select out all of the LOBs in the table as long as they are all under 20MB in length. If the LOB is longer than 5MB (ora_piece_size) DBD::Oracle will fetch it in at least 2 pieces to a
-maximum of 4 pieces (4*5MB=20MB). Like the other fetch methods LOBs longer than 20MB will throw an error.
+Will select out all of the LOBs in the table as long as they are all under 20MB in length. If the LOB is longer than 5MB (ora\_piece\_size) DBD::Oracle will fetch it in at least 2 pieces to a
+maximum of 4 pieces (4\*5MB=20MB). Like the other fetch methods LOBs longer than 20MB will throw an error.
Finally with this code;
- $dbh->{LongReadLen} = 20*1024*1024; #2 meg
- $SQL='select p_id,long1 from test_long';
- $sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
- $sth->execute();
- while (my ( $p_id,$long )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "long=".$long."\n";
- }
+ $dbh->{LongReadLen} = 20*1024*1024; #2 meg
+ $SQL='select p_id,long1 from test_long';
+ $sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
+ $sth->execute();
+ while (my ( $p_id,$long )=$sth->fetchrow()){
+ print "p_id=".$p_id."\n";
+ print "long=".$long."\n";
+ }
-Will select all of the long1 fields from table as long as they are is under 20MB in length. If the long1 field is longer than 5MB (ora_piece_size) DBD::Oracle will fetch it in at least 2 pieces to a
-maximum of 4 pieces (4*5MB=20MB). Like the other examples long1 fields longer than 20MB will throw an error.
+Will select all of the long1 fields from table as long as they are is under 20MB in length. If the long1 field is longer than 5MB (ora\_piece\_size) DBD::Oracle will fetch it in at least 2 pieces to a
+maximum of 4 pieces (4\*5MB=20MB). Like the other examples long1 fields longer than 20MB will throw an error.
### Binding for Updates and Inserts for CLOBs and BLOBs
To bind for updates and inserts all that is required to use this interface is to set the statement handle's prepare method
-'ora_type' attribute to 'SQLT_CHR' in the case of CLOBs and NCLOBs or 'SQLT_BIN' in the case of BLOBs as in this example for an insert;
+'ora\_type' attribute to 'SQLT\_CHR' in the case of CLOBs and NCLOBs or 'SQLT\_BIN' in the case of BLOBs as in this example for an insert;
- my $in_clob = "<document>\n";
- $in_clob .= " <value>$_</value>\n" for 1 .. 10_000;
- $in_clob .= "</document>\n";
- my $in_blob ="0101" for 1 .. 10_000;
-
- $SQL='insert into test_lob3@tpgtest (id,clob1,clob2, blob1,blob2) values(?,?,?,?,?)';
- $sth=$dbh->prepare($SQL );
- $sth->bind_param(1,3);
- $sth->bind_param(2,$in_clob,{ora_type=>SQLT_CHR});
- $sth->bind_param(3,$in_clob,{ora_type=>SQLT_CHR});
- $sth->bind_param(4,$in_blob,{ora_type=>SQLT_BIN});
- $sth->bind_param(5,$in_blob,{ora_type=>SQLT_BIN});
- $sth->execute();
+ my $in_clob = "<document>\n";
+ $in_clob .= " <value>$_</value>\n" for 1 .. 10_000;
+ $in_clob .= "</document>\n";
+ my $in_blob ="0101" for 1 .. 10_000;
+
+ $SQL='insert into test_lob3@tpgtest (id,clob1,clob2, blob1,blob2) values(?,?,?,?,?)';
+ $sth=$dbh->prepare($SQL );
+ $sth->bind_param(1,3);
+ $sth->bind_param(2,$in_clob,{ora_type=>SQLT_CHR});
+ $sth->bind_param(3,$in_clob,{ora_type=>SQLT_CHR});
+ $sth->bind_param(4,$in_blob,{ora_type=>SQLT_BIN});
+ $sth->bind_param(5,$in_blob,{ora_type=>SQLT_BIN});
+ $sth->execute();
So far the only limit reached with this form of insert is the LOBs must be under 2GB in size.
### Support for Remote LOBs;
-Starting with Oracle 10gR2 the interface for Persistent LOBs was expanded to support remote LOBs (access over a dblink). Given a database called 'lob_test' that has a 'LINK' defined like this;
+Starting with Oracle 10gR2 the interface for Persistent LOBs was expanded to support 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';
+ 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;
+to a remote database called 'test\_lobs', the following code will work;
- $dbh = DBI->connect('dbi:Oracle:','test@lob_test','test');
- $dbh->{LongReadLen} = 2*1024*1024; #2 meg
- $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs@link_test';
- $sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
- $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";
- }
+ $dbh = DBI->connect('dbi:Oracle:','test@lob_test','test');
+ $dbh->{LongReadLen} = 2*1024*1024; #2 meg
+ $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs@link_test';
+ $sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
+ $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;
@@ -2788,25 +2777,25 @@
so the following returns an error:
- SELECT t1.lobcol,
- a2.lobcol
- FROM t1,
- t2.lobcol@dbs2 a2 W
- WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);
+ SELECT t1.lobcol,
+ a2.lobcol
+ FROM t1,
+ t2.lobcol@dbs2 a2 W
+ WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);
as does:
- SELECT t1.lobcol
- FROM t1@dbs1
- UNION ALL
- SELECT t2.lobcol
- FROM t2@dbs2;
+ SELECT t1.lobcol
+ FROM t1@dbs1
+ UNION ALL
+ SELECT t2.lobcol
+ FROM t2@dbs2;
- DDL commands are not supported;
so the following returns an error:
- CREATE VIEW v AS SELECT lob_col FROM tab@dbs;
+ CREATE VIEW v AS SELECT lob_col FROM tab@dbs;
- Only binds and defines for data going into remote persistent LOBs are supported.
@@ -2814,19 +2803,19 @@
These statements all produce errors:
- SELECT foo() FROM table1@dbs2;
+ SELECT foo() FROM table1@dbs2;
- SELECT foo()@dbs INTO char_val FROM DUAL;
+ SELECT foo()@dbs INTO char_val FROM DUAL;
- SELECT XMLType().getclobval FROM table1@dbs2;
+ SELECT XMLType().getclobval FROM table1@dbs2;
- If the remote object is a view such as
- CREATE VIEW v AS SELECT foo() FROM ...
+ CREATE VIEW v AS SELECT foo() FROM ...
the following would not work:
- SELECT * FROM v@dbs2;
+ SELECT * FROM v@dbs2;
- Limited PL/SQL parameter passing
@@ -2837,7 +2826,7 @@
so the following returns an error:
- SELECT t1.lobcol as test, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 RETURNING test
+ SELECT t1.lobcol as test, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 RETURNING test
## Locator Data Interface
@@ -2858,11 +2847,11 @@
To insert or update a large LOB using a placeholder, DBD::Oracle has to
know in advance that it is a LOB type. So you need to say:
- $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });
+ $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });
-The ORA_CLOB and ORA_BLOB constants can be imported using
+The ORA\_CLOB and ORA\_BLOB constants can be imported using
- use DBD::Oracle qw(:ora_types);
+ use DBD::Oracle qw(:ora_types);
or use the corresponding integer values (112 and 113).
@@ -2872,23 +2861,23 @@
however, if there are multiple LOB fields of the same type in the table
then you need to tell it which field each LOB param relates to:
- $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });
+ $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });
There are some limitations inherent in the way DBD::Oracle makes typical
LOB operations simple by hiding the LOB Locator processing:
- - Can't read/write LOBs in chunks (except via DBMS_LOB.WRITEAPPEND in PL/SQL)
- - To INSERT a LOB, you need UPDATE privilege.
+ - Can't read/write LOBs in chunks (except via DBMS_LOB.WRITEAPPEND in PL/SQL)
+ - To INSERT a LOB, you need UPDATE privilege.
The alternative is to disable the automatic LOB Locator processing.
-If [ora_auto_lob](#pod_ora_auto_lob) is 0 in prepare(), you can fetch the LOB Locators and
-do all the work yourself using the ora_lob_*() methods.
-See the [Data Interface for LOB Locators](#pod_Data Interface for LOB Locators) section below.
+If ["ora\_auto\_lob"](#ora\_auto\_lob) is 0 in prepare(), you can fetch the LOB Locators and
+do all the work yourself using the ora\_lob\_\*() methods.
+See the ["Data Interface for LOB Locators"](#Data Interface for LOB Locators) section below.
### LOB support in PL/SQL
LOB Locators can be passed to PL/SQL calls by binding them to placeholders
-with the proper `ora_type`. If [ora_auto_lob](#pod_ora_auto_lob) is true, output LOB
+with the proper `ora_type`. If ["ora\_auto\_lob"](#ora\_auto\_lob) is true, output LOB
parameters will be automatically returned as strings.
If the Oracle driver has support for temporary LOBs (Oracle 9i and higher),
@@ -2896,8 +2885,8 @@
converted to LOBs.
Example:
- # Build a large XML document, bind it as a CLOB,
- # extract elements through PL/SQL and return as a CLOB
+ \# Build a large XML document, bind it as a CLOB,
+ \# extract elements through PL/SQL and return as a CLOB
# $dbh is a connected database handle
# output will be large
@@ -2931,7 +2920,7 @@
If you ever get an
- ORA-01691 unable to extend lob segment sss.ggg by nnn in tablespace ttt
+ ORA-01691 unable to extend lob segment sss.ggg by nnn in tablespace ttt
error, while attempting to insert a LOB, this means the Oracle user has insufficient space for LOB you are trying to insert.
One solution it to use "alter database datafile 'sss.ggg' resize Mnnn" to increase the available memory for LOBs.
@@ -2957,12 +2946,12 @@
Not all of the Persistent interface has been implemented yet, the following are not supported;
- 1) Piecewise, polling and callback binds for INSERT and UPDATE operations.
- 2) Piecewise array binds for SELECT, INSERT and UPDATE operations.
+ 1) Piecewise, polling and callback binds for INSERT and UPDATE operations.
+ 2) Piecewise array binds for SELECT, INSERT and UPDATE operations.
-Most of the time you should just use the [Locator Data Interface](#pod_Locator Data Interface) as this is in one that has the best combination of speed and size.
+Most of the time you should just use the ["Locator Data Interface"](#Locator Data Interface) as this is in one that has the best combination of speed and size.
-All this being said if you are doing some critical programming I would use the [Data Interface for LOB Locators](#pod_Data Interface for LOB Locators) as this gives you very
+All this being said if you are doing some critical programming I would use the ["Data Interface for LOB Locators"](#Data Interface for LOB Locators) as this gives you very
fine grain control of your LOBs, of course the code for this will be somewhat more involved.
## Data Interface for LOB Locators
@@ -2980,57 +2969,57 @@
created them is valid. When all references to the original statement
handle are lost, the handle is destroyed and the locators are freed.
-- ora_lob_read
+- ora\_lob\_read
- $data = $dbh->ora_lob_read($lob_locator, $offset, $length);
+ $data = $dbh->ora_lob_read($lob_locator, $offset, $length);
Read a portion of the LOB. $offset starts at 1.
Uses the Oracle OCILobRead function.
NOTE: DBD::Oracle post 1.46 will return undef for any read lob if the
-length specified in the ora_lob_read is 0. See RT 55028. This avoids
-the potential problem with empty lobs (created with empty_clob) which
-return a length of 0 from ora_lob_length and prior to 1.46 a call to
-ora_lob_read with a 0 length would segfault.
+length specified in the ora\_lob\_read is 0. See RT 55028. This avoids
+the potential problem with empty lobs (created with empty\_clob) which
+return a length of 0 from ora\_lob\_length and prior to 1.46 a call to
+ora\_lob\_read with a 0 length would segfault.
-- ora_lob_write
+- ora\_lob\_write
- $rc = $dbh->ora_lob_write($lob_locator, $offset, $data);
+ $rc = $dbh->ora_lob_write($lob_locator, $offset, $data);
Write/overwrite a portion of the LOB. $offset starts at 1.
Uses the Oracle OCILobWrite function.
-- ora_lob_append
+- ora\_lob\_append
- $rc = $dbh->ora_lob_append($lob_locator, $data);
+ $rc = $dbh->ora_lob_append($lob_locator, $data);
Append $data to the LOB. Uses the Oracle OCILobWriteAppend function.
-- ora_lob_trim
+- ora\_lob\_trim
- $rc = $dbh->ora_lob_trim($lob_locator, $length);
+ $rc = $dbh->ora_lob_trim($lob_locator, $length);
Trims the length of the LOB to $length.
Uses the Oracle OCILobTrim function.
-- ora_lob_length
+- ora\_lob\_length
- $length = $dbh->ora_lob_length($lob_locator);
+ $length = $dbh->ora_lob_length($lob_locator);
Returns the length of the LOB.
Uses the Oracle OCILobGetLength function.
-- ora_lob_is_init
+- ora\_lob\_is\_init
- $is_init = $dbh->ora_lob_is_init($lob_locator);
+ $is_init = $dbh->ora_lob_is_init($lob_locator);
Returns true(1) if the Lob Locator is initialized false(0) if it is not, or 'undef'
if there is an error.
Uses the Oracle OCILobLocatorIsInit function.
-- ora_lob_chunk_size
+- ora\_lob\_chunk\_size
- $chunk_size = $dbh->ora_lob_chunk_size($lob_locator);
+ $chunk_size = $dbh->ora_lob_chunk_size($lob_locator);
Returns the chunk size of the LOB.
Uses the Oracle OCILobGetChunkSize function.
@@ -3053,16 +3042,16 @@
object columns, one binary and one character, with a primary
key column, defined as follows:
- CREATE TABLE lob_example (
- lob_id INTEGER PRIMARY KEY,
- bindata BLOB,
- chardata CLOB
- )
+ CREATE TABLE lob_example (
+ lob_id INTEGER PRIMARY KEY,
+ bindata BLOB,
+ chardata CLOB
+ )
It also assumes a sequence for use in generating unique
-lob_id field values, defined as follows:
+lob\_id field values, defined as follows:
- CREATE SEQUENCE lob_example_seq
+ CREATE SEQUENCE lob_example_seq
### Example: Inserting a new row with large data
@@ -3085,64 +3074,64 @@
are only valid within the transaction that fetched them, so
can't be used effectively if AutoCommit is enabled).
- my $lob_id = $dbh->selectrow_array( <<" SQL" );
- SELECT lob_example_seq.nextval FROM DUAL
- SQL
-
- my $sth = $dbh->prepare( <<" SQL" );
- INSERT INTO lob_example
- ( lob_id, bindata, chardata )
- VALUES ( ?, EMPTY_BLOB(),EMPTY_CLOB() )
- SQL
- $sth->execute( $lob_id );
-
- $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
- SELECT bindata, chardata
- FROM lob_example
- WHERE lob_id = ?
- FOR UPDATE
- SQL
- $sth->execute( $lob_id );
- my ( $bin_locator, $char_locator ) = $sth->fetchrow_array();
- $sth->finish();
-
- open BIN_FH, "/binary/data/source" or die;
- open CHAR_FH, "/character/data/source" or die;
- my $chunk_size = $dbh->ora_lob_chunk_size( $bin_locator );
-
- # BEGIN WRITING BIN_DATA COLUMN
- my $offset = 1; # Offsets start at 1, not 0
- my $length = 0;
- my $buffer = '';
- while( $length = read( BIN_FH, $buffer, $chunk_size ) ) {
- $dbh->ora_lob_write( $bin_locator, $offset, $buffer );
- $offset += $length;
- }
-
- # BEGIN WRITING CHAR_DATA COLUMN
- $chunk_size = $dbh->ora_lob_chunk_size( $char_locator );
- $offset = 1; # Offsets start at 1, not 0
- $length = 0;
- $buffer = '';
- while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
- $dbh->ora_lob_write( $char_locator, $offset, $buffer );
- $offset += $length;
- }
-
-In this example we demonstrate the use of ora_lob_write()
-interactively to append data to the columns 'bin_data' and
-'char_data'. Had we used ora_lob_append(), we could have
+ my $lob_id = $dbh->selectrow_array( <<" SQL" );
+ SELECT lob_example_seq.nextval FROM DUAL
+ SQL
+
+ my $sth = $dbh->prepare( <<" SQL" );
+ INSERT INTO lob_example
+ ( lob_id, bindata, chardata )
+ VALUES ( ?, EMPTY_BLOB(),EMPTY_CLOB() )
+ SQL
+ $sth->execute( $lob_id );
+
+ $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
+ SELECT bindata, chardata
+ FROM lob_example
+ WHERE lob_id = ?
+ FOR UPDATE
+ SQL
+ $sth->execute( $lob_id );
+ my ( $bin_locator, $char_locator ) = $sth->fetchrow_array();
+ $sth->finish();
+
+ open BIN_FH, "/binary/data/source" or die;
+ open CHAR_FH, "/character/data/source" or die;
+ my $chunk_size = $dbh->ora_lob_chunk_size( $bin_locator );
+
+ # BEGIN WRITING BIN_DATA COLUMN
+ my $offset = 1; # Offsets start at 1, not 0
+ my $length = 0;
+ my $buffer = '';
+ while( $length = read( BIN_FH, $buffer, $chunk_size ) ) {
+ $dbh->ora_lob_write( $bin_locator, $offset, $buffer );
+ $offset += $length;
+ }
+
+ # BEGIN WRITING CHAR_DATA COLUMN
+ $chunk_size = $dbh->ora_lob_chunk_size( $char_locator );
+ $offset = 1; # Offsets start at 1, not 0
+ $length = 0;
+ $buffer = '';
+ while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
+ $dbh->ora_lob_write( $char_locator, $offset, $buffer );
+ $offset += $length;
+ }
+
+In this example we demonstrate the use of ora\_lob\_write()
+interactively to append data to the columns 'bin\_data' and
+'char\_data'. Had we used ora\_lob\_append(), we could have
saved ourselves the trouble of keeping track of the offset
into the lobs. The snippet of code beneath the comment
-'BEGIN WRITING BIN_DATA COLUMN' could look as follows:
+'BEGIN WRITING BIN\_DATA COLUMN' could look as follows:
- my $buffer = '';
- while ( read( BIN_FH, $buffer, $chunk_size ) ) {
- $dbh->ora_lob_append( $bin_locator, $buffer );
- }
+ my $buffer = '';
+ while ( read( BIN_FH, $buffer, $chunk_size ) ) {
+ $dbh->ora_lob_append( $bin_locator, $buffer );
+ }
The scalar variables $offset and $length are no longer
-needed, because ora_lob_append() keeps track of the offset
+needed, because ora\_lob\_append() keeps track of the offset
for us.
### Example: Updating an existing row with large data
@@ -3153,23 +3142,23 @@
unchanged. Hence, this technique could be used for updating
fixed length subfields embedded in a binary field.
- my $lob_id = 5; # Arbitrary row identifier, for example
+ my $lob_id = 5; # Arbitrary row identifier, for example
- $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
- SELECT bindata
- FROM lob_example
- WHERE lob_id = ?
- FOR UPDATE
- SQL
- $sth->execute( $lob_id );
- my ( $bin_locator ) = $sth->fetchrow_array();
-
- my $offset = 100234;
- my $data = "This string will overwrite a portion of the blob";
- $dbh->ora_lob_write( $bin_locator, $offset, $data );
+ $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
+ SELECT bindata
+ FROM lob_example
+ WHERE lob_id = ?
+ FOR UPDATE
+ SQL
+ $sth->execute( $lob_id );
+ my ( $bin_locator ) = $sth->fetchrow_array();
+
+ my $offset = 100234;
+ my $data = "This string will overwrite a portion of the blob";
+ $dbh->ora_lob_write( $bin_locator, $offset, $data );
-After running this code, the row where lob_id = 5 will
-contain, starting at position 100234 in the bin_data column,
+After running this code, the row where lob\_id = 5 will
+contain, starting at position 100234 in the bin\_data column,
the string "This string will overwrite a portion of the blob".
### Example: Streaming character data from the database
@@ -3179,24 +3168,24 @@
STDOUT. This allows more data to be read in and written out
than could be stored in memory at a given time.
- my $lob_id = 17; # Arbitrary row identifier, for example
+ my $lob_id = 17; # Arbitrary row identifier, for example
- $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
- SELECT chardata
- FROM lob_example
- WHERE lob_id = ?
- SQL
- $sth->execute( $lob_id );
- my ( $char_locator ) = $sth->fetchrow_array();
-
- my $chunk_size = 1034; # Arbitrary chunk size, for example
- my $offset = 1; # Offsets start at 1, not 0
- while(1) {
- my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size );
- last unless length $data;
- print STDOUT $data;
- $offset += $chunk_size;
- }
+ $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
+ SELECT chardata
+ FROM lob_example
+ WHERE lob_id = ?
+ SQL
+ $sth->execute( $lob_id );
+ my ( $char_locator ) = $sth->fetchrow_array();
+
+ my $chunk_size = 1034; # Arbitrary chunk size, for example
+ my $offset = 1; # Offsets start at 1, not 0
+ while(1) {
+ my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size );
+ last unless length $data;
+ print STDOUT $data;
+ $offset += $chunk_size;
+ }
Notice that the select statement does not contain the phrase
"FOR UPDATE". Because we are only reading from the LOB
@@ -3204,13 +3193,13 @@
the select statement does not require the "FOR UPDATE"
clause.
-A word of caution when using the data returned from an ora_lob_read in a conditional statement.
+A word of caution when using the data returned from an ora\_lob\_read in a conditional statement.
for example if the code below;
- while( my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size ) ) {
- print STDOUT $data;
- $offset += $chunk_size;
- }
+ while( my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size ) ) {
+ print STDOUT $data;
+ $offset += $chunk_size;
+ }
was used with a chunk size of 4096 against a blob that requires more than 1 chunk to return
the data and the last chunk is one byte long and contains a zero (ASCII 48) you will miss this last byte
@@ -3225,21 +3214,21 @@
After acquiring a LOB Locator for the column, we query its
length, then we trim the length by half. Because we modify
-the large objects with the call to ora_lob_trim(), we must
+the large objects with the call to ora\_lob\_trim(), we must
select the LOB locators 'FOR UPDATE'.
- my $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
- SELECT bindata
- FROM lob_example
- FOR UPATE
- SQL
- $sth->execute();
- while( my ( $bin_locator ) = $sth->fetchrow_array() ) {
- my $binlength = $dbh->ora_lob_length( $bin_locator );
- if( $binlength > 0 ) {
- $dbh->ora_lob_trim( $bin_locator, $binlength/2 );
- }
- }
+ my $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
+ SELECT bindata
+ FROM lob_example
+ FOR UPATE
+ SQL
+ $sth->execute();
+ while( my ( $bin_locator ) = $sth->fetchrow_array() ) {
+ my $binlength = $dbh->ora_lob_length( $bin_locator );
+ if( $binlength > 0 ) {
+ $dbh->ora_lob_trim( $bin_locator, $binlength/2 );
+ }
+ }
# SPACES AND PADDING
@@ -3255,10 +3244,10 @@
SQL Reference for more details.
To preserve trailing spaces in placeholder values for Oracle clients that use OCI 8,
-either change the default placeholder type with [ora_ph_type](#pod_ora_ph_type) or the placeholder
-type for a particular call to L<DBI/bind> or L<DBI/bind_param_inout>
-with [ora_type](#pod_ora_type) or `TYPE`.
-Using [ORA_CHAR](http://search.cpan.org/perldoc?ORA_CHAR) with [ora_type](http://search.cpan.org/perldoc?ora_type) or `SQL_CHAR` with `TYPE`
+either change the default placeholder type with ["ora\_ph\_type"](#ora\_ph\_type) or the placeholder
+type for a particular call to ["bind" in DBI](http://search.cpan.org/perldoc?DBI#bind) or ["bind\_param\_inout" in DBI](http://search.cpan.org/perldoc?DBI#bind\_param\_inout)
+with ["ora\_type"](#ora\_type) or `TYPE`.
+Using [ORA\_CHAR](http://search.cpan.org/perldoc?ORA\_CHAR) with [ora\_type](http://search.cpan.org/perldoc?ora\_type) or `SQL_CHAR` with `TYPE`
allows the placeholder to be used with Padded Comparison Semantics
if the value it is being compared to is a CHAR, NCHAR, or literal.
@@ -3271,10 +3260,10 @@
## Padded Char Fields
Oracle Clients after OCI 9.2 will automatically pad CHAR placeholder values to the size of the CHAR.
-As the default placeholder type value in DBD::Oracle is ORA_VARCHAR2 to access this behaviour you will
-have to change the default placeholder type with [ora_ph_type](#pod_ora_ph_type) or placeholder
-type for a particular call with L<DBI/bind> or L<DBI/bind_param_inout>
-with [ORA_CHAR](#pod_ORA_CHAR).
+As the default placeholder type value in DBD::Oracle is ORA\_VARCHAR2 to access this behaviour you will
+have to change the default placeholder type with ["ora\_ph\_type"](#ora\_ph\_type) or placeholder
+type for a particular call with ["bind" in DBI](http://search.cpan.org/perldoc?DBI#bind) or ["bind\_param\_inout" in DBI](http://search.cpan.org/perldoc?DBI#bind\_param\_inout)
+with ["ORA\_CHAR"](#ORA\_CHAR).
# UNICODE
@@ -3296,8 +3285,8 @@
you are _strongly_ urged to use Perl 5.8.2 or later and to _carefully_ read
the Perl documentation on Unicode:
- perldoc perluniintro # in Perl 5.8 or later
- perldoc perlunicode
+ perldoc perluniintro # in Perl 5.8 or later
+ perldoc perlunicode
And then read it again.
@@ -3309,47 +3298,47 @@
Oracle supports many characters sets, including several different forms
of Unicode. These include:
- AL16UTF16 => valid for NCHAR columns (CSID=2000)
- UTF8 => valid for NCHAR columns (CSID=871), deprecated
- AL32UTF8 => valid for NCHAR and CHAR columns (CSID=873)
+ AL16UTF16 => valid for NCHAR columns (CSID=2000)
+ UTF8 => valid for NCHAR columns (CSID=871), deprecated
+ AL32UTF8 => valid for NCHAR and CHAR columns (CSID=873)
When you create an Oracle database, you must specify the DATABASE
character set (used for DDL, DML and CHAR datatypes) and the NATIONAL
character set (used for NCHAR and NCLOB types).
The character sets used in your database can be found using:
- $hash_ref = $dbh->ora_nls_parameters()
- $database_charset = $hash_ref->{NLS_CHARACTERSET};
- $national_charset = $hash_ref->{NLS_NCHAR_CHARACTERSET};
+ $hash_ref = $dbh->ora_nls_parameters()
+ $database_charset = $hash_ref->{NLS_CHARACTERSET};
+ $national_charset = $hash_ref->{NLS_NCHAR_CHARACTERSET};
The Oracle 9.2 and later default for the national character set is AL16UTF16.
The default for the database character set is often US7ASCII.
Although many experienced DBAs will consider an 8bit character set like
WE8ISO8859P1 or WE8MSWIN1252. To use any character set with Oracle
-other than US7ASCII, requires that the NLS_LANG environment variable be set.
-See the L<"Oracle UTF8 is not UTF-8"> section below.
+other than US7ASCII, requires that the NLS\_LANG environment variable be set.
+See the ["Oracle UTF8 is not UTF-8"](#Oracle UTF8 is not UTF-8) section below.
You are strongly urged to read the Oracle Internationalization documentation
specifically with respect the choices and trade offs for creating
a databases for use with international character sets.
-Oracle uses the NLS_LANG environment variable to indicate what
+Oracle uses the NLS\_LANG environment variable to indicate what
character set is being used on the client. When fetching data Oracle
will convert from whatever the database character set is to the client
-character set specified by NLS_LANG. Similarly, when sending data to
+character set specified by NLS\_LANG. Similarly, when sending data to
the database Oracle will convert from the character set specified by
-NLS_LANG to the database character set.
+NLS\_LANG to the database character set.
-The NLS_NCHAR environment variable can be used to define a different
+The NLS\_NCHAR environment variable can be used to define a different
character set for 'national' (NCHAR) character types.
-Both UTF8 and AL32UTF8 can be used in NLS_LANG and NLS_NCHAR.
+Both UTF8 and AL32UTF8 can be used in NLS\_LANG and NLS\_NCHAR.
For example:
- NLS_LANG=AMERICAN_AMERICA.UTF8
- NLS_LANG=AMERICAN_AMERICA.AL32UTF8
- NLS_NCHAR=UTF8
- NLS_NCHAR=AL32UTF8
+ NLS_LANG=AMERICAN_AMERICA.UTF8
+ NLS_LANG=AMERICAN_AMERICA.AL32UTF8
+ NLS_NCHAR=UTF8
+ NLS_NCHAR=AL32UTF8
## Oracle UTF8 is not UTF-8
@@ -3364,17 +3353,17 @@
Technically the encoding that Oracle calls "UTF8" is known as "CESU-8".
Here are a couple of extracts from [http://www.unicode.org/reports/tr26/](http://www.unicode.org/reports/tr26/):
- CESU-8 is useful in 8-bit processing environments where binary
- collation with UTF-16 is required. It is designed and recommended
- for use only within products requiring this UTF-16 binary collation
- equivalence. It is not intended nor recommended for open interchange.
-
- As a very small percentage of characters in a typical data stream
- are expected to be supplementary characters, there is a strong
- possibility that CESU-8 data may be misinterpreted as UTF-8.
- Therefore, all use of CESU-8 outside closed implementations is
- strongly discouraged, such as the emittance of CESU-8 in output
- files, markup language or other open transmission forms.
+ CESU-8 is useful in 8-bit processing environments where binary
+ collation with UTF-16 is required. It is designed and recommended
+ for use only within products requiring this UTF-16 binary collation
+ equivalence. It is not intended nor recommended for open interchange.
+
+ As a very small percentage of characters in a typical data stream
+ are expected to be supplementary characters, there is a strong
+ possibility that CESU-8 data may be misinterpreted as UTF-8.
+ Therefore, all use of CESU-8 outside closed implementations is
+ strongly discouraged, such as the emittance of CESU-8 in output
+ files, markup language or other open transmission forms.
Oracle uses this internally because it collates (sorts) in the same order
as UTF16, which is the basis of Oracle's internal collation definitions.
@@ -3403,59 +3392,59 @@
character set will be marked as UTF-8 to ensure correct handling by Perl.
For Oracle to return data in the AL32UTF8 character set the
-NLS_LANG or NLS_NCHAR environment variable _must_ be set as described
+NLS\_LANG or NLS\_NCHAR environment variable _must_ be set as described
in the previous section.
When fetching NCHAR, NVARCHAR, or NCLOB data from Oracle, DBD::Oracle
-will set the Perl UTF-8 flag on the returned data if either NLS_NCHAR
-is AL32UTF8, or NLS_NCHAR is not set and NLS_LANG is AL32UTF8.
+will set the Perl UTF-8 flag on the returned data if either NLS\_NCHAR
+is AL32UTF8, or NLS\_NCHAR is not set and NLS\_LANG is AL32UTF8.
When fetching other character data from Oracle, DBD::Oracle
-will set the Perl UTF-8 flag on the returned data if NLS_LANG is AL32UTF8.
+will set the Perl UTF-8 flag on the returned data if NLS\_LANG is AL32UTF8.
__Sending Data using Placeholders__
Data bound to a placeholder is assumed to be in the default client
-character set (specified by NLS_LANG) except for a few special
+character set (specified by NLS\_LANG) except for a few special
cases. These are listed here with the highest precedence first:
-If the `ora_csid` attribute is given to bind_param() then that
+If the `ora_csid` attribute is given to bind\_param() then that
is passed to Oracle and takes precedence.
If the value is a Perl Unicode string (UTF-8) then DBD::Oracle
ensures that Oracle uses the Unicode character set, regardless of
-the NLS_LANG and NLS_NCHAR settings.
+the NLS\_LANG and NLS\_NCHAR settings.
-If the placeholder is for inserting an NCLOB then the client NLS_NCHAR
+If the placeholder is for inserting an NCLOB then the client NLS\_NCHAR
character set is used. (That's useful but inconsistent with the other behaviour
so may change. Best to be explicit by using the `ora_csform`
attribute.)
-If the `ora_csform` attribute is given to bind_param() then that
+If the `ora_csform` attribute is given to bind\_param() then that
determines if the value should be assumed to be in the default
-(NLS_LANG) or NCHAR (NLS_NCHAR) client character set.
+(NLS\_LANG) or NCHAR (NLS\_NCHAR) client character set.
- use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
- ...
- $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR });
+ use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
+ ...
+ $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR });
or
- $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders
+ $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders
-Binding with bind_param_array and execute_array is also UTF-8 compatible in the same way. If you attempt to
+Binding with bind\_param\_array and execute\_array is also UTF-8 compatible in the same way. If you attempt to
insert UTF-8 data into a non UTF-8 Oracle instance or with an non UTF-8 NCHAR or NVARCHAR the insert
will still happen but a error code of 0 will be returned with the following warning;
- DBD Oracle Warning: You have mixed utf8 and non-utf8 in an array bind in parameter#1. This may result in corrupt data.
- The Query charset id=1, name=US7ASCII
+ DBD Oracle Warning: You have mixed utf8 and non-utf8 in an array bind in parameter#1. This may result in corrupt data.
+ The Query charset id=1, name=US7ASCII
The warning will report the parameter number and the NCHAR setting that the query is running.
__Sending Data using SQL__
Oracle assumes the SQL statement is in the default client character
-set (as specified by NLS_LANG). So Unicode strings containing
+set (as specified by NLS\_LANG). So Unicode strings containing
non-ASCII characters should not be used unless the default client
character set is AL32UTF8.
@@ -3477,114 +3466,114 @@
Array example, given this type and table;
- CREATE OR REPLACE TYPE "PHONE_NUMBERS" as varray(10) of varchar(30);
+ CREATE OR REPLACE TYPE "PHONE_NUMBERS" as varray(10) of varchar(30);
- CREATE TABLE "CONTACT"
- ( "COMPANYNAME" VARCHAR2(40),
- "ADDRESS" VARCHAR2(100),
- "PHONE_NUMBERS" "PHONE_NUMBERS"
- )
+ CREATE TABLE "CONTACT"
+ ( "COMPANYNAME" VARCHAR2(40),
+ "ADDRESS" VARCHAR2(100),
+ "PHONE_NUMBERS" "PHONE_NUMBERS"
+ )
The code to access all the data in the table could be something like this;
- my $sth = $dbh->prepare('SELECT * FROM CONTACT');
- $sth->execute;
- while ( my ($company, $address, $phone) = $sth->fetchrow()) {
- print "Company: ".$company."\n";
- print "Address: ".$address."\n";
- print "Phone #: ";
+ my $sth = $dbh->prepare('SELECT * FROM CONTACT');
+ $sth->execute;
+ while ( my ($company, $address, $phone) = $sth->fetchrow()) {
+ print "Company: ".$company."\n";
+ print "Address: ".$address."\n";
+ print "Phone #: ";
- foreach my $items (@$phone){
- print $items.", ";
- }
- print "\n";
- }
+ foreach my $items (@$phone){
+ print $items.", ";
+ }
+ print "\n";
+ }
-Note that values in PHONE_NUMBERS are returned as an array reference '@$phone'.
+Note that values in PHONE\_NUMBERS are returned as an array reference '@$phone'.
As stated before DBD::Oracle will automatically drill into the embedded object and extract
all of the data as reference arrays of scalars. The example below has OBJECT type embedded in a TABLE type embedded in an
SQL TABLE;
- CREATE OR REPLACE TYPE GRADELIST AS TABLE OF NUMBER;
+ CREATE OR REPLACE TYPE GRADELIST AS TABLE OF NUMBER;
- CREATE OR REPLACE TYPE STUDENT AS OBJECT(
- NAME VARCHAR2(60),
- SOME_GRADES GRADELIST);
-
- CREATE OR REPLACE TYPE STUDENTS_T AS TABLE OF STUDENT;
-
- CREATE TABLE GROUPS(
- GRP_ID NUMBER(4),
- GRP_NAME VARCHAR2(10),
- STUDENTS STUDENTS_T)
- NESTED TABLE STUDENTS STORE AS GROUP_STUDENTS_TAB
- (NESTED TABLE SOME_GRADES STORE AS GROUP_STUDENT_GRADES_TAB);
+ CREATE OR REPLACE TYPE STUDENT AS OBJECT(
+ NAME VARCHAR2(60),
+ SOME_GRADES GRADELIST);
+
+ CREATE OR REPLACE TYPE STUDENTS_T AS TABLE OF STUDENT;
+
+ CREATE TABLE GROUPS(
+ GRP_ID NUMBER(4),
+ GRP_NAME VARCHAR2(10),
+ STUDENTS STUDENTS_T)
+ NESTED TABLE STUDENTS STORE AS GROUP_STUDENTS_TAB
+ (NESTED TABLE SOME_GRADES STORE AS GROUP_STUDENT_GRADES_TAB);
The following code will access all of the embedded data;
- $SQL='select grp_id,grp_name,students as my_students_test from groups';
- $sth=$dbh->prepare($SQL);
- $sth->execute();
- while (my ($grp_id,$grp_name,$students)=$sth->fetchrow()){
- print "Group ID#".$grp_id." Group Name =".$grp_name."\n";
- foreach my $student (@$students){
- print "Name:".$student->[0]."\n";
- print "Marks:";
- foreach my $grades (@$student->[1]){
- foreach my $marks (@$grades){
- print $marks.",";
- }
- }
- print "\n";
- }
- print "\n";
- }
+ $SQL='select grp_id,grp_name,students as my_students_test from groups';
+ $sth=$dbh->prepare($SQL);
+ $sth->execute();
+ while (my ($grp_id,$grp_name,$students)=$sth->fetchrow()){
+ print "Group ID#".$grp_id." Group Name =".$grp_name."\n";
+ foreach my $student (@$students){
+ print "Name:".$student->[0]."\n";
+ print "Marks:";
+ foreach my $grades (@$student->[1]){
+ foreach my $marks (@$grades){
+ print $marks.",";
+ }
+ }
+ print "\n";
+ }
+ print "\n";
+ }
Object example, given this object and table;
- CREATE OR REPLACE TYPE Person AS OBJECT (
- name VARCHAR2(20),
- age INTEGER)
- ) NOT FINAL;
-
- CREATE TYPE Employee UNDER Person (
- salary NUMERIC(8,2)
- );
+ CREATE OR REPLACE TYPE Person AS OBJECT (
+ name VARCHAR2(20),
+ age INTEGER)
+ ) NOT FINAL;
+
+ CREATE TYPE Employee UNDER Person (
+ salary NUMERIC(8,2)
+ );
- CREATE TABLE people (id INTEGER, obj Person);
+ CREATE TABLE people (id INTEGER, obj Person);
- INSERT INTO people VALUES (1, Person('Black', 25));
- INSERT INTO people VALUES (2, Employee('Smith', 44, 5000));
+ INSERT INTO people VALUES (1, Person('Black', 25));
+ INSERT INTO people VALUES (2, Employee('Smith', 44, 5000));
The following code will access the data;
- $dbh{'ora_objects'} =>1;
+ $dbh{'ora_objects'} =>1;
- $sth = $dbh->prepare("select * from people order by id");
- $sth->execute();
+ $sth = $dbh->prepare("select * from people order by id");
+ $sth->execute();
- # object are fetched as instance of DBD::Oracle::Object
- my ($id1, $obj1) = $sth->fetchrow();
- my ($id2, $obj2) = $sth->fetchrow();
-
- # get full type-name of object
- print $obj1->type_name."44\n"; # 'TEST.PERSON' is printed
- print $obj2->type_name."4\n"; # 'TEST.EMPLOYEE' is printed
-
- # get attribute NAME from object
- print $obj1->attr('NAME')."3\n"; # 'Black' is printed
- print $obj2->attr('NAME')."3\n"; # 'Smith' is printed
-
- # get all atributes as hash reference
- my $h1 = $obj1->attr; # returns {'NAME' => 'Black', 'AGE' => 25}
- my $h2 = $obj2->attr; # returns {'NAME' => 'Smith', 'AGE' => 44,
- # 'SALARY' => 5000 }
-
- # get all attributes (names and values) as array
- my @a1 = $obj1->attributes; # returns ('NAME', 'Black', 'AGE', 25)
- my @a2 = $obj2->attributes; # returns ('NAME', 'Smith', 'AGE', 44,
- # 'SALARY', 5000 )
+ # object are fetched as instance of DBD::Oracle::Object
+ my ($id1, $obj1) = $sth->fetchrow();
+ my ($id2, $obj2) = $sth->fetchrow();
+
+ # get full type-name of object
+ print $obj1->type_name."44\n"; # 'TEST.PERSON' is printed
+ print $obj2->type_name."4\n"; # 'TEST.EMPLOYEE' is printed
+
+ # get attribute NAME from object
+ print $obj1->attr('NAME')."3\n"; # 'Black' is printed
+ print $obj2->attr('NAME')."3\n"; # 'Smith' is printed
+
+ # get all atributes as hash reference
+ my $h1 = $obj1->attr; # returns {'NAME' => 'Black', 'AGE' => 25}
+ my $h2 = $obj2->attr; # returns {'NAME' => 'Smith', 'AGE' => 44,
+ # 'SALARY' => 5000 }
+
+ # get all attributes (names and values) as array
+ my @a1 = $obj1->attributes; # returns ('NAME', 'Black', 'AGE', 25)
+ my @a2 = $obj2->attributes; # returns ('NAME', 'Smith', 'AGE', 44,
+ # 'SALARY', 5000 )
So far DBD::Oracle has been tested on a table with 20 embedded Objects, Varrays and Tables
nested to 10 levels.
@@ -3608,7 +3597,7 @@
will be generated if the contents of the string don't match the
NLS format. If you're dealing in dates which don't match the default
NLS format then you can either change the default NLS format or, more
-commonly, use TO_CHAR(field, "format") and TO_DATE(?, "format")
+commonly, use TO\_CHAR(field, "format") and TO\_DATE(?, "format")
to explicitly specify formats for converting to and from strings.
A slightly more subtle problem can occur with NUMBER types. The
@@ -3617,11 +3606,11 @@
Perl will generate warnings and use incorrect values when numbers,
returned and formatted as strings in this way by Oracle, are used
in a numeric context. You could explicitly convert each numeric
-value using the TO_CHAR(...) function but that gets tedious very
+value using the TO\_CHAR(...) function but that gets tedious very
quickly. The best fix is to change the NLS settings. That can be
done for an individual connection by doing:
- $dbh->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");
+ $dbh->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");
There are some types, like BOOLEAN, that Oracle does not automatically
convert to or from strings (pity). These need to be converted
@@ -3629,70 +3618,70 @@
Examples:
- # DATE values
- my $sth0 = $dbh->prepare( <<SQL_END );
- SELECT username, TO_CHAR( created, ? )
- FROM all_users
- WHERE created >= TO_DATE( ?, ? )
- SQL_END
- $sth0->execute( 'YYYY-MM-DD HH24:MI:SS', "2003", 'YYYY' );
-
- # BOOLEAN values
- my $sth2 = $dbh->prepare( <<PLSQL_END );
- DECLARE
- b0 BOOLEAN;
- b1 BOOLEAN;
- o0 VARCHAR2(32);
- o1 VARCHAR2(32);
+ # DATE values
+ my $sth0 = $dbh->prepare( <<SQL_END );
+ SELECT username, TO_CHAR( created, ? )
+ FROM all_users
+ WHERE created >= TO_DATE( ?, ? )
+ SQL_END
+ $sth0->execute( 'YYYY-MM-DD HH24:MI:SS', "2003", 'YYYY' );
- FUNCTION to_bool( i VARCHAR2 ) RETURN BOOLEAN IS
- BEGIN
- IF i IS NULL THEN RETURN NULL;
- ELSIF i = 'F' OR i = '0' THEN RETURN FALSE;
- ELSE RETURN TRUE;
- END IF;
- END;
- FUNCTION from_bool( i BOOLEAN ) RETURN NUMBER IS
- BEGIN
- IF i IS NULL THEN RETURN NULL;
- ELSIF i THEN RETURN 1;
- ELSE RETURN 0;
- END IF;
- END;
- BEGIN
- -- Converting values to BOOLEAN
- b0 := to_bool( :i0 );
- b1 := to_bool( :i1 );
-
- -- Converting values from BOOLEAN
- :o0 := from_bool( b0 );
- :o1 := from_bool( b1 );
- END;
- PLSQL_END
- my ( $i0, $i1, $o0, $o1 ) = ( "", "Something else" );
- $sth2->bind_param( ":i0", $i0 );
- $sth2->bind_param( ":i1", $i1 );
- $sth2->bind_param_inout( ":o0", \$o0, 32 );
- $sth2->bind_param_inout( ":o1", \$o1, 32 );
- $sth2->execute();
- foreach ( $i0, $b0, $o0, $i1, $b1, $o1 ) {
- $_ = "(undef)" if ! defined $_;
- }
- print "$i0 to $o0, $i1 to $o1\n";
- # Result is : "'' to '(undef)', 'Something else' to '1'"
+ # BOOLEAN values
+ my $sth2 = $dbh->prepare( <<PLSQL_END );
+ DECLARE
+ b0 BOOLEAN;
+ b1 BOOLEAN;
+ o0 VARCHAR2(32);
+ o1 VARCHAR2(32);
+
+ FUNCTION to_bool( i VARCHAR2 ) RETURN BOOLEAN IS
+ BEGIN
+ IF i IS NULL THEN RETURN NULL;
+ ELSIF i = 'F' OR i = '0' THEN RETURN FALSE;
+ ELSE RETURN TRUE;
+ END IF;
+ END;
+ FUNCTION from_bool( i BOOLEAN ) RETURN NUMBER IS
+ BEGIN
+ IF i IS NULL THEN RETURN NULL;
+ ELSIF i THEN RETURN 1;
+ ELSE RETURN 0;
+ END IF;
+ END;
+ BEGIN
+ -- Converting values to BOOLEAN
+ b0 := to_bool( :i0 );
+ b1 := to_bool( :i1 );
+
+ -- Converting values from BOOLEAN
+ :o0 := from_bool( b0 );
+ :o1 := from_bool( b1 );
+ END;
+ PLSQL_END
+ my ( $i0, $i1, $o0, $o1 ) = ( "", "Something else" );
+ $sth2->bind_param( ":i0", $i0 );
+ $sth2->bind_param( ":i1", $i1 );
+ $sth2->bind_param_inout( ":o0", \$o0, 32 );
+ $sth2->bind_param_inout( ":o1", \$o1, 32 );
+ $sth2->execute();
+ foreach ( $i0, $b0, $o0, $i1, $b1, $o1 ) {
+ $_ = "(undef)" if ! defined $_;
+ }
+ print "$i0 to $o0, $i1 to $o1\n";
+ # Result is : "'' to '(undef)', 'Something else' to '1'"
-## Support for Insert of XMLType (ORA_XMLTYPE)
+## Support for Insert of XMLType (ORA\_XMLTYPE)
Inserting large XML data sets into tables with XMLType fields is now supported by DBD::Oracle. The only special
-requirement is the use of bind_param() with an attribute hash parameter that specifies ora_type as ORA_XMLTYPE. For
+requirement is the use of bind\_param() with an attribute hash parameter that specifies ora\_type as ORA\_XMLTYPE. For
example with a table like this;
- create table books (book_id number, book_xml XMLType);
+ create table books (book_id number, book_xml XMLType);
one can insert data using this code
- $SQL='insert into books values (1,:p_xml)';
- $xml= '<Books>
+ $SQL='insert into books values (1,:p_xml)';
+ $xml= '<Books>
<Book id=1>
<Title>Programming the Perl DBI</Title>
<Subtitle>The Cheetah Book</Subtitle>
@@ -3704,9 +3693,9 @@
</Book>
<Book id=10000>...
</Books>';
- my $sth =$dbh-> prepare($SQL);
- $sth-> bind_param("p_xml", $xml, { ora_type => ORA_XMLTYPE });
- $sth-> execute();
+ my $sth =$dbh-> prepare($SQL);
+ $sth-> bind_param("p_xml", $xml, { ora_type => ORA_XMLTYPE });
+ $sth-> execute();
In the above case we will assume that $xml has 10000 Book nodes and is over 32k in size and is well formed XML.
This will also work for XML that is smaller than 32k as well. Attempting to insert malformed XML will cause an error.
@@ -3717,21 +3706,21 @@
functions (or procedures with OUT parameters) or
from direct `OPEN` statements, as shown below:
- use DBI;
- use DBD::Oracle qw(:ora_types);
- my $dbh = DBI->connect(...);
- my $sth1 = $dbh->prepare(q{
- BEGIN OPEN :cursor FOR
- SELECT table_name, tablespace_name
- FROM user_tables WHERE tablespace_name = :space;
- END;
- });
- $sth1->bind_param(":space", "USERS");
- my $sth2;
- $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
- $sth1->execute;
- # $sth2 is now a valid DBI statement handle for the cursor
- while ( my @row = $sth2->fetchrow_array ) { ... }
+ use DBI;
+ use DBD::Oracle qw(:ora_types);
+ my $dbh = DBI->connect(...);
+ my $sth1 = $dbh->prepare(q{
+ BEGIN OPEN :cursor FOR
+ SELECT table_name, tablespace_name
+ FROM user_tables WHERE tablespace_name = :space;
+ END;
+ });
+ $sth1->bind_param(":space", "USERS");
+ my $sth2;
+ $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
+ $sth1->execute;
+ # $sth2 is now a valid DBI statement handle for the cursor
+ while ( my @row = $sth2->fetchrow_array ) { ... }
The only special requirement is the use of `bind_param_inout()` with an
attribute hash parameter that specifies `ora_type` as `ORA_RSET`.
@@ -3741,34 +3730,34 @@
Here's an alternative form using a function that returns a cursor.
This example uses the pre-defined weak (or generic) REF CURSOR type
-SYS_REFCURSOR. This is an Oracle 9 feature.
+SYS\_REFCURSOR. This is an Oracle 9 feature.
- # Create the function that returns a cursor
- $dbh->do(q{
- CREATE OR REPLACE FUNCTION sp_ListEmp RETURN SYS_REFCURSOR
- AS l_cursor SYS_REFCURSOR;
- BEGIN
- OPEN l_cursor FOR select ename, empno from emp
- ORDER BY ename;
- RETURN l_cursor;
- END;
- });
+ # Create the function that returns a cursor
+ $dbh->do(q{
+ CREATE OR REPLACE FUNCTION sp_ListEmp RETURN SYS_REFCURSOR
+ AS l_cursor SYS_REFCURSOR;
+ BEGIN
+ OPEN l_cursor FOR select ename, empno from emp
+ ORDER BY ename;
+ RETURN l_cursor;
+ END;
+ });
- # Use the function that returns a cursor
- my $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
- my $sth2;
- $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
- $sth1->execute;
- # $sth2 is now a valid DBI statement handle for the cursor
- while ( my @row = $sth2->fetchrow_array ) { ... }
+ # Use the function that returns a cursor
+ my $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
+ my $sth2;
+ $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
+ $sth1->execute;
+ # $sth2 is now a valid DBI statement handle for the cursor
+ while ( my @row = $sth2->fetchrow_array ) { ... }
A cursor obtained from PL/SQL as above may be passed back to PL/SQL
by binding for input, as shown in this example, which explicitly
closes a cursor:
- my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
- $sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
- $sth3->execute;
+ my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
+ $sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
+ $sth3->execute;
It is not normally necessary to close a cursor
explicitly in this way. Oracle will close the cursor automatically
@@ -3859,13 +3848,13 @@
Most of these PL/SQL examples come from: Eric Bartley <[email protected]>.
- /*
- * PL/SQL to create package with stored procedures invoked by
- * Perl examples. Execute using sqlplus.
- *
- * Use of "... OR REPLACE" prevents failure in the event that the
- * package already exists.
- */
+ /*
+ * PL/SQL to create package with stored procedures invoked by
+ * Perl examples. Execute using sqlplus.
+ *
+ * Use of "... OR REPLACE" prevents failure in the event that the
+ * package already exists.
+ */
CREATE OR REPLACE PACKAGE plsql_example
IS
@@ -3883,8 +3872,8 @@
FUNCTION func_np
RETURN VARCHAR2;
- END plsql_example;
- /
+ END plsql_example;
+ /
CREATE OR REPLACE PACKAGE BODY plsql_example
IS
@@ -3921,126 +3910,126 @@
RETURN ret_val;
END;
- END plsql_example;
- /
- /* End PL/SQL for example package creation. */
-
- use DBI;
-
- my($db, $csr, $ret_val);
-
- $db = DBI->connect('dbi:Oracle:database','user','password')
- or die "Unable to connect: $DBI::errstr";
-
- # So we don't have to check every DBI call we set RaiseError.
- # See the DBI docs now if you're not familiar with RaiseError.
- $db->{RaiseError} = 1;
-
- # Example 1 Eric Bartley <[email protected]>
- #
- # Calling a PLSQL procedure that takes no parameters. This shows you the
- # basic's of what you need to execute a PLSQL procedure. Just wrap your
- # procedure call in a BEGIN END; block just like you'd do in SQL*Plus.
- #
- # p.s. If you've used SQL*Plus's exec command all it does is wrap the
- # command in a BEGIN END; block for you.
+ END plsql_example;
+ /
+ /* End PL/SQL for example package creation. */
- $csr = $db->prepare(q{
- BEGIN
- PLSQL_EXAMPLE.PROC_NP;
- END;
- });
- $csr->execute;
+ use DBI;
+
+ my($db, $csr, $ret_val);
+ $db = DBI->connect('dbi:Oracle:database','user','password')
+ or die "Unable to connect: $DBI::errstr";
+ # So we don't have to check every DBI call we set RaiseError.
+ # See the DBI docs now if you're not familiar with RaiseError.
+ $db->{RaiseError} = 1;
+
+ # Example 1 Eric Bartley <[email protected]>
+ #
+ # Calling a PLSQL procedure that takes no parameters. This shows you the
+ # basic's of what you need to execute a PLSQL procedure. Just wrap your
+ # procedure call in a BEGIN END; block just like you'd do in SQL*Plus.
+ #
+ # p.s. If you've used SQL*Plus's exec command all it does is wrap the
+ # command in a BEGIN END; block for you.
- # Example 2 Eric Bartley <[email protected]>
- #
- # Now we call a procedure that has 1 IN parameter. Here we use bind_param
- # to bind out parameter to the prepared statement just like you might
- # do for an INSERT, UPDATE, DELETE, or SELECT statement.
- #
- # I could have used positional placeholders (e.g. :1, :2, etc.) or
- # ODBC style placeholders (e.g. ?), but I prefer Oracle's named
- # placeholders (but few DBI drivers support them so they're not portable).
-
- my $err_code = -20001;
-
- $csr = $db->prepare(q{
- BEGIN
- PLSQL_EXAMPLE.PROC_IN(:err_code);
- END;
- });
-
- $csr->bind_param(":err_code", $err_code);
-
- # PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to 'fail'.
- # Because we set RaiseError, the DBI will croak (die) so we catch that with eval.
- eval {
+ $csr = $db->prepare(q{
+ BEGIN
+ PLSQL_EXAMPLE.PROC_NP;
+ END;
+ });
$csr->execute;
- };
- print 'After proc_in: $@=',"'$@', errstr=$DBI::errstr, ret_val=$ret_val\n";
- # Example 3 Eric Bartley <[email protected]>
- #
- # Building on the last example, I've added 1 IN OUT parameter. We still
- # use a placeholders in the call to prepare, the difference is that
- # we now call bind_param_inout to bind the value to the place holder.
- #
- # Note that the third parameter to bind_param_inout is the maximum size
- # of the variable. You normally make this slightly larger than necessary.
- # But note that the Perl variable will have that much memory assigned to
- # it even if the actual value returned is shorter.
+ # Example 2 Eric Bartley <[email protected]>
+ #
+ # Now we call a procedure that has 1 IN parameter. Here we use bind_param
+ # to bind out parameter to the prepared statement just like you might
+ # do for an INSERT, UPDATE, DELETE, or SELECT statement.
+ #
+ # I could have used positional placeholders (e.g. :1, :2, etc.) or
+ # ODBC style placeholders (e.g. ?), but I prefer Oracle's named
+ # placeholders (but few DBI drivers support them so they're not portable).
+
+ my $err_code = -20001;
+
+ $csr = $db->prepare(q{
+ BEGIN
+ PLSQL_EXAMPLE.PROC_IN(:err_code);
+ END;
+ });
+
+ $csr->bind_param(":err_code", $err_code);
+
+ # PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to 'fail'.
+ # Because we set RaiseError, the DBI will croak (die) so we catch that with eval.
+ eval {
+ $csr->execute;
+ };
+ print 'After proc_in: $@=',"'$@', errstr=$DBI::errstr, ret_val=$ret_val\n";
- my $test_num = 5;
- my $is_odd;
- $csr = $db->prepare(q{
- BEGIN
- PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
- END;
- });
- # The value of $test_num is _copied_ here
- $csr->bind_param(":test_num", $test_num);
+ # Example 3 Eric Bartley <[email protected]>
+ #
+ # Building on the last example, I've added 1 IN OUT parameter. We still
+ # use a placeholders in the call to prepare, the difference is that
+ # we now call bind_param_inout to bind the value to the place holder.
+ #
+ # Note that the third parameter to bind_param_inout is the maximum size
+ # of the variable. You normally make this slightly larger than necessary.
+ # But note that the Perl variable will have that much memory assigned to
+ # it even if the actual value returned is shorter.
+
+ my $test_num = 5;
+ my $is_odd;
+
+ $csr = $db->prepare(q{
+ BEGIN
+ PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
+ END;
+ });
+
+ # The value of $test_num is _copied_ here
+ $csr->bind_param(":test_num", $test_num);
- $csr->bind_param_inout(":is_odd", \$is_odd, 1);
+ $csr->bind_param_inout(":is_odd", \$is_odd, 1);
- # The execute will automagically update the value of $is_odd
- $csr->execute;
+ # The execute will automagically update the value of $is_odd
+ $csr->execute;
- print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n";
+ print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n";
- # Example 4 Eric Bartley <[email protected]>
- #
- # What about the return value of a PLSQL function? Well treat it the same
- # as you would a call to a function from SQL*Plus. We add a placeholder
- # for the return value and bind it with a call to bind_param_inout so
- # we can access it's value after execute.
+ # Example 4 Eric Bartley <[email protected]>
+ #
+ # What about the return value of a PLSQL function? Well treat it the same
+ # as you would a call to a function from SQL*Plus. We add a placeholder
+ # for the return value and bind it with a call to bind_param_inout so
+ # we can access it's value after execute.
- my $whoami = "";
+ my $whoami = "";
- $csr = $db->prepare(q{
- BEGIN
- :whoami := PLSQL_EXAMPLE.FUNC_NP;
- END;
- });
+ $csr = $db->prepare(q{
+ BEGIN
+ :whoami := PLSQL_EXAMPLE.FUNC_NP;
+ END;
+ });
- $csr->bind_param_inout(":whoami", \$whoami, 20);
- $csr->execute;
- print "Your database user name is $whoami\n";
+ $csr->bind_param_inout(":whoami", \$whoami, 20);
+ $csr->execute;
+ print "Your database user name is $whoami\n";
- $db->disconnect;
+ $db->disconnect;
You can find more examples in the t/plsql.t file in the DBD::Oracle
source directory.
Oracle 9.2 appears to have a bug where a variable bound
-with bind_param_inout() that isn't assigned to by the executed
+with bind\_param\_inout() that isn't assigned to by the executed
PL/SQL block may contain garbage.
See [http://www.mail-archive.com/[email protected]/msg18835.html](http://www.mail-archive.com/[email protected]/msg18835.html)
@@ -4052,7 +4041,7 @@
ignored and returned as null. According to Oracle support this is part of the same
mechanism where;
- select (select * from dual where 0=1) from dual
+ select (select * from dual where 0=1) from dual
returns a null value rather than an exception.
@@ -4069,7 +4058,7 @@
you'll need to install a Subversion client. Then, to get the source
code, do:
- svn checkout http://svn.perl.org/modules/dbd-oracle/trunk
+ svn checkout http://svn.perl.org/modules/dbd-oracle/trunk
If it prompts for a username and password use your perl.org account
if you have one, else just 'guest' and 'guest'. The source code will
@@ -4082,12 +4071,12 @@
After making your changes you can generate a patch file, but before
you do, make sure your source is still upto date using:
- svn update
+ svn update
If you get any conflicts reported you'll need to fix them first.
Then generate the patch file from within the `trunk` directory using:
- svn diff > foo.patch
+ svn diff > foo.patch
Read the patch file, as a sanity check, and then email it to [email protected].
@@ -4095,30 +4084,30 @@
Unpack a fresh copy of the distribution:
- tar xfz DBD-Oracle-1.40.tar.gz
+ tar xfz DBD-Oracle-1.40.tar.gz
Rename the newly created top level directory:
- mv DBD-Oracle-1.40 DBD-Oracle-1.40.your_foo
+ mv DBD-Oracle-1.40 DBD-Oracle-1.40.your_foo
-Edit the contents of DBD-Oracle-1.40.your_foo/* till it does what you want.
+Edit the contents of DBD-Oracle-1.40.your\_foo/\* till it does what you want.
Test your changes and then remove all temporary files:
- make test && make distclean
+ make test && make distclean
Go back to the directory you originally unpacked the distribution:
- cd ..
+ cd ..
Unpack _another_ copy of the original distribution you started with:
- tar xfz DBD-Oracle-1.40.tar.gz
+ tar xfz DBD-Oracle-1.40.tar.gz
Then create a patch file by performing a recursive `diff` on the two
top level directories:
- diff -r -u DBD-Oracle-1.40 DBD-Oracle-1.40.your_foo > DBD-Oracle-1.40.your_foo.patch
+ diff -r -u DBD-Oracle-1.40 DBD-Oracle-1.40.your_foo > DBD-Oracle-1.40.your_foo.patch
## Speak before you patch
@@ -4131,7 +4120,7 @@
## GitHub repository
A git mirror of the subversion is also available at
-`https://github.com/yanick/DBD-Oracle`.
+\`https://github.com/yanick/DBD-Oracle\`.
# Oracle Related Links
@@ -4142,25 +4131,25 @@
If you are still stuck with an older version of Oracle or its client you might want to look at the table below.
- +---------------------+-----------------------------------------------------+
- | | Oracle Version |
- +---------------------+----+-------------+---------+------+--------+--------+
- | DBD::Oracle Version | <8 | 8.0.3~8.0.6 | 8iR1~R2 | 8iR3 | 9i | 9.2~11 |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 0.1~16 | Y | Y | Y | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.17 | Y | Y | Y | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.18 | N | N | N | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.19 | N | N | N | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.20 | N | N | N | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.21~1.24 | N | N | N | N | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.25+ | N | N | N | N | N | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
+ +---------------------+-----------------------------------------------------+
+ | | Oracle Version |
+ +---------------------+----+-------------+---------+------+--------+--------+
+ | DBD::Oracle Version | <8 | 8.0.3~8.0.6 | 8iR1~R2 | 8iR3 | 9i | 9.2~11 |
+ +---------------------+----+-------------+---------+------+--------+--------+
+ | 0.1~16 | Y | Y | Y | Y | Y | Y |
+ +---------------------+----+-------------+---------+------+--------+--------+
+ | 1.17 | Y | Y | Y | Y | Y | Y |
+ +---------------------+----+-------------+---------+------+--------+--------+
+ | 1.18 | N | N | N | Y | Y | Y |
+ +---------------------+----+-------------+---------+------+--------+--------+
+ | 1.19 | N | N | N | Y | Y | Y |
+ +---------------------+----+-------------+---------+------+--------+--------+
+ | 1.20 | N | N | N | Y | Y | Y |
+ +---------------------+----+-------------+---------+------+--------+--------+
+ | 1.21~1.24 | N | N | N | N | Y | Y |
+ +---------------------+----+-------------+---------+------+--------+--------+
+ | 1.25+ | N | N | N | N | N | Y |
+ +---------------------+----+-------------+---------+------+--------+--------+
As there are dozens of different versions of Oracle's clients this
list does not include all of them, just the major released versions of
@@ -4229,7 +4218,7 @@
- Free Oracle Tools and Links
-ora_explain supplied and installed with DBD::Oracle.
+ora\_explain supplied and installed with DBD::Oracle.
http://www.orafaq.com/
@@ -4272,25 +4261,14 @@
# AUTHORS
-- *
-
-Tim Bunce <[email protected]>
-
-- *
-
-John Scoles
-
-- *
-
-Yanick Champoux <[email protected]>
-
-- *
-
-Martin J. Evans <[email protected]>
+- Tim Bunce <[email protected]>
+- John Scoles
+- Yanick Champoux <[email protected]>
+- Martin J. Evans <[email protected]>
# COPYRIGHT AND LICENSE
This software is copyright (c) 1994 by Tim Bunce.
This is free software; you can redistribute it and/or modify it under
-the same terms as the Perl 5 programming language system itself.
\ No newline at end of file
+the same terms as the Perl 5 programming language system itself.
Modified: dbd-oracle/trunk/lib/DBD/Oracle.pm
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle.pm (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle.pm Fri Aug 17 08:35:55 2012
@@ -12,7 +12,7 @@
{
package DBD::Oracle;
{
- $DBD::Oracle::VERSION = '1.49_00';
+ $DBD::Oracle::VERSION = '1.50';
}
BEGIN {
$DBD::Oracle::AUTHORITY = 'cpan:PYTHIAN';
@@ -1146,7 +1146,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 SYNOPSIS
Modified: dbd-oracle/trunk/lib/DBD/Oracle/GetInfo.pm
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/GetInfo.pm (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/GetInfo.pm Fri Aug 17 08:35:55 2012
@@ -1,6 +1,6 @@
package DBD::Oracle::GetInfo;
{
- $DBD::Oracle::GetInfo::VERSION = '1.49_00';
+ $DBD::Oracle::GetInfo::VERSION = '1.50';
}
BEGIN {
$DBD::Oracle::GetInfo::AUTHORITY = 'cpan:PYTHIAN';
@@ -295,7 +295,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 AUTHORS
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Object.pm
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Object.pm (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Object.pm Fri Aug 17 08:35:55 2012
@@ -1,6 +1,6 @@
package DBD::Oracle::Object;
{
- $DBD::Oracle::Object::VERSION = '1.49_00';
+ $DBD::Oracle::Object::VERSION = '1.50';
}
BEGIN {
$DBD::Oracle::Object::AUTHORITY = 'cpan:PYTHIAN';
@@ -39,7 +39,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 AUTHORS
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 CONNECTING TO ORACLE
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Aix.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Aix.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Aix.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 Using Visual Age 7 C Compiler
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Cygwin.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Cygwin.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Cygwin.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 General Info
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Hpux.pm
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Hpux.pm (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Hpux.pm Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 INTRODUCTION
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Linux.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Linux.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Linux.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 Installing with Instantclient .rpm files.
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Macos.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Macos.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Macos.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 General Info
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Sun.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Sun.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Sun.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 General Info
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Vms.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Vms.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Vms.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 General Info
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Win32.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Win32.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Win32.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 GENERAL INFO
Modified: dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Win64.pod
==============================================================================
--- dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Win64.pod (original)
+++ dbd-oracle/trunk/lib/DBD/Oracle/Troubleshooting/Win64.pod Fri Aug 17 08:35:55 2012
@@ -11,7 +11,7 @@
=head1 VERSION
-version 1.49_00
+version 1.50
=head1 DBD::Oracle and Windows 64bit
Modified: dbd-oracle/trunk/t/000-report-versions.t
==============================================================================
--- dbd-oracle/trunk/t/000-report-versions.t (original)
+++ dbd-oracle/trunk/t/000-report-versions.t Fri Aug 17 08:35:55 2012
@@ -415,6 +415,7 @@
Test::Pod::Coverage
Test::Portability::Files
Test::YAML::Meta
+ open
);
my $Test = Test::Builder->new;
@@ -437,7 +438,7 @@
next;
}
local $SIG{__WARN__} = sub { note "$module: $_[0]" };
- use_ok $module or BAIL_OUT("can't load $module");
+ require_ok $module or BAIL_OUT("can't load $module");
my $version = $module->VERSION;
$version = 'undefined' unless defined $version;
diag(" $module version is $version");
Modified: dbd-oracle/trunk/t/10general.t
==============================================================================
--- dbd-oracle/trunk/t/10general.t (original)
+++ dbd-oracle/trunk/t/10general.t Fri Aug 17 08:35:55 2012
@@ -4,7 +4,6 @@
use Test::More;
use DBI;
-use Oraperl;
use Config;
use DBD::Oracle qw(ORA_OCI);
@@ -22,7 +21,7 @@
});
if ($dbh) {
- plan tests => 30;
+ plan tests => 28;
} else {
plan skip_all => "Unable to connect to Oracle";
}
@@ -114,8 +113,6 @@
#print "''$warn''";
ok($warn =~ /DBD::Oracle::db do failed:/, "warn error: ``$warn'' expected 'do failed:'");
ok($DBI::err, 'err defined');
-ok($ora_errno, 'ora_errno defined');
-is($ora_errno, $DBI::err, 'ora_errno and err equal');
$dbh->{RaiseError} = 0;
$dbh->{PrintError} = 0;
# ---
Modified: dbd-oracle/trunk/t/34pres_lobs.t
==============================================================================
--- dbd-oracle/trunk/t/34pres_lobs.t (original)
+++ dbd-oracle/trunk/t/34pres_lobs.t Fri Aug 17 08:35:55 2012
@@ -3,7 +3,6 @@
use Test::More;
use DBI;
-use Oraperl;
use Config;
use DBD::Oracle qw(:ora_types);