Filewatcher File Search File Search
Catalog
Content Search
» » » » DBD-Oracle-1.44.tar.gz » Content »
pkg://DBD-Oracle-1.44.tar.gz:466821/DBD-Oracle-1.44/  info  downloads

README.mkdn

# NAME

DBD::Oracle - Oracle database driver for the DBI module

# VERSION

version 1.44

# SYNOPSIS

  use DBI;

  $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);

  $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $passwd);

  # See the DBI module documentation for full details

  # for some advanced uses you may need Oracle type values:
  use DBD::Oracle qw(:ora_types);

# DESCRIPTION

DBD::Oracle is a Perl module which works with the DBI module to provide
access to Oracle databases.

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 [DBI](http://search.cpan.org/perldoc?DBI) documentation first!

# CONSTANTS

- :ora_session_modes

ORA_SYSDBA ORA_SYSOPER ORA_SYSASM

- :ora_types

  ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
  ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB
  ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI
  SQLT_CHR SQLT_BIN

- SQLCS_IMPLICIT

- SQLCS_NCHAR

SQLCS_IMPLICIT and SQLCS_NCHAR are _character set form_ values.
See notes about Unicode elsewhere in this document.

- SQLT_INT

- SQLT_FLT

These types are used only internally, and may be specified as internal
bind type for ORA_NUMBER_TABLE. See notes about ORA_NUMBER_TABLE elsewhere
in this document

- ORA_OCI

Oracle doesn't provide a formal API for determining the exact version
number of the OCI client library used, so DBD::Oracle has to go digging
(and sometimes has to more or less guess).  The ORA_OCI constant
holds the result of that process.

In string context ORA_OCI returns the full "A.B.C.D" version string.

In numeric context ORA_OCI returns the major.minor version number
(8.1, 9.2, 10.0 etc).  But note that version numbers are not actually
floating point and so if Oracle ever makes a release that has a two
digit minor version, such as `9.10` it will have a lower numeric
value than the preceding `9.9` release. So use with care.

The contents and format of ORA_OCI are subject to change (it may,
for example, become a _version object_ in later releases).
I recommend that you avoid checking for exact values.

- :ora_fetch_orient

  OCI_FETCH_CURRENT OCI_FETCH_NEXT OCI_FETCH_FIRST OCI_FETCH_LAST
  OCI_FETCH_PRIOR OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVE

These constants are used to set the orientation of a fetch on a scrollable cursor.

- :ora_exe_modes

  OCI_STMT_SCROLLABLE_READONLY

- :ora_fail_over

  OCI_FO_END OCI_FO_ABORT OCI_FO_REAUTH OCI_FO_BEGIN OCI_FO_ERROR
  OCI_FO_NONE OCI_FO_SESSION OCI_FO_SELECT OCI_FO_TXNAL

# DBI CLASS METHODS

## __connect__

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.

The connection string is always of the form: "dbi:Oracle:<db identifier>"
There are several ways to identify a database:

- 1

If the database is local, specifying the SID or service name will be enough.

- 2

If the database is defined in a TNSNAMES.ORA file, you can use the service name given in the file

- 3

To connect without TNSNAMES.ORA file, you can use an EZCONNECT url, of the form:
//host[:port][/service_name]

If port name is not specified, 1521 is the default. If service name is not specified, the hostname will be used as a service name.

The following examples show several ways a connection can be created:

  $dbh = DBI->connect('dbi:Oracle:DB','username','password');

  $dbh = DBI->connect('dbi:Oracle:DB','username/password','');

  $dbh = DBI->connect('dbi:Oracle:','username@DB','password');

  $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=DB;port=1521', 'scott/tiger', '');

  $dbh = DBI->connect("dbi:Oracle://myhost:1522/ORCL",'username', 'password');

### OS authentication

To connect to a local database with a user which has been set up to
authenticate via the OS ("ALTER USER username IDENTIFIED EXTERNALLY"):

  $dbh = DBI->connect('dbi:Oracle:','/','');

Note the lack of a connection name (use the ORACLE_SID environment
variable). If an explicit SID is used you will probably get an ORA-01004 error.

That only works for local databases. (Authentication to remote Oracle
databases using your Unix login name without a password is possible
but it is not secure and not recommended so not documented here.

### Oracle Environment Variables

To use DBD::ORACLE to connect to an Oracle database, ORACLE_HOME environment variable should be set correctly.
In general, the value used should match the version of Oracle that was used to build DBD::Oracle.  If using dynamic linking then ORACLE_HOME should match the version of Oracle that will be used to load in the Oracle client libraries (via LD_LIBRARY_PATH, ldconfig, or similar on Unix).

Oracle can use two environment variables to specify default connections: ORACLE_SID and TWO_TASK.

To use them, specify either a local SID or service name, or a service name that is specified in the TNSNAMES.ORA file.

Note that if you have *both* local and remote databases, and you have ORACLE_SID *and* TWO_TASK set, and you don't specify a fully
qualified connect string on the command line, TWO_TASK takes precedence over ORACLE_SID (i.e. you get connected to remote system).

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.

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.

### Timezones

If the query is run through SQL*Net (mostly queries that are executed on remote servers), Oracle will return the time zone based on the setting of the UNIX environment variable "TZ" for the user who started the listener.

If the query is run locally, Oracle will return the time zone based on the "TZ" environment variable setting of the user running
the query.

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.

### Oracle DRCP

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;

  $dbh = DBI->connect('dbi:Oracle:DB:POOLED','username','password')

  $dbh = DBI->connect('dbi:Oracle:','username@DB:POOLED','password')

  $dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1})

  $dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1,
                                                             ora_drcp_class=>'my_app',
                                                             ora_drcp_min  =>10})

  $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521;SERVER=POOLED', 'scott/tiger', '')

  $dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
  (CONNECT_DATA=(SID=ORCL)(SERVER=POOLED)))}, "")

  if the ORA_DRCP environment variable is set then just this

  $dbh = DBI->connect('dbi:Oracle:DB','username','password')

