oracle.pm

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

The TABLE_CAT field of a fetched row is always NULL (undef).
See L</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 I<as is>, i.e. as the user provides or
Oracle returns it.
See L</table_info()> for more detailed information.

=head2 C<foreign_key_info()>

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

Oracle does not support catalogs, so C<$pk_catalog> and C<$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 L</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 I<as is>, i.e. as the user provides or
Oracle returns it.
See L</table_info()> for more detailed information.

=head2 C<column_info()>

Oracle does not support catalogs so TABLE_CAT is ignored as
selection criterion.
The TABLE_CAT field of a fetched row is always NULL (undef).
See L</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.

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

An identifier is passed I<as is>, i.e. as the user provides or
Oracle returns it.
See L</table_info()> for more detailed information.


=head1 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:
L<http://www.unicode.org/>. It is well worth reading because there are
many misconceptions about Unicode and you may be holding some of them.

=head2 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 I<strongly> urged to use perl 5.8.2 or later and to I<carefully> read
the perl documentaion 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.

=head2 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 L<"International NLS / 8-bit text issues"> 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 AL32UTF32 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 8 client libraries have a number of bugs related to character
set handling, especially when connected to an Oracle 9+ server.
For this reason a number of DBD::Oracle tests are disabled when
using an Oracle 8 client. If you wish to use Unicode, I recommend
upgrading client and server to Oracle 9 or later.

=head2 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 L<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.

=head2 DBD::Oracle and Unicode

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

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

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

B<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 C<ora_csid> attribute is given to bind_param() then that
is passed to Oracle and takes precedence.

If the value is a Perl Unicode string (UTF-8) then DBD::Oracle
ensures that Oracle uses the Unicode character set, regardless of
the NLS_LANG and NLS_NCHAR settings.

If the placeholder is for inserting an NCLOB then the client NLS_NCHAR
character set is used. (That's useful but inconsistent with the other behaviour
so may change. Best to be explicit by using the C<ora_csform>
attribute.)

If the C<ora_csform> attribute is given to bind_param() then that
determines if the value should be assumed to be in the default
(NLS_LANG) or NCHAR (NLS_NCHAR) client character set. 


   use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
   ...
   $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR }); 

or

   $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders

B<Sending Data using SQL>

Oracle assumes the SQL statement is in the default client character
set (as specified by NLS_LANG). So Unicode strings containing
non-ASCII characters should not be used unless the default client
character set is AL32UTF8.

=head2 DBD::Oracle and Other Character Sets and Encodings

The only multi-byte Oracle character set supported by DBD::Oracle is
"AL32UTF8" (and "UTF8"). Single-byte character sets should work well.

=head1 SYS.DBMS_SQL datatypes

DBD::Oracle has built-in support for B<SYS.DBMS_SQL.VARCHAR2_TABLE>
and B<SYS.DBMS_SQL.NUMBER_TABLE> data types. The simple example is here:

    my $statement='
    DECLARE
    	tbl	SYS.DBMS_SQL.VARCHAR2_TABLE;
    BEGIN
    	tbl := :mytable;
    	:cc := tbl.count();
    	tbl(1) := \'def\';
    	tbl(2) := \'ijk\';
    	:mytable := tbl;
    END;
    ';

    my $sth=$dbh->prepare( $statement );

    my @arr=( "abc" );

    $sth->bind_param_inout(":mytable", \@arr, 10, {
            ora_type => ORA_VARCHAR2_TABLE,
            ora_maxarray_numentries => 100
    } ) );
    $sth->bind_param_inout(":cc", \$cc, 100 ) );
    $sth->execute();
    print	"Result: cc=",$cc,"\n",
    	"\tarr=",Data::Dumper::Dumper(\@arr),"\n";

=over

=item OCI_VARCHAR2_TABLE

SYS.DBMS_SQL.VARCHAR2_TABLE object is always bound to array reference.
( in bind_param() and bind_param_inout() ). When you bind array, you need
to specify full buffer size for OUT data. So, there are two parameters:
I<max_len> (specified as 3rd argument of bind_param_inout() ),
and I<ora_maxarray_numentries>. They define maximum array entry length and
maximum rows, that can be passed to Oracle and back to you. In this
example we send array with 1 element with length=3, but allocate space for 100
Oracle array entries with maximum length 10 of each. So, you can get no more
than 100 array entries with length <= 10. 

If you set I<max_len> to zero, maximum array entry length is calculated
as maximum length of entry of array bound. If 0 < I<max_len> < length( $some_element ),
truncation occur.

If you set I<ora_maxarray_numentries> to zero, current (at bind time) bound
array length is used as maximum. If 0 < I<ora_maxarray_numentries> < scalar(@array),
not all array entries are bound.

=item OCI_NUMBER_TABLE

SYS.DBMS_SQL.NUMBER_TABLE object handling is much alike ORA_VARCHAR2_TABLE.
The main difference is internal data representation. Currently 2 types of
bind is allowed : as C-integer, or as C-double type. To select one of them,
you may specify additional bind parameter I<ora_internal_type> as either
B<SQLT_INT> or B<SQLT_FLT> for C-integer and C-double types.
Integer size is architecture-specific and is usually 32 or 64 bit.
Double is standard IEEE 754 type.

I<ora_internal_type> defaults to double (SQLT_FLT).

I<max_len> is ignored for OCI_NUMBER_TABLE.

Currently, you cannot bind full native Oracle NUMBER(38). If you really need,
send request to dbi-dev list.

The usage example is here:

    $statement='
    DECLARE
            tbl     SYS.DBMS_SQL.NUMBER_TABLE;
    BEGIN
            tbl := :mytable;
            :cc := tbl(2);
            tbl(4) := -1;
            tbl(5) := -2;
            :mytable := tbl;
    END;
    ';
    
    $sth=$dbh->prepare( $statement );
    
    if( ! defined($sth) ){
            die "Prapare error: ",$dbh->errstr,"\n";
    }
    
    @arr=( 1,"2E0","3.5" );
    
    # note, that ora_internal_type defaults to SQLT_FLT for ORA_NUMBER_TABLE .
    if( not $sth->bind_param_inout(":mytable", \@arr, 10, {
                    ora_type => ORA_NUMBER_TABLE,
                    ora_maxarray_numentries => (scalar(@arr)+2),
                    ora_internal_type => SQLT_FLT
              } ) ){
            die "bind :mytable error: ",$dbh->errstr,"\n";
    }
    $cc=undef;
    if( not $sth->bind_param_inout(":cc", \$cc, 100 ) ){
            die "bind :cc error: ",$dbh->errstr,"\n";
    }
    
    if( not $sth->execute() ){
            die "Execute failed: ",$dbh->errstr,"\n";
    }
    print   "Result: cc=",$cc,"\n",
            "\tarr=",Data::Dumper::Dumper(\@arr),"\n";

The result is like:

    Result: cc=2
            arr=$VAR1 = [
              '1',
              '2',
              '3.5',
              '-1',
              '-2'
            ];

If you change bind type to B<SQLT_INT>, like:

    ora_internal_type => SQLT_INT

you get:

    Result: cc=2
            arr=$VAR1 = [
              1,
              2,
              3,
              -1,
              -2
            ];

=back

=head1 Other Data Types

DBD::Oracle does not I<explicitly> support most Oracle data types.
It simply asks Oracle to return them as strings and