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

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

    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" attributes to "bind_param" in DBI and "bind_param_inout" in
    DBI 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" method has been called, or when one of the
    child statement handles has issued an "execute". Issuing a "commit" or
    "rollback" always resets the attribute to false for database handles.
    For statement handles, any call to "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" method. See the sections on
    "trace" and "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" 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 "prepare"
    in DBI 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" 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" method for the "Data
        Interface for Persistent LOBs" will be used for LOBs rather than the
        default method "Data Interface for LOB Locators".

    ora_clbk_lob
        If true the "Piecewise Fetch with Callback" method for the "Data
        Interface for Persistent LOBs" will be used for LOBs.

    ora_piece_lob
        If true the "Piecewise Fetch with Polling" method for the "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"
        and "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" 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" 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" 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
        "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" 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").

    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" or
    "rollback", if "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()" 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()" 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()" 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()" 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()" 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()" 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".
    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", except that it returns a
    reference to an array, by internal use of the "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", except that it returns a
    reference to an hash, by internal use of the "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".

  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" 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". 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
    "bind_param" in DBI or "bind_param_inout" in DBI 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" for the correct way to use ORA_RSET.

        See "LOBs and LONGs" for how to use ORA_CLOB and ORA_BLOB.

        See "SYS.DBMS_SQL datatypes" for ORA_VARCHAR2_TABLE,
        ORA_NUMBER_TABLE.

        See "Data Interface for Persistent LOBs" for the correct way to use
        SQLT_CHR and SQLT_BIN.

        See "Other Data Types" for more information.

        See also "Placeholders and Bind Values" in DBI.

    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" 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]) 
Results 1 - 1 of 1
Help - FTP Sites List - Software Dir.
Search over 15 billion files
© 1997-2017 FileWatcher.com