You can find a white paper on setting up DRCP and its advantages at [http://www.oracle.com/technetwork/articles/oracledrcp11g-1-133381.pdf](http://www.oracle.com/technetwork/articles/oracledrcp11g-1-133381.pdf).

Please note that DRCP support in DBD::Oracle is relatively new so the
mechanics or its implementation are subject to change.

### 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
event of a failure of the instance. The reconnect happens
automatically from within the OCI (Oracle Call Interface) library.
DBD::Oracle now supports a callback function that will fire when a TAF
event takes place. The main use of the callback is to give your
program the opportunity to inform the user that a failover is taking
place.

You will have to set up TAF on your instance before you can use this
callback.  You can test your instance to see if you can use TAF
callback with

  $dbh->ora_can_taf();

If you try to set up a callback without it being enabled DBD::Oracle will croak.

It is outside the scope of this documents to go through all of the
possible TAF situations you might want to set up but here is a simple
example:

The TNS entry for the instance has had the following added to the
CONNECT_DATA section

   (FAILOVER_MODE=
               (TYPE=select)
               (METHOD=basic)
               (RETRIES=10)
               (DELAY=10))

You will also have to create your own perl function that will be
called from the client.  You can name it anything you want and it will
always be passed two parameters, the failover event value and the
failover type.  You can also set a sleep value in case of failover
error and the OCI client will sleep for the specified seconds before it
attempts another event.

  use DBD::Oracle(qw(:ora_fail_over));
  #import the ora fail over constants

  #set up TAF on the connection
  my $dbh = DBI->connect('dbi:Oracle:XE','hr','hr',{ora_taf=>1,taf_sleep=>5,ora_taf_function=>'handle_taf'});

  #create the perl TAF event function

  sub handle_taf {
    my ($fo_event,$fo_type) = @_;
    if ($fo_event == OCI_FO_BEGIN){

      print " Instance Unavailable Please stand by!! \n";
      printf(" Your TAF type is %s \n",
                       (($fo_type==OCI_FO_NONE) ? "NONE"
                       :($fo_type==OCI_FO_SESSION) ? "SESSION"
                       :($fo_type==OCI_FO_SELECT) ? "SELECT"
                       : "UNKNOWN!"));
    }
    elsif ($fo_event == OCI_FO_ABORT){
       print " Failover aborted. Failover will not take place.\n";
    }
    elsif ($fo_event == OCI_FO_END){
       printf(" Failover ended ...Resuming your %s\n",(($fo_type==OCI_FO_NONE) ? "NONE"
                                                      :($fo_type==OCI_FO_SESSION) ? "SESSION"
                                                      :($fo_type==OCI_FO_SELECT) ? "SELECT"
                                                      : "UNKNOWN!"));
    }
    elsif ($fo_event == OCI_FO_REAUTH){
       print " Failed over user. Resuming services\n";
    }
    elsif ($fo_event == OCI_FO_ERROR){
       print " Failover error Sleeping...\n";
    }
    else {
       printf(" Bad Failover Event: %d.\n",  $fo_event);

    }
    return 0;
  }

The TAF types are as follows

  OCI_FO_SESSION indicates the user has requested only session failover.
  OCI_FO_SELECT indicates the user has requested select failover.
  OCI_FO_NONE indicates the user has not requested a failover type.
  OCI_FO_TXNAL indicates the user has requested a transaction failover.

The TAF events are as follows

  OCI_FO_BEGIN indicates that failover has detected a lost connection and failover is starting.
  OCI_FO_END   indicates successful completion of failover.
  OCI_FO_ABORT indicates that failover was unsuccessful, and there is no option of retrying.
  OCI_FO_ERROR also indicates that failover was unsuccessful, but it gives the application the opportunity to handle the error and retry failover.
  OCI_FO_REAUTH indicates that you have multiple authentication handles and failover has occurred after the original authentication. It indicates that a user handle has been re-authenticated. To find out which, the application checks the OCI_ATTR_SESSION attribute of the service context handle (which is the first parameter).

### Connect Attributes

#### ora_ncs_buff_mtpl

You can customize the size of the buffer when selecting LOBs with
the built-in AUTO Lob.  The default value is 4 which is probably
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.

This value can also be specified with the `ORA_DBD_NCS_BUFFER`
environment variable in which case it sets the value at the connect
stage.

#### ora_drcp

For Oracle 11.2 or greater.

Set to _1_ to enable DRCP. Can also be set via the `ORA_DRCP` environment variable.

#### ora_drcp_class

If you are using DRCP, you can set a CONNECTION_CLASS for your pools
as well.  As sessions from a DRCP cannot be shared by users, you can
use this setting to identify the same user across different
applications. OCI will ensure that sessions belonging to a 'class' are
not shared outside the class'.

The values for ora_drcp_class cannot contain a '*' and must be less
than 1024 characters.

This value can be also be specified with the `ORA_DRCP_CLASS`
environment variable.

#### ora_drcp_min

This optional value specifies the minimum number of sessions that are
initially opened.  New sessions are only opened after this value has
been reached.

The default value is 4 and  any value above 0 is valid.

Generally, it should be set to the number of concurrent statements the
application is planning or expecting to run.

This value can also be specified with the `ORA_DRCP_MIN` environment
variable.

#### ora_drcp_max

This optional value specifies the maximum number of sessions that can
be open at one time.  Once reached no more sessions can be opened
until one becomes free. The default value is 40 and any value above 1
is valid.  You should not set this value lower than ora_drcp_min as
that will just waste resources.

This value can also be specified with the `ORA_DRCP_MAX` environment
variable.

#### ora_drcp_incr

This optional value specifies the next increment for sessions to be
started if the current number of sessions are less than
ora_drcp_max. The default value is 2 and any value above 0 is
valid as long as the value of ora_drcp_min + ora_drcp_incr is not
greater than ora_drcp_max.

This value can also be specified with the `ORA_DRCP_INCR` environment
variable.

#### ora_taf

If your Oracle instance has been configured to use TAF events you can
enable the TAF callback by setting this option to any _true_ value.

#### ora_taf_function

The name of the Perl subroutine that will be called from OCI when a
TAF event occurs. You must supply a perl function to use the callback
and it will always receive two parameters, the failover event value
and the failover type. Below is an example of a TAF function

  sub taf_event{
     my ($event, $type) = @_;

     print "My TAF event=$event\n";
     print "My TAF type=$type\n";
     return;
  }

#### ora_taf_sleep

The amount of time in seconds the OCI client will sleep between attempting
successive failover events when the event is OCI_FO_ERROR.

#### ora_session_mode

The ora_session_mode attribute can be used to connect with SYSDBA,
SYSOPER and ORA_SYSASM authorization.
The ORA_SYSDBA, ORA_SYSOPER and ORA_SYSASM constants can be imported using

  use DBD::Oracle qw(:ora_session_modes);

This is one case where setting ORACLE_SID may be useful since
connecting as SYSDBA or SYSOPER via SQL*Net is frequently disabled
for security reasons.

Example:

  $dsn = "dbi:Oracle:";       # no dbname here
  $ENV{ORACLE_SID} = "orcl";  # set ORACLE_SID as needed
  delete $ENV{TWO_TASK};      # make sure TWO_TASK isn't set

  $dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA });

It has been reported that this only works if `$dsn` does not contain
a SID so that Oracle then uses the value of ORACLE_SID (not
TWO_TASK) environment variable to connect to a local instance. Also
the username and password should be empty, and the user executing the
script needs to be part of the dba group or osdba group.

#### ora_oratab_orahome

Passing a true value for the ora_oratab_orahome attribute will make
DBD::Oracle change `$ENV{ORACLE_HOME}` to make the Oracle home directory
that specified in the `/etc/oratab` file _if_ the database to connect to
is specified as a SID that exists in the oratab file, and DBD::Oracle was
built to use the Oracle 7 OCI API (not Oracle 8+).

#### ora_module_name

