view release on metacpan or  search on metacpan

README.mkdn  view on Meta::CPAN

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

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

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

# ORACLE-SPECIFIC DATABASE FUNCTIONS

## **ora\_server\_version**

    $versions = $dbh->func('ora_server_version');

Returns an array reference of server version strings e.g.,

    [11,2,0,2,0]

# 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**

README.mkdn  view on Meta::CPAN

select the LOB locators 'FOR UPDATE'.

    my $sth = $dbh->prepare( <<"   SQL", { ora_auto_lob => 0 } );
       SELECT bindata
       FROM lob_example
       FOR UPATE
    SQL
    $sth->execute();
    while( my ( $bin_locator ) = $sth->fetchrow_array() ) {
       my $binlength = $dbh->ora_lob_length( $bin_locator );
       if( $binlength > 0 ) {
          $dbh->ora_lob_trim( $bin_locator, $binlength/2 );
       }
    }

# SPACES AND PADDING

## Trailing Spaces

Only the Oracle OCI 8 strips trailing spaces from VARCHAR placeholder
values and uses Nonpadded Comparison Semantics with the result.
This causes trouble if the spaces are needed for
comparison with a CHAR value or to prevent the value from
becoming '' which Oracle treats as NULL.
Look for Blank-padded Comparison Semantics and Nonpadded
Comparison Semantics in Oracle's SQL Reference or Server
SQL Reference for more details.

