[svn:dbd-oracle] r15008 - in dbd-oracle/branches/DBD-Oracle-1.35_00: . examples lib/DBD/Oracle t
[email protected] Fri, 18 Nov 2011 11:41:57 -0800 (PST)
| Newsgroups | perl.dbd.oracle.changes |
|---|---|
| Message-ID | <[email protected]> |
Author: yanick
Date: Fri Nov 18 11:41:57 2011
New Revision: 15008
Added:
dbd-oracle/branches/DBD-Oracle-1.35_00/examples/inserting_longs.pl
dbd-oracle/branches/DBD-Oracle-1.35_00/examples/read_long_via_blob_read.pl
dbd-oracle/branches/DBD-Oracle-1.35_00/lib/DBD/Oracle/Troubleshooting.pm
dbd-oracle/branches/DBD-Oracle-1.35_00/t/rt13865.t
Modified:
dbd-oracle/branches/DBD-Oracle-1.35_00/Changes
dbd-oracle/branches/DBD-Oracle-1.35_00/MANIFEST
dbd-oracle/branches/DBD-Oracle-1.35_00/META.yml
dbd-oracle/branches/DBD-Oracle-1.35_00/Makefile.PL
dbd-oracle/branches/DBD-Oracle-1.35_00/Oracle.pm
dbd-oracle/branches/DBD-Oracle-1.35_00/dbdimp.h
dbd-oracle/branches/DBD-Oracle-1.35_00/examples/ora_explain.pl
dbd-oracle/branches/DBD-Oracle-1.35_00/oci8.c
dbd-oracle/branches/DBD-Oracle-1.35_00/t/58object.t
Log:
v1.35_00
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/Changes
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/Changes (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/Changes Fri Nov 18 11:41:57 2011
@@ -1,5 +1,32 @@
Revision history for DBD::Oracle
+Changes in DBD-Oracle 1.35_00 (18-11-2011)
+
+ [BUG FIXES]
+ - if bind_col is called with a TYPE but no bind attributes like
+ StrictlyTyped or DiscardString are set DBD::Oracle still attempts
+ to call sql_type_cast which is pointless (Martin J. Evans)
+ - if bind_col is called with a TYPE other than SQL_NUMERIC,
+ SQL_INTEGER or SQL_DOUBLE and bind attributes like StrictlyTyped or
+ DiscardString a warning was not issued that the type is unsupported
+ and no data was returned (Martin J. Evans)
+ - Fix test so it works with perl compiled with -Duselongdouble [RT71852]
+ - Apply patch from Charles Jardine for better building against a full
+ Oracle 11 install [RT72463] (Martin J. Evans)
+
+ [DOCUMENTATION]
+ - Added notes to bind_col documenting the fact that setting a TYPE
+ does not affect how the column is bound in Oracle, only what
+ happens after the column data is retrieved (Martin J. Evans)
+ - fix typo (thanks to Julián Moreno Patiño) [RT72038]
+ - shuffle POD around to improve documentation flow [RT72252]
+ - major tidying up of the connect() documentation. (by Gwen Shapira)
+ - Moved LONG examples out of POD and into examples/
+
+ [OTHER]
+ - Commented out some functions in oci8.c which were not used to
+ reduce the size of the driver a little (Martin J. Evans)
+
Changes in DBD-Oracle 1.34 (31-10-2011)
- promote 1.33_00 to official release
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/MANIFEST
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/MANIFEST (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/MANIFEST Fri Nov 18 11:41:57 2011
@@ -36,20 +36,13 @@
README.64bit.txt
README.aix.txt
README.clients.txt
-README.explain.txt
README.help.txt
README.hpux.txt
README.java.txt
-README.linux.txt
-README.login.txt
-README.longs.txt
README.macosx.txt
README.sec.txt
-README.sun.txt
-README.vms.txt
README.win32.txt
README.win64.txt
-README.wingcc.txt Notes about building with mingw32 and cygwin32
t/01base.t
t/10general.t
t/12impdata.t
@@ -88,4 +81,8 @@
typemap
examples/ora_explain.pl
t/00versions.t
+lib/DBD/Oracle/Troubleshooting.pm
+examples/inserting_longs.pl
+examples/read_long_via_blob_read.pl
+t/rt13865.t
META.yml Module meta-data (added by MakeMaker)
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/META.yml
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/META.yml (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/META.yml Fri Nov 18 11:41:57 2011
@@ -1,6 +1,6 @@
--- #YAML:1.0
name: DBD-Oracle
-version: 1.31_00
+version: 1.35_00
abstract: Oracle database driver for the DBI module
author:
- Tim Bunce ([email protected])
@@ -15,6 +15,15 @@
Test::Simple: 0.90
requires:
DBI: 1.51
+resources:
+ bugtracker:
+ mailto: bug-dbd-oracle at rt.cpan.org
+ web: http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Oracle
+ 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
no_index:
directory:
- t
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/Makefile.PL
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/Makefile.PL (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/Makefile.PL Fri Nov 18 11:41:57 2011
@@ -1149,7 +1149,9 @@
my @mk_oci32 = (
'rdbms/demo/demo_xe.mk',
- 'rdbms/demo/demo_rdbms32.mk'
+ 'rdbms/demo/demo_rdbms32.mk',
+ 'rdbms/demo/demo_rdbms.mk',
+ 'rdbms/lib/ins_rdbms.mk' #Oracle 11 full client
);
my @mk_oci64 = (
'rdbms/demo/demo_xe.mk',
@@ -1157,7 +1159,7 @@
'rdbms/demo/oracle.mk',
'rdbms/demo/demo_rdbms.mk',
'rdbms/demo/demo_rdbms64.mk',
- 'rdbms/lib/ins_rdbms.mk' #Oracle 11 full client for 64 bit and maybe 32 bit??
+ 'rdbms/lib/ins_rdbms.mk' #Oracle 11 full client
);
my @mk_oci = perl_is_64bit() ? @mk_oci64 : @mk_oci32;
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/Oracle.pm
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/Oracle.pm (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/Oracle.pm Fri Nov 18 11:41:57 2011
@@ -7,7 +7,7 @@
require 5.006;
-$DBD::Oracle::VERSION = '1.34';
+$DBD::Oracle::VERSION = '1.35_00';
my $ORACLE_ENV = ($^O eq 'VMS') ? 'ORA_ROOT' : 'ORACLE_HOME';
@@ -1118,66 +1118,11 @@
DBD::Oracle is a Perl module which works with the DBI module to provide
access to Oracle databases.
-=head1 Module Documentation
-
-
This documentation describes driver specific behaviour and restrictions. It is
not supposed to be used as the only reference for the user. In any case
-consult the B<DBI> documentation first!
-
-=for html <a href="http://search.cpan.org/~timb/DBI/DBI.pm">Latest DBI documentation.</a>
-
-=head1 Which version DBD::Oracle is for me?
-
-From version 1.25 onwards DBD::Oracle will only support Oracle clients
-9.2 or greater. Support for ProC connections was dropped in 1.29.
-
-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 |
- +---------------------+----+-------------+---------+------+--------+--------+
-
-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
-Oracle.
+consult the L<DBI> documentation first!
-Note that one can still connect to any Oracle version with the older
-DBD::Oracle versions the only problem you will have is that some of
-the newer OCI and Oracle features available in later DBD::Oracle
-releases will not be available to you.
-
-So to make a short story a little longer;
-
- 1) If you are using Oracle 7 or early 8 DB and you can manage to get a 9 client and you can use
- any DBD::Oracle version.
- 2) If you have to use an Oracle 7 client then DBD::Oracle 1.17 should work
- 3) Same thing for 8 up to R2, use 1.17, if you are lucky and have the right patch-set you might
- go with 1.18.
- 4) For 8iR3 you can use any of the DBD::Oracle versions up to 1.21. Again this depends on your
- patch-set, If you run into trouble go with 1.19
- 5) After 9.2 you can use any version you want.
- 6) For you Luddites out there ORAPERL still works and is still included but not updated or supported anymore and was removed in 1.29.
- 7) It seems that the 10g client can only connect to 9 and 11 DBs while the 9 can go back to 7
- and even get to 10. I am not sure what the 11g client can connect to.
-
-=head1 Constants
+=head1 CONSTANTS
=over 4
@@ -1244,143 +1189,49 @@
=back
-=head1 The DBI Class
-
-=head2 DBI Class Methods
-
-=head3 B<connect>
-
-This method creates a database handle by connecting to a database, and is the DBI
-equivalent of the "new" method.
-
-This is a topic which often causes problems. Mainly due to Oracle's many
-and sometimes complex ways of specifying and connecting to databases.
-James Taylor and Lane Sharman have contributed much of the text in
-this section. Unfortunately it is only really relative for connecting into older Oracle (<9) versions.
-Most of this stuff is well out of date but it will be left in for now.
-See the next section L</Connecting To Oracle> for some more up to date connection hints.
-
-=head4 Connecting without environment variables or tnsnames.ora file
-
-If you use the C<host=$host;sid=$sid> style syntax, for example:
-
- $dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=ORCL", $user, $passwd);
-
-then DBD::Oracle will construct a full connection descriptor string
-for you and Oracle will not need to consult the tnsnames.ora file.
-
-If a C<port> number is not specified then the descriptor will try both
-1526 and 1521 in that order (e.g., new then old). You can check which
-port(s) are in use by typing "$ORACLE_HOME/bin/lsnrctl stat" on the server.
-
-=head4 Oracle Environment Variables
-
-Oracle typically no longer needs two environment variables to specify default
-connections: ORACLE_SID and TWO_TASK.
-
-ORACLE_SID is really unnecessary to set since TWO_TASK provides the
-same functionality in addition to allowing remote connections.
-
- % setenv //xxx.yyy.zzz:1521/ORACLE_SID # for csh shell
- $ TWO_TASK=T:hostname:ORACLE_SID export TWO_TASK # for sh shell
-
- % sqlplus username/password
-
-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).
-
- TWO_TASK=P:sid
-
-will use the pipe driver for local connections using SQL*Net v1.
-
- TWO_TASK=T:machine:sid
-
-will use TCP/IP (or D for DECNET, etc.) for remote SQL*Net v1 connection.
-
- TWO_TASK=dbname
-
-will use the info stored in the SQL*Net v2 F<tnsnames.ora>
-configuration file for local or remote connections.
-
-Support for 'T:' syntax of Oracle SQL*Net V1 is only supported on older 7 clients and
-I have my doubts it will even work if the DB or client has been patched and I know it
-will not work on any later clients.
-
-The 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_HOME can be left unset if you aren't using any of Oracle's
-executables, but it is I<not> recommended and error messages may not display.
-It should be set to the ORACLE_HOME directory of the version of Oracle
-that DBD::Oracle was compiled with.
-
-Discouraging the use of ORACLE_SID makes it easier on the users to see
-what is going on. (It's unfortunate that TWO_TASK couldn't be renamed,
-since it makes no sense to the end user, and doesn't have the ORACLE prefix).
+=head1 DBI CLASS METHODS
-Also remember that depending on the operating system you are using
-the differing "ORACLE" environment variables may be case sensitive, so if you are not connecting
-as you should double check the case of both the variable and its value.
+=head2 B<connect>
-=head4 Connection Examples Using DBD::Oracle
+This method creates a database handle by connecting to a database, and is the DBI equivalent of the "new" method.
+To open a connection to an Oracle database you need to specify a database connection string (URL), username and password.
-First, how to connect to a local database I<without> using a Listener:
+The connection string is always of the form: "dbi:Oracle:<db identifier>"
+There are several ways to identify a database:
- $dbh = DBI->connect('dbi:Oracle:SID','scott', 'tiger');
+=over
-you can also leave the SID empty:
+=item 1
- $dbh = DBI->connect('dbi:Oracle:','scott', 'tiger');
+If the database is local, specifying the SID or service name will be enough.
-in which case Oracle client code will use the ORACLE_SID environment
-variable (if the TWO_TASK environment varariable is not defined).
+=item 2
-Below are various ways of connecting to an Oracle database using
-SQL*Net 1.x and SQL*Net 2.x. "Machine" is the computer the database is
-running on, "SID" is the SID of the database, "DB" is the SQL*Net 2.x
-connection descriptor for the database.
+If the database is defined in a TNSNAMES.ORA file, you can use the service name given in the file
-B<Note:> Some of these formats may not work with Oracle 9+.
+=item 3
- BEGIN {
- $ENV{ORACLE_HOME} = '/home/oracle/product/10.x.x';
- $ENV{TWO_TASK} = 'DB';
- }
- $dbh = DBI->connect('dbi:Oracle:','scott', 'tiger');
- # - or -
- $dbh = DBI->connect('dbi:Oracle:','scott/tiger');
+To connect without TNSNAMES.ORA file, you can use an EZCONNECT url, of the form:
+//host[:port][/service_name]
-Refer to your Oracle documentation for valid values of TWO_TASK.
+=back
-Here are some variations (not setting TWO_TASK) in order of preference:
+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.
- $dbh = DBI->connect('dbi:Oracle:DB','username','password')
+The following examples show several ways a connection can be created:
- $dbh = DBI->connect('dbi:Oracle:DB','username/password','')
- $dbh = DBI->connect('dbi:Oracle:','username@DB','password')
+ $dbh = DBI->connect('dbi:Oracle:DB','username','password');
- $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521', 'scott/tiger', '')
+ $dbh = DBI->connect('dbi:Oracle:DB','username/password','');
- $dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
- (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
- (CONNECT_DATA=(SID=ORCL)))}, "")
+ $dbh = DBI->connect('dbi:Oracle:','username@DB','password');
-If you are having problems with login taking a long time (>10 seconds say)
-then you might have tripped up on an Oracle bug. You can try using one
-of the ...@DB variants as a workaround. e.g.,
+ $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=DB;port=1521', 'scott/tiger', '');
- $dbh = DBI->connect('','username/password@DB','');
+ $dbh = DBI->connect("dbi:Oracle://myhost:1522/ORCL",'username', 'password');
-On the other hand, that may cause you to trip up on another Oracle bug
-that causes alternating connection attempts to fail! (in reality only
-a small proportion of people experience these problems)
+=head3 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"):
@@ -1394,121 +1245,37 @@
databases using your Unix login name without a password is possible
but it is not secure and not recommended so not documented here.
+=head3 Oracle Environment Variables
-=head4 Connecting To Oracle
-
-If you are reading this it is assumed that you have successfully
-installed DBD::Oracle and you are having some problems connecting to
-Oracle.
-
-First off you will have to tell DBD::Oracle where the binaries reside
-for the Oracle client it was compiled against. This is the case when
-you encounter a
-
- DBI connect('','system',...) failed: ERROR OCIEnvNlsCreate.
-
-error in Linux or in Windows when you get
-
- OCI.DLL not found
-
-The solution to this problem in the case of Linux is to ensure your
-'ORACLE_HOME' (or LD_LIBRARY_PATH for InstantClient) environment
-variable points to the correct directory.
-
- export ORACLE_HOME=/app/oracle/product/xx.x.x
-
-For Windows the solution is to add this value to you PATH
-
- PATH=c:\app\oracle\product\xx.x.x;%PATH%
-
-
-If you get past this stage and get a
-
- ORA-12154: TNS:could not resolve the connect identifier specified
-
-error then the most likely cause is DBD::ORACLE cannot find your .ORA
-(F<TNSNAMES.ORA>, F<LISTENER.ORA>, F<SQLNET.ORA>) files. This can be
-solved by setting the TNS_ADMIN environment variable to the directory
-where these files can be found.
-
-If you get to this stage and you have either one of the following
-errors;
-
- ORA-12560: TNS:protocol adapter error
- ORA-12162: TNS:net service name is incorrectly specified
-
-usually means that DBD::Oracle can find the listener but the it cannot connect to the DB because the listener cannot find the DB you asked for.
-
-=head4 Connection Examples Using DBD::Oracle
-
-It is best to not use ORACLE_SID or TWO_TASK as both of these are rather out of date. You are better off keeping it simple like the following examples
-
- $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:host=foobar;sid=DB;port=1521', 'scott/tiger', '');
-
-
-For those who really want to use ORACLE_SID and TWO_TASK here are examples of it in use;
-
-Given this TNS entry;
-
- DB.TEST =
- (DESCRIPTION =
- (ADDRESS =
- (PROTOCOL = TCP)
- (HOST = xxx.xxx.xxx.xx)
- (PORT = 1523))
- (CONNECT_DATA = (SID = DB) )
-)
-
-and this code
-
- BEGIN {
- $ENV{ORACLE_SID} = 'DB';
- }
-
- $dbh = DBI->connect('dbi:Oracle:','username/password','');
+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).
-you will be able to connect to DB. Note this may not work for Windows.
+Oracle can use two environment variables to specify default connections: ORACLE_SID and TWO_TASK.
-TWO_TASK works the same way except it should override the value in ORACLE_SID so this
+To use them, specify either a local SID or service name, or a service name that is specified in the TNSNAMES.ORA file.
- BEGIN {
- $ENV{ORACLE_SID} = 'DB';
- $ENV{TWO_TASK} = 'DB.TEST';
+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).
- }
-
- $dbh = DBI->connect('dbi:Oracle:','username/password','');
+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.
-will work as well. Note this may not work for Windows.
+Also remember that depending on the operating system you are using the differing "ORACLE" environment variables may be case sensitive, so if you are not connecting as you should double check the case of both the variable and its value.
-=head4 Timezones
+=head3 Timezones
-If TWO_TASK isn't set, Oracle uses the TZ variable from the local environment.
+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 TWO_TASK IS set, Oracle uses the TZ variable of the listener process
-running on the server.
+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.
-You could have multiple listeners, each with their own TZ, and assign
-users to the appropriate listener by setting TNS_ADMIN to a directory
-that contains a tnsnames.ora file that points to the port that their
-listener is on.
+With local queries, you can change the time zone for a particular user by simply changing the setting of "TZ". To check the current setting,
+issue the UNIX "date" command.
-[Brad Howerter, who supplied this info said: "I've done this to simulate
-running a Perl script at the end of the previous month even though it
-was the 6th of the new month. I had the dba start up a listener with
-TZ=X+144. (144 hours = 6 days)"]
+=head3 Oracle DRCP
-=head4 Oracle DRCP
-
-DBD::Oracle now supports DRCP (Database Resident Connection Pool) so
-if you have an 11.2 database and DRCP is enabled you can now direct
-all of your connections to it by simply adding ':POOLED' to the SID or
+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
when using a TNSENTRY style connection or even by setting an
environment variable ORA_DRCP. All of which are demonstrated below;
@@ -1538,7 +1305,7 @@
Please note that DRCP support in DBD::Oracle is relatively new so the
mechanics or its implementation are subject to change.
-=head4 TAF (Transparent Application Failover)
+=head3 TAF (Transparent Application Failover)
Transparent Application Failover (TAF) is the feature in OCI that
allows for clients to automatically reconnect to an instance in the
@@ -1633,95 +1400,12 @@
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).
-
-=head4 Optimizing Oracle's listener
-
-[By Lane Sharman <[email protected]>] I spent a lot of time optimizing
-listener.ora and I am including it here for anyone to benefit from. My
-connections over tnslistener on the same humble Netra 1 take an average
-of 10-20 milli seconds according to tnsping. If anyone knows how to
-make it better, please let me know!
-
- LISTENER =
- (ADDRESS_LIST =
- (ADDRESS =
- (PROTOCOL = TCP)
- (Host = aa.bbb.cc.d)
- (Port = 1521)
- (QUEUESIZE=10)
- )
- )
-
- STARTUP_WAIT_TIME_LISTENER = 0
- CONNECT_TIMEOUT_LISTENER = 10
- TRACE_LEVEL_LISTENER = OFF
- SID_LIST_LISTENER =
- (SID_LIST =
- (SID_DESC =
- (SID_NAME = xxxx)
- (ORACLE_HOME = /xxx/local/oracle7-3)
- (PRESPAWN_MAX = 40)
- (PRESPAWN_LIST=
- (PRESPAWN_DESC=(PROTOCOL=tcp) (POOL_SIZE=40) (TIMEOUT=120))
- )
- )
- )
-
-1) When the application is co-located on the host and there is no need for
-outside SQLNet connectivity, stop the listener. You do not need it. Get
-your application/cgi/whatever working using pipes and shared memory. I am
-convinced that this is one of the connection bugs (sockets over the same
-machine). Note the $ENV{ORAPIPES} env var. The essential code to do
-this at the end of this section.
-
-2) Be careful in how you implement the multi-threaded server. Currently I
-am not using it in the initxxxx.ora file but will be doing some more testing.
-
-3) Be sure to create user rollback segments and use them; do not use the
-system rollback segments; however, you must also create a small rollback
-space for the system as well.
-
-5) Use large tuning settings and get lots of RAM. Check out all the
-parameters you can set in v$parameters because there are quite a few not
-documented you may to set in your initxxx.ora file.
-
-6) Use svrmgrl to control oracle from the command line. Write lots of small
-SQL scripts to get at V$ info.
-
- use DBI;
- # Environmental variables used by Oracle
- $ENV{ORACLE_SID} = "xxx";
- $ENV{ORACLE_HOME} = "/opt/oracle7";
- $ENV{EPC_DISABLED} = "TRUE";
- $ENV{ORAPIPES} = "V2";
- my $dbname = "xxx";
- my $dbuser = "xxx";
- my $dbpass = "xxx";
- my $dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser, $dbpass)
- || die "Unable to connect to $dbname: $DBI::errstr\n";
-
-=head4 Oracle utilities
-
-If you are still having problems connecting then the Oracle adapters
-utility may offer some help. Run these two commands:
-
- $ORACLE_HOME/bin/adapters
- $ORACLE_HOME/bin/adapters $ORACLE_HOME/bin/sqlplus
-
-and check the output. The "Protocol Adapters" section should be the
-same. It should include at least "IPC Protocol Adapter" and "TCP/IP
-Protocol Adapter".
-
-If it generates any errors which look relevant then please talk to your
-Oracle technical support (and not the dbi-users mailing list). Thanks.
-Thanks to Mark Dedlow for this information.
-
-=head3 Private Connect Attributes
+=head3 Connect Attributes
=head4 ora_ncs_buff_mtpl
-You can now customize the size of the buffer when selecting LOBs with
-the built in AUTO Lob. The default value is 4 which is probably
+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
excessive for most situations but is needed for backward
compatibility. If you not converting between a NCS on the DB and the
Client then you might want to set this to 1 to reduce memory usage.
@@ -1730,14 +1414,11 @@
environment variable in which case it sets the value at the connect
stage.
-See more details in the LOB section of the POD
-
=head4 ora_drcp
-If you have an 11.2 or greater database your can utilize the DRCP by setting
-this attribute to 1 at connect time.
+For Oracle 11.2 or greater.
-This value can also be set with the C<ORA_DRCP> environment variable.
+Set to I<1> to enable DRCP. Can also be set via the C<ORA_DRCP> environment variable.
=head4 ora_drcp_class
@@ -1792,7 +1473,7 @@
=head4 ora_taf
If your Oracle instance has been configured to use TAF events you can
-enable the TAF callback by setting this value to anything other than 0.
+enable the TAF callback by setting this option to any I<true> value.
=head4 ora_taf_function
@@ -1814,7 +1495,6 @@
The amount of time in seconds the OCI client will sleep between attempting
successive failover events when the event is OCI_FO_ERROR.
-
=head4 ora_session_mode
The ora_session_mode attribute can be used to connect with SYSDBA
@@ -1911,7 +1591,9 @@
=head4 ora_dbh_share
-Requires at least Perl 5.8.0 compiled with ithreads. Allows you to 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
attribute will use the same database connection. The value must be a
@@ -2074,11 +1756,12 @@
NOTE disabling the signal handlers the OCI library sets up may affect
functionality in the OCI library.
-=head3 B<connect_cached>
+=head2 B<connect_cached>
-Implemented by DBI, no driver-specific impact. Please note that connect_cached as not been tested with DRCP.
+Implemented by DBI, no driver-specific impact.
+Please note that connect_cached as not been tested with DRCP.
-=head3 B<data_sources>
+=head2 B<data_sources>
@data_sources = DBI->data_sources('Oracle');
@data_sources = $dbh->data_sources();
@@ -2087,29 +1770,26 @@
'TNS_ADMIN' environment value to retrieve this list. It will read these values from
TNSNAMES.ORA file entries.
-
-
-
-=head2 Methods Common To All Handles
+=head1 METHODS COMMON TO ALL HANDLES
For all of the methods below, B<$h> can be either a database handle (B<$dbh>)
or a statement handle (B<$sth>). Note that I<$dbh> and I<$sth> can be replaced with
any variable name you choose: these are just the names most often used. Another
common variable used in this documentation is $I<rv>, which stands for "return value".
-=head3 B<err>
+=head2 B<err>
$rv = $h->err;
Returns the error code from the last method called.
-=head3 B<errstr>
+=head2 B<errstr>
$str = $h->errstr;
Returns the last error that was reported by Oracle. Starting with "ORA-00000" code followed by the error message.
-=head3 B<state>
+=head2 B<state>
$str = $h->state;
@@ -2119,29 +1799,29 @@
While this method can be called as either C<< $sth->state >> or C<< $dbh->state >>, it
is usually clearer to always use C<< $dbh->state >>.
-=head3 B<trace>
+=head2 B<trace>
Implemented by DBI, no driver-specific impact.
-=head3 B<trace_msg>
+=head2 B<trace_msg>
Implemented by DBI, no driver-specific impact.
-=head3 B<parse_trace_flag> and B<parse_trace_flags>
+=head2 B<parse_trace_flag> and B<parse_trace_flags>
Implemented by DBI, no driver-specific impact.
-=head3 B<func>
+=head2 B<func>
DBD::Oracle uses the C<func> method to support a variety of functions.
-=head3 B<Private database handle functions>
+=head2 B<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_
can be called directly.
-=head3 B<plsql_errstr>
+=head2 B<plsql_errstr>
This function returns a string which describes the errors
from the most recent PL/SQL function, procedure, package,
@@ -2171,7 +1851,7 @@
die $msg if $msg;
}
-=head3 B<dbms_output_enable / dbms_output_put / dbms_output_get>
+=head2 B<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
@@ -2210,7 +1890,7 @@
# retrieve the string
$date_string = $dbh->func( 'dbms_output_get' );
-=head3 B<dbms_output_enable ( [ buffer_size ] )>
+=head2 B<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
@@ -2221,7 +1901,7 @@
buffer and must be between 2000 and 1,000,000. If buffer_size is not
given, the default is 20,000 bytes.
-=head3 B<dbms_output_put ( [ @lines ] )>
+=head2 B<dbms_output_put ( [ @lines ] )>
This function calls DBMS_OUTPUT.PUT_LINE to add lines to the buffer.
@@ -2232,7 +1912,7 @@
is raised and the function call fails. Some of the text might be in
the buffer.
-=head3 B<dbms_output_get>
+=head2 B<dbms_output_get>
This function calls DBMS_OUTPUT.GET_LINE to retrieve lines of text from
the buffer.
@@ -2248,13 +1928,13 @@
DBMS_OUTPUT.GET is discarded by the next call to DBMS_OUTPUT.PUT_LINE,
DBMS_OUTPUT.PUT, or DBMS_OUTPUT.NEW_LINE.
-=head3 B<reauthenticate ( $username, $password )>
+=head2 B<reauthenticate ( $username, $password )>
Starts a new session against the current database using the credentials
supplied.
-=head3 B<private_attribute_info>
+=head2 B<private_attribute_info>
$hashref = $dbh->private_attribute_info();
$hashref = $sth->private_attribute_info();
@@ -2262,127 +1942,127 @@
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.
-=head2 Attributes Common To All Handles
+=head1 ATTRIBUTES COMMON TO ALL HANDLES
-=head3 B<InactiveDestroy> (boolean)
+=head2 B<InactiveDestroy> (boolean)
Implemented by DBI, no driver-specific impact.
-=head3 B<RaiseError> (boolean, inherited)
+=head2 B<RaiseError> (boolean, inherited)
Forces errors to always raise an exception. Although it defaults to off, it is recommended that this
be turned on, as the alternative is to check the return value of every method (prepare, execute, fetch, etc.)
manually, which is easy to forget to do.
-=head3 B<PrintError> (boolean, inherited)
+=head2 B<PrintError> (boolean, inherited)
Forces database errors to also generate warnings, which can then be filtered with methods such as
locally redefining I<$SIG{__WARN__}> or using modules such as C<CGI::Carp>. This attribute is on
by default.
-=head3 B<ShowErrorStatement> (boolean, inherited)
+=head2 B<ShowErrorStatement> (boolean, inherited)
Appends information about the current statement to error messages. If placeholder information
is available, adds that as well. Defaults to true.
-=head3 B<Warn> (boolean, inherited)
+=head2 B<Warn> (boolean, inherited)
Enables warnings. This is on by default, and should only be turned off in a local block
for a short a time only when absolutely needed.
-=head3 B<Executed> (boolean, read-only)
+=head2 B<Executed> (boolean, read-only)
Indicates if a handle has been executed. For database handles, this value is true after the L</do> method has been called, or
when one of the child statement handles has issued an L</execute>. Issuing a L</commit> or L</rollback> always resets the
attribute to false for database handles. For statement handles, any call to L</execute> or its variants will flip the value to
true for the lifetime of the statement handle.
-=head3 B<TraceLevel> (integer, inherited)
+=head2 B<TraceLevel> (integer, inherited)
Sets the trace level, similar to the L</trace> method. See the sections on
L</trace> and L</parse_trace_flag> for more details.
-=head3 B<Active> (boolean, read-only)
+=head2 B<Active> (boolean, read-only)
Indicates if a handle is active or not. For database handles, this indicates if the database has
been disconnected or not. For statement handles, it indicates if all the data has been fetched yet
or not. Use of this attribute is not encouraged.
-=head3 B<Kids> (integer, read-only)
+=head2 B<Kids> (integer, read-only)
Returns the number of child processes created for each handle type. For a driver handle, indicates the number
of database handles created. For a database handle, indicates the number of statement handles created. For
statement handles, it always returns zero, because statement handles do not create kids.
-=head3 B<ActiveKids> (integer, read-only)
+=head2 B<ActiveKids> (integer, read-only)
Same as C<Kids>, but only returns those that are active.
-=head3 B<CachedKids> (hash ref)
+=head2 B<CachedKids> (hash ref)
Returns a hashref of handles. If called on a database handle, returns all statement handles created by use of the
C<prepare_cached> method. If called on a driver handle, returns all database handles created by the L</connect_cached>
method.
-=head3 B<ChildHandles> (array ref)
+=head2 B<ChildHandles> (array ref)
Implemented by DBI, no driver-specific impact.
-=head3 B<PrintWarn> (boolean, inherited)
+=head2 B<PrintWarn> (boolean, inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<HandleError> (boolean, inherited)
+=head2 B<HandleError> (boolean, inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<HandleSetErr> (code ref, inherited)
+=head2 B<HandleSetErr> (code ref, inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<ErrCount> (unsigned integer)
+=head2 B<ErrCount> (unsigned integer)
Implemented by DBI, no driver-specific impact.
-=head3 B<FetchHashKeyName> (string, inherited)
+=head2 B<FetchHashKeyName> (string, inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<ChopBlanks> (boolean, inherited)
+=head2 B<ChopBlanks> (boolean, inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<Taint> (boolean, inherited)
+=head2 B<Taint> (boolean, inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<TaintIn> (boolean, inherited)
+=head2 B<TaintIn> (boolean, inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<TaintOut> (boolean, inherited)
+=head2 B<TaintOut> (boolean, inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<Profile> (inherited)
+=head2 B<Profile> (inherited)
Implemented by DBI, no driver-specific impact.
-=head3 B<Type> (scalar)
+=head2 B<Type> (scalar)
Returns C<dr> for a driver handle, C<db> for a database handle, and C<st> for a statement handle.
Should be rarely needed.
-=head3 B<LongReadLen>
+=head2 B<LongReadLen>
Implemented by DBI, no driver-specific impact.
-=head3 B<LongTruncOk>
+=head2 B<LongTruncOk>
Implemented by DBI, no driver-specific impact.
-=head3 B<CompatMode>
+=head2 B<CompatMode>
Type: boolean, inherited
@@ -2391,11 +2071,35 @@
It also has the effect of disabling the 'quick FETCH' of attribute values from the handles attribute cache. So all attribute values are handled by the drivers own FETCH method. This makes them slightly slower but is useful for special-purpose drivers like DBD::Multiplex.
-=head1 DBI Database Handle Object
+=head1 ORACLE-SPECIFIC DATABASE HANDLE METHODS
+
+=head2 B<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
+$refresh parameter to it.
+
+0 = Neither character set is a Unicode encoding.
+
+1 = National character set is a Unicode encoding.
+
+2 = Database character set is a Unicode encoding.
+
+3 = Both character sets are Unicode encodings.
+
+=head2 B<ora_can_taf>
+
+Returns true if the current connection supports TAF events. False if otherise.
+
+=head2 B<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.
+To cause the latest values to be fetched, pass a true value to the function.
-=head2 Database Handle Methods
+=head1 DATABASE HANDLE METHODS
-=head3 B<selectall_arrayref>
+=head2 B<selectall_arrayref>
$ary_ref = $dbh->selectall_arrayref($sql);
$ary_ref = $dbh->selectall_arrayref($sql, \%attr);
@@ -2404,14 +2108,14 @@
Returns a reference to an array containing the rows returned by preparing and executing the SQL string.
See the DBI documentation for full details.
-=head3 B<selectall_hashref>
+=head2 B<selectall_hashref>
$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.
-=head3 B<selectcol_arrayref>
+=head2 B<selectcol_arrayref>
$ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
@@ -2419,13 +2123,13 @@
from each rows returned by preparing and executing the SQL string. It is possible to specify exactly
which columns to return. See the DBI documentation for full details.
-=head3 B<prepare>
+=head2 B<prepare>
$sth = $dbh->prepare($statement, \%attr);
Prepares a statement for later execution by the database engine and returns a reference to a statement handle object.
-=head4 B<Prepare Attributes>
+=head3 B<Prepare Attributes>
These attributes may be used in the C<\%attr> parameter of the
L<DBI/prepare> database handle method.
@@ -2506,7 +2210,7 @@
=back
-=head4 B<Placeholders>
+=head3 B<Placeholders>
There are two types of placeholders that can be used in DBD::Oracle. The first is
the "question mark" type, in which each placeholder is represented by a single
@@ -2527,14 +2231,14 @@
stick to one style within your program.
-=head3 B<prepare_cached>
+=head2 B<prepare_cached>
$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.
-=head3 B<do>
+=head2 B<do>
$rv = $dbh->do($statement);
$rv = $dbh->do($statement, \%attr);
@@ -2546,7 +2250,7 @@
of 0 for 'no rows were affected', in order to always return a true value if no error occurred.
-=head3 B<last_insert_id>
+=head2 B<last_insert_id>
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,
@@ -2567,7 +2271,7 @@
$sth->execute();
$db->commit();
-=head3 B<commit>
+=head2 B<commit>
$rv = $dbh->commit;
@@ -2575,7 +2279,7 @@
all changes made will be visible to other processes. If AutoCommit is enabled, then
a warning is given and no COMMIT is issued. Returns true on success, false on error.
-=head3 B<rollback>
+=head2 B<rollback>
$rv = $dbh->rollback;
@@ -2583,13 +2287,13 @@
is enabled, then a warning is given and no ROLLBACK is issued. Returns true on success, and
false on error.
-=head3 B<begin_work>
+=head2 B<begin_work>
This method turns on transactions until the next call to L</commit> or L</rollback>, if L</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.
-=head3 B<disconnect>
+=head2 B<disconnect>
$rv = $dbh->disconnect;
@@ -2602,7 +2306,7 @@
methods automatically. It is best to explicitly disconnect rather than rely on this behavior.
-=head3 B<ping>
+=head2 B<ping>
$rv = $dbh->ping;
@@ -2610,13 +2314,13 @@
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.
-=head3 B<get_info()>
+=head2 B<get_info()>
$value = $dbh->get_info($info_type);
DBD::Oracle supports C<get_info()>, but (currently) only a few info types.
-=head3 B<table_info()>
+=head2 B<table_info()>
DBD::Oracle supports attributes for C<table_info()>.
@@ -2653,7 +2357,7 @@
C<table_info()> has no special quote handling, neither adds nor
removes quotes.
-=head3 B<primary_key_info()>
+=head2 B<primary_key_info()>
Oracle does not support catalogues so TABLE_CAT is ignored as
selection criterion.
@@ -2669,7 +2373,7 @@
Oracle returns it.
See L</table_info()> for more detailed information.
-=head3 B<foreign_key_info()>
+=head2 B<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 B<and> alternate keys.
@@ -2702,7 +2406,7 @@
Oracle returns it.
See L</table_info()> for more detailed information.
-=head3 B<column_info()>
+=head2 B<column_info()>
Oracle does not support catalogues so TABLE_CAT is ignored as
selection criterion.
@@ -2735,7 +2439,7 @@
any case can be used to get info on the column.
-=head3 B<selectrow_array>
+=head2 B<selectrow_array>
@row_ary = $dbh->selectrow_array($sql);
@row_ary = $dbh->selectrow_array($sql, \%attr);
@@ -2746,7 +2450,7 @@
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.
-=head3 B<selectrow_arrayref>
+=head2 B<selectrow_arrayref>
$ary_ref = $dbh->selectrow_arrayref($statement);
$ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
@@ -2755,7 +2459,7 @@
Exactly the same as L</selectrow_array>, except that it returns a reference to an array, by internal use of
the L</fetchrow_arrayref> method.
-=head3 B<selectrow_hashref>
+=head2 B<selectrow_hashref>
$hash_ref = $dbh->selectrow_hashref($sql);
$hash_ref = $dbh->selectrow_hashref($sql, \%attr);
@@ -2764,49 +2468,22 @@
Exactly the same as L</selectrow_array>, except that it returns a reference to an hash, by internal use of
the L</fetchrow_hashref> method.
-=head3 B<clone>
+=head2 B<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.
-=head2 Private Database Handle Methods
-
-=head3 B<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
-$refresh parameter to it.
-
-0 = Neither character set is a Unicode encoding.
-
-1 = National character set is a Unicode encoding.
-
-2 = Database character set is a Unicode encoding.
-
-3 = Both character sets are Unicode encodings.
-
-=head3 B<ora_can_taf>
-
-Returns true if the current connection supports TAF events. False if otherise.
-
-=head3 B<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.
-To cause the latest values to be fetched, pass a true value to the function.
-
-
-=head2 Database Handle Attributes
+=head1 DATABASE HANDLE ATTRIBUTES
-=head3 B<AutoCommit> (boolean)
+=head2 B<AutoCommit> (boolean)
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 L</connect>.
-=head3 B<ReadOnly> (boolean)
+=head2 B<ReadOnly> (boolean)
$dbh->{ReadOnly} = 1;
@@ -2818,29 +2495,29 @@
This method method requires DBI version 1.55 or better.
-=head3 B<Name> (string, read-only)
+=head2 B<Name> (string, read-only)
Returns the name of the current database. This is the same as the DSN, without the
"dbi:Oracle:" part.
-=head3 B<Username> (string, read-only)
+=head2 B<Username> (string, read-only)
Returns the name of the user connected to the database.
-=head3 B<Driver> (handle, read-only)
+=head2 B<Driver> (handle, read-only)
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}
-=head3 B<RowCacheSize>
+=head2 B<RowCacheSize>
DBD::Oracle supports both Server pre-fetch and Client side row caching. By default both
are turned on to give optimum performance. Most of the time one can just let DBD::Oracle
figure out the best optimization.
-=head4 B<Row Caching>
+=head3 B<Row Caching>
Row caching occurs on the client side and the object of it is to cut down the number of round
trips made to the server when fetching rows. At each fetch a set number of rows will be retrieved
@@ -2858,7 +2535,7 @@
For any SQL statement that contains a LOB, Long or Object Type Row Caching will be turned off. However server side
caching still works. If you are only selecting a LOB Locator then Row Caching will still work.
-=head4 Row Prefetching
+=head3 Row Prefetching
Row prefetching occurs on the server side and uses the DBI database handle attribute C<RowCacheSize> and or the
Prepare Attribute 'ora_prefetch_memory'. Tweaking these values may yield improved performance.
@@ -2884,12 +2561,19 @@
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.
+=head1 ORACLE-SPECIFIC STATEMENT HANDLE METHODS
-=head1 DBI Statement Handle Object
+=head2 B<ora_stmt_type>
-=head2 Statement Handle Methods
+Returns the OCI Statement Type number for the SQL of a statement handle.
+
+=head2 B<ora_stmt_type_name>
+
+Returns the OCI Statement Type name for the SQL of a statement handle.
-=head3 B<bind_param>
+=head1 DBI STATEMENT HANDLE OBJECT METHODS
+
+=head2 B<bind_param>
$rv = $sth->bind_param($param_num, $bind_value);
$rv = $sth->bind_param($param_num, $bind_value, $bind_type);
@@ -3061,2226 +2745,2220 @@
Set L</ora_check_sql> to 0 in prepare() to enable this behaviour.
-=head3 Spaces & Padding
+=head2 B<bind_param_inout>
-=head4 Trailing Spaces
+ $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
-Please note that only the Oracle OCI 8 strips trailing spaces from VARCHAR placeholder
-values and uses Nonpadded Comparison Semantics with the result.
-This causes trouble if the spaces are needed for
-comparison with a CHAR value or to prevent the value from
-becoming '' which Oracle treats as NULL.
-Look for Blank-padded Comparison Semantics and Nonpadded
-Comparison Semantics in Oracle's SQL Reference or Server
-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 L</ora_ph_type> or the placeholder
-type for a particular call to L<DBI/bind> or L<DBI/bind_param_inout>
-with L</ora_type> or C<TYPE>.
-Using L<ORA_CHAR> with L<ora_type> or C<SQL_CHAR> with C<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.
+DBD::Oracle fully supports bind_param_inout below are some uses for this method.
-Please remember that using spaces as a value or at the end of
-a value makes visually distinguishing values with different
-numbers of spaces difficult and should be avoided.
-Oracle Clients that use OCI 9.2 do not strip trailing spaces.
+=head3 B<Returning A Value from an INSERT>
-=head4 Padded Char Fields
+Oracle supports an extended SQL insert syntax which will return one
+or more of the values inserted. This can be particularly useful for
+single-pass insertion of values with re-used sequence values
+(avoiding a separate "select seq.nextval from dual" step).
-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 L</ora_ph_type> or placeholder
-type for a particular call with L<DBI/bind> or L<DBI/bind_param_inout>
-with L</ORA_CHAR>.
+ $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";
-=head4 Unicode
+If you have many columns to bind you can use code like this:
-DBD::Oracle now supports Unicode UTF-8. There are, however, a number
-of issues you should be aware of, so please read all this section
-carefully.
+ @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;
-In this section we'll discuss "Perl and Unicode", then "Oracle and
-Unicode", and finally "DBD::Oracle and Unicode".
+If you have many rows to insert you can take advantage of Oracle's built in execute array feature
+with code like this:
-Information about Unicode in general can be found at:
-L<http://www.unicode.org/>. It is well worth reading because there are
-many misconceptions about Unicode and you may be holding some of them.
+ 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';
+ }
-=head4 Perl and Unicode
+Which will return all the ids into @out_values.
-Perl began implementing Unicode with version 5.6, but the implementation
-did not mature until version 5.8 and later. If you plan to use Unicode
-you are I<strongly> urged to use Perl 5.8.2 or later and to I<carefully> read
-the Perl documentation on Unicode:
+=over
- perldoc perluniintro # in Perl 5.8 or later
- perldoc perlunicode
-
-And then read it again.
+=item B<Note:>
-Perl's internal Unicode format is UTF-8
-which corresponds to the Oracle character set called AL32UTF8.
+=item This will only work for numbered (?) placeholders,
-=head4 Oracle and Unicode
+=item The third parameter of bind_param_inout_array, (0 in the example), "maxlen" is required by DBI but not used by DBD::Oracle
-Oracle supports many characters sets, including several different forms
-of Unicode. These include:
+=item The "ora_type" attribute is not needed but only ORA_VARCHAR2 will work.
- AL16UTF16 => valid for NCHAR columns (CSID=2000)
- UTF8 => valid for NCHAR columns (CSID=871), deprecated
- AL32UTF8 => valid for NCHAR and CHAR columns (CSID=873)
+=back
-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:
+=head3 Returning A Recordset
- $hash_ref = $dbh->ora_nls_parameters()
- $database_charset = $hash_ref->{NLS_CHARACTERSET};
- $national_charset = $hash_ref->{NLS_NCHAR_CHARACTERSET};
+DBD::Oracle does not currently support binding a PL/SQL table (aka array)
+as an IN OUT parameter to any Perl data structure. You cannot therefore call
+a PL/SQL function or procedure from DBI that uses a non-atomic datatype as
+either a parameter, or a return value. However, if you are using Oracle 9.0.1
+or later, you can make use of table (or pipelined) functions.
-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.
+For example, assume you have the existing PL/SQL Package :
+ 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 ;
-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.
+ 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 ;
+ /
-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
-the database Oracle will convert from the character set specified by
-NLS_LANG to the database character set.
+Currently, there is no way to directly call the function
+Array_Example.Array_Func from DBI. However, by making the following relatively
+painless additions, its not only possible, but extremely efficient.
-The NLS_NCHAR environment variable can be used to define a different
-character set for 'national' (NCHAR) character types.
+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 :
-Both UTF8 and AL32UTF8 can be used in NLS_LANG and NLS_NCHAR.
-For example:
+ CREATE OR REPLACE TYPE tArray_Example__taRec
+ AS OBJECT (
+ Col1 NUMBER,
+ Col2 VARCHAR2 (10),
+ Col3 DATE
+ ) ;
- NLS_LANG=AMERICAN_AMERICA.UTF8
- NLS_LANG=AMERICAN_AMERICA.AL32UTF8
- NLS_NCHAR=UTF8
- NLS_NCHAR=AL32UTF8
+ CREATE OR REPLACE TYPE taArray_Example__taRec
+ AS TABLE OF tArray_Example__taRec ;
-=head4 Oracle UTF8 is not UTF-8
+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 :
-AL32UTF8 should be used in preference to UTF8 if it works for you,
-which it should for Oracle 9.2 or later. If you're using an old
-version of Oracle that doesn't support AL32UTF8 then you should
-avoid using any Unicode characters that require surrogates, in other
-words characters beyond the Unicode BMP (Basic Multilingual Plane).
+ 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 ;
-That's because the character set that Oracle calls "UTF8" doesn't
-conform to the UTF-8 standard in its handling of surrogate characters.
-Technically the encoding that Oracle calls "UTF8" is known as "CESU-8".
-Here are a couple of extracts from L<http://www.unicode.org/reports/tr26/>:
+ 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 ;
- 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.
+ 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,
+ l_Set (i).Col2,
+ l_Set (i).Col3
+ )
+ ) ;
+ END LOOP ;
+ RETURN ;
+ END ;
+ --
+ END Array_Example ;
- 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.
+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 :
-Oracle uses this internally because it collates (sorts) in the same order
-as UTF16, which is the basis of Oracle's internal collation definitions.
+ my $sth = $dbh->prepare('SELECT * FROM TABLE(Array_Example.Array_Func_DBI)');
+ $sth->execute;
+ while ( my ($col1, $col2, $col3) = $sth->fetchrow_array {
+ ...
+ }
-Rather than change UTF8 for clients Oracle chose to define a new character
-set called "AL32UTF8" which does conform to the UTF-8 standard.
-(The AL32UTF8 character set can't be used on the server because it
-would break collation.)
-Because of that, for the rest of this document we'll use "AL32UTF8".
-If you're using an Oracle version below 9.2 you'll need to use "UTF8"
-until you upgrade.
-=head4 DBD::Oracle and Unicode
-DBD::Oracle Unicode support has been implemented for Oracle versions 9
-or greater, and Perl version 5.6 or greater (though we I<strongly>
-suggest that you use Perl 5.8.2 or later).
-You can check which Oracle version your DBD::Oracle was built with by
-importing the C<ORA_OCI> constant from DBD::Oracle.
+=head3 B<SYS.DBMS_SQL datatypes>
-B<Fetching Data>
+DBD::Oracle has built-in support for B<SYS.DBMS_SQL.VARCHAR2_TABLE>
+and B<SYS.DBMS_SQL.NUMBER_TABLE> datatypes. The simple example is here:
-Any data returned from Oracle to DBD::Oracle in the AL32UTF8
-character set will be marked as UTF-8 to ensure correct handling by Perl.
+ my $statement='
+ DECLARE
+ tbl SYS.DBMS_SQL.VARCHAR2_TABLE;
+ BEGIN
+ tbl := :mytable;
+ :cc := tbl.count();
+ tbl(1) := \'def\';
+ tbl(2) := \'ijk\';
+ :mytable := tbl;
+ END;
+ ';
-For Oracle to return data in the AL32UTF8 character set the
-NLS_LANG or NLS_NCHAR environment variable I<must> be set as described
-in the previous section.
+ my $sth=$dbh->prepare( $statement );
-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.
+ my @arr=( "abc","efg","hij" );
-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.
+ $sth->bind_param_inout(":mytable", \\@arr, 10, {
+ ora_type => ORA_VARCHAR2_TABLE,
+ ora_maxarray_numentries => 100
+ } ) ;
+ $sth->bind_param_inout(":cc", \$cc, 100 );
+ $sth->execute();
+ print "Result: cc=",$cc,"\n",
+ "\tarr=",Data::Dumper::Dumper(\@arr),"\n";
-B<Sending Data using Placeholders>
+=over
-Data bound to a placeholder is assumed to be in the default client
-character set (specified by NLS_LANG) except for a few special
-cases. These are listed here with the highest precedence first:
+=item B<Note:>
-If the C<ora_csid> attribute is given to bind_param() then that
-is passed to Oracle and takes precedence.
+=item Take careful note that we use '\\@arr' here because the 'bind_param_inout'
+ will only take a reference to a scalar.
-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.
+=back
-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 C<ora_csform>
-attribute.)
-If the C<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.
+=head3 B<ORA_VARCHAR2_TABLE>
- use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
- ...
- $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR });
+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:
+I<max_len> (specified as 3rd argument of bind_param_inout() ),
+and I<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.
-or
+If you set I<max_len> to zero, maximum array entry length is calculated
+as maximum length of entry of array bound. If 0 < I<max_len> < length( $some_element ),
+truncation occur.
- $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders
+If you set I<ora_maxarray_numentries> to zero, current (at bind time) bound
+array length is used as maximum. If 0 < I<ora_maxarray_numentries> < scalar(@array),
+not all array entries are bound.
-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;
+=head3 B<ORA_NUMBER_TABLE>
- 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
+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 I<ora_internal_type> as either
+B<SQLT_INT> or B<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.
-The warning will report the parameter number and the NCHAR setting that the query is running.
+I<ora_internal_type> defaults to double (SQLT_FLT).
-B<Sending Data using SQL>
+I<max_len> is ignored for OCI_NUMBER_TABLE.
-Oracle assumes the SQL statement is in the default client character
-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.
+Currently, you cannot bind full native Oracle NUMBER(38). If you really need,
+send request to dbi-dev list.
-=head4 DBD::Oracle and Other Character Sets and Encodings
+The usage example is here:
-The only multi-byte Oracle character set supported by DBD::Oracle is
-"AL32UTF8" (and "UTF8"). Single-byte character sets should work well.
+ $statement='
+ DECLARE
+ tbl SYS.DBMS_SQL.NUMBER_TABLE;
+ BEGIN
+ tbl := :mytable;
+ :cc := tbl(2);
+ tbl(4) := -1;
+ tbl(5) := -2;
+ :mytable := tbl;
+ END;
+ ';
-=head4 Other Data Types
+ $sth=$dbh->prepare( $statement );
-DBD::Oracle does not I<explicitly> support most Oracle datatypes.
-It simply asks Oracle to return them as strings and Oracle does so.
-Mostly. Similarly when binding placeholder values DBD::Oracle binds
-them as strings and Oracle converts them to the appropriate type,
-such as DATE, when used.
+ if( ! defined($sth) ){
+ die "Prepare error: ",$dbh->errstr,"\n";
+ }
-Some of these automatic conversions to and from strings use NLS
-settings to control the formatting for output and the parsing for
-input. The most common example is the DATE type. The default NLS
-format for DATE might be DD-MON-YYYY and so when a DATE type is
-fetched that's how Oracle will format the date. NLS settings also
-control the default parsing of strings into DATE values. An error
-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")
-to explicitly specify formats for converting to and from strings.
+ @arr=( 1,"2E0","3.5" );
-A slightly more subtle problem can occur with NUMBER types. The
-default NLS settings might format numbers with a fullstop ("C<.>")
-to separate thousands and a comma ("C<,>") as the decimal point.
-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
-quickly. The best fix is to change the NLS settings. That can be
-done for an individual connection by doing:
+ # note, that ora_internal_type defaults to SQLT_FLT for ORA_NUMBER_TABLE .
+ if( not $sth->bind_param_inout(":mytable", \\@arr, 10, {
+ ora_type => ORA_NUMBER_TABLE,
+ ora_maxarray_numentries => (scalar(@arr)+2),
+ ora_internal_type => SQLT_FLT
+ } ) ){
+ die "bind :mytable error: ",$dbh->errstr,"\n";
+ }
+ $cc=undef;
+ if( not $sth->bind_param_inout(":cc", \$cc, 100 ) ){
+ die "bind :cc error: ",$dbh->errstr,"\n";
+ }
- $dbh->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");
+ if( not $sth->execute() ){
+ die "Execute failed: ",$dbh->errstr,"\n";
+ }
+ print "Result: cc=",$cc,"\n",
+ "\tarr=",Data::Dumper::Dumper(\@arr),"\n";
-There are some types, like BOOLEAN, that Oracle does not automatically
-convert to or from strings (pity). These need to be converted
-explicitly using SQL or PL/SQL functions.
+The result is like:
+ Result: cc=2
+ arr=$VAR1 = [
+ '1',
+ '2',
+ '3.5',
+ '-1',
+ '-2'
+ ];
-Examples:
+If you change bind type to B<SQLT_INT>, like:
- # 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' );
+ ora_internal_type => SQLT_INT
- # BOOLEAN values
- my $sth2 = $dbh->prepare( <<PLSQL_END );
- DECLARE
- b0 BOOLEAN;
- b1 BOOLEAN;
- o0 VARCHAR2(32);
- o1 VARCHAR2(32);
+you get:
- 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 );
+ Result: cc=2
+ arr=$VAR1 = [
+ 1,
+ 2,
+ 3,
+ -1,
+ -2
+ ];
- -- 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'"
+=head2 B<bind_param_inout_array>
+DBD::Oracle supports this undocumented feature of DBI. See L</Returning A Value from an INSERT> for an example.
-=head4 Object & Collection Data Types
-Oracle databases allow for the creation of object oriented like user-defined types.
-There are two types of objects, Embedded--an object stored in a column of a regular table
-and REF--an object that uses the REF retrieval mechanism.
+=head2 B<bind_param_array>
-DBD::Oracle supports only the 'selection' of embedded objects of the following types OBJECT, VARRAY
-and TABLE in any combination. Support is seamless and recursive, meaning you
-need only supply a simple SQL statement to get all the values in an embedded object.
-You can either get the values as an array of scalars or they can be returned into a DBD::Oracle::Object.
+ $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 L</execute_array> method.
-Array example, given this type and table;
- 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"
- )
+=head2 B<execute>
-The code to access all the data in the table could be something like this;
+ $rv = $sth->execute(@bind_values);
- 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 #: ";
+Perform whatever processing is necessary to execute the prepared statement.
- foreach my $items (@$phone){
- print $items.", ";
- }
- print "\n";
- }
+=head2 B<execute_array>
-Note that values in PHONE_NUMBERS are returned as an array reference '@$phone'.
+ $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;
-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;
+ ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
+ ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
- CREATE OR REPLACE TYPE GRADELIST AS TABLE OF NUMBER;
+Execute a prepared statement once for each item in a passed-in hashref, or items that
+were previously bound via the L</bind_param_array> method. See the DBI documentation
+for more details.
- CREATE OR REPLACE TYPE STUDENT AS OBJECT(
- NAME VARCHAR2(60),
- SOME_GRADES GRADELIST);
+DBD::Oracle takes full advantage of OCI's array interface so inserts and updates using this interface will run very
+quickly.
- CREATE OR REPLACE TYPE STUDENTS_T AS TABLE OF STUDENT;
+=head2 B<execute_for_fetch>
- 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);
+ $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
+ $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
-The following code will access all of the embedded data;
+ ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
+ ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
- $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";
- }
+Used internally by the L</execute_array> method, and rarely used directly. See the
+DBI documentation for more details.
-Object example, given this object and table;
+=head2 B<fetchrow_arrayref>
- CREATE OR REPLACE TYPE Person AS OBJECT (
- name VARCHAR2(20),
- age INTEGER)
- ) NOT FINAL;
+ $ary_ref = $sth->fetchrow_arrayref;
- CREATE TYPE Employee UNDER Person (
- salary NUMERIC(8,2)
- );
+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.
- CREATE TABLE people (id INTEGER, obj Person);
+If there are no more rows or if an error occurs, the this method return undef. You should
+check C<< $sth->err >> afterwards (or use the L</RaiseError> attribute) to discover if the undef returned
+was due to an error.
- INSERT INTO people VALUES (1, Person('Black', 25));
- INSERT INTO people VALUES (2, Employee('Smith', 44, 5000));
+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 L</bind_columns>.
-The following code will access the data;
+=head2 B<fetchrow_array>
- $dbh{'ora_objects'} =>1;
+ @ary = $sth->fetchrow_array;
- $sth = $dbh->prepare("select * from people order by id");
- $sth->execute();
+Similar to the L</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.
- # object are fetched as instance of DBD::Oracle::Object
- my ($id1, $obj1) = $sth->fetchrow();
- my ($id2, $obj2) = $sth->fetchrow();
+=head2 B<fetchrow_hashref>
- # 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
+ $hash_ref = $sth->fetchrow_hashref;
+ $hash_ref = $sth->fetchrow_hashref($name);
- # get attribute NAME from object
- print $obj1->attr('NAME')."3\n"; # 'Black' is printed
- print $obj2->attr('NAME')."3\n"; # 'Smith' is printed
+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.
- # 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 }
+If there are no more rows or if an error occurs, the this method return undef. You should
+check C<< $sth->err >> afterwards (or use the L</RaiseError> attribute) to discover if the undef returned
+was due to an error.
- # 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 )
+The optional C<$name> argument should be either C<NAME>, C<NAME_lc> or C<NAME_uc>, and indicates
+what sort of transformation to make to the keys in the hash. By default Oracle uses upper case.
-So far DBD::Oracle has been tested on a table with 20 embedded Objects, Varrays and Tables
-nested to 10 levels.
+=head2 B<fetchall_arrayref>
-Any NULL values found in the embedded object will be returned as 'undef'.
+ $tbl_ary_ref = $sth->fetchall_arrayref();
+ $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
+ $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
-=head4 Support for Insert of XMLType (ORA_XMLTYPE)
+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 C<< $sth->err >> after
+calling this method, unless L</RaiseError> has been enabled.
-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
-example with a table like this;
+If C<$slice> is an array reference, fetchall_arrayref uses the L</fetchrow_arrayref> method to fetch each
+row as an array ref. If the C<$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).
- create table books (book_id number, book_xml XMLType);
+With no parameters, or if $slice is undefined, fetchall_arrayref acts as if passed an empty array ref.
-one can insert data using this code
+If C<$slice> is a hash reference, fetchall_arrayref uses L</fetchrow_hashref> to fetch each row as a hash reference.
- $SQL='insert into books values (1,:p_xml)';
- $xml= '<Books>
- <Book id=1>
- <Title>Programming the Perl DBI</Title>
- <Subtitle>The Cheetah Book</Subtitle>
- <Authors>
- <Author>T. Bunce</Author>
- <Author>Alligator Descartes</Author>
- </Authors>
+See the DBI documentation for a complete discussion.
- </Book>
- <Book id=10000>...
- </Books>';
- my $sth =$dbh-> prepare($SQL);
- $sth-> bind_param("p_xml", $xml, { ora_type => ORA_XMLTYPE });
- $sth-> execute();
+=head2 B<fetchall_hashref>
-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.
+ $hash_ref = $sth->fetchall_hashref( $key_field );
-=head4 Binding Cursors
+Returns a hashref containing all rows to be fetched from the statement handle. See the DBI documentation for
+a full discussion.
-Cursors can be returned from PL/SQL blocks, either from stored
-functions (or procedures with OUT parameters) or
-from direct C<OPEN> statements, as shown below:
+=head2 B<finish>
- 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 ) { ... }
+ $rv = $sth->finish;
-The only special requirement is the use of C<bind_param_inout()> with an
-attribute hash parameter that specifies C<ora_type> as C<ORA_RSET>.
-If you don't do that you'll get an error from the C<execute()> like:
-"ORA-06550: line X, column Y: PLS-00306: wrong number or types of
-arguments in call to ...".
+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.
-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.
+=head2 B<rows>
- # 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;
- });
+ $rv = $sth->rows;
- # 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 ) { ... }
+Returns the number of rows affected for updates, deletes and inserts and -1 for selects.
-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:
+=head2 B<bind_col>
- my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
- $sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
- $sth3->execute;
+ $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 );
-It is not normally necessary to close a cursor
-explicitly in this way. Oracle will close the cursor automatically
-at the first client-server interaction after the cursor statement handle is
-destroyed. An explicit close may be desirable if the reference to
-the cursor handle from the PL/SQL statement handle delays the destruction
-of the cursor handle for too long. This reference remains until the
-PL/SQL handle is re-bound, re-executed or destroyed.
+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.
-See the C<curref.pl> script in the Oracle.ex directory in the DBD::Oracle
-source distribution for a complete working example.
+NOTE: DBD::Oracle does not use the C<$bind_type> to determine how to
+bind the column; it uses what Oracle says the data type is. You can
+however set a numeric bind type with the bind attributes
+StrictlyTyped/DiscardString as these attributes are applied after the
+column is retrieved.
-=head4 Fetching Nested Cursors
+See the DBI documentation for a discussion of the optional parameters C<\%attr> and C<$bind_type>
-Oracle supports the use of select list expressions of type REF CURSOR.
-These may be explicit cursor expressions - C<CURSOR(SELECT ...)>, or
-calls to PL/SQL functions which return REF CURSOR values. The values
-of these expressions are known as nested cursors.
+=head2 B<bind_columns>
-The value returned to a Perl program when a nested cursor is fetched
-is a statement handle. This statement handle is ready to be fetched from.
-It should not (indeed, must not) be executed.
+ $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
-Oracle imposes a restriction on the order of fetching when nested
-cursors are used. Suppose C<$sth1> is a handle for a select statement
-involving nested cursors, and C<$sth2> is a nested cursor handle fetched
-from C<$sth1>. C<$sth2> can only be fetched from while C<$sth1> is
-still active, and the row containing C<$sth2> is still current in C<$sth1>.
-Any attempt to fetch another row from C<$sth1> renders all nested cursor
-handles previously fetched from C<$sth1> defunct.
+Calls the L</bind_col> method for each column in the SELECT statement, using the supplied list.
-Fetching from such a defunct handle results in an error with the message
-C<ERROR nested cursor is defunct (parent row is no longer current)>.
+=head2 B<dump_results>
-This means that the C<fetchall...> or C<selectall...> methods are not useful
-for queries returning nested cursors. By the time such a method returns,
-all the nested cursor handles it has fetched will be defunct.
+ $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
-It is necessary to use an explicit fetch loop, and to do all the
-fetching of nested cursors within the loop, as the following example
-shows:
+Fetches all the rows from the statement handle, calls C<DBI::neat_list> for each row, and
+prints the results to C<$fh> (which defaults to F<STDOUT>). Rows are separated by C<$lsep> (which defaults
+to a newline). Columns are separated by C<$fsep> (which defaults to a comma). The C<$maxlen> controls
+how wide the output can be, and defaults to 35.
- use DBI;
- my $dbh = DBI->connect(...);
- my $sth = $dbh->prepare(q{
- SELECT dname, CURSOR(
- SELECT ename FROM emp
- WHERE emp.deptno = dept.deptno
- ORDER BY ename
- ) FROM dept ORDER BY dname
- });
- $sth->execute;
- while ( my ($dname, $nested) = $sth->fetchrow_array ) {
- print "$dname\n";
- while ( my ($ename) = $nested->fetchrow_array ) {
- print " $ename\n";
- }
- }
+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
+for data transfer applications.
+=head1 STATEMENT HANDLE ATTRIBUTES
-The cursor returned by the function C<sp_ListEmp> defined in the
-previous section can be fetched as a nested cursor as follows:
+=head2 B<NUM_OF_FIELDS> (integer, read-only)
- my $sth = $dbh->prepare(q{SELECT sp_ListEmp FROM dual});
- $sth->execute;
- my ($nested) = $sth->fetchrow_array;
- while ( my @row = $nested->fetchrow_array ) { ... }
+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 C<execute()>.
-=head4 Pre-fetching Nested Cursors
+=head2 B<NUM_OF_PARAMS> (integer, read-only)
-By default, DBD::Oracle pre-fetches rows in order to reduce the number of
-round trips to the server. For queries which do not involve nested cursors,
-the number of pre-fetched rows is controlled by the DBI database handle
-attribute C<RowCacheSize> (q.v.).
+Returns the number of placeholders in the current statement.
-In Oracle, server side open cursors are a controlled resource, limited in
-number, on a per session basis, to the value of the initialization
-parameter C<OPEN_CURSORS>. Nested cursors count towards this limit.
-Each nested cursor in the current row counts 1, as does
-each nested cursor in a pre-fetched row. Defunct nested cursors do not count.
+=head2 B<NAME> (arrayref, read-only)
-An Oracle specific database handle attribute, C<ora_max_nested_cursors>,
-further controls pre-fetching for queries involving nested cursors. For
-each statement handle, the total number of nested cursors in pre-fetched
-rows is limited to the value of this parameter. The default value
-is 0, which disables pre-fetching for queries involving nested cursors.
+Returns an arrayref of column names for the current statement. This
+method will only work for SELECT statements, for SHOW statements, and for
+INSERT, UPDATE, and DELETE statements which contain a RETURNING clause.
+This method returns undef if called before C<execute()>.
+=head2 B<NAME_lc> (arrayref, read-only)
-=head3 B<bind_param_inout>
+The same as the C<NAME> attribute, except that all column names are forced to lower case.
- $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
+=head2 B<NAME_uc> (arrayref, read-only)
+The same as the C<NAME> attribute, except that all column names are forced to upper case.
-DBD::Oracle fully supports bind_param_inout below are some uses for this method.
+=head2 B<NAME_hash> (hashref, read-only)
+Similar to the C<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 C<execute()>.
-=head4 B<Returning A Value from an INSERT>
+=head2 B<NAME_lc_hash> (hashref, read-only)
-Oracle supports an extended SQL insert syntax which will return one
-or more of the values inserted. This can be particularly useful for
-single-pass insertion of values with re-used sequence values
-(avoiding a separate "select seq.nextval from dual" step).
+The same as the C<NAME_hash> attribute, except that all column names are forced to lower case.
- $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";
+=head2 B<NAME_uc_hash> (hashref, read-only)
-If you have many columns to bind you can use code like this:
+The same as the C<NAME_hash> attribute, except that all column names are forced to lower case.
- @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;
+=head2 B<TYPE> (arrayref, read-only)
-If you have many rows to insert you can take advantage of Oracle's built in execute array feature
-with code like this:
+Returns an arrayref indicating the data type for each column in the statement.
+This method returns undef if called before C<execute()>.
- 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';
- }
+=head2 B<PRECISION> (arrayref, read-only)
-Which will return all the ids into @out_values.
+Returns an arrayref of integer values for each column returned by the statement.
+The number indicates the precision for C<NUMERIC> columns, the size in number of
+characters for C<CHAR> and C<VARCHAR> columns, and for all other types of columns
+it returns the number of I<bytes>.
+This method returns undef if called before C<execute()>.
-=over
+=head2 B<SCALE> (arrayref, read-only)
-=item B<Note:>
+Returns an arrayref of integer values for each column returned by the statement. The number
+indicates the scale of the that column. The only type that will return a value is C<NUMERIC>.
+This method returns undef if called before C<execute()>.
-=item This will only work for numbered (?) placeholders,
+=head2 B<NULLABLE> (arrayref, read-only)
-=item The third parameter of bind_param_inout_array, (0 in the example), "maxlen" is required by DBI but not used by DBD::Oracle
+Returns an arrayref of integer values for each column returned by the statement. The number
+indicates if the column is nullable or not. 0 = not nullable, 1 = nullable, 2 = unknown.
+This method returns undef if called before C<execute()>.
-=item The "ora_type" attribute is not needed but only ORA_VARCHAR2 will work.
+=head2 B<Database> (dbh, read-only)
-=back
+Returns the database handle this statement handle was created from.
-=head4 Returning A Recordset
+=head2 B<ParamValues> (hash ref, read-only)
-DBD::Oracle does not currently support binding a PL/SQL table (aka array)
-as an IN OUT parameter to any Perl data structure. You cannot therefore call
-a PL/SQL function or procedure from DBI that uses a non-atomic datatype as
-either a parameter, or a return value. However, if you are using Oracle 9.0.1
-or later, you can make use of table (or pipelined) functions.
+Returns a reference to a hash containing the values currently bound to placeholders. If the "named parameters"
+type of placeholders are being used (such as ":foo"), then the keys of the hash will be the names of the
+placeholders (without the colon). If the "dollar sign numbers" type of placeholders are being used, the keys of the hash will
+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.
-For example, assume you have the existing PL/SQL Package :
+If this method is called before L</execute>, the literal values passed in are returned. If called after
+L</execute>, then the quoted versions of the values are returned.
- 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 ;
+=head2 B<ParamTypes> (hash ref, read-only)
- 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 ;
- /
+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
+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 L</bind_param> method.
-Currently, there is no way to directly call the function
-Array_Example.Array_Func from DBI. However, by making the following relatively
-painless additions, its not only possible, but extremely efficient.
+=head2 B<Statement> (string, read-only)
-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 :
+Returns the statement string passed to the most recent "prepare" method called in this database handle, even if that method
+failed. This is especially useful where "RaiseError" is enabled and the exception handler checks $@ and sees that a C<prepare>
+method call failed.
- CREATE OR REPLACE TYPE tArray_Example__taRec
- AS OBJECT (
- Col1 NUMBER,
- Col2 VARCHAR2 (10),
- Col3 DATE
- ) ;
+=head2 B<RowsInCache>
- CREATE OR REPLACE TYPE taArray_Example__taRec
- AS TABLE OF tArray_Example__taRec ;
+Returns the number of un-fetched rows in the cache for selects.
-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 :
+=head1 SCROLLABLE CURSORS
- 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 ;
+Oracle supports the concept of a 'Scrollable Cursor' which is defined as a 'Result Set' where
+the rows can be fetched either sequentially or non-sequentially. One can fetch rows forward,
+backwards, from any given position or the n-th row from the current position in the result set.
- 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 ;
+Rows are numbered sequentially starting at one and client-side caching of the partial or entire result set
+can improve performance by limiting round trips to the server.
- 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,
- l_Set (i).Col2,
- l_Set (i).Col3
- )
- ) ;
- END LOOP ;
- RETURN ;
- END ;
- --
- END Array_Example ;
+Oracle does not support DML type operations with scrollable cursors so you are limited
+to simple 'Select' operations only. As well you can not use this functionality with remote
+mapped queries or if the LONG datatype is part of the select list.
-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 :
+However, LOBSs, CLOBSs, and BLOBs do work as do all the regular bind, and fetch methods.
- my $sth = $dbh->prepare('SELECT * FROM TABLE(Array_Example.Array_Func_DBI)');
- $sth->execute;
- while ( my ($col1, $col2, $col3) = $sth->fetchrow_array {
- ...
- }
+Only use scrollable cursors if you really have a good reason to. They do use up considerable
+more server and client resources and have poorer response times than non-scrolling cursors.
+=head2 Enabling Scrollable Cursors
+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);
-=head4 B<SYS.DBMS_SQL datatypes>
+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;
-DBD::Oracle has built-in support for B<SYS.DBMS_SQL.VARCHAR2_TABLE>
-and B<SYS.DBMS_SQL.NUMBER_TABLE> datatypes. The simple example is here:
+ $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
- my $statement='
- DECLARE
- tbl SYS.DBMS_SQL.VARCHAR2_TABLE;
- BEGIN
- tbl := :mytable;
- :cc := tbl.count();
- tbl(1) := \'def\';
- tbl(2) := \'ijk\';
- :mytable := tbl;
- END;
- ';
+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.
- my $sth=$dbh->prepare( $statement );
- my @arr=( "abc","efg","hij" );
+=head2 Scrollable Cursor Methods
- $sth->bind_param_inout(":mytable", \\@arr, 10, {
- ora_type => ORA_VARCHAR2_TABLE,
- ora_maxarray_numentries => 100
- } ) ;
- $sth->bind_param_inout(":cc", \$cc, 100 );
- $sth->execute();
- print "Result: cc=",$cc,"\n",
- "\tarr=",Data::Dumper::Dumper(\@arr),"\n";
+The following driver-specific methods are used with scrollable cursors.
=over
-=item B<Note:>
-
-=item Take careful note that we use '\\@arr' here because the 'bind_param_inout'
- will only take a reference to a scalar.
-
-=back
-
-
+=item ora_scroll_position
-=head4 B<ORA_VARCHAR2_TABLE>
+ $position = $sth->ora_scroll_position();
-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:
-I<max_len> (specified as 3rd argument of bind_param_inout() ),
-and I<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.
+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.
-If you set I<max_len> to zero, maximum array entry length is calculated
-as maximum length of entry of array bound. If 0 < I<max_len> < length( $some_element ),
-truncation occur.
+=item ora_fetch_scroll
-If you set I<ora_maxarray_numentries> to zero, current (at bind time) bound
-array length is used as maximum. If 0 < I<ora_maxarray_numentries> < scalar(@array),
-not all array entries are bound.
+ @ary = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
-=head4 B<ORA_NUMBER_TABLE>
+Works the same as fetchrow_array method however, one passes in a 'Fetch Orientation' constant and a fetch_offset
+value which will then determine the row that will be fetched. It returns the row as a list containing the field values.
+Null fields are returned as undef values in the list.
-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 I<ora_internal_type> as either
-B<SQLT_INT> or B<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.
+The valid orientation constant and fetch offset values combination are detailed below
-I<ora_internal_type> defaults to double (SQLT_FLT).
+ 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_RELATIVE, fetches the row relative from the current position as specified by the
+ fetch offset value.
-I<max_len> is ignored for OCI_NUMBER_TABLE.
+ 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.
-Currently, you cannot bind full native Oracle NUMBER(38). If you really need,
-send request to dbi-dev list.
+ 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 usage example is here:
+The effect that a ora_fetch_scroll method call has on the current_positon attribute is detailed below.
- $statement='
- DECLARE
- tbl SYS.DBMS_SQL.NUMBER_TABLE;
- BEGIN
- tbl := :mytable;
- :cc := tbl(2);
- tbl(4) := -1;
- tbl(5) := -2;
- :mytable := tbl;
- END;
- ';
+ 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.
- $sth=$dbh->prepare( $statement );
+ 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.
- if( ! defined($sth) ){
- die "Prepare error: ",$dbh->errstr,"\n";
- }
+The effects of the differing orientation constants on the first fetch (current_postion attribute at 0) are as follows.
- @arr=( 1,"2E0","3.5" );
+ 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.
- # note, that ora_internal_type defaults to SQLT_FLT for ORA_NUMBER_TABLE .
- if( not $sth->bind_param_inout(":mytable", \\@arr, 10, {
- ora_type => ORA_NUMBER_TABLE,
- ora_maxarray_numentries => (scalar(@arr)+2),
- ora_internal_type => SQLT_FLT
- } ) ){
- die "bind :mytable error: ",$dbh->errstr,"\n";
- }
- $cc=undef;
- if( not $sth->bind_param_inout(":cc", \$cc, 100 ) ){
- die "bind :cc error: ",$dbh->errstr,"\n";
- }
+ 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.
- if( not $sth->execute() ){
- die "Execute failed: ",$dbh->errstr,"\n";
- }
- print "Result: cc=",$cc,"\n",
- "\tarr=",Data::Dumper::Dumper(\@arr),"\n";
+=back
-The result is like:
+=head2 Scrollable Cursor Usage
- Result: cc=2
- arr=$VAR1 = [
- '1',
- '2',
- '3.5',
- '-1',
- '-2'
- ];
+Given a simple code like this:
-If you change bind type to B<SQLT_INT>, like:
+ 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;
- ora_internal_type => SQLT_INT
+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;
-you get:
+=over
- Result: cc=2
- arr=$VAR1 = [
- 1,
- 2,
- 3,
- -1,
- -2
- ];
+=item Fetching the Last Row
-=head3 B<bind_param_inout_array>
+ $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";
-DBD::Oracle supports this undocumented feature of DBI. See L</Returning A Value from an INSERT> for an example.
+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.
+=item Fetching the Current Row
-=head3 B<bind_param_array>
+ $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";
- $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)
+The current_positon attribute will still be 20 after this snippet.
-Binds an array of values to a placeholder, so that each is used in turn by a call
-to the L</execute_array> method.
+=item Fetching the First Row
+ $value = $sth->ora_fetch_scroll(OCI_FETCH_FIRST,0);
+ print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
+ print "current scroll position=".$sth->ora_scroll_position()."\n";
+The current_positon attribute will be 1 after this snippet.
-=head3 B<execute>
+=item Fetching the Next Row
- $rv = $sth->execute(@bind_values);
+ 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";
-Perform whatever processing is necessary to execute the prepared statement.
+The current_positon attribute will be 5 after this snippet.
-=head3 B<execute_array>
+=item Fetching the Prior Row
- $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;
+ 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";
- ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
- ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
+The current_positon attribute will be 1 after this snippet.
-Execute a prepared statement once for each item in a passed-in hashref, or items that
-were previously bound via the L</bind_param_array> method. See the DBI documentation
-for more details.
+=item Fetching the 10th Row
-DBD::Oracle takes full advantage of OCI's array interface so inserts and updates using this interface will run very
-quickly.
+ $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";
-=head3 B<execute_for_fetch>
+The current_positon attribute will be 10 after this snippet.
- $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
- $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
+=item Fetching the 10th to 14th Row
- ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
- ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
+ 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";
-Used internally by the L</execute_array> method, and rarely used directly. See the
-DBI documentation for more details.
+The current_positon attribute will be 14 after this snippet.
-=head3 B<fetchrow_arrayref>
+=item Fetching the 14th to 10th Row
- $ary_ref = $sth->fetchrow_arrayref;
+ 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";
-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.
+The current_positon attribute will be 10 after this snippet.
-If there are no more rows or if an error occurs, the this method return undef. You should
-check C<< $sth->err >> afterwards (or use the L</RaiseError> attribute) to discover if the undef returned
-was due to an error.
+=item Fetching the 5th Row From the Present Position.
-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 L</bind_columns>.
+ $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";
-=head3 B<fetchrow_array>
+The current_positon attribute will be 15 after this snippet.
- @ary = $sth->fetchrow_array;
+=item Fetching the 9th Row Prior From the Present Position
-Similar to the L</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.
+ $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";
-=head3 B<fetchrow_hashref>
+The current_positon attribute will be 6 after this snippet.
- $hash_ref = $sth->fetchrow_hashref;
- $hash_ref = $sth->fetchrow_hashref($name);
+=item Use Finish
-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.
+ $sth->finish();
-If there are no more rows or if an error occurs, the this method return undef. You should
-check C<< $sth->err >> afterwards (or use the L</RaiseError> attribute) to discover if the undef returned
-was due to an error.
+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.
-The optional C<$name> argument should be either C<NAME>, C<NAME_lc> or C<NAME_uc>, and indicates
-what sort of transformation to make to the keys in the hash. By default Oracle uses upper case.
+=back
-=head3 B<fetchall_arrayref>
+=head1 LOBS AND LONGS
- $tbl_ary_ref = $sth->fetchall_arrayref();
- $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
- $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
+The key to working with LOBs (CLOB, BLOBs) is to remember the value of an Oracle LOB column is not the content of the LOB. It's a
+'LOB Locator' which, after being selected or inserted needs extra processing to read or write the content of the LOB. There are also legacy LONG types (LONG, LONG RAW, VARCHAR2)
+which are presently deprecated by Oracle but are still in use. These LONG types do not utilize a 'LOB Locator' and also are more limited in
+functionality than CLOB or BLOB fields.
-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 C<< $sth->err >> after
-calling this method, unless L</RaiseError> has been enabled.
+DBD::Oracle now offers three interfaces to LOB and LONG data,
-If C<$slice> is an array reference, fetchall_arrayref uses the L</fetchrow_arrayref> method to fetch each
-row as an array ref. If the C<$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).
+=over
-With no parameters, or if $slice is undefined, fetchall_arrayref acts as if passed an empty array ref.
+=item L</Data Interface for Persistent LOBs>
-If C<$slice> is a hash reference, fetchall_arrayref uses L</fetchrow_hashref> to fetch each row as a hash reference.
+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.
-See the DBI documentation for a complete discussion.
+=item L</Data Interface for LOB Locators>
-=head3 B<fetchall_hashref>
+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.
- $hash_ref = $sth->fetchall_hashref( $key_field );
+=item L</LOB Locator Method Interface>
-Returns a hashref containing all rows to be fetched from the statement handle. See the DBI documentation for
-a full discussion.
+This allows the user direct access to the LOB Locator methods, so you have to take case of the LOB Locator operations yourself.
-=head3 B<finish>
+=back
- $rv = $sth->finish;
+Generally speaking the interface that you will chose will be dependent on what end you are trying to achieve. All have their benefits and
+drawbacks.
-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.
+One point to remember when working with LOBs (CLOBs, BLOBs) is if your LOB column can be in one of three states;
-=head3 B<rows>
+=over
- $rv = $sth->rows;
+=item NULL
-Returns the number of rows affected for updates, deletes and inserts and -1 for selects.
+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
-=head3 B<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 );
+ DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr
-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.
+error when working with a LOB.
-See the DBI documentation for a discussion of the optional parameters C<\%attr> and C<$bind_type>
+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;
-=head3 B<bind_columns>
+ UPDATE lob_example
+ SET bindata=EMPTY_BLOB()
+ WHERE bindata IS NULL.
- $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
+=item Empty
-Calls the L</bind_col> method for each column in the SELECT statement, using the supplied list.
+A LOB instance with a locator exists in the cell, but it has no value. The length of the LOB is zero. In this case DBD::Oracle will return 'undef' for the field.
-=head3 B<dump_results>
+=item Populated
- $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
+A LOB instance with a locator and a value exists in the cell. You actually get the LOB value.
-Fetches all the rows from the statement handle, calls C<DBI::neat_list> for each row, and
-prints the results to C<$fh> (which defaults to F<STDOUT>). Rows are separated by C<$lsep> (which defaults
-to a newline). Columns are separated by C<$fsep> (which defaults to a comma). The C<$maxlen> controls
-how wide the output can be, and defaults to 35.
+=back
-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
-for data transfer applications.
+=head2 Data Interface for Persistent LOBs
+This is the original interface for LONG and LONG RAW datatypes and from Oracle 9iR1 and later the OCI API was extended to work directly with the other LOB datatypes.
+In other words you can treat all LOB type data (BLOB, CLOB) as if it was a LONG, LONG RAW, or VARCHAR2. So you can perform INSERT, UPDATE, fetch, bind, and define operations on LOBs using the same techniques
+you would use on other datatypes that store character or binary data. In some cases there are fewer round trips to the server as no 'LOB Locators' are
+used, normally one can get an entire LOB is a single round trip.
-=head2 Private Statement Handle Methods
+=head3 Simple Fetch for LONGs and LONG RAWs
-=head3 B<ora_stmt_type>
+As the name implies this is the simplest way to use this interface. DBD::Oracle just attempts to get your LONG datatypes as a single large piece.
+There are no special settings, simply set the database handle's 'LongReadLen' attribute to a value that will be the larger than the expected size of the LONG or LONG RAW.
+If the size of the LONG or LONG RAW 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. You have most likely reached this limit if you run into
+an 'ORA-01062: unable to allocate memory for define buffer' error. One solution is to set the size of 'LongReadLen' to a lower value.
-Returns the OCI Statement Type number for the SQL of a statement handle.
+For example give this table;
-=head3 B<ora_stmt_type_name>
+ CREATE TABLE test_long (
+ id NUMBER,
+ long1 long)
-Returns the OCI Statement Type name for the SQL of a statement handle.
+this code;
-=head2 Statement Handle Attributes
+ $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";
+ }
-=head3 B<NUM_OF_FIELDS> (integer, read-only)
+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;
-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 C<execute()>.
+ $dbh->{LongTruncOk}=1;
-=head3 B<NUM_OF_PARAMS> (integer, read-only)
+before the execute will return all the long1 fields but they will be truncated at 2MBs.
-Returns the number of placeholders in the current statement.
+=head3 Using ora_ncs_buff_mtpl
-=head3 B<NAME> (arrayref, read-only)
+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.
-Returns an arrayref of column names for the current statement. This
-method will only work for SELECT statements, for SHOW statements, and for
-INSERT, UPDATE, and DELETE statements which contain a RETURNING clause.
-This method returns undef if called before C<execute()>.
+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
+(maybe less characters though) but you are using allot more memory that you need.
-=head3 B<NAME_lc> (arrayref, read-only)
+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 same as the C<NAME> attribute, except that all column names are forced to lower case.
+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.
-=head3 B<NAME_uc> (arrayref, read-only)
+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;
-The same as the C<NAME> attribute, except that all column names are forced to upper case.
+ ORA-03127: no new operations allowed until the active operation ends
-=head3 B<NAME_hash> (hashref, read-only)
+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)
-Similar to the C<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 C<execute()>.
+If you get this, simply increment the ora_ncs_buff_mtpl by one until it goes away.
-=head3 B<NAME_lc_hash> (hashref, read-only)
+This should greatly increase your ability to select very large CLOBs or NCLOBs, by freeing up a large block of memory.
-The same as the C<NAME_hash> attribute, except that all column names are forced to lower case.
+You can tune this value by setting ora_oci_success_warn which will display the following
-=head3 B<NAME_uc_hash> (hashref, read-only)
+ OCILobRead field 2 of 3 SUCCESS: csform 1 (SQLCS_IMPLICIT), LOBlen 10240(characters), LongReadLen
+ 20(characters), BufLen 80(characters), Got 28(characters)
-The same as the C<NAME_hash> attribute, except that all column names are forced to lower case.
+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.
-=head3 B<TYPE> (arrayref, read-only)
-Returns an arrayref indicating the data type for each column in the statement.
-This method returns undef if called before C<execute()>.
+=head3 Simple Fetch for CLOBs and BLOBs
-=head3 B<PRECISION> (arrayref, read-only)
+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.
-Returns an arrayref of integer values for each column returned by the statement.
-The number indicates the precision for C<NUMERIC> columns, the size in number of
-characters for C<CHAR> and C<VARCHAR> columns, and for all other types of columns
-it returns the number of I<bytes>.
-This method returns undef if called before C<execute()>.
+For CLOBs and NCLOBs the limit is 64k chars if there is no truncation, this is an internal OCI limit complain to them if you want it changed. However if you CLOB is longer than this
+and also larger than the 'LongReadLen' than the 'LongReadLen' in chars is returned.
-=head3 B<SCALE> (arrayref, read-only)
+It seems with BLOBs you are not limited by the 64k.
-Returns an arrayref of integer values for each column returned by the statement. The number
-indicates the scale of the that column. The only type that will return a value is C<NUMERIC>.
-This method returns undef if called before C<execute()>.
+For example give this table;
-=head3 B<NULLABLE> (arrayref, read-only)
+ CREATE TABLE test_lob (id NUMBER,
+ clob1 CLOB,
+ clob2 CLOB,
+ blob1 BLOB,
+ blob2 BLOB)
-Returns an arrayref of integer values for each column returned by the statement. The number
-indicates if the column is nullable or not. 0 = not nullable, 1 = nullable, 2 = unknown.
-This method returns undef if called before C<execute()>.
+this code;
-=head3 B<Database> (dbh, read-only)
+ $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";
+ }
-Returns the database handle this statement handle was created from.
+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;
-=head3 B<ParamValues> (hash ref, read-only)
+ $dbh->{LongTruncOk}=1;
-Returns a reference to a hash containing the values currently bound to placeholders. If the "named parameters"
-type of placeholders are being used (such as ":foo"), then the keys of the hash will be the names of the
-placeholders (without the colon). If the "dollar sign numbers" type of placeholders are being used, the keys of the hash will
-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.
+before the execute will return all the lobs but they will be truncated at 2MBs.
-If this method is called before L</execute>, the literal values passed in are returned. If called after
-L</execute>, then the quoted versions of the values are returned.
+=head3 Piecewise Fetch with Callback
-=head3 B<ParamTypes> (hash ref, read-only)
+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
+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 L</Simple Fetch for LONGs and LONG RAWs> and L</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.
-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
-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 L</bind_param> method.
+Using the table from the last example this code;
-=head3 B<Statement> (string, read-only)
+ $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";
+ }
-Returns the statement string passed to the most recent "prepare" method called in this database handle, even if that method
-failed. This is especially useful where "RaiseError" is enabled and the exception handler checks $@ and sees that a C<prepare>
-method call failed.
+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.
-=head3 B<RowsInCache>
+Using the table from the first example (LONG) this code;
-Returns the number of un-fetched rows in the cache for selects.
+ $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";
+ }
-=head2 Scrollable Cursors
+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.
-Oracle supports the concept of a 'Scrollable Cursor' which is defined as a 'Result Set' where
-the rows can be fetched either sequentially or non-sequentially. One can fetch rows forward,
-backwards, from any given position or the n-th row from the current position in the result set.
+=head4 Piecewise Fetch with Polling
-Rows are numbered sequentially starting at one and client-side caching of the partial or entire result set
-can improve performance by limiting round trips to the server.
+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 L</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 L</Piecewise Fetch with Callback>.
-Oracle does not support DML type operations with scrollable cursors so you are limited
-to simple 'Select' operations only. As well you can not use this functionality with remote
-mapped queries or if the LONG datatype is part of the select list.
+Using the table from the example above this code;
-However, LOBSs, CLOBSs, and BLOBs do work as do all the regular bind, and fetch methods.
+ $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";
+ }
-Only use scrollable cursors if you really have a good reason to. They do use up considerable
-more server and client resources and have poorer response times than non-scrolling cursors.
+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;
-=head3 B<Enabling Scrollable Cursors>
+ $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";
+ }
-To enable this functionality you must first import the 'Fetch Orientation' and the 'Execution Mode' constants by using;
+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.
- use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes);
+=head3 Binding for Updates and Inserts for CLOBs and BLOBs
-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;
+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;
- $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
+ 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;
-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.
+ $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.
-=head3 B<Scrollable Cursor Methods>
+=head3 Support for Remote LOBs;
-The following driver-specific methods are used with scrollable cursors.
+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;
-=over
+ CREATE DATABASE LINK link_test CONNECT TO test_lobs IDENTIFIED BY tester USING 'lob_test';
-=item ora_scroll_position
+to a remote database called 'test_lobs', the following code will work;
- $position = $sth->ora_scroll_position();
+ $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";
+ }
-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.
+Below are the limitations of Remote LOBs;
-=item ora_fetch_scroll
+=over
- @ary = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
+=item Queries involving more than one database are not supported;
-Works the same as fetchrow_array method however, one passes in a 'Fetch Orientation' constant and a fetch_offset
-value which will then determine the row that will be fetched. It returns the row as a list containing the field values.
-Null fields are returned as undef values in the list.
+so the following returns an error:
-The valid orientation constant and fetch offset values combination are detailed below
+ SELECT t1.lobcol,
+ a2.lobcol
+ FROM t1,
+ t2.lobcol@dbs2 a2 W
+ WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);
- 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_RELATIVE, fetches the row relative from the current position as specified by the
- fetch offset value.
+as does:
- 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.
+ SELECT t1.lobcol
+ FROM t1@dbs1
+ UNION ALL
+ SELECT t2.lobcol
+ FROM t2@dbs2;
- 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.
+=item DDL commands are not supported;
-The effect that a ora_fetch_scroll method call has on the current_positon attribute is detailed below.
+so the following returns an error:
- 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.
+ CREATE VIEW v AS SELECT lob_col FROM tab@dbs;
- 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.
+=item Only binds and defines for data going into remote persistent LOBs are supported.
-The effects of the differing orientation constants on the first fetch (current_postion attribute at 0) are as follows.
+so that parameter passing in PL/SQL where CHAR data is bound or defined for remote LOBs is not allowed .
- 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.
+These statements all produce errors:
- 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.
+ SELECT foo() FROM table1@dbs2;
-=back
+ SELECT foo()@dbs INTO char_val FROM DUAL;
-=head3 B<Scrollable Cursor Usage>
+ SELECT XMLType().getclobval FROM table1@dbs2;
-Given a simple code like this:
+=item If the remote object is a view such as
- 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;
+ CREATE VIEW v AS SELECT foo() FROM ...
-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;
+the following would not work:
-=over
+ SELECT * FROM v@dbs2;
-=item Fetching the Last Row
+=item Limited PL/SQL parameter passing
- $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";
+PL/SQL parameter passing is not allowed where the actual argument is a LOB type
+and the remote argument is one of VARCHAR2, NVARCHAR2, CHAR, NCHAR, or RAW.
-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.
+=item RETURNING INTO does not support implicit conversions between CHAR and CLOB.
-=item Fetching the Current Row
+so the following returns an error:
- $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";
+ SELECT t1.lobcol as test, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 RETURNING test
-The current_positon attribute will still be 20 after this snippet.
+=back
-=item Fetching the First Row
+=head2 Locator Data Interface
- $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";
+=head3 Simple Usage
-The current_positon attribute will be 1 after this snippet.
+When fetching LOBs with this interface a 'LOB Locator' is created then used to get the lob with the LongReadLen and LongTruncOk attributes.
+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.
-=item Fetching the Next Row
+When inserting or updating LOBs some I<major> magic has to be performed
+behind the scenes to make it transparent. Basically the driver has to
+insert a 'LOB Locator' and then refetch the newly inserted LOB
+Locator before being able to write the data into it. However, it works
+well most of the time, and I've made it as fast as possible, just one
+extra server-round-trip per insert or update after the first. For the
+time being, only single-row LOB updates are supported.
- 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";
+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:
-The current_positon attribute will be 5 after this snippet.
+ $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });
-=item Fetching the Prior Row
+The ORA_CLOB and ORA_BLOB constants can be imported using
- 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";
+ use DBD::Oracle qw(:ora_types);
-The current_positon attribute will be 1 after this snippet.
+or use the corresponding integer values (112 and 113).
-=item Fetching the 10th Row
+One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has
+to be able to tell which parameters relate to which table fields.
+In all cases where it can possibly work it out for itself, it does,
+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:
- $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";
+ $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });
-The current_positon attribute will be 10 after this snippet.
+There are some limitations inherent in the way DBD::Oracle makes typical
+LOB operations simple by hiding the LOB Locator processing:
-=item Fetching the 10th to 14th Row
+ - Can't read/write LOBs in chunks (except via DBMS_LOB.WRITEAPPEND in PL/SQL)
+ - To INSERT a LOB, you need UPDATE privilege.
- 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 alternative is to disable the automatic LOB Locator processing.
+If L</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 L</Data Interface for LOB Locators> section below.
-The current_positon attribute will be 14 after this snippet.
+=head3 LOB support in PL/SQL
-=item Fetching the 14th to 10th Row
+LOB Locators can be passed to PL/SQL calls by binding them to placeholders
+with the proper C<ora_type>. If L</ora_auto_lob> is true, output LOB
+parameters will be automatically returned as strings.
- 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";
+If the Oracle driver has support for temporary LOBs (Oracle 9i and higher),
+strings can be bound to input LOB placeholders and will be automatically
+converted to LOBs.
-The current_positon attribute will be 10 after this snippet.
+Example:
+ # Build a large XML document, bind it as a CLOB,
+ # extract elements through PL/SQL and return as a CLOB
-=item Fetching the 5th Row From the Present Position.
+ # $dbh is a connected database handle
+ # output will be large
- $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";
+ local $dbh->{LongReadLen} = 1_000_000;
-The current_positon attribute will be 15 after this snippet.
+ my $in_clob = "<document>\n";
+ $in_clob .= " <value>$_</value>\n" for 1 .. 10_000;
+ $in_clob .= "</document>\n";
-=item Fetching the 9th Row Prior From the Present Position
+ my $out_clob;
- $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.
+ my $sth = $dbh->prepare(<<PLSQL_END);
+ -- extract 'value' nodes
+ DECLARE
+ x XMLTYPE := XMLTYPE(:in);
+ BEGIN
+ :out := x.extract('/document/value').getClobVal();
+ END;
-=item Use Finish
+ PLSQL_END
- $sth->finish();
+ # :in param will be converted to a temp lob
+ # :out parameter will be returned as a string.
-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.
+ $sth->bind_param( ':in', $in_clob, { ora_type => ORA_CLOB } );
+ $sth->bind_param_inout( ':out', \$out_clob, 0, { ora_type => ORA_CLOB } );
+ $sth->execute;
-=back
+If you ever get an
-=head2 LOBs and LONGs
+ ORA-01691 unable to extend lob segment sss.ggg by nnn in tablespace ttt
-The key to working with LOBs (CLOB, BLOBs) is to remember the value of an Oracle LOB column is not the content of the LOB. It's a
-'LOB Locator' which, after being selected or inserted needs extra processing to read or write the content of the LOB. There are also legacy LONG types (LONG, LONG RAW, VARCHAR2)
-which are presently deprecated by Oracle but are still in use. These LONG types do not utilize a 'LOB Locator' and also are more limited in
-functionality than CLOB or BLOB fields.
+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.
-DBD::Oracle now offers three interfaces to LOB and LONG data,
+=head2 Persistent & Locator Interface Caveats
-=over
+Now that one has the option of using the Persistent or the Locator interface for LOBs the questions arises
+which one to use. For starters, if you want to access LOBs over a dblink you will have to use the Persistent
+interface so that choice is simple. The question of which one to use after that is a little more tricky.
+It basically boils down to a choice between LOB size and speed.
-=item L</Data Interface for Persistent LOBs>
+The Callback and Polling piecewise fetches are very very slow
+when compared to the Simple and the Locator fetches but they can handle very large blocks of data. Given a situation where a
+large LOB is to be read the Locator fetch may time out while either of the piecewise fetches may not.
-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.
+With the Simple fetch you are limited by physical memory of your server but it runs a little faster than the Locator, as there are fewer round trips
+to the server. So if you have small LOBs and need to save a little bandwidth this is the one to use. It you are going after large LOBs then the Locator interface is the one to use.
-=item L</Data Interface for LOB Locators>
+If you need to update more than a single row of with LOB data then the Persistent interface can do it while the Locator can't.
-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.
+If you encounter a situation where you have to access the legacy LOBs (LONG, LONG RAW) and the values are to large for you system then you can use
+the Callback or Polling piecewise fetches to get all of the data.
-=item L</LOB Locator Method Interface>
+Not all of the Persistent interface has been implemented yet, the following are not supported;
-This allows the user direct access to the LOB Locator methods, so you have to take case of the LOB Locator operations yourself.
+ 1) Piecewise, polling and callback binds for INSERT and UPDATE operations.
+ 2) Piecewise array binds for SELECT, INSERT and UPDATE operations.
-=back
+Most of the time you should just use the L</Locator Data Interface> as this is in one that has the best combination of speed and size.
-Generally speaking the interface that you will chose will be dependent on what end you are trying to achieve. All have their benefits and
-drawbacks.
+All this being said if you are doing some critical programming I would use the L</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.
-One point to remember when working with LOBs (CLOBs, BLOBs) is if your LOB column can be in one of three states;
+=head2 Data Interface for LOB Locators
-=over
+The following driver-specific methods let you manipulate "LOB Locators" directly.
+To select a LOB locator directly set the if the C<ora_auto_lob>
+attribute to false, or alternatively they can be returned via PL/SQL procedure calls.
-=item NULL
+(If using a DBI version earlier than 1.36 they must be called via the
+func() method. Note that methods called via func() don't honour
+RaiseError etc, and so it's important to check $dbh->err after each call.
+It's recommended that you upgrade to DBI 1.38 or later.)
-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
+Note that LOB locators are only valid while the statement handle that
+created them is valid. When all references to the original statement
+handle are lost, the handle is destroyed and the locators are freed.
- DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr
+=over 4
-error when working with a LOB.
+=item ora_lob_read
-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;
+ $data = $dbh->ora_lob_read($lob_locator, $offset, $length);
- UPDATE lob_example
- SET bindata=EMPTY_BLOB()
- WHERE bindata IS NULL.
+Read a portion of the LOB. $offset starts at 1.
+Uses the Oracle OCILobRead function.
-=item Empty
+=item ora_lob_write
-A LOB instance with a locator exists in the cell, but it has no value. The length of the LOB is zero. In this case DBD::Oracle will return 'undef' for the field.
+ $rc = $dbh->ora_lob_write($lob_locator, $offset, $data);
-=item Populated
+Write/overwrite a portion of the LOB. $offset starts at 1.
+Uses the Oracle OCILobWrite function.
-A LOB instance with a locator and a value exists in the cell. You actually get the LOB value.
+=item ora_lob_append
-=back
+ $rc = $dbh->ora_lob_append($lob_locator, $data);
-=head3 B<Data Interface for Persistent LOBs>
+Append $data to the LOB. Uses the Oracle OCILobWriteAppend function.
-This is the original interface for LONG and LONG RAW datatypes and from Oracle 9iR1 and later the OCI API was extended to work directly with the other LOB datatypes.
-In other words you can treat all LOB type data (BLOB, CLOB) as if it was a LONG, LONG RAW, or VARCHAR2. So you can perform INSERT, UPDATE, fetch, bind, and define operations on LOBs using the same techniques
-you would use on other datatypes that store character or binary data. In some cases there are fewer round trips to the server as no 'LOB Locators' are
-used, normally one can get an entire LOB is a single round trip.
+=item ora_lob_trim
-=head4 Simple Fetch for LONGs and LONG RAWs
+ $rc = $dbh->ora_lob_trim($lob_locator, $length);
-As the name implies this is the simplest way to use this interface. DBD::Oracle just attempts to get your LONG datatypes as a single large piece.
-There are no special settings, simply set the database handle's 'LongReadLen' attribute to a value that will be the larger than the expected size of the LONG or LONG RAW.
-If the size of the LONG or LONG RAW 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. You have most likely reached this limit if you run into
-an 'ORA-01062: unable to allocate memory for define buffer' error. One solution is to set the size of 'LongReadLen' to a lower value.
+Trims the length of the LOB to $length.
+Uses the Oracle OCILobTrim function.
-For example give this table;
+=item ora_lob_length
- CREATE TABLE test_long (
- id NUMBER,
- long1 long)
+ $length = $dbh->ora_lob_length($lob_locator);
-this code;
+Returns the length of the LOB.
+Uses the Oracle OCILobGetLength function.
- $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;
+=item ora_lob_is_init
- $dbh->{LongTruncOk}=1;
+ $is_init = $dbh->ora_lob_is_init($lob_locator);
-before the execute will return all the long1 fields but they will be truncated at 2MBs.
+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.
-=head4 Using ora_ncs_buff_mtpl
+=item ora_lob_chunk_size
-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.
+ $chunk_size = $dbh->ora_lob_chunk_size($lob_locator);
-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
-(maybe less characters though) but you are using allot more memory that you need.
+Returns the chunk size of the LOB.
+Uses the Oracle OCILobGetChunkSize function.
-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.
+For optimal performance, Oracle recommends reading from and
+writing to a LOB in batches using a multiple of the LOB chunk size.
+In Oracle 10g and before, when all defaults are in place, this
+chunk size defaults to 8k (8192).
-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.
+=back
-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;
+=head3 LOB Locator Method Examples
- ORA-03127: no new operations allowed until the active operation ends
+I<Note:> Make sure you first read the note in the section above about
+multi-byte character set issues with these methods.
-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)
+The following examples demonstrate the usage of LOB Locators
+to read, write, and append data, and to query the size of
+large data.
-If you get this, simply increment the ora_ncs_buff_mtpl by one until it goes away.
+The following examples assume a table containing two large
+object columns, one binary and one character, with a primary
+key column, defined as follows:
-This should greatly increase your ability to select very large CLOBs or NCLOBs, by freeing up a large block of memory.
+ CREATE TABLE lob_example (
+ lob_id INTEGER PRIMARY KEY,
+ bindata BLOB,
+ chardata CLOB
+ )
-You can tune this value by setting ora_oci_success_warn which will display the following
+It also assumes a sequence for use in generating unique
+lob_id field values, defined as follows:
- OCILobRead field 2 of 3 SUCCESS: csform 1 (SQLCS_IMPLICIT), LOBlen 10240(characters), LongReadLen
- 20(characters), BufLen 80(characters), Got 28(characters)
+ CREATE SEQUENCE lob_example_seq
-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.
+=head3 Example: Inserting a new row with large data
-=head4 Simple Fetch for CLOBs and BLOBs
+Unless enough memory is available to store and bind the
+entire LOB data for insert all at once, the LOB columns must
+be written interactively, piece by piece. In the case of a new row,
+this is performed by first inserting a row, with empty values in
+the LOB columns, then modifying the row by writing the large data
+interactively to the LOB columns using their LOB locators as handles.
-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.
+The insert statement must create token values in the LOB
+columns. Here, we use the empty string for both the binary
+and character large object columns 'bindata' and 'chardata'.
-For CLOBs and NCLOBs the limit is 64k chars if there is no truncation, this is an internal OCI limit complain to them if you want it changed. However if you CLOB is longer than this
-and also larger than the 'LongReadLen' than the 'LongReadLen' in chars is returned.
+After the INSERT statement, a SELECT statement is used to
+acquire LOB locators to the 'bindata' and 'chardata' fields
+of the newly inserted row. Because these LOB locators are
+subsequently written, they must be acquired from a select
+statement containing the clause 'FOR UPDATE' (LOB locators
+are only valid within the transaction that fetched them, so
+can't be used effectively if AutoCommit is enabled).
-It seems with BLOBs you are not limited by the 64k.
+ my $lob_id = $dbh->selectrow_array( <<" SQL" );
+ SELECT lob_example_seq.nextval FROM DUAL
+ SQL
-For example give this table;
+ my $sth = $dbh->prepare( <<" SQL" );
+ INSERT INTO lob_example
+ ( lob_id, bindata, chardata )
+ VALUES ( ?, EMPTY_BLOB(),EMPTY_CLOB() )
+ SQL
+ $sth->execute( $lob_id );
- CREATE TABLE test_lob (id NUMBER,
- clob1 CLOB,
- clob2 CLOB,
- blob1 BLOB,
- blob2 BLOB)
+ $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();
-this code;
+ 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 );
- $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";
- }
+ # 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;
+ }
-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;
+ # 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;
+ }
- $dbh->{LongTruncOk}=1;
-before the execute will return all the lobs but they will be truncated at 2MBs.
+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:
-=head4 Piecewise Fetch with Callback
+ my $buffer = '';
+ while ( read( BIN_FH, $buffer, $chunk_size ) ) {
+ $dbh->ora_lob_append( $bin_locator, $buffer );
+ }
-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
-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 L</Simple Fetch for LONGs and LONG RAWs> and L</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.
+The scalar variables $offset and $length are no longer
+needed, because ora_lob_append() keeps track of the offset
+for us.
-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";
- }
-
-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";
- }
+=head3 Example: Updating an existing row with large data
-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.
+In this example, we demonstrate a technique for overwriting
+a portion of a blob field with new binary data. The blob
+data before and after the section overwritten remains
+unchanged. Hence, this technique could be used for updating
+fixed length subfields embedded in a binary field.
-=head4 Piecewise Fetch with Polling
+ my $lob_id = 5; # Arbitrary row identifier, for example
-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 L</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 L</Piecewise Fetch with Callback>.
+ $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();
-Using the table from the example above this code;
+ my $offset = 100234;
+ my $data = "This string will overwrite a portion of the blob";
+ $dbh->ora_lob_write( $bin_locator, $offset, $data );
- $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";
- }
+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".
-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.
+=head3 Example: Streaming character data from the database
-Finally with this code;
+In this example, we demonstrate a technique for streaming
+data from the database to a file handle, in this case
+STDOUT. This allows more data to be read in and written out
+than could be stored in memory at a given time.
- $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";
- }
+ my $lob_id = 17; # Arbitrary row identifier, for example
-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.
+ $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();
-=head4 Binding for Updates and Inserts for CLOBs and BLOBs
+ 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;
+ }
-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;
+Notice that the select statement does not contain the phrase
+"FOR UPDATE". Because we are only reading from the LOB
+Locator returned, and not modifying the LOB it refers to,
+the select statement does not require the "FOR UPDATE"
+clause.
- 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;
+A word of caution when using the data returned from an ora_lob_read in a conditional statement.
+for example if the code below;
- $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();
+ while( my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size ) ) {
+ print STDOUT $data;
+ $offset += $chunk_size;
+ }
-So far the only limit reached with this form of insert is the LOBs must be under 2GB in 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
+as $data will contain 0 which PERL will see as false and not print it out.
-=head4 Support for Remote LOBs;
+=head3 Example: Truncating existing large data
-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;
+In this example, we truncate the data already present in a
+large object column in the database. Specifically, for each
+row in the table, we truncate the 'bindata' value to half
+its previous length.
- CREATE DATABASE LINK link_test CONNECT TO test_lobs IDENTIFIED BY tester USING 'lob_test';
+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
+select the LOB locators 'FOR UPDATE'.
-to a remote database called 'test_lobs', the following code will work;
+ 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 );
+ }
+ }
- $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";
- }
+=head1 SPACES AND PADDING
-Below are the limitations of Remote LOBs;
+=head2 Trailing Spaces
-=over
+Only the Oracle OCI 8 strips trailing spaces from VARCHAR placeholder
+values and uses Nonpadded Comparison Semantics with the result.
+This causes trouble if the spaces are needed for
+comparison with a CHAR value or to prevent the value from
+becoming '' which Oracle treats as NULL.
+Look for Blank-padded Comparison Semantics and Nonpadded
+Comparison Semantics in Oracle's SQL Reference or Server
+SQL Reference for more details.
-=item Queries involving more than one database are not supported;
+To preserve trailing spaces in placeholder values for Oracle clients that use OCI 8,
+either change the default placeholder type with L</ora_ph_type> or the placeholder
+type for a particular call to L<DBI/bind> or L<DBI/bind_param_inout>
+with L</ora_type> or C<TYPE>.
+Using L<ORA_CHAR> with L<ora_type> or C<SQL_CHAR> with C<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.
-so the following returns an error:
+Please remember that using spaces as a value or at the end of
+a value makes visually distinguishing values with different
+numbers of spaces difficult and should be avoided.
- SELECT t1.lobcol,
- a2.lobcol
- FROM t1,
- t2.lobcol@dbs2 a2 W
- WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);
+Oracle Clients that use OCI 9.2 do not strip trailing spaces.
-as does:
+=head2 Padded Char Fields
- SELECT t1.lobcol
- FROM t1@dbs1
- UNION ALL
- SELECT t2.lobcol
- FROM t2@dbs2;
+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 L</ora_ph_type> or placeholder
+type for a particular call with L<DBI/bind> or L<DBI/bind_param_inout>
+with L</ORA_CHAR>.
-=item DDL commands are not supported;
+=head1 UNICODE
-so the following returns an error:
+DBD::Oracle now supports Unicode UTF-8. There are, however, a number
+of issues you should be aware of, so please read all this section
+carefully.
- CREATE VIEW v AS SELECT lob_col FROM tab@dbs;
+In this section we'll discuss "Perl and Unicode", then "Oracle and
+Unicode", and finally "DBD::Oracle and Unicode".
-=item Only binds and defines for data going into remote persistent LOBs are supported.
+Information about Unicode in general can be found at:
+L<http://www.unicode.org/>. It is well worth reading because there are
+many misconceptions about Unicode and you may be holding some of them.
-so that parameter passing in PL/SQL where CHAR data is bound or defined for remote LOBs is not allowed .
+=head2 Perl and Unicode
-These statements all produce errors:
+Perl began implementing Unicode with version 5.6, but the implementation
+did not mature until version 5.8 and later. If you plan to use Unicode
+you are I<strongly> urged to use Perl 5.8.2 or later and to I<carefully> read
+the Perl documentation on Unicode:
- SELECT foo() FROM table1@dbs2;
+ perldoc perluniintro # in Perl 5.8 or later
+ perldoc perlunicode
- SELECT foo()@dbs INTO char_val FROM DUAL;
+And then read it again.
- SELECT XMLType().getclobval FROM table1@dbs2;
+Perl's internal Unicode format is UTF-8
+which corresponds to the Oracle character set called AL32UTF8.
-=item If the remote object is a view such as
+=head2 Oracle and Unicode
- CREATE VIEW v AS SELECT foo() FROM ...
+Oracle supports many characters sets, including several different forms
+of Unicode. These include:
-the following would not work:
+ AL16UTF16 => valid for NCHAR columns (CSID=2000)
+ UTF8 => valid for NCHAR columns (CSID=871), deprecated
+ AL32UTF8 => valid for NCHAR and CHAR columns (CSID=873)
- SELECT * FROM v@dbs2;
+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:
-=item Limited PL/SQL parameter passing
+ $hash_ref = $dbh->ora_nls_parameters()
+ $database_charset = $hash_ref->{NLS_CHARACTERSET};
+ $national_charset = $hash_ref->{NLS_NCHAR_CHARACTERSET};
-PL/SQL parameter passing is not allowed where the actual argument is a LOB type
-and the remote argument is one of VARCHAR2, NVARCHAR2, CHAR, NCHAR, or RAW.
+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.
-=item RETURNING INTO does not support implicit conversions between CHAR and CLOB.
-so the following returns an error:
+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.
- SELECT t1.lobcol as test, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 RETURNING test
+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
+the database Oracle will convert from the character set specified by
+NLS_LANG to the database character set.
-=back
+The NLS_NCHAR environment variable can be used to define a different
+character set for 'national' (NCHAR) character types.
-=head3 B<Locator Data Interface>
+Both UTF8 and AL32UTF8 can be used in NLS_LANG and NLS_NCHAR.
+For example:
-=head4 Simple Usage
+ NLS_LANG=AMERICAN_AMERICA.UTF8
+ NLS_LANG=AMERICAN_AMERICA.AL32UTF8
+ NLS_NCHAR=UTF8
+ NLS_NCHAR=AL32UTF8
-When fetching LOBs with this interface a 'LOB Locator' is created then used to get the lob with the LongReadLen and LongTruncOk attributes.
-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.
+=head2 Oracle UTF8 is not UTF-8
-When inserting or updating LOBs some I<major> magic has to be performed
-behind the scenes to make it transparent. Basically the driver has to
-insert a 'LOB Locator' and then refetch the newly inserted LOB
-Locator before being able to write the data into it. However, it works
-well most of the time, and I've made it as fast as possible, just one
-extra server-round-trip per insert or update after the first. For the
-time being, only single-row LOB updates are supported.
+AL32UTF8 should be used in preference to UTF8 if it works for you,
+which it should for Oracle 9.2 or later. If you're using an old
+version of Oracle that doesn't support AL32UTF8 then you should
+avoid using any Unicode characters that require surrogates, in other
+words characters beyond the Unicode BMP (Basic Multilingual Plane).
-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:
+That's because the character set that Oracle calls "UTF8" doesn't
+conform to the UTF-8 standard in its handling of surrogate characters.
+Technically the encoding that Oracle calls "UTF8" is known as "CESU-8".
+Here are a couple of extracts from L<http://www.unicode.org/reports/tr26/>:
- $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });
+ 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.
-The ORA_CLOB and ORA_BLOB constants can be imported using
+ 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.
- use DBD::Oracle qw(:ora_types);
+Oracle uses this internally because it collates (sorts) in the same order
+as UTF16, which is the basis of Oracle's internal collation definitions.
-or use the corresponding integer values (112 and 113).
+Rather than change UTF8 for clients Oracle chose to define a new character
+set called "AL32UTF8" which does conform to the UTF-8 standard.
+(The AL32UTF8 character set can't be used on the server because it
+would break collation.)
-One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has
-to be able to tell which parameters relate to which table fields.
-In all cases where it can possibly work it out for itself, it does,
-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:
+Because of that, for the rest of this document we'll use "AL32UTF8".
+If you're using an Oracle version below 9.2 you'll need to use "UTF8"
+until you upgrade.
- $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });
+=head2 DBD::Oracle and Unicode
-There are some limitations inherent in the way DBD::Oracle makes typical
-LOB operations simple by hiding the LOB Locator processing:
+DBD::Oracle Unicode support has been implemented for Oracle versions 9
+or greater, and Perl version 5.6 or greater (though we I<strongly>
+suggest that you use Perl 5.8.2 or later).
- - Can't read/write LOBs in chunks (except via DBMS_LOB.WRITEAPPEND in PL/SQL)
- - To INSERT a LOB, you need UPDATE privilege.
+You can check which Oracle version your DBD::Oracle was built with by
+importing the C<ORA_OCI> constant from DBD::Oracle.
-The alternative is to disable the automatic LOB Locator processing.
-If L</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 L</Data Interface for LOB Locators> section below.
+B<Fetching Data>
-=head4 LOB support in PL/SQL
+Any data returned from Oracle to DBD::Oracle in the AL32UTF8
+character set will be marked as UTF-8 to ensure correct handling by Perl.
-LOB Locators can be passed to PL/SQL calls by binding them to placeholders
-with the proper C<ora_type>. If L</ora_auto_lob> is true, output LOB
-parameters will be automatically returned as strings.
+For Oracle to return data in the AL32UTF8 character set the
+NLS_LANG or NLS_NCHAR environment variable I<must> be set as described
+in the previous section.
-If the Oracle driver has support for temporary LOBs (Oracle 9i and higher),
-strings can be bound to input LOB placeholders and will be automatically
-converted to LOBs.
+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.
-Example:
- # Build a large XML document, bind it as a CLOB,
- # extract elements through PL/SQL and return as a CLOB
+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.
- # $dbh is a connected database handle
- # output will be large
+B<Sending Data using Placeholders>
- local $dbh->{LongReadLen} = 1_000_000;
+Data bound to a placeholder is assumed to be in the default client
+character set (specified by NLS_LANG) except for a few special
+cases. These are listed here with the highest precedence first:
- my $in_clob = "<document>\n";
- $in_clob .= " <value>$_</value>\n" for 1 .. 10_000;
- $in_clob .= "</document>\n";
+If the C<ora_csid> attribute is given to bind_param() then that
+is passed to Oracle and takes precedence.
- my $out_clob;
+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.
+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 C<ora_csform>
+attribute.)
- my $sth = $dbh->prepare(<<PLSQL_END);
- -- extract 'value' nodes
- DECLARE
- x XMLTYPE := XMLTYPE(:in);
- BEGIN
- :out := x.extract('/document/value').getClobVal();
- END;
+If the C<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.
- PLSQL_END
- # :in param will be converted to a temp lob
- # :out parameter will be returned as a string.
+ use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
+ ...
+ $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR });
- $sth->bind_param( ':in', $in_clob, { ora_type => ORA_CLOB } );
- $sth->bind_param_inout( ':out', \$out_clob, 0, { ora_type => ORA_CLOB } );
- $sth->execute;
+or
-If you ever get an
+ $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders
- ORA-01691 unable to extend lob segment sss.ggg by nnn in tablespace ttt
+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;
-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.
+ 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
-=head3 B<Persistent & Locator Interface Caveats>
+The warning will report the parameter number and the NCHAR setting that the query is running.
-Now that one has the option of using the Persistent or the Locator interface for LOBs the questions arises
-which one to use. For starters, if you want to access LOBs over a dblink you will have to use the Persistent
-interface so that choice is simple. The question of which one to use after that is a little more tricky.
-It basically boils down to a choice between LOB size and speed.
+B<Sending Data using SQL>
-The Callback and Polling piecewise fetches are very very slow
-when compared to the Simple and the Locator fetches but they can handle very large blocks of data. Given a situation where a
-large LOB is to be read the Locator fetch may time out while either of the piecewise fetches may not.
+Oracle assumes the SQL statement is in the default client character
+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.
-With the Simple fetch you are limited by physical memory of your server but it runs a little faster than the Locator, as there are fewer round trips
-to the server. So if you have small LOBs and need to save a little bandwidth this is the one to use. It you are going after large LOBs then the Locator interface is the one to use.
+=head2 DBD::Oracle and Other Character Sets and Encodings
-If you need to update more than a single row of with LOB data then the Persistent interface can do it while the Locator can't.
+The only multi-byte Oracle character set supported by DBD::Oracle is
+"AL32UTF8" (and "UTF8"). Single-byte character sets should work well.
-If you encounter a situation where you have to access the legacy LOBs (LONG, LONG RAW) and the values are to large for you system then you can use
-the Callback or Polling piecewise fetches to get all of the data.
+=head1 OBJECT & COLLECTION DATA TYPES
-Not all of the Persistent interface has been implemented yet, the following are not supported;
+Oracle databases allow for the creation of object oriented like user-defined types.
+There are two types of objects, Embedded--an object stored in a column of a regular table
+and REF--an object that uses the REF retrieval mechanism.
- 1) Piecewise, polling and callback binds for INSERT and UPDATE operations.
- 2) Piecewise array binds for SELECT, INSERT and UPDATE operations.
+DBD::Oracle supports only the 'selection' of embedded objects of the following types OBJECT, VARRAY
+and TABLE in any combination. Support is seamless and recursive, meaning you
+need only supply a simple SQL statement to get all the values in an embedded object.
+You can either get the values as an array of scalars or they can be returned into a DBD::Oracle::Object.
-Most of the time you should just use the L</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 L</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.
+Array example, given this type and table;
-=head3 B<Data Interface for LOB Locators>
+ CREATE OR REPLACE TYPE "PHONE_NUMBERS" as varray(10) of varchar(30);
-The following driver-specific methods let you manipulate "LOB Locators" directly.
-To select a LOB locator directly set the if the C<ora_auto_lob>
-attribute to false, or alternatively they can be returned via PL/SQL procedure calls.
+ CREATE TABLE "CONTACT"
+ ( "COMPANYNAME" VARCHAR2(40),
+ "ADDRESS" VARCHAR2(100),
+ "PHONE_NUMBERS" "PHONE_NUMBERS"
+ )
-(If using a DBI version earlier than 1.36 they must be called via the
-func() method. Note that methods called via func() don't honour
-RaiseError etc, and so it's important to check $dbh->err after each call.
-It's recommended that you upgrade to DBI 1.38 or later.)
+The code to access all the data in the table could be something like this;
-Note that LOB locators are only valid while the statement handle that
-created them is valid. When all references to the original statement
-handle are lost, the handle is destroyed and the locators are freed.
+ 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 #: ";
-=over 4
+ foreach my $items (@$phone){
+ print $items.", ";
+ }
+ print "\n";
+ }
-=item ora_lob_read
+Note that values in PHONE_NUMBERS are returned as an array reference '@$phone'.
- $data = $dbh->ora_lob_read($lob_locator, $offset, $length);
+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;
-Read a portion of the LOB. $offset starts at 1.
-Uses the Oracle OCILobRead function.
+ CREATE OR REPLACE TYPE GRADELIST AS TABLE OF NUMBER;
-=item ora_lob_write
+ CREATE OR REPLACE TYPE STUDENT AS OBJECT(
+ NAME VARCHAR2(60),
+ SOME_GRADES GRADELIST);
- $rc = $dbh->ora_lob_write($lob_locator, $offset, $data);
+ CREATE OR REPLACE TYPE STUDENTS_T AS TABLE OF STUDENT;
-Write/overwrite a portion of the LOB. $offset starts at 1.
-Uses the Oracle OCILobWrite function.
+ 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);
-=item ora_lob_append
+The following code will access all of the embedded data;
- $rc = $dbh->ora_lob_append($lob_locator, $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";
+ }
-Append $data to the LOB. Uses the Oracle OCILobWriteAppend function.
+Object example, given this object and table;
-=item ora_lob_trim
+ CREATE OR REPLACE TYPE Person AS OBJECT (
+ name VARCHAR2(20),
+ age INTEGER)
+ ) NOT FINAL;
- $rc = $dbh->ora_lob_trim($lob_locator, $length);
+ CREATE TYPE Employee UNDER Person (
+ salary NUMERIC(8,2)
+ );
-Trims the length of the LOB to $length.
-Uses the Oracle OCILobTrim function.
+ CREATE TABLE people (id INTEGER, obj Person);
-=item ora_lob_length
+ INSERT INTO people VALUES (1, Person('Black', 25));
+ INSERT INTO people VALUES (2, Employee('Smith', 44, 5000));
- $length = $dbh->ora_lob_length($lob_locator);
+The following code will access the data;
-Returns the length of the LOB.
-Uses the Oracle OCILobGetLength function.
+ $dbh{'ora_objects'} =>1;
+ $sth = $dbh->prepare("select * from people order by id");
+ $sth->execute();
-=item ora_lob_is_init
+ # object are fetched as instance of DBD::Oracle::Object
+ my ($id1, $obj1) = $sth->fetchrow();
+ my ($id2, $obj2) = $sth->fetchrow();
- $is_init = $dbh->ora_lob_is_init($lob_locator);
+ # 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
-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.
+ # get attribute NAME from object
+ print $obj1->attr('NAME')."3\n"; # 'Black' is printed
+ print $obj2->attr('NAME')."3\n"; # 'Smith' is printed
-=item ora_lob_chunk_size
+ # 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 }
- $chunk_size = $dbh->ora_lob_chunk_size($lob_locator);
+ # 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 )
-Returns the chunk size of the LOB.
-Uses the Oracle OCILobGetChunkSize function.
+So far DBD::Oracle has been tested on a table with 20 embedded Objects, Varrays and Tables
+nested to 10 levels.
-For optimal performance, Oracle recommends reading from and
-writing to a LOB in batches using a multiple of the LOB chunk size.
-In Oracle 10g and before, when all defaults are in place, this
-chunk size defaults to 8k (8192).
+Any NULL values found in the embedded object will be returned as 'undef'.
-=back
+=head1 OTHER DATA TYPES
-=head4 LOB Locator Method Examples
+DBD::Oracle does not I<explicitly> support most Oracle datatypes.
+It simply asks Oracle to return them as strings and Oracle does so.
+Mostly. Similarly when binding placeholder values DBD::Oracle binds
+them as strings and Oracle converts them to the appropriate type,
+such as DATE, when used.
-I<Note:> Make sure you first read the note in the section above about
-multi-byte character set issues with these methods.
+Some of these automatic conversions to and from strings use NLS
+settings to control the formatting for output and the parsing for
+input. The most common example is the DATE type. The default NLS
+format for DATE might be DD-MON-YYYY and so when a DATE type is
+fetched that's how Oracle will format the date. NLS settings also
+control the default parsing of strings into DATE values. An error
+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")
+to explicitly specify formats for converting to and from strings.
-The following examples demonstrate the usage of LOB Locators
-to read, write, and append data, and to query the size of
-large data.
+A slightly more subtle problem can occur with NUMBER types. The
+default NLS settings might format numbers with a fullstop ("C<.>")
+to separate thousands and a comma ("C<,>") as the decimal point.
+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
+quickly. The best fix is to change the NLS settings. That can be
+done for an individual connection by doing:
-The following examples assume a table containing two large
-object columns, one binary and one character, with a primary
-key column, defined as follows:
+ $dbh->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");
- CREATE TABLE lob_example (
- lob_id INTEGER PRIMARY KEY,
- bindata BLOB,
- chardata CLOB
- )
+There are some types, like BOOLEAN, that Oracle does not automatically
+convert to or from strings (pity). These need to be converted
+explicitly using SQL or PL/SQL functions.
-It also assumes a sequence for use in generating unique
-lob_id field values, defined as follows:
- CREATE SEQUENCE lob_example_seq
+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' );
-=head4 Example: Inserting a new row with large data
+ # BOOLEAN values
+ my $sth2 = $dbh->prepare( <<PLSQL_END );
+ DECLARE
+ b0 BOOLEAN;
+ b1 BOOLEAN;
+ o0 VARCHAR2(32);
+ o1 VARCHAR2(32);
-Unless enough memory is available to store and bind the
-entire LOB data for insert all at once, the LOB columns must
-be written interactively, piece by piece. In the case of a new row,
-this is performed by first inserting a row, with empty values in
-the LOB columns, then modifying the row by writing the large data
-interactively to the LOB columns using their LOB locators as handles.
+ 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 );
-The insert statement must create token values in the LOB
-columns. Here, we use the empty string for both the binary
-and character large object columns 'bindata' and 'chardata'.
+ -- 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'"
-After the INSERT statement, a SELECT statement is used to
-acquire LOB locators to the 'bindata' and 'chardata' fields
-of the newly inserted row. Because these LOB locators are
-subsequently written, they must be acquired from a select
-statement containing the clause 'FOR UPDATE' (LOB locators
-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
+=head4 Support for Insert of XMLType (ORA_XMLTYPE)
- my $sth = $dbh->prepare( <<" SQL" );
- INSERT INTO lob_example
- ( lob_id, bindata, chardata )
- VALUES ( ?, EMPTY_BLOB(),EMPTY_CLOB() )
- SQL
- $sth->execute( $lob_id );
+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
+example with a table like this;
- $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();
+ create table books (book_id number, book_xml XMLType);
- 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 );
+one can insert data using this code
- # 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;
- }
+ $SQL='insert into books values (1,:p_xml)';
+ $xml= '<Books>
+ <Book id=1>
+ <Title>Programming the Perl DBI</Title>
+ <Subtitle>The Cheetah Book</Subtitle>
+ <Authors>
+ <Author>T. Bunce</Author>
+ <Author>Alligator Descartes</Author>
+ </Authors>
- # 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;
- }
+ </Book>
+ <Book id=10000>...
+ </Books>';
+ 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.
+=head4 Binding Cursors
+
+Cursors can be returned from PL/SQL blocks, either from stored
+functions (or procedures with OUT parameters) or
+from direct C<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 ) { ... }
-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:
+The only special requirement is the use of C<bind_param_inout()> with an
+attribute hash parameter that specifies C<ora_type> as C<ORA_RSET>.
+If you don't do that you'll get an error from the C<execute()> like:
+"ORA-06550: line X, column Y: PLS-00306: wrong number or types of
+arguments in call to ...".
- my $buffer = '';
- while ( read( BIN_FH, $buffer, $chunk_size ) ) {
- $dbh->ora_lob_append( $bin_locator, $buffer );
- }
+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.
-The scalar variables $offset and $length are no longer
-needed, because ora_lob_append() keeps track of the offset
-for us.
+ # 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 ) { ... }
-=head4 Example: Updating an existing row with large data
+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:
-In this example, we demonstrate a technique for overwriting
-a portion of a blob field with new binary data. The blob
-data before and after the section overwritten remains
-unchanged. Hence, this technique could be used for updating
-fixed length subfields embedded in a binary field.
+ my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
+ $sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
+ $sth3->execute;
- my $lob_id = 5; # Arbitrary row identifier, for example
+It is not normally necessary to close a cursor
+explicitly in this way. Oracle will close the cursor automatically
+at the first client-server interaction after the cursor statement handle is
+destroyed. An explicit close may be desirable if the reference to
+the cursor handle from the PL/SQL statement handle delays the destruction
+of the cursor handle for too long. This reference remains until the
+PL/SQL handle is re-bound, re-executed or destroyed.
- $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();
+See the C<curref.pl> script in the Oracle.ex directory in the DBD::Oracle
+source distribution for a complete working example.
- my $offset = 100234;
- my $data = "This string will overwrite a portion of the blob";
- $dbh->ora_lob_write( $bin_locator, $offset, $data );
+=head4 Fetching Nested Cursors
-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".
+Oracle supports the use of select list expressions of type REF CURSOR.
+These may be explicit cursor expressions - C<CURSOR(SELECT ...)>, or
+calls to PL/SQL functions which return REF CURSOR values. The values
+of these expressions are known as nested cursors.
-=head4 Example: Streaming character data from the database
+The value returned to a Perl program when a nested cursor is fetched
+is a statement handle. This statement handle is ready to be fetched from.
+It should not (indeed, must not) be executed.
-In this example, we demonstrate a technique for streaming
-data from the database to a file handle, in this case
-STDOUT. This allows more data to be read in and written out
-than could be stored in memory at a given time.
+Oracle imposes a restriction on the order of fetching when nested
+cursors are used. Suppose C<$sth1> is a handle for a select statement
+involving nested cursors, and C<$sth2> is a nested cursor handle fetched
+from C<$sth1>. C<$sth2> can only be fetched from while C<$sth1> is
+still active, and the row containing C<$sth2> is still current in C<$sth1>.
+Any attempt to fetch another row from C<$sth1> renders all nested cursor
+handles previously fetched from C<$sth1> defunct.
- my $lob_id = 17; # Arbitrary row identifier, for example
+Fetching from such a defunct handle results in an error with the message
+C<ERROR nested cursor is defunct (parent row is no longer current)>.
- $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();
+This means that the C<fetchall...> or C<selectall...> methods are not useful
+for queries returning nested cursors. By the time such a method returns,
+all the nested cursor handles it has fetched will be defunct.
- 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;
- }
+It is necessary to use an explicit fetch loop, and to do all the
+fetching of nested cursors within the loop, as the following example
+shows:
-Notice that the select statement does not contain the phrase
-"FOR UPDATE". Because we are only reading from the LOB
-Locator returned, and not modifying the LOB it refers to,
-the select statement does not require the "FOR UPDATE"
-clause.
+ use DBI;
+ my $dbh = DBI->connect(...);
+ my $sth = $dbh->prepare(q{
+ SELECT dname, CURSOR(
+ SELECT ename FROM emp
+ WHERE emp.deptno = dept.deptno
+ ORDER BY ename
+ ) FROM dept ORDER BY dname
+ });
+ $sth->execute;
+ while ( my ($dname, $nested) = $sth->fetchrow_array ) {
+ print "$dname\n";
+ while ( my ($ename) = $nested->fetchrow_array ) {
+ print " $ename\n";
+ }
+ }
-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;
- }
+The cursor returned by the function C<sp_ListEmp> defined in the
+previous section can be fetched as a nested cursor as follows:
-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
-as $data will contain 0 which PERL will see as false and not print it out.
+ my $sth = $dbh->prepare(q{SELECT sp_ListEmp FROM dual});
+ $sth->execute;
+ my ($nested) = $sth->fetchrow_array;
+ while ( my @row = $nested->fetchrow_array ) { ... }
-=head4 Example: Truncating existing large data
+=head4 Pre-fetching Nested Cursors
-In this example, we truncate the data already present in a
-large object column in the database. Specifically, for each
-row in the table, we truncate the 'bindata' value to half
-its previous length.
+By default, DBD::Oracle pre-fetches rows in order to reduce the number of
+round trips to the server. For queries which do not involve nested cursors,
+the number of pre-fetched rows is controlled by the DBI database handle
+attribute C<RowCacheSize> (q.v.).
-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
-select the LOB locators 'FOR UPDATE'.
+In Oracle, server side open cursors are a controlled resource, limited in
+number, on a per session basis, to the value of the initialization
+parameter C<OPEN_CURSORS>. Nested cursors count towards this limit.
+Each nested cursor in the current row counts 1, as does
+each nested cursor in a pre-fetched row. Defunct nested cursors do not count.
- 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 );
- }
- }
+An Oracle specific database handle attribute, C<ora_max_nested_cursors>,
+further controls pre-fetching for queries involving nested cursors. For
+each statement handle, the total number of nested cursors in pre-fetched
+rows is limited to the value of this parameter. The default value
+is 0, which disables pre-fetching for queries involving nested cursors.
=head1 PL/SQL Examples
@@ -5559,36 +5237,75 @@
=head1 Oracle Related Links
-=head2 DBD::Oracle Tutorial
+=head1 WHICH VERSION OF DBD::ORACLE IS FOR ME?
+
+From version 1.25 onwards DBD::Oracle only support Oracle clients
+9.2 or greater. Support for ProC connections was dropped in 1.29.
+
+If you are still stuck with an older version of Oracle or its client you might want to look at the table below.
- http://www.pythian.com/blogs/wp-content/uploads/introduction-dbd-oracle.html
+ +---------------------+-----------------------------------------------------+
+ | | 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 |
+ +---------------------+----+-------------+---------+------+--------+--------+
-=head2 Oracle Instant Client
+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
+Oracle.
- http://www.oracle.com/technology/tech/oci/instantclient/index.html
+Note that one can still connect to any Oracle version with the older
+DBD::Oracle versions the only problem you will have is that some of
+the newer OCI and Oracle features available in later DBD::Oracle
+releases will not be available to you.
-=head2 Oracle on Linux
+So to make a short story a little longer:
- http://www.ixora.com.au/
+=over
-=head2 Free Oracle Tools and Links
+=item 1
- ora_explain supplied and installed with DBD::Oracle.
+If you are using Oracle 7 or early 8 DB and you can manage to get a 9 client and you can use
+any DBD::Oracle version.
- http://www.orafaq.com/
+=item 2
- http://vonnieda.org/oracletool/
+If you have to use an Oracle 7 client then DBD::Oracle 1.17 should work
-=head2 Commercial Oracle Tools and Links
+=item 3
-Assorted tools and references for general information.
-No recommendation implied.
+Same thing for 8 up to R2, use 1.17, if you are lucky and have the right patch-set you might
+go with 1.18.
- http://www.platinum.com
- http://www.SoftTreeTech.com
+=item 4
-Also PL/Vision from RevealNet and Steven Feuerstein, and
-"Q" from Savant Corporation.
+For 8iR3 you can use any of the DBD::Oracle versions up to 1.21. Again this depends on your
+patch-set, If you run into trouble go with 1.19
+
+=item 5
+
+After 9.2 you can use any version you want.
+
+=item 6
+
+It seems that the 10g client can only connect to 9 and 11 DBs while the 9 can go back to 7
+and even get to 10. I am not sure what the 11g client can connect to.
+
+=back
=head1 BUGS AND LIMITATIONS
@@ -5599,31 +5316,61 @@
=head1 SEE ALSO
-DBI
+=over
+
+=item L<DBI>
http://search.cpan.org/~timb/DBD-Oracle/MANIFEST for all files in
the DBD::Oracle source distribution including the examples in the
Oracle.ex directory
- http://search.cpan.org/search?query=Oracle&mode=dist
+=item DBD::Oracle Tutorial
-=head1 AUTHOR
+http://www.pythian.com/blogs/wp-content/uploads/introduction-dbd-oracle.html
-DBD::Oracle by Tim Bunce. DBI by Tim Bunce.
+=item Oracle Instant Client
-=head1 ACKNOWLEDGEMENTS
+http://www.oracle.com/technology/tech/oci/instantclient/index.html
-A great many people have helped me with DBD::Oracle over the 17 years
-between 1994 and 2011. Far too many to name, but I thank them all.
-Many are named in the Changes file.
+=item Oracle on Linux
+
+http://www.ixora.com.au/
+
+=item Free Oracle Tools and Links
+
+ora_explain supplied and installed with DBD::Oracle.
+
+http://www.orafaq.com/
-See also L<DBI/ACKNOWLEDGEMENTS>.
+http://vonnieda.org/oracletool/
-=head1 MAINTAINER
+=item Commercial Oracle Tools and Links
-As of release 1.17 in February 2006 The Pythian Group, Inc. (L<http://www.pythian.com>)
-are taking the lead in maintaining DBD::Oracle with my assistance and
-gratitude. That frees more of my time to work on DBI for Perl 5 and Perl 6.
+Assorted tools and references for general information.
+No recommendation implied.
+
+http://www.platinum.com
+
+http://www.SoftTreeTech.com
+
+Also PL/Vision from RevealNet and Steven Feuerstein, and
+"Q" from Savant Corporation.
+
+=back
+
+=head1 AUTHORS
+
+DBI by Tim Bunce L<http://www.tim.bunce.name>.
+
+The original C<DBD::Oracle> was by Tim Bunce.
+Maintained as of release 1.17 (February 2006) by John Scoles, then Yanick Champoux, under the
+auspice of the Pythian Group (L<http://www.pythian.com>).
+
+=head1 ACKNOWLEDGEMENTS
+
+A great many people have helped with DBD::Oracle over the 17 years
+between 1994 and 2011. Far too many to name, but we thank them all.
+Many are named in the Changes file.
=head1 COPYRIGHT
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/dbdimp.h
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/dbdimp.h (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/dbdimp.h Fri Nov 18 11:41:57 2011
@@ -352,8 +352,8 @@
char *dbd_yes_no _((int yes_no));
char *oci_col_return_codes _((int rc));
char *oci_csform_name _((ub4 attr));
-char *oci_sql_function_code_name _((int sqlfncode));
-char *oci_ptype_name _((int ptype));
+/*char *oci_sql_function_code_name _((int sqlfncode));
+ char *oci_ptype_name _((int ptype));*/
int dbd_rebind_ph_lob _((SV *sth, imp_sth_t *imp_sth, phs_t *phs));
Added: dbd-oracle/branches/DBD-Oracle-1.35_00/examples/inserting_longs.pl
==============================================================================
--- (empty file)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/examples/inserting_longs.pl Fri Nov 18 11:41:57 2011
@@ -0,0 +1,43 @@
+#!perl
+
+use strict;
+use warnings;
+
+use DBI;
+
+my $db = DBI->connect( 'dbi:Oracle:mydb', 'username', 'password' );
+
+my $table = 'TABLE';
+my %clauses;
+my %attrib;
+my @types;
+my $longrawtype;
+my @row;
+
+# Assuming the existence of @row and an associative array (%clauses) containing the
+# column names and placeholders, and an array @types containing column types ...
+
+my $ih = $db->prepare("INSERT INTO $table ($clauses{names})
+ VALUES ($clauses{places})")
+ or die "prepare insert into $table: " . $db->errstr;
+
+$attrib{'ora_type'} = $longrawtype; # $longrawtype == 24
+
+##-- bind the parameter for each of the columns
+for my $i ( 0..$#types ) {
+
+ ##-- long raw values must have their type attribute explicitly specified
+ if ($types[$i] == $longrawtype) {
+ $ih->bind_param($i+1, $row[$i], \%attrib)
+ || die "binding placeholder for LONG RAW " . $db->errstr;
+ }
+ ##-- other values work OK with the default attributes
+ else {
+ $ih->bind_param($i+1, $row[$i])
+ || die "binding placeholder" . $db->errstr;
+ }
+}
+
+$ih->execute || die "execute INSERT into $table: " . $db->errstr;
+
+
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/examples/ora_explain.pl
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/examples/ora_explain.pl (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/examples/ora_explain.pl Fri Nov 18 11:41:57 2011
@@ -1687,7 +1687,7 @@
column represents an index, and the figures define the order that the table
columns appears in the index. To find out the name of an index, position the
mouse over the index column. A single click will display the definition of the
-index in a seperate dialog.
+index in a separate dialog.
Right-clicking on a plan step that refers to a table will pop up a menu showing
a list of the indexes available for the table. Selecting an index will display
Added: dbd-oracle/branches/DBD-Oracle-1.35_00/examples/read_long_via_blob_read.pl
==============================================================================
--- (empty file)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/examples/read_long_via_blob_read.pl Fri Nov 18 11:41:57 2011
@@ -0,0 +1,31 @@
+#!perl
+
+use strict;
+use warnings;
+
+use DBI;
+
+my $dbh = DBI->connect( 'dbi:Oracle:mydb', 'username', 'password' );
+
+$dbh->{RaiseError} = 1;
+$dbh->{LongTruncOk} = 1; # truncation on initial fetch is ok
+
+my $sth = $dbh->prepare("SELECT key, long_field FROM table_name");
+$sth->execute;
+
+while ( my ($key) = $sth->fetchrow_array) {
+ my $offset = 0;
+ my $lump = 4096; # use benchmarks to get best value for you
+ my @frags;
+ while (1) {
+ my $frag = $sth->blob_read(1, $offset, $lump);
+ last unless defined $frag;
+ my $len = length $frag;
+ last unless $len;
+ push @frags, $frag;
+ $offset += $len;
+ }
+ my $blob = join "", @frags;
+ print "$key: $blob\n";
+}
+
Added: dbd-oracle/branches/DBD-Oracle-1.35_00/lib/DBD/Oracle/Troubleshooting.pm
==============================================================================
--- (empty file)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/lib/DBD/Oracle/Troubleshooting.pm Fri Nov 18 11:41:57 2011
@@ -0,0 +1,339 @@
+=pod
+
+=head1 NAME
+
+DBD::Oracle::Troubleshooting - Tips and Hints to Troubleshoot DBD::Oracle
+
+=head1 CONNECTING TO ORACLE
+
+If you are reading this it is assumed that you have successfully
+installed DBD::Oracle and you are having some problems connecting to
+Oracle.
+
+First off you will have to tell DBD::Oracle where the binaries reside
+for the Oracle client it was compiled against. This is the case when
+you encounter a
+
+ DBI connect('','system',...) failed: ERROR OCIEnvNlsCreate.
+
+error in Linux or in Windows when you get
+
+ OCI.DLL not found
+
+The solution to this problem in the case of Linux is to ensure your
+'ORACLE_HOME' (or LD_LIBRARY_PATH for InstantClient) environment
+variable points to the correct directory.
+
+ export ORACLE_HOME=/app/oracle/product/xx.x.x
+
+For Windows the solution is to add this value to you PATH
+
+ PATH=c:\app\oracle\product\xx.x.x;%PATH%
+
+
+If you get past this stage and get a
+
+ ORA-12154: TNS:could not resolve the connect identifier specified
+
+error then the most likely cause is DBD::ORACLE cannot find your .ORA
+(F<TNSNAMES.ORA>, F<LISTENER.ORA>, F<SQLNET.ORA>) files. This can be
+solved by setting the TNS_ADMIN environment variable to the directory
+where these files can be found.
+
+If you get to this stage and you have either one of the following
+errors;
+
+ ORA-12560: TNS:protocol adapter error
+ ORA-12162: TNS:net service name is incorrectly specified
+
+usually means that DBD::Oracle can find the listener but the it cannot connect to the DB because the listener cannot find the DB you asked for.
+
+=head2 Oracle utilities
+
+If you are still having problems connecting then the Oracle adapters
+utility may offer some help. Run these two commands:
+
+ $ORACLE_HOME/bin/adapters
+ $ORACLE_HOME/bin/adapters $ORACLE_HOME/bin/sqlplus
+
+and check the output. The "Protocol Adapters" should include at least "IPC Protocol Adapter" and "TCP/IP
+Protocol Adapter".
+
+If it generates any errors which look relevant then please talk to your
+Oracle technical support (and not the dbi-users mailing list).
+
+
+=head1 USING THE LONG TYPES
+
+Some examples related to the use of LONG types are available in
+the C<examples/> directory of the distribution.
+
+=head1 LINUX
+
+=head2 Installing with Instantclient .rpm files.
+
+Nothing special with this you just have to set up you permissions as follows;
+
+1) Have permission for RWE on '/usr/lib/oracle/10.2.0.3/client/' or the other directory where you RPMed to
+
+2) Set export ORACLE_HOME=/usr/lib/oracle/10.2.0.3/client
+
+3) Set export LD_LIBRARY_PATH=$ORACLE_HOME/lib
+
+4) If you plan to use tnsnames to connect to remote servers and your tnsnames.ora file is not in $ORACLE_HOME/network/admin, you will need to Export TNS_ADMIN=dir to point DBD::Oracle to where your tnsnames.ora file is
+
+=head2 undefined symbol: __cmpdi2 comes up when Oracle isn't properly linked to the libgcc.a library.
+
+In version 8, this was correctd by changing the SYSLIBS entry in
+$ORACLE_HOME/bin/genclntsh to include
+"-L/usr/lib/gcc-lib/i386-redhat-linux/3.2 -lgcc".
+
+I had tried this with no success as when this program was then run, the
+error "unable to find libgcc" was generated. Of course, this was the
+library I was trying to describe!
+
+It turns out that now it is necessary to edit the same file and append
+"`gcc -print-libgcc-file-name`" (including the backquotes!). If you do
+this and then run "genclntsh", the libclntsh is properly generated and
+the linkage with DBD::Oracle proceeds properly.
+
+
+=head2 cc1: invalid option `tune=pentium4'" error
+
+If you get the above it seems that eiter your Perl or OS where compiled with a different version of GCC or the GCC that is on your system is very old.
+
+No real problem with the above however you will have to
+
+1) run Perl Makefile.PL
+
+2) edit the Makefile and remove the offending '-mtune=pentium4' text
+
+3) save and exit
+
+4) do the make install and it should work fine for you
+
+=head2 Oracle 9i Lite
+
+The advice is to use the regular Oracle9i not the lite version.
+
+Another great source of help was: http://www.puschitz.com/InstallingOracle9i.html
+
+just getting 9i and 9i lite installed. I use fvwm2(nvidia X driver) as
+a window manager which does not work with the 9i install program, works
+fine with the default Gnomish(nv X driver), it could have been the X
+driver too.
+
+With Redhat9 it is REAL important to set LD_ASSUME_KERNEL to 2.4.1.
+
+I didn't try this but it may be possible to install what is needed by
+only downloading the first disk saving some 1.3GB of download fun.
+
+I installed a custom install from the client group. The packages I
+installed are the Programmers section and sqlplus. I noticed that the
+Pro*C when on as a result of the checking the Programmers section I
+assume.
+
+Once Oracle was installed properly the DBD::Oracle install went as
+smooth as just about every other CPAN module.
+
+=head2 Oracle 10g Instantclient
+
+The Makefile.PL will now work for Oracle 10g Instantclient. To have both the Compile and
+the test.pl to work you must first have the LD_LIBRARY_PATH correctly set to your
+"instantclient" directory. (http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html)
+
+The present version of the make creates a link on your "instantclient" directory as follows
+"ln -s libclntsh.so.10.1 libclntsh.so". It is needed for both the makefile creation and the compile
+but is not need for the test.pl. It should be removed after the compile.
+
+If the Makefile.PL or make fails try creating this link directly in your "instantclient" directory.
+
+=head2 Oracle Database 10g Express Edition 10.2
+
+To get 10Xe to compile correctly I had to add $ORACLE_HOME/lib to the LD_LIBRARY_PATH
+as you would for an install against 10g Standard Edition, Standard Edition One, or
+Enterprise Edition
+
+=head2 UTF8 bug in Oracle 9.2.0.5.0 and 9.2.0.7.0
+
+DBD::Oracle seems to hit some sort of bug with the above two versions of DB.
+The bug seems to hit when you when the Oracle database charset: US7ASCII and the Oracle nchar charset: AL16UTF16 and it has also
+been reported when the Oracle database charset: WE8ISO8850P1 Oracle nchar charset: AL32UTF16.
+
+So far there is no patch for this but here are some work arounds
+
+ use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
+ ...
+ $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR });
+
+ or this way
+
+ $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders
+
+ or this way
+
+ utf8::downgrade($parameter, 1);
+
+
+=head1 CYGWIN
+
+Makefile.PL should find and make use of OCI include
+files, but you have to build an import library for
+OCI.DLL and put it somewhere in library search path.
+one of the possible ways to do this is issuing command
+
+ dlltool --input-def oci.def --output-lib liboci.a
+
+in the directory where you unpacked DBD::Oracle distribution
+archive. this will create import library for Oracle 8.0.4.
+
+Note: make clean removes '*.a' files, so put a copy in a safe place.
+
+=head2 Compiling DBD::Oracle using the Oracle Instant Client, Cygwin Perl and gcc
+
+=over
+
+=item 1
+
+Download these two packages from Oracle's Instant Client for
+Windows site
+(http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html):
+
+Instant Client Package - Basic: All files required to run OCI,
+OCCI, and JDBC-OCI applications
+
+Instant Client Package - SDK: Additional header files and an
+example makefile for developing Oracle applications with Instant Client
+
+(I usually just use the latest version of the client)
+
+=item 2
+
+Unpack both into C:\oracle\instantclient_11_1
+
+=item 3
+
+Download and unpack DBD::Oracle from CPAN to some place with no
+spaces in the path (I used /tmp/DBD-Oracle) and cd to it.
+
+=item 4
+
+Set up some environment variables (it didn't work until I got the
+DSN right):
+
+ ORACLE_DSN=DBI:Oracle:host=oraclehost;sid=oracledb1
+ ORACLE_USERID=username/password
+
+=item 5
+
+ perl Makefile.PL
+ make
+ make test
+ make install
+
+=back
+
+Note, the TNS Names stuff doesn't always seem to work with the instant
+client so Perl scripts need to explicitly use host/sid in the DSN, like
+this:
+
+ my $dbh = DBI->connect('dbi:Oracle:host=oraclehost;sid=oracledb1',
+ 'username', 'password');
+
+=head2 SUN
+
+If you get this on a Solaris 9 and 10 box
+
+ "Outofmemory!
+ Callback called exit.
+ END failed--call queue aborted."
+
+The solution may be as simple as not having you "ORACLE_HOME" Defined in the
+environment.
+
+It seems that having it defined will prevent the error.
+
+=head2 VMS
+
+This is related to Oracle RDBMS 9.2 and later, since Oracle
+made fundamental changes to oracle installation requirements
+and factual installation with this release.
+
+Oracle's goal was to make VMS installation be more like on
+*nix and Windows, with an all new Oracle Home structure too,
+requiring an ODS-5 disk to install Oracle Home on instead of
+the good old ODS-2.
+
+Another major change is the introduction of an Oracle generated
+logical name table for oracle logical names like ORA_ROOT and all
+its derivatives like ORA_PROGINT etc. And that this logical name
+table is inserted in LNM$FILE_DEV in LNM$PROCESS_DIRECTORY.
+
+ (LNM$PROCESS_DIRECTORY)
+
+ "LNM$FILE_DEV" = "SERVER_810111112"
+ = "LNM$PROCESS"
+ = "LNM$JOB"
+ = "LNM$GROUP"
+ = "LNM$SYSTEM"
+ = "DECW$LOGICAL_NAMES"
+
+This ensures that any process that needs to have access to
+oracle gets the environment by just adding one logical name table
+to a central process specific mechanism.
+
+But as it is inserted at the very top of LNM$FILE_DEV it also
+represents a source of misfortune - especially if a user with
+enough privilege to update the oracle table does so (presumably
+unintentionally), as an examble by changing NLS_LANG.
+
+PERL has the abillity to define, redefine and undefine (deassign)
+logical names, but if not told otherwise by the user does it
+in the first table in above list, and not as one would normally
+expect in the process table.
+
+Installing DBI and DBD::Oracle has influence upon this since in
+both cases a few enviroment variables are read or set in the
+test phase.
+For DBI it is the logical SYS$SCRATCH, which is a JOB logical.
+For DBD-Oracle it is when testing a new feature in the Oracle
+RDBMS: UTF8 and UTF16 character set functionallity, and in order
+to do this it sets and unsets the related environment variables
+NLS_NCHAR and NLS_LANG.
+
+If one is not careful this changes the values set in the oracle
+table - and in the worst case stays active until the next major
+system reset. It can also be a very hard error to track down
+since it happens in a place where one normally never looks.
+
+Furthermore, it is very possibly that some or all of the UTF tests
+fails, since if one have a variable like NLS_LANG in his process
+table, then even though 'mms test' sets it in the wrong table
+it is not invoked as it is overruled by the process logical...
+
+The way to ensure that no logicals are set in the oracle table and
+that the UTF tests get the best environment to test in, and that
+DBI correctly translates the SYS$SCRATCH logical, use the
+logical
+
+ PERL_ENV_TABLES
+
+to ensure that PERL's behavior is to leave the oracle table alone and
+use the process table instead:
+
+ $ DEFINE PERL_ENV_TABLES LNM$PROCESS, LNM$JOB
+
+This tells PERL to use the LNM$PROCESS table as the default place to
+set and unset variables so that only the perl users environment
+is affected when installing DBD::Oracle, and ensures that the
+LNM$JOB table is read when SYS$SCRATCH is to be translated.
+
+PERL_ENV_TABLES is well documented in the PERLVMS man page.
+
+Oracle8 releases are not affected, as they don't have the
+oracle table implementation, and no UTF support.
+
+Oracle 9.0 is uncertain, since testing has not been possible yet,
+but the remedy will not hurt :)
+
+=cut
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/oci8.c
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/oci8.c (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/oci8.c Fri Nov 18 11:41:57 2011
@@ -23,7 +23,7 @@
int dump_struct(imp_sth_t *imp_sth,fbh_obj_t *obj,int level);
-
+/*
char *
dbd_yes_no(int yes_no)
{
@@ -33,6 +33,7 @@
}
return "No";
}
+*/
void
dbd_init_oci(dbistate_t *dbistate)
@@ -52,6 +53,7 @@
}
+/*
char *
oci_sql_function_code_name(int sqlfncode)
{
@@ -71,7 +73,9 @@
sprintf(SvPVX(sv),"(UNKNOWN SQL FN Code %d)", sqlfncode);
return SvPVX(sv);
}
+*/
+ /*
char *
oci_ptype_name(int ptype)
{
@@ -104,7 +108,8 @@
sprintf(SvPVX(sv),"(UNKNOWN PTYPE Code %d)", ptype);
return SvPVX(sv);
}
-
+ */
+
char *
oci_exe_mode(ub4 mode)
{
@@ -3738,6 +3743,7 @@
AV *
dbd_st_fetch(SV *sth, imp_sth_t *imp_sth){
dTHX;
+ D_imp_xxh(sth);
sword status;
D_imp_dbh_from_sth;
int num_fields = DBIc_NUM_FIELDS(imp_sth);
@@ -3927,12 +3933,13 @@
#if DBIXS_REVISION > 13590
/* If a bind type was specified we use DBI's sql_type_cast
to cast it - currently only number types are handled */
- if (fbh->req_type != 0) {
+ if ((fbh->req_type != 0) && (fbh->bind_flags != 0)) {
int sts;
char errstr[256];
sts = DBIc_DBISTATE(imp_sth)->sql_type_cast_svpv(
aTHX_ sv, fbh->req_type, fbh->bind_flags, NULL);
+
if (sts == 0) {
sprintf(errstr,
"over/under flow converting column %d to type %"IVdf"",
@@ -3945,7 +3952,11 @@
sprintf(errstr,
"unsupported bind type %"IVdf" for column %d",
fbh->req_type, i+1);
- return Nullav;
+ /* issue warning */
+ DBIh_SET_ERR_CHAR(sth, imp_xxh, "0", 1, errstr, Nullch, Nullch);
+ if (CSFORM_IMPLIES_UTF8(fbh->csform) ){
+ SvUTF8_on(sv);
+ }
}
}
else
@@ -4669,4 +4680,4 @@
}
imp_dbh->server_version = vernum;
return vernum;
-}
\ No newline at end of file
+}
Modified: dbd-oracle/branches/DBD-Oracle-1.35_00/t/58object.t
==============================================================================
--- dbd-oracle/branches/DBD-Oracle-1.35_00/t/58object.t (original)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/t/58object.t Fri Nov 18 11:41:57 2011
@@ -174,15 +174,24 @@
ok (scalar @row2, 'new: Fetch second row');
cmp_ok(ref $row2[1], 'eq', 'DBD::Oracle::Object', 'new: Row 2 column 2 is an DBD::Oracle::Object');
cmp_ok(uc $row2[1]->type_name, "eq", uc "$schema.$sub_type", "new: Row 2 column 2 object type");
-is_deeply([$row2[1]->attributes], ['NUM', undef, 'NAME', 'obj2',
- 'DATETIME', '2004-11-30T14:27:18', 'AMOUNT', 12345.6789], "new: Row 1 column 2 object attributes");
+
+my %attrs = $row2[1]->attributes;
+
+$attrs{AMOUNT} = sprintf "%9.4f", $attrs{AMOUNT};
+
+is_deeply( \%attrs, {'NUM', undef, 'NAME', 'obj2',
+ 'DATETIME', '2004-11-30T14:27:18', 'AMOUNT', '12345.6789'}, "new: Row 1 column 2 object attributes");
@row3 = $sth->fetchrow();
ok (scalar @row3, 'new: Fetch third row');
cmp_ok(ref $row3[1], 'eq', 'DBD::Oracle::Object', 'new: Row 3 column 2 is an DBD::Oracle::Object');
cmp_ok(uc $row3[1]->type_name, "eq", uc "$schema.$sub_type", "new: Row 3 column 2 object type");
-is_deeply([$row3[1]->attributes], ['NUM', 5, 'NAME', 'obj3',
- 'DATETIME', undef, 'AMOUNT', 777.666], "new: Row 1 column 2 object attributes");
+
+%attrs = $row3[1]->attributes;
+$attrs{AMOUNT} = sprintf "%6.3f", $attrs{AMOUNT};
+
+is_deeply( \%attrs, {'NUM', 5, 'NAME', 'obj3',
+ 'DATETIME', undef, 'AMOUNT', '777.666'}, "new: Row 1 column 2 object attributes");
ok (!$sth->fetchrow(), 'new: No more rows expected');
@@ -196,7 +205,10 @@
DATETIME => undef,
AMOUNT => 777.666,
};
-is_deeply($obj->attr_hash, $expected_hash, 'DBD::Oracle::Object->attr_hash');
+my $attrs = $obj->attr_hash;
+$attrs->{AMOUNT} = sprintf "%6.3f", $attrs->{AMOUNT};
+
+is_deeply($attrs, $expected_hash, 'DBD::Oracle::Object->attr_hash');
is_deeply($obj->attr, $expected_hash, 'DBD::Oracle::Object->attr');
is($obj->attr("NAME"), 'obj3', 'DBD::Oracle::Object->attr("NAME")');
Added: dbd-oracle/branches/DBD-Oracle-1.35_00/t/rt13865.t
==============================================================================
--- (empty file)
+++ dbd-oracle/branches/DBD-Oracle-1.35_00/t/rt13865.t Fri Nov 18 11:41:57 2011
@@ -0,0 +1,69 @@
+use strict;
+
+use DBI;
+use DBD::Oracle;
+
+use Test::More;
+
+use lib 't';
+require 'nchar_test_lib.pl';
+
+my $dbh = db_handle() or plan skip_all => "can't connect to database";
+
+my %priv = map { $_ => 1 } get_privs( $dbh );
+
+unless ( ( $priv{'CREATE TABLE'} or $priv{'CREATE ANY TABLE'} )
+ and ( $priv{'DROP TABLE'} or $priv{'DROP ANY TABLE'} ) ) {
+ plan skip_all => q{requires permissions 'CREATE TABLE' and 'DROP TABLE'};
+}
+
+plan tests => 5;
+
+$dbh->do( 'DROP TABLE RT13865' );
+
+$dbh->do( <<'END_SQL' ) or die $dbh->errstr;
+CREATE TABLE RT13865(
+ COL_INTEGER INTEGER,
+ COL_NUMBER NUMBER,
+ COL_NUMBER_37 NUMBER(37),
+ COL_DECIMAL NUMBER(9,2),
+ COL_FLOAT FLOAT(126),
+ COL_VC2 VARCHAR2(67),
+ COL_VC2_69CHAR VARCHAR2(69 CHAR)
+)
+END_SQL
+
+my $col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_INTEGER' );
+
+is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 38,
+ "INTEGER is alias for NUMBER(38)";
+
+$col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_NUMBER_37' );
+is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 37,
+ "NUMBER(37)";
+
+$col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_NUMBER' );
+cmp_ok $col_h->fetchrow_hashref->{COLUMN_SIZE}, '>', 0,
+ "NUMBER";
+
+$col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_VC2' );
+is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 67,
+ "VARCHAR(67)";
+
+$col_h = $dbh->column_info( undef, undef, 'RT13865', 'COL_VC2_69CHAR' );
+is $col_h->fetchrow_hashref->{COLUMN_SIZE} => 69,
+ "VARCHAR(69)";
+
+
+$dbh->do( 'DROP TABLE RT13865' );
+
+# utility functions
+
+sub get_privs {
+ my $dbh = shift;
+
+ my $sth = $dbh->prepare( 'SELECT PRIVILEGE from session_privs' );
+ $sth->execute;
+
+ return map { $_->[0] } @{ $sth->fetchall_arrayref };
+}