After connecting to the database the value of this attribute is passed
to the SET_MODULE() function in the `DBMS_APPLICATION_INFO` PL/SQL
package. This can be used to identify the application to the DBA for
monitoring and performance tuning purposes. For example:

  my $dbh = DBI->connect($dsn, $user, $passwd, { ora_module_name => $0 });

  $dbh->{ora_module_name} = $y;

The maximum size is 48 bytes.

#### ora_driver_name

For 11g and later you can now set the name of the driver layer using OCI.
Perl, Perl5, ApachePerl so on. Names starting with "ORA" are reserved. You
can enter up to 8 characters.  If none is enter then this will default to
DBDOxxxx where xxxx is the current version number. This value can be
retrieved on the server side using V$SESSION_CONNECT_INFO or
GV$SESSION_CONNECT_INFO

  my $dbh = DBI->connect($dsn, $user, $passwd, { ora_driver_name => 'ModPerl_1' });

  $dbh->{ora_driver_name} = $q;

#### ora_client_info

Allows you to add any value (up to 64 bytes) to your session and it can be
retrieved on the server side from the `V$SESSION`a view.

  my $dbh = DBI->connect($dsn, $user, $passwd, { ora_client_info => 'Remote2' });

  $dbh->{ora_client_info} = "Remote2";

#### ora_client_identifier

Allows you to specify the user identifier in the session handle.

Most useful for web applications as it can pass in the session user
name which might be different to the connection user name. Can be up
to 64 bytes long but do not to include the password for security
reasons and the first character of the identifier should not be
':'. This value can be retrieved on the server side using `V$SESSION`
view.

  my $dbh = DBI->connect($dsn, $user, $passwd, { ora_client_identifier => $some_web_user });

  $dbh->{ora_client_identifier} = $local_user;

#### ora_action

Allows you to specify any string up to 32 bytes which may be retrieved
on the server side using `V$SESSION` view.

   my $dbh = DBI->connect($dsn, $user, $passwd, { ora_action => "Login"});

   $dbh->{ora_action} = "New Long Query 22";

#### ora_dbh_share

Requires at least Perl 5.8.0 compiled with ithreads.

Allows you to share
database connections between threads. The first connect will make the
connection, all following calls to connect with the same ora_dbh_share
attribute will use the same database connection. The value must be a
reference to a already shared scalar which is initialized to an empty
string.

  our $orashr : shared = '' ;

  $dbh = DBI->connect ($dsn, $user, $passwd, {ora_dbh_share => \$orashr}) ;

#### ora_envhp

The first time a connection is made a new OCI 'environment' is
created by DBD::Oracle and stored in the driver handle.
Subsequent connects reuse (share) that same OCI environment
by default.

The ora_envhp attribute can be used to disable the reuse of the OCI
environment from a previous connect. If the value is `0` then
a new OCI environment is allocated and used for this connection.

The OCI environment holds information about the client side context,
such as the local NLS environment. By altering `%ENV` and setting
ora_envhp to 0 you can create connections with different NLS
settings. This is most useful for testing.

#### ora_charset, ora_ncharset

For oracle versions >= 9.2 you can specify the client charset and
ncharset with the ora_charset and ora_ncharset attributes.  You
still need to pass `ora_envhp = 0` for all but the first connect.

These attributes override the settings from environment variables.

  $dbh = DBI->connect ($dsn, $user, $passwd,
                       {ora_charset => 'AL32UTF8'});

#### ora_verbose

Use this value to enable DBD::Oracle only tracing.  Simply either set
the ora_verbose attribute on the connect() method to the trace level
you desire like this

  my $dbh = DBI->connect($dsn, "", "", {ora_verbose=>6});

or set it directly on the DB handle like this;

  $dbh->{ora_verbose} =6;

In both cases the DBD::Oracle trace level is set to 6, which is the highest
level tracing most of the calls to OCI.

#### ora_oci_success_warn

Use this value to print otherwise silent OCI warnings that may happen
when an execute or fetch returns "Success With Info" or when you want
to tune RowCaching and LOB Reads

  $dbh->{ora_oci_success_warn} = 1;

#### ora_objects

Use this value to enable extended embedded oracle objects mode. In extended:

- 1

Embedded objects are returned as <DBD::Oracle::Object> instance (including type-name etc.) instead of simple ARRAY.

- 2