To preserve trailing spaces in placeholder values for Oracle clients that use OCI 8,
either change the default placeholder type with ["ora\_ph\_type"](#ora_ph_type) or the placeholder
type for a particular call to ["bind" in DBI](https://metacpan.org/pod/DBI#bind) or ["bind\_param\_inout" in DBI](https://metacpan.org/pod/DBI#bind_param_inout)
with ["ora\_type"](#ora_type) or `TYPE`.
Using [ORA\_CHAR](https://metacpan.org/pod/ORA_CHAR) with [ora\_type](https://metacpan.org/pod/ora_type) or `SQL_CHAR` with `TYPE`
allows the placeholder to be used with Padded Comparison Semantics
if the value it is being compared to is a CHAR, NCHAR, or literal.

Please remember that using spaces as a value or at the end of
a value makes visually distinguishing values with different
numbers of spaces difficult and should be avoided.

Oracle Clients that use OCI 9.2 do not strip trailing spaces.

## Padded Char Fields

Oracle Clients after OCI 9.2 will automatically pad CHAR placeholder values to the size of the CHAR.
As the default placeholder type value in DBD::Oracle is ORA\_VARCHAR2 to access this behaviour you will
have to change the default placeholder type with ["ora\_ph\_type"](#ora_ph_type) or placeholder
type for a particular call with ["bind" in DBI](https://metacpan.org/pod/DBI#bind) or ["bind\_param\_inout" in DBI](https://metacpan.org/pod/DBI#bind_param_inout)
with ["ORA\_CHAR"](#ora_char).

# UNICODE

DBD::Oracle now supports Unicode UTF-8. There are, however, a number
of issues you should be aware of, so please read all this section
carefully.

In this section we'll discuss "Perl and Unicode", then "Oracle and
Unicode", and finally "DBD::Oracle and Unicode".

Information about Unicode in general can be found at:
[http://www.unicode.org/](http://www.unicode.org/). It is well worth reading because there are
many misconceptions about Unicode and you may be holding some of them.

## Perl and Unicode

Perl began implementing Unicode with version 5.6, but the implementation
did not mature until version 5.8 and later. If you plan to use Unicode
you are _strongly_ urged to use Perl 5.8.2 or later and to _carefully_ read
the Perl documentation on Unicode:

    perldoc perluniintro    # in Perl 5.8 or later
    perldoc perlunicode

And then read it again.

Perl's internal Unicode format is UTF-8
which corresponds to the Oracle character set called AL32UTF8.

## Oracle and Unicode

Oracle supports many characters sets, including several different forms
of Unicode.  These include:

    AL16UTF16  =>  valid for NCHAR columns (CSID=2000)
    UTF8       =>  valid for NCHAR columns (CSID=871), deprecated
    AL32UTF8   =>  valid for NCHAR and CHAR columns (CSID=873)

When you create an Oracle database, you must specify the DATABASE
character set (used for DDL, DML and CHAR datatypes) and the NATIONAL
character set (used for NCHAR and NCLOB types).
The character sets used in your database can be found using:

    $hash_ref = $dbh->ora_nls_parameters()
    $database_charset = $hash_ref->{NLS_CHARACTERSET};
    $national_charset = $hash_ref->{NLS_NCHAR_CHARACTERSET};

The Oracle 9.2 and later default for the national character set is AL16UTF16.
The default for the database character set is often US7ASCII.
Although many experienced DBAs will consider an 8bit character set like
WE8ISO8859P1 or WE8MSWIN1252.  To use any character set with Oracle
other than US7ASCII, requires that the NLS\_LANG environment variable be set.
See the ["Oracle UTF8 is not UTF-8"](#oracle-utf8-is-not-utf-8) section below.

You are strongly urged to read the Oracle Internationalization documentation
specifically with respect the choices and trade offs for creating
a databases for use with international character sets.

Oracle uses the NLS\_LANG environment variable to indicate what
character set is being used on the client.  When fetching data Oracle
will convert from whatever the database character set is to the client
character set specified by NLS\_LANG. Similarly, when sending data to
the database Oracle will convert from the character set specified by
NLS\_LANG to the database character set.

The NLS\_NCHAR environment variable can be used to define a different
character set for 'national' (NCHAR) character types.

Both UTF8 and AL32UTF8 can be used in NLS\_LANG and NLS\_NCHAR.
For example:

    NLS_LANG=AMERICAN_AMERICA.UTF8
    NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    NLS_NCHAR=UTF8
    NLS_NCHAR=AL32UTF8

## Oracle UTF8 is not UTF-8

AL32UTF8 should be used in preference to UTF8 if it works for you,
which it should for Oracle 9.2 or later. If you're using an old
version of Oracle that doesn't support AL32UTF8 then you should
avoid using any Unicode characters that require surrogates, in other
words characters beyond the Unicode BMP (Basic Multilingual Plane).

That's because the character set that Oracle calls "UTF8" doesn't
conform to the UTF-8 standard in its handling of surrogate characters.
Technically the encoding that Oracle calls "UTF8" is known as "CESU-8".
Here are a couple of extracts from [http://www.unicode.org/reports/tr26/](http://www.unicode.org/reports/tr26/):

    CESU-8 is useful in 8-bit processing environments where binary
    collation with UTF-16 is required. It is designed and recommended
    for use only within products requiring this UTF-16 binary collation
    equivalence. It is not intended nor recommended for open interchange.

    As a very small percentage of characters in a typical data stream
    are expected to be supplementary characters, there is a strong
    possibility that CESU-8 data may be misinterpreted as UTF-8.
    Therefore, all use of CESU-8 outside closed implementations is
    strongly discouraged, such as the emittance of CESU-8 in output
    files, markup language or other open transmission forms.

Oracle uses this internally because it collates (sorts) in the same order
as UTF16, which is the basis of Oracle's internal collation definitions.

Rather than change UTF8 for clients Oracle chose to define a new character
set called "AL32UTF8" which does conform to the UTF-8 standard.
(The AL32UTF8 character set can't be used on the server because it
would break collation.)

Because of that, for the rest of this document we'll use "AL32UTF8".
If you're using an Oracle version below 9.2 you'll need to use "UTF8"
until you upgrade.

## DBD::Oracle and Unicode

DBD::Oracle Unicode support has been implemented for Oracle versions 9
or greater, and Perl version 5.6 or greater (though we _strongly_
suggest that you use Perl 5.8.2 or later).

You can check which Oracle version your DBD::Oracle was built with by
importing the `ORA_OCI` constant from DBD::Oracle.

**Fetching Data**

Any data returned from Oracle to DBD::Oracle in the AL32UTF8
character set will be marked as UTF-8 to ensure correct handling by Perl.

For Oracle to return data in the AL32UTF8 character set the
NLS\_LANG or NLS\_NCHAR environment variable _must_ be set as described
in the previous section.

When fetching NCHAR, NVARCHAR, or NCLOB data from Oracle, DBD::Oracle
will set the Perl UTF-8 flag on the returned data if either NLS\_NCHAR
is AL32UTF8, or NLS\_NCHAR is not set and NLS\_LANG is AL32UTF8.

When fetching other character data from Oracle, DBD::Oracle
will set the Perl UTF-8 flag on the returned data if NLS\_LANG is AL32UTF8.

**Sending Data using Placeholders**

Data bound to a placeholder is assumed to be in the default client
character set (specified by NLS\_LANG) except for a few special
cases. These are listed here with the highest precedence first:

If the `ora_csid` attribute is given to bind\_param() then that
is passed to Oracle and takes precedence.

If the value is a Perl Unicode string (UTF-8) then DBD::Oracle