Determine object type for each instance. All object attributes are returned (not only super-type's attributes).

  $dbh->{ora_objects} = 1;

#### ora_ph_type

The default placeholder datatype for the database session.
The `TYPE` or [ora_type](#pod_ora_type) attributes to L<DBI/bind_param> and
L<DBI/bind_param_inout> override the datatype for individual placeholders.
The most frequent reason for using this attribute is to permit trailing spaces
in values passed by placeholders.

Constants for the values allowed for this attribute can be imported using

  use DBD::Oracle qw(:ora_types);

Only the following values are permitted for this attribute.

- ORA_VARCHAR2

Oracle clients using OCI 8 will strip trailing spaces and allow embedded \0 bytes.
Oracle clients using OCI 9.2 do not strip trailing spaces and allow embedded \0 bytes.
This is the normal default placeholder type.

- ORA_STRING

Do not strip trailing spaces and end the string at the first \0.

- ORA_CHAR

Do not strip trailing spaces and allow embedded \0.
Force 'blank-padded comparison semantics'.

For example:

  use DBD::Oracle qw(:ora_types);

  $SQL="select username from all_users where username = ?";
  #username is a char(8)
  $sth=$dbh->prepare($SQL)";
  $sth->bind_param(1,'bloggs',{ ora_type => ORA_CHAR});

Will pad bloggs out to 8 characters and return the username.

#### ora_parse_error_offset

If the previous error was from a failed `prepare` due to a syntax error,
this attribute gives the offset into the `Statement` attribute where the
error was found.

#### ora_array_chunk_size

Due to OCI limitations, DBD::Oracle needs to buffer up rows of
bind values in its `execute_for_fetch` implementation. This attribute
sets the number of rows to buffer at a time (default value is 1000).

The `execute_for_fetch` function will collect (at most) this many
rows in an array, send them off to the DB for execution, then go back
to collect the next chunk of rows and so on. This attribute can be
used to limit or extend the number of rows processed at a time.

Note that this attribute also applies to `execute_array`, since that
method is implemented using `execute_for_fetch`.

#### ora_connect_with_default_signals

Sometimes the Oracle client seems to change some of the signal
handlers of the process during the connect phase.  For instance, some
users have observed Perl's default `$SIG{INT}` handler being ignored
after connecting to an Oracle database.  If this causes problems in
your application, set this attribute to an array reference of signals
you would like to be localized during the connect process.  Once the
connect is complete, the signal handlers should be returned to their
previous state.

For example:

  $dbh = DBI->connect ($dsn, $user, $passwd,
                       {ora_connect_with_default_signals => [ 'INT' ] });

NOTE disabling the signal handlers the OCI library sets up may affect
functionality in the OCI library.

NOTE If you are using connect_cached then the above example will lead
to DBI thinking each connection is different as an anonymous array reference
is being used. To avoid this when using connect_cached you are advised
to use:

  my @ora_default_signals = (...);
  $dbh = DBI->connect($dsn, $user, $passwd,
      {ora_connect_with_default_signals => \@ora_default_signals});

In more recent Perl versions you could possibly make use of new state
variables.

## __connect_cached__

Implemented by DBI, no driver-specific impact.
Please note that connect_cached as not been tested with DRCP.

## __data_sources__

  @data_sources = DBI->data_sources('Oracle');
  @data_sources = $dbh->data_sources();

Returns a list of available databases. You will have to set either the 'ORACLE_HOME' or
'TNS_ADMIN' environment value to retrieve this list.  It will read these values from
TNSNAMES.ORA file entries.

# METHODS COMMON TO ALL HANDLES

For all of the methods below, __$h__ can be either a database handle (__$dbh__)
or a statement handle (__$sth__). Note that _$dbh_ and _$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 $_rv_, which stands for "return value".

## __err__

  $rv = $h->err;

Returns the error code from the last method called.

## __errstr__

  $str = $h->errstr;

Returns the last error that was reported by Oracle. Starting with "ORA-00000" code followed by the error message.

## __state__

  $str = $h->state;

Oracle hasn't supported SQLSTATE since the early versions OCI. It will return empty when the command succeeds and
'S1000' (General Error) for all other errors.

While this method can be called as either `$sth->state` or `$dbh->state`, it
is usually clearer to always use `$dbh->state`.

## __trace__

Implemented by DBI, no driver-specific impact.

## __trace_msg__

Implemented by DBI, no driver-specific impact.

## __parse_trace_flag__ and __parse_trace_flags__

Implemented by DBI, no driver-specific impact.

## __func__

DBD::Oracle uses the `func` method to support a variety of functions.

## __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.

## __plsql_errstr__

This function returns a string which describes the errors
from the most recent PL/SQL function, procedure, package,
or package body compile in a format similar to the output
of the SQL*Plus command 'show errors'.

The function returns undef if the error string could not
be retrieved due to a database error.
Look in $dbh->errstr for the cause of the failure.

If there are no compile errors, an empty string is returned.

Example:

    # Show the errors if CREATE PROCEDURE fails
    $dbh->{RaiseError} = 0;
    if ( $dbh->do( q{
        CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test as
        BEGIN
            PROCEDURE filltab( stuff OUT TAB ); asdf
        END; } ) ) {} # Statement succeeded
    }
    elsif ( 6550 != $dbh->err ) { die $dbh->errstr; } # Utter failure
    else {
        my $msg = $dbh->func( 'plsql_errstr' );
        die $dbh->errstr if ! defined $msg;
        die $msg if $msg;
    }

## __dbms_output_enable / dbms_output_put / dbms_output_get__

These functions use the PL/SQL DBMS_OUTPUT package to store and
retrieve text using the DBMS_OUTPUT buffer.  Text stored in this buffer
by dbms_output_put or any PL/SQL block can be retrieved by
dbms_output_get or any PL/SQL block connected to the same database
session.

Stored text is not available until after dbms_output_put or the PL/SQL
block that saved it completes its execution.  This means you __CAN NOT__
use these functions to monitor long running PL/SQL procedures.

Example 1:

  # Enable DBMS_OUTPUT and set the buffer size
  $dbh->{RaiseError} = 1;
  $dbh->func( 1000000, 'dbms_output_enable' );

  # Put text in the buffer . . .
  $dbh->func( @text, 'dbms_output_put' );

  # . . . and retrieve it later
  @text = $dbh->func( 'dbms_output_get' );

Example 2:

  $dbh->{RaiseError} = 1;
  $sth = $dbh->prepare(q{
    DECLARE tmp VARCHAR2(50);
    BEGIN
      SELECT SYSDATE INTO tmp FROM DUAL;
      dbms_output.put_line('The date is '||tmp);
    END;
  });
  $sth->execute;

  # retrieve the string
  $date_string = $dbh->func( 'dbms_output_get' );

## __dbms_output_enable ( [ buffer_size ] )__

This function calls DBMS_OUTPUT.ENABLE to enable calls to package
DBMS_OUTPUT procedures GET, GET_LINE, PUT, and PUT_LINE.  Calls to
these procedures are ignored unless DBMS_OUTPUT.ENABLE is called
first.

The buffer_size is the maximum amount of text that can be saved in the
buffer and must be between 2000 and 1,000,000.  If buffer_size is not
given, the default is 20,000 bytes.

## __dbms_output_put ( [ @lines ] )__

This function calls DBMS_OUTPUT.PUT_LINE to add lines to the buffer.

If all lines were saved successfully the function returns 1.  Depending
on the context, an empty list or undef is returned for failure.

If any line causes buffer_size to be exceeded, a buffer overflow error
is raised and the function call fails.  Some of the text might be in
the buffer.

## __dbms_output_get__

This function calls DBMS_OUTPUT.GET_LINE to retrieve lines of text from
the buffer.

In an array context, all complete lines are removed from the buffer and
returned as a list.  If there are no complete lines, an empty list is
returned.

In a scalar context, the first complete line is removed from the buffer
and returned.  If there are no complete lines, undef is returned.

Any text in the buffer after a call to DBMS_OUTPUT.GET_LINE or
DBMS_OUTPUT.GET is discarded by the next call to DBMS_OUTPUT.PUT_LINE,
DBMS_OUTPUT.PUT, or DBMS_OUTPUT.NEW_LINE.

## __reauthenticate ( $username, $password )__

Starts a new session against the current database using the credentials
supplied.

## __private_attribute_info__

  $hashref = $dbh->private_attribute_info();
  $hashref = $sth->private_attribute_info();

Returns a hash of all private attributes used by DBD::Oracle, for either
a database or a statement handle. Currently, all the hash values are undef.

# ATTRIBUTES COMMON TO ALL HANDLES

## __InactiveDestroy__ (boolean)

Implemented by DBI, no driver-specific impact.

## __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.

## __PrintError__ (boolean, inherited)

Forces database errors to also generate warnings, which can then be filtered with methods such as
locally redefining _$SIG{__WARN__}_ or using modules such as `CGI::Carp`. This attribute is on
by default.

## __ShowErrorStatement__ (boolean, inherited)

Appends information about the current statement to error messages. If placeholder information
is available, adds that as well. Defaults to true.

## __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.

## __Executed__ (boolean, read-only)

Indicates if a handle has been executed. For database handles, this value is true after the [do](#pod_do) method has been called, or
when one of the child statement handles has issued an [execute](#pod_execute). Issuing a [commit](#pod_commit) or [rollback](#pod_rollback) always resets the
attribute to false for database handles. For statement handles, any call to [execute](#pod_execute) or its variants will flip the value to
true for the lifetime of the statement handle.

## __TraceLevel__ (integer, inherited)

Sets the trace level, similar to the [trace](#pod_trace) method. See the sections on
[trace](#pod_trace) and [parse_trace_flag](#pod_parse_trace_flag) for more details.

## __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.

## __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.

## __ActiveKids__ (integer, read-only)

Same as `Kids`, but only returns those that are active.

## __CachedKids__ (hash ref)

Returns a hashref of handles. If called on a database handle, returns all statement handles created by use of the
`prepare_cached` method. If called on a driver handle, returns all database handles created by the [connect_cached](#pod_connect_cached)
method.

## __ChildHandles__ (array ref)

Implemented by DBI, no driver-specific impact.

## __PrintWarn__ (boolean, inherited)

Implemented by DBI, no driver-specific impact.

## __HandleError__ (boolean, inherited)

Implemented by DBI, no driver-specific impact.

## __HandleSetErr__ (code ref, inherited)

Implemented by DBI, no driver-specific impact.

## __ErrCount__ (unsigned integer)

Implemented by DBI, no driver-specific impact.

## __FetchHashKeyName__ (string, inherited)

Implemented by DBI, no driver-specific impact.

## __ChopBlanks__ (boolean, inherited)

Implemented by DBI, no driver-specific impact.

## __Taint__ (boolean, inherited)

Implemented by DBI, no driver-specific impact.

## __TaintIn__ (boolean, inherited)

Implemented by DBI, no driver-specific impact.

## __TaintOut__ (boolean, inherited)

Implemented by DBI, no driver-specific impact.

## __Profile__ (inherited)

Implemented by DBI, no driver-specific impact.

## __Type__ (scalar)

Returns `dr` for a driver handle, `db` for a database handle, and `st` for a statement handle.
Should be rarely needed.

## __LongReadLen__

The maximum size of long or longraw columns to retrieve. If one of
these columns is longer than LongReadLen then either a data truncation
error will be raised (LongTrunkOk is false) or the column will be
silently truncated (LongTruncOk is true).

DBI currently defaults this to 80.

## __LongTruncOk__

Implemented by DBI, no driver-specific impact.

## __CompatMode__

Type: boolean, inherited

The CompatMode attribute is used by emulation layers (such as Oraperl) to enable compatible behaviour in the underlying driver (e.g., DBD::Oracle) for this handle. Not normally set by application code.

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.

# ORACLE-SPECIFIC DATABASE HANDLE METHODS

## __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.

## __ora_can_taf__

Returns true if the current connection supports TAF events. False if otherise.

## __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.

# DATABASE HANDLE METHODS

## __selectall_arrayref__

  $ary_ref = $dbh->selectall_arrayref($sql);
  $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
  $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);

Returns a reference to an array containing the rows returned by preparing and executing the SQL string.
See the DBI documentation for full details.

## __selectall_hashref__

  $hash_ref = $dbh->selectall_hashref($sql, $key_field);

Returns a reference to a hash containing the rows returned by preparing and executing the SQL string.
See the DBI documentation for full details.

## __selectcol_arrayref__

  $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);

Returns a reference to an array containing the first column
from each rows returned by preparing and executing the SQL string. It is possible to specify exactly
which columns to return. See the DBI documentation for full details.

## __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.

### __Prepare Attributes__

These attributes may be used in the `\%attr` parameter of the
L<DBI/prepare> database handle method.

- ora_placeholders

Set to false to disable processing of placeholders. Used mainly for loading a
PL/SQL package that has been _wrapped_ with Oracle's `wrap` utility.

- ora_auto_lob

If true (the default), fetching retrieves the contents of the CLOB or
BLOB column in most circumstances.  If false, fetching retrieves the
Oracle "LOB Locator" of the CLOB or BLOB value.

See [LOBs and LONGs](#pod_LOBs and LONGs) for more details.

See also the LOB tests in 05dbi.t of Oracle::OCI for examples
of how to use LOB Locators.

- ora_pers_lob

If true the [Simple Fetch for CLOBs and BLOBs](#pod_Simple Fetch for CLOBs and BLOBs) method for the [Data Interface for Persistent LOBs](#pod_Data Interface for Persistent LOBs) will be
used for LOBs rather than the default method [Data Interface for LOB Locators](#pod_Data Interface for LOB Locators).

- ora_clbk_lob

If true the [Piecewise Fetch with Callback](#pod_Piecewise Fetch with Callback) method for the L</Data
Interface for Persistent LOBs> will be used for LOBs.

- ora_piece_lob

If true the [Piecewise Fetch with Polling](#pod_Piecewise Fetch with Polling) method for the L</Data
Interface for Persistent LOBs> will be used for LOBs.

- ora_piece_size

This is the max piece size for the [Piecewise Fetch with Callback](#pod_Piecewise Fetch with Callback)
and [Piecewise Fetch with Polling](#pod_Piecewise Fetch with Polling) methods, in chars for CLOBS, and
bytes for BLOBS.

- ora_check_sql

If 1 (default), force SELECT statements to be described in prepare().
If 0, allow SELECT statements to defer describe until execute().

See [Prepare Postponed Till Execute](#pod_Prepare Postponed Till Execute) for more information.

- ora_exe_mode

This will set the execute mode of the current statement. Presently
only one mode is supported;

  OCI_STMT_SCROLLABLE_READONLY - make result set scrollable

See [Scrollable Cursors](#pod_Scrollable Cursors) for more details.

- ora_prefetch_rows

Sets the number of rows to be prefetched. If it is not set, then the
default value is 1.  See [Row Prefetching](#pod_Row Prefetching) for more details.

- ora_prefetch_memory

Sets the memory level for rows to be prefetched. The application then
fetches as many rows as will fit into that much memory.  See L</Row
Prefetching> for more details.

- ora_row_cache_off

By default DBD::Oracle will use a row cache when fetching to cut down
the number of round trips to the server. If you do not want to use an
array fetch set this value to any value other than 0;

See [Row Prefetching](#pod_Row Prefetching) for more details.

### __Placeholders__

There are three 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 question mark character. This is the method
recommended by the DBI and is the most portable. Each question
mark is internally replaced by a "dollar sign number" in the order in
which they appear in the query (important when using [bind_param](#pod_bind_param)).

The second type of placeholder is "named parameters" in the format
":foo" which is the one Oracle prefers.

   $dbh->{RaiseError} = 1;        # save having to check each method call
   $sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE :name");
   $sth->bind_param(':name', "John%");
   $sth->execute;
   DBI::dump_results($sth);

Note when calling bind_param with named parameters you must include
the leading colon. The advantage of this placeholder type is that you
can use the same placeholder more than once in the same SQL statement
but you only need to bind it once.

The last placeholder type is a variation of the two above where you
name each placeholder :N (where N is a number). Like the named
placeholders above you can use the same placeholder multiple times in
the SQL but when you call bind_param you only need to pass the N
(e.g., for :1 you use bind_param(1,...) and not bind_param(':1',...).

The different types of placeholders cannot be mixed within a statement, but you may
use different ones for each statement handle you have. This is confusing at best, so
stick to one style within your program.

## __prepare_cached__

  $sth = $dbh->prepare_cached($statement, \%attr);

Implemented by DBI, no driver-specific impact. This method is most useful
if the same query is used over and over as it will cut down round trips to the server.

## __do__

  $rv = $dbh->do($statement);
  $rv = $dbh->do($statement, \%attr);
  $rv = $dbh->do($statement, \%attr, @bind_values);

Prepare and execute a single statement. Returns the number of rows affected if the
query was successful, returns undef if an error occurred, and returns -1 if the
number of rows is unknown or not available. Note that this method will return __0E0__ instead
of 0 for 'no rows were affected', in order to always return a true value if no error occurred.

## __last_insert_id__

Oracle does not implement auto_increment of serial type columns it uses predefined
sequences where the id numbers are either selected before insert, at insert time with a trigger,
 or as part of the query.

Below is an example of you to use the latter with the SQL returning clause to get the ID number back
on insert with the bind_param_inout method.
.

  $dbh->do('CREATE SEQUENCE lii_seq START 1');
  $dbh->do(q{CREATE TABLE lii (
    foobar INTEGER NOT NULL UNIQUE,
    baz VARCHAR)});
  $SQL = "INSERT INTO lii (foobar,baz) VALUES (lii_seq.nextval,'XX') returning foobar into :p_new_id";";
  $sth = $dbh->prepare($SQL);
  my $p_new_id='-1';
  $sth->bind_param_inout(":p_new_id",\$p_new_id,38);
  $sth->execute();
  $db->commit();

## __commit__

  $rv = $dbh->commit;

Issues a COMMIT to the server, indicating that the current transaction is finished and that
all changes made will be visible to other processes. If AutoCommit is enabled, then
a warning is given and no COMMIT is issued. Returns true on success, false on error.

## __rollback__

  $rv = $dbh->rollback;

Issues a ROLLBACK to the server, which discards any changes made in the current transaction. If AutoCommit
is enabled, then a warning is given and no ROLLBACK is issued. Returns true on success, and
false on error.

## __begin_work__

This method turns on transactions until the next call to [commit](#pod_commit) or [rollback](#pod_rollback), if [AutoCommit](#pod_AutoCommit) is
currently enabled. If it is not enabled, calling begin_work will issue an error. Note that the
transaction will not actually begin until the first statement after begin_work is called.

## __disconnect__

  $rv = $dbh->disconnect;

Disconnects from the Oracle database. Any uncommitted changes will be rolled back upon disconnection. It's
good policy to always explicitly call commit or rollback at some point before disconnecting, rather than
relying on the default rollback behavior.

If the script exits before disconnect is called (or, more precisely, if the database handle is no longer
referenced by anything), then the database handle's DESTROY method will call the rollback() and disconnect()
methods automatically. It is best to explicitly disconnect rather than rely on this behavior.

## __ping__

  $rv = $dbh->ping;

This `ping` method is used to check the validity of a database handle. The value returned is
either 0, indicating that the connection is no longer valid, or 1, indicating the connection is valid.
This function does 1 round trip to the Oracle Server.

## __get_info()__

 $value = $dbh->get_info($info_type);

DBD::Oracle supports `get_info()`, but (currently) only a few info types.

## __table_info()__

DBD::Oracle supports attributes for `table_info()`.

In Oracle, the concept of _user_ and _schema_ is (currently) the
same. Because database objects are owned by an user, the owner names
in the data dictionary views correspond to schema names.
Oracle does not support catalogues so TABLE_CAT is ignored as
selection criterion.

Search patterns are supported for TABLE_SCHEM and TABLE_NAME.

TABLE_TYPE may contain a comma-separated list of table types.
The following table types are supported:

  TABLE
  VIEW
  SYNONYM
  SEQUENCE

The result set is ordered by TABLE_TYPE, TABLE_SCHEM, TABLE_NAME.

The special enumerations of catalogues, schemas and table types are
supported. However, TABLE_CAT is always NULL.

An identifier is passed _as is_, i.e. as the user provides or
Oracle returns it.
`table_info()` performs a case-sensitive search. So, a selection
criterion should respect upper and lower case.
Normally, an identifier is case-insensitive. Oracle stores and
returns it in upper case. Sometimes, database objects are created
with quoted identifiers (for reserved words, mixed case, special
characters, ...). Such an identifier is case-sensitive (if not all
upper case). Oracle stores and returns it as given.
`table_info()` has no special quote handling, neither adds nor
removes quotes.

## __primary_key_info()__

Oracle does not support catalogues so TABLE_CAT is ignored as
selection criterion.
The TABLE_CAT field of a fetched row is always NULL (undef).
See [table_info()](#pod_table_info()) for more detailed information.

If the primary key constraint was created without an identifier,
PK_NAME contains a system generated name with the form SYS_Cn.

The result set is ordered by TABLE_SCHEM, TABLE_NAME, KEY_SEQ.

An identifier is passed _as is_, i.e. as the user provides or
Oracle returns it.
See [table_info()](#pod_table_info()) for more detailed information.

## __foreign_key_info()__

This method (currently) supports the extended behaviour of SQL/CLI, i.e. the
result set contains foreign keys that refer to primary __and__ alternate keys.
The field UNIQUE_OR_PRIMARY distinguishes these keys.

Oracle does not support catalogues, so `$pk_catalog` and `$fk_catalog` are
ignored as selection criteria (in the new style interface).
The UK_TABLE_CAT and FK_TABLE_CAT fields of a fetched row are always
NULL (undef).
See [table_info()](#pod_table_info()) for more detailed information.

If the primary or foreign key constraints were created without an identifier,
UK_NAME or FK_NAME contains a system generated name with the form SYS_Cn.

The UPDATE_RULE field is always 3 ('NO ACTION'), because Oracle (currently)
does not support other actions.

The DELETE_RULE field may contain wrong values. This is a known Bug (#1271663)
in Oracle's data dictionary views. Currently (as of 8.1.7), 'RESTRICT' and
'SET DEFAULT' are not supported, 'CASCADE' is mapped correctly and all other
actions (incl. 'SET NULL') appear as 'NO ACTION'.

The DEFERABILITY field is always NULL, because this columns is
not present in the ALL_CONSTRAINTS view of older Oracle releases.

The result set is ordered by UK_TABLE_SCHEM, UK_TABLE_NAME, FK_TABLE_SCHEM,
FK_TABLE_NAME, ORDINAL_POSITION.

An identifier is passed _as is_, i.e. as the user provides or
Oracle returns it.
See [table_info()](#pod_table_info()) for more detailed information.

## __column_info()__

Oracle does not support catalogues so TABLE_CAT is ignored as
selection criterion.
The TABLE_CAT field of a fetched row is always NULL (undef).
See [table_info()](#pod_table_info()) for more detailed information.

The CHAR_OCTET_LENGTH field is (currently) always NULL (undef).

Don't rely on the values of the BUFFER_LENGTH field!
Especially the length of FLOATs may be wrong.

Datatype codes for non-standard types are subject to change.

Attention! The DATA_DEFAULT (COLUMN_DEF) column is of type LONG so you
may have to set LongReadLen on the connection handle before calling
column_info if you have a large default column. After DBD::Oracle 1.40
LongReadLen is set automatically to 1Mb when calling column_info and
reset aftwerwards.

The result set is ordered by TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION.

An identifier is passed _as is_, i.e. as the user provides or
Oracle returns it.
See [table_info()](#pod_table_info()) for more detailed information.

It is possible with Oracle to make the names of the various DB objects (table,column,index etc)
case sensitive.

  alter table bloggind add ("Bla_BLA" NUMBER)

So in the example the exact case "Bla_BLA" must be used to get it info on the column. While this

 alter table bloggind add (Bla_BLA NUMBER)

any case can be used to get info on the column.

## __selectrow_array__

  @row_ary = $dbh->selectrow_array($sql);
  @row_ary = $dbh->selectrow_array($sql, \%attr);
  @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);

Returns an array of row information after preparing and executing the provided SQL string. The rows are returned
by calling [fetchrow_array](#pod_fetchrow_array). The string can also be a statement handle generated by a previous prepare. Note that
only the first row of data is returned. If called in a scalar context, only the first column of the first row is
returned. Because this is not portable, it is not recommended that you use this method in that way.

## __selectrow_arrayref__

  $ary_ref = $dbh->selectrow_arrayref($statement);
  $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
  $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);

Exactly the same as [selectrow_array](#pod_selectrow_array), except that it returns a reference to an array, by internal use of
the [fetchrow_arrayref](#pod_fetchrow_arrayref) method.

## __selectrow_hashref__

  $hash_ref = $dbh->selectrow_hashref($sql);
  $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
  $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);

Exactly the same as [selectrow_array](#pod_selectrow_array), except that it returns a reference to an hash, by internal use of
the [fetchrow_hashref](#pod_fetchrow_hashref) method.

## __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.

# DATABASE HANDLE ATTRIBUTES

## __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 [connect](#pod_connect).

## __ReadOnly__ (boolean)

  $dbh->{ReadOnly} = 1;

Specifies if the current database connection should be in read-only mode or not.

Please not that this method is not foolproof: there are still ways to update the
database. Consider this a safety net to catch applications that should not be
issuing commands such as INSERT, UPDATE, or DELETE.

This method method requires DBI version 1.55 or better.

## __Name__ (string, read-only)

Returns the name of the current database. This is the same as the DSN, without the
"dbi:Oracle:" part.

## __Username__ (string, read-only)

Returns the name of the user connected to the database.

## __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}

## __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.

### __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
from the server and stored locally. Further calls the server are made only when the end of the
local buffer(cache) is reached.

Rows up to the specified top level row
count `RowCacheSize` are fetched if it occupies no more than the specified memory usage limit.
The default value is 0, which means that memory size is not included in computing the number of rows to prefetch. If
the `RowCacheSize` value is set to a negative number then the positive value of RowCacheSize is used
to compute the number of rows to prefetch.

By default `RowCacheSize` is automatically set. If you want to totally turn off prefetching set this to 1.

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.

### Row Prefetching

Row prefetching occurs on the server side and uses the DBI database handle attribute `RowCacheSize` and or the
Prepare Attribute 'ora_prefetch_memory'. Tweaking these values may yield improved performance.

  $dbh->{RowCacheSize} = 100;
  $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY,ora_prefetch_memory=>10000});

In the above example 10 rows will be prefetched up to a maximum of 10000 bytes of data.  The Oracle® Call Interface Programmer's Guide,
suggests a good row cache value for a scrollable cursor is about 20% of expected size of the record set.

The prefetch settings tell the DBD::Oracle to grab x rows (or x-bytes) when it needs to get new rows. This happens on the first
fetch that sets the current_positon to any value other than 0. In the above example if we do a OCI_FETCH_FIRST the first 10 rows are
loaded into the buffer and DBD::Oracle will not have to go back to the server for more rows. When record 11 is fetched DBD::Oracle
fetches and returns this row and the next 9 rows are loaded into the buffer. In this case if you fetch backwards from 10 to 1
no server round trips are made.

With large record sets it is best not to attempt to go to the last record as this may take some time, A large buffer size might even slow down
the fetch. If you must get the number of rows in a large record set you might try using an few large OCI_FETCH_ABSOLUTEs and then an OCI_FETCH_LAST,
this might save some time. So if you had a record set of 10000 rows and you set the buffer to 5000 and did a OCI_FETCH_LAST one would fetch the first 5000 rows into the buffer then the next 5000 rows.
If one requires only the first few rows there is no need to set a large prefetch value.

If the ora_prefetch_memory less than 1 or not present then memory size is not included in computing the
number of rows to prefetch otherwise the number of rows will be limited to memory size. Likewise if the RowCacheSize is less than 1 it
is not included in the computing of the prefetch rows.

# ORACLE-SPECIFIC STATEMENT HANDLE METHODS

## __ora_stmt_type__

Returns the OCI Statement Type number for the SQL of a statement handle.

## __ora_stmt_type_name__

Returns the OCI Statement Type name for the SQL of a statement handle.

# DBI STATEMENT HANDLE OBJECT METHODS

## __bind_param__

  $rv = $sth->bind_param($param_num, $bind_value);
  $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
  $rv = $sth->bind_param($param_num, $bind_value, \%attr);

Allows the user to bind a value and/or a data type to a placeholder.

The value of `$param_num` is a number if using the '?' or if using ":foo" style placeholders, the complete name
(e.g. ":foo") must be given.
The `$bind_value` argument is fairly self-explanatory. A value of `undef` will
bind a `NULL` to the placeholder. Using `undef` is useful when you want
to change just the type and will be overwriting the value later.
(Any value is actually usable, but `undef` is easy and efficient).

The `\%attr` hash is used to indicate the data type of the placeholder.
The default value is "varchar". If you need something else, you must
use one of the values provided by DBI or by DBD::Pg. To use a SQL value,
modify your "use DBI" statement at the top of your script as follows:

  use DBI qw(:sql_types);

This will import some constants into your script. You can plug those
directly into the [bind_param](#pod_bind_param) call. Some common ones that you will
encounter are:

  SQL_INTEGER

To use Oracle SQL data types, import the list of values like this:

  use DBD::Pg qw(:ora_types);

You can then set the data types by setting the value of the `ora_type`
key in the hash passed to [bind_param](#pod_bind_param).
The current list of Oracle data types exported is:

  ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
  ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB
  ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI
  SQLT_CHR SQLT_BIN

Data types are "sticky," in that once a data type is set to a certain placeholder,
it will remain for that placeholder, unless it is explicitly set to something
else afterwards. If the statement has already been prepared, and you switch the
data type to something else, DBD::Oracle will re-prepare the statement for you before
doing the next execute.

Examples:

  use DBI qw(:sql_types);
  use DBD::Pg qw(:ora_types);

  $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
  $sth = $dbh->prepare($SQL);

  ## Both arguments below are bound to placeholders as "varchar"
  $sth->execute(123, "Merk");

  ## Reset the datatype for the first placeholder to an integer
  $sth->bind_param(1, undef, SQL_INTEGER);

  ## The "undef" bound above is not used, since we supply params to execute
  $sth->execute(123, "Merk");

  ## Set the first placeholder's value and data type
  $sth->bind_param(1, 234, { pg_type => ORA_NUMBER });

  ## Set the second placeholder's value and data type.
  ## We don't send a third argument, so the default "varchar" is used
  $sth->bind_param('$2', "Zool");

  ## We realize that the wrong data type was set above, so we change it:
  $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });

  ## We also got the wrong value, so we change that as well.
  ## Because the data type is sticky, we don't need to change it
  $sth->bind_param(1, 567);

  ## This executes the statement with 567 (integer) and "Zool" (varchar)
  $sth->execute();

These attributes may be used in the `\%attr` parameter of the
L<DBI/bind_param> or L<DBI/bind_param_inout> statement handle methods.

- ora_type

Specify the placeholder's datatype using an Oracle datatype.
A fatal error is raised if `ora_type` and the DBI `TYPE` attribute
are used for the same placeholder.
Some of these types are not supported by the current version of
DBD::Oracle and will cause a fatal error if used.
Constants for the Oracle datatypes may be imported using

  use DBD::Oracle qw(:ora_types);

Potentially useful values when DBD::Oracle was built using OCI 7 and later:

  ORA_VARCHAR2, ORA_STRING, ORA_LONG, ORA_RAW, ORA_LONGRAW,
  ORA_CHAR, ORA_MLSLABEL, ORA_RSET

Additional values when DBD::Oracle was built using OCI 8 and later:

  ORA_CLOB, ORA_BLOB, ORA_XMLTYPE, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE

Additional values when DBD::Oracle was built using OCI 9.2 and later:

  SQLT_CHR, SQLT_BIN

See [Binding Cursors](#pod_Binding Cursors) for the correct way to use ORA_RSET.

See [LOBs and LONGs](#pod_LOBs and LONGs) for how to use ORA_CLOB and ORA_BLOB.

See [SYS.DBMS_SQL datatypes](#pod_SYS.DBMS_SQL datatypes) for ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE.

See [Data Interface for Persistent LOBs](#pod_Data Interface for Persistent LOBs) for the correct way to use SQLT_CHR and SQLT_BIN.

See [Other Data Types](#pod_Other Data Types) for more information.

See also L<DBI/Placeholders and Bind Values>.

- ora_csform

Specify the OCI_ATTR_CHARSET_FORM for the bind value. Valid values
are SQLCS_IMPLICIT (1) and SQLCS_NCHAR (2). Both those constants can
be imported from the DBD::Oracle module. Rarely needed.

- ora_csid

Specify the _integer_ OCI_ATTR_CHARSET_ID for the bind value.
Character set names can't be used currently.

- ora_maxdata_size

Specify the integer OCI_ATTR_MAXDATA_SIZE for the bind value.
May be needed if a character set conversion from client to server
causes the data to use more space and so fail with a truncation error.

- ora_maxarray_numentries

Specify the maximum number of array entries to allocate. Used with
ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE. Define the maximum number of
array entries Oracle can pass back to you in OUT variable of type
TABLE OF ... .

- ora_internal_type

Specify internal data representation. Currently is supported only for
ORA_NUMBER_TABLE.

### Optimizing Results

#### Prepare Postponed Till Execute

The DBD::Oracle module can avoid an explicit 'describe' operation
prior to the execution of the statement unless the application requests
information about the results (such as $sth->{NAME}). This reduces
communication with the server and increases performance (reducing the
number of PARSE_CALLS inside the server).

However, it also means that SQL errors are not detected until
`execute()` (or $sth->{NAME} etc) is called instead of when
`prepare()` is called. Note that if the describe is triggered by the
use of $sth->{NAME} or a similar attribute and the describe fails then
_an exception is thrown_ even if `RaiseError` is false!

Set [ora_check_sql](#pod_ora_check_sql) to 0 in prepare() to enable this behaviour.

## __bind_param_inout__

  $rv = $sth->bind_param_inout($param_num, \$scalar, 0);

DBD::Oracle fully supports bind_param_inout below are some uses for this method.

### __Returning A Value from an INSERT__

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).

  $sth = $dbh->prepare(qq{
      INSERT INTO foo (id, bar)
      VALUES (foo_id_seq.nextval, :bar)
      RETURNING id INTO :id
  });
  $sth->bind_param(":bar", 42);
  $sth->bind_param_inout(":id", \my $new_id, 99);
  $sth->execute;
  print "The id of the new record is $new_id\n";

If you have many columns to bind you can use code like this:

  @params = (... column values for record to be inserted ...);
  $sth->bind_param($_, $params[$_-1]) for (1..@params);
  $sth->bind_param_inout(@params+1, \my $new_id, 99);
  $sth->execute;

If you have many rows to insert you can take advantage of Oracle's built in execute array feature
with code like this:

  my @in_values=('1',2,'3','4',5,'6',7,'8',9,'10');
  my @out_values;
  my @status;
  my $sth = $dbh->prepare(qq{
        INSERT INTO foo (id, bar)
        VALUES (foo_id_seq.nextval, ?)
        RETURNING id INTO ?
  });
  $sth->bind_param_array(1,\@in_values);
  $sth->bind_param_inout_array(2,\@out_values,0,{ora_type => ORA_VARCHAR2});
  $sth->execute_array({ArrayTupleStatus=>\@status}) or die "error inserting";
  foreach my $id (@out_values){
	print 'returned id='.$id.'\n';
  }

Which will return all the ids into @out_values.

- __Note:__

- This will only work for numbered (?) placeholders,

- The third parameter of bind_param_inout_array, (0 in the example), "maxlen" is required by DBI but not used by DBD::Oracle

- The "ora_type" attribute is not needed but only ORA_VARCHAR2 will work.

### Returning A Recordset

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.

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 ;

  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 ;
  /

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.

First, you need to create database object types that correspond to the record
and table types in the package.  From the above example, these would be :

  CREATE OR REPLACE TYPE tArray_Example__taRec
  AS OBJECT (
      Col1    NUMBER,
      Col2    VARCHAR2 (10),
      Col3    DATE
  ) ;

  CREATE OR REPLACE TYPE taArray_Example__taRec
  AS TABLE OF tArray_Example__taRec ;

Now, assuming the existing function needs to remain unchanged (it is probably
bei
Results 1 - 1 of 1
Help - FTP Sites List - Software Dir.
Search over 15 billion files
© 1997-2017 FileWatcher.com