dbd::oracle.3

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

\&      IS
\&      BEGIN
\&        RAISE_APPLICATION_ERROR(err_code, \*(AqThis is a test.\*(Aq);
\&      END;
\&
\&      PROCEDURE proc_in_inout (
\&        test_num IN NUMBER,
\&        is_odd IN OUT NUMBER
\&      )
\&      IS
\&      BEGIN
\&        is_odd := MOD(test_num, 2);
\&      END;
\&
\&      FUNCTION func_np
\&        RETURN VARCHAR2
\&      IS
\&        ret_val VARCHAR2(20);
\&      BEGIN
\&        SELECT USER INTO ret_val FROM DUAL;
\&        RETURN ret_val;
\&      END;
\&
\&    END plsql_example;
\&  /
\&  /* End PL/SQL for example package creation. */
\&
\&  use DBI;
\&
\&  my($db, $csr, $ret_val);
\&
\&  $db = DBI\->connect(\*(Aqdbi:Oracle:database\*(Aq,\*(Aquser\*(Aq,\*(Aqpassword\*(Aq)
\&        or die "Unable to connect: $DBI::errstr";
\&
\&  # So we don\*(Aqt have to check every DBI call we set RaiseError.
\&  # See the DBI docs now if you\*(Aqre not familiar with RaiseError.
\&  $db\->{RaiseError} = 1;
\&
\&  # Example 1   Eric Bartley <[e-mail]>
\&  #
\&  # Calling a PLSQL procedure that takes no parameters. This shows you the
\&  # basic\*(Aqs of what you need to execute a PLSQL procedure. Just wrap your
\&  # procedure call in a BEGIN END; block just like you\*(Aqd do in SQL*Plus.
\&  #
\&  # p.s. If you\*(Aqve used SQL*Plus\*(Aqs exec command all it does is wrap the
\&  #      command in a BEGIN END; block for you.
\&
\&  $csr = $db\->prepare(q{
\&    BEGIN
\&      PLSQL_EXAMPLE.PROC_NP;
\&    END;
\&  });
\&  $csr\->execute;
\&
\&
\&  # Example 2   Eric Bartley <[e-mail]>
\&  #
\&  # Now we call a procedure that has 1 IN parameter. Here we use bind_param
\&  # to bind out parameter to the prepared statement just like you might
\&  # do for an INSERT, UPDATE, DELETE, or SELECT statement.
\&  #
\&  # I could have used positional placeholders (e.g. :1, :2, etc.) or
\&  # ODBC style placeholders (e.g. ?), but I prefer Oracle\*(Aqs named
\&  # placeholders (but few DBI drivers support them so they\*(Aqre not portable).
\&
\&  my $err_code = \-20001;
\&
\&  $csr = $db\->prepare(q{
\&        BEGIN
\&            PLSQL_EXAMPLE.PROC_IN(:err_code);
\&        END;
\&  });
\&
\&  $csr\->bind_param(":err_code", $err_code);
\&
\&  # PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to \*(Aqfail\*(Aq.
\&  # Because we set RaiseError, the DBI will croak (die) so we catch that with eval.
\&  eval {
\&    $csr\->execute;
\&  };
\&  print \*(AqAfter proc_in: $@=\*(Aq,"\*(Aq$@\*(Aq, errstr=$DBI::errstr, ret_val=$ret_val\en";
\&
\&
\&  # Example 3   Eric Bartley <[e-mail]>
\&  #
\&  # Building on the last example, I\*(Aqve added 1 IN OUT parameter. We still
\&  # use a placeholders in the call to prepare, the difference is that
\&  # we now call bind_param_inout to bind the value to the place holder.
\&  #
\&  # Note that the third parameter to bind_param_inout is the maximum size
\&  # of the variable. You normally make this slightly larger than necessary.
\&  # But note that the perl variable will have that much memory assigned to
\&  # it even if the actual value returned is shorter.
\&
\&  my $test_num = 5;
\&  my $is_odd;
\&
\&  $csr = $db\->prepare(q{
\&        BEGIN
\&            PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
\&        END;
\&  });
\&
\&  # The value of $test_num is _copied_ here
\&  $csr\->bind_param(":test_num", $test_num);
\&
\&  $csr\->bind_param_inout(":is_odd", \e$is_odd, 1);
\&
\&  # The execute will automagically update the value of $is_odd
\&  $csr\->execute;
\&
\&  print "$test_num is ", ($is_odd) ? "odd \- ok" : "even \- error!", "\en";
\&
\&
\&  # Example 4   Eric Bartley <[e-mail]>
\&  #
\&  # What about the return value of a PLSQL function? Well treat it the same
\&  # as you would a call to a function from SQL*Plus. We add a placeholder
\&  # for the return value and bind it with a call to bind_param_inout so
\&  # we can access it\*(Aqs value after execute.
\&
\&  my $whoami = "";
\&
\&  $csr = $db\->prepare(q{
\&        BEGIN
\&            :whoami := PLSQL_EXAMPLE.FUNC_NP;
\&        END;
\&  });
\&
\&  $csr\->bind_param_inout(":whoami", \e$whoami, 20);
\&  $csr\->execute;
\&  print "Your database user name is $whoami\en";
\&
\&  $db\->disconnect;
.Ve
.PP
You can find more examples in the t/plsql.t file in the DBD::Oracle
source directory.
.PP
Oracle 9.2 appears to have a bug where a variable bound
with \fIbind_param_inout()\fR that isn't assigned to by the executed
\&\s-1PL/SQL\s0 block may contain garbage.
See <http://www.mail\-archive.com/dbi\[e-mail]/msg18835.html>
.SH "Private database handle functions"
.IX Header "Private database handle functions"
These functions are called through the method \fIfunc()\fR
which is described in the \s-1DBI\s0 documentation.
.Sh "plsql_errstr"
.IX Subsection "plsql_errstr"
This function returns a string which describes the errors
from the most recent \s-1PL/SQL\s0 function, procedure, package,
or package body compile in a format similar to the output
of the SQL*Plus command 'show errors'.
.PP
The function returns undef if the error string could not
be retrieved due to a database error.
Look in \f(CW$dbh\fR\->errstr for the cause of the failure.
.PP
If there are no compile errors, an empty string is returned.
.PP
Example:
.PP
.Vb 10
\&    # 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( \*(Aqplsql_errstr\*(Aq );
\&        die $dbh\->errstr if ! defined $msg;
\&        die $msg if $msg;
\&    }
.Ve
.Sh "dbms_output_enable / dbms_output_put / dbms_output_get"
.IX Subsection "dbms_output_enable / dbms_output_put / dbms_output_get"
These functions use the \s-1PL/SQL\s0 \s-1DBMS_OUTPUT\s0 package to store and
retrieve text using the \s-1DBMS_OUTPUT\s0 buffer.  Text stored in this buffer
by dbms_output_put or any \s-1PL/SQL\s0 block can be retrieved by
dbms_output_get or any \s-1PL/SQL\s0 block connected to the same database
session.
.PP
Stored text is not available until after dbms_output_put or the \s-1PL/SQL\s0
block that saved it completes its execution.  This means you \fB\s-1CAN\s0 \s-1NOT\s0\fR
use these functions to monitor long running \s-1PL/SQL\s0 procedures.
.PP
Example 1:
.PP
.Vb 3
\&  # Enable DBMS_OUTPUT and set the buffer size
\&  $dbh\->{RaiseError} = 1;
\&  $dbh\->func( 1000000, \*(Aqdbms_output_enable\*(Aq );
\&
\&  # Put text in the buffer . . .
\&  $dbh\->func( @text, \*(Aqdbms_output_put\*(Aq );
\&
\&  # . . . and retreive it later
\&  @text = $dbh\->func( \*(Aqdbms_output_get\*(Aq );
.Ve
.PP
Example 2:
.PP
.Vb 9
\&  $dbh\->{RaiseError} = 1;
\&  $sth = $dbh\->prepare(q{
\&    DECLARE tmp VARCHAR2(50);
\&    BEGIN
\&      SELECT SYSDATE INTO tmp FROM DUAL;
\&      dbms_output.put_line(\*(AqThe date is \*(Aq||tmp);
\&    END;
\&  });
\&  $sth\->execute;
\&
\&  # retreive the string
\&  $date_string = $dbh\->func( \*(Aqdbms_output_get\*(Aq );
.Ve
.IP "dbms_output_enable ( [ buffer_size ] )" 4
.IX Item "dbms_output_enable ( [ buffer_size ] )"
This function calls \s-1DBMS_OUTPUT\s0.ENABLE to enable calls to package
\&\s-1DBMS_OUTPUT\s0 procedures \s-1GET\s0, \s-1GET_LINE\s0, \s-1PUT\s0, and \s-1PUT_LINE\s0.  Calls to
these procedures are ignored unless \s-1DBMS_OUTPUT\s0.ENABLE is called
first.
.Sp
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.
.ie n .IP "dbms_output_put ( [ @lines ] )" 4
.el .IP "dbms_output_put ( [ \f(CW@lines\fR ] )" 4
.IX Item "dbms_output_put ( [ @lines ] )"
This function calls \s-1DBMS_OUTPUT\s0.PUT_LINE to add lines to the buffer.
.Sp
If all lines were saved successfully the function returns 1.  Depending
on the context, an empty list or undef is returned for failure.
.Sp
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.
.IP "dbms_output_get" 4
.IX Item "dbms_output_get"
This function calls \s-1DBMS_OUTPUT\s0.GET_LINE to retrieve lines of text from
the buffer.
.Sp
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.
.Sp
In a scalar context, the first complete line is removed from the buffer
and returned.  If there are no complete lines, undef is returned.
.Sp
Any text in the buffer after a call to \s-1DBMS_OUTPUT\s0.GET_LINE or
\&\s-1DBMS_OUTPUT\s0.GET is discarded by the next call to \s-1DBMS_OUTPUT\s0.PUT_LINE,
\&\s-1DBMS_OUTPUT\s0.PUT, or \s-1DBMS_OUTPUT\s0.NEW_LINE.
.ie n .IP "reauthenticate ( $username\fR, \f(CW$password )" 4
.el .IP "reauthenticate ( \f(CW$username\fR, \f(CW$password\fR )" 4
.IX Item "reauthenticate ( $username, $password )"
Starts a new session against the current database using the credentials
supplied.
.ie n .IP "ora_nls_parameters ( [ $refresh ] )" 4
.el .IP "ora_nls_parameters ( [ \f(CW$refresh\fR ] )" 4
.IX Item "ora_nls_parameters ( [ $refresh ] )"
Returns a hash reference containing the current \s-1NLS\s0 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.
.ie n .IP "ora_can_unicode ( [ $refresh ] )" 4
.el .IP "ora_can_unicode ( [ \f(CW$refresh\fR ] )" 4
.IX Item "ora_can_unicode ( [ $refresh ] )"
Returns a number indicating whether either of the database character sets
is a Unicode encoding. Calls \fIora_nls_parameters()\fR and passes the optional
\&\f(CW$refresh\fR parameter to it.
.Sp
0 = Neither character set is a Unicode encoding.
.Sp
1 = National character set is a Unicode encoding.
.Sp
2 = Database character set is a Unicode encoding.
.Sp
3 = Both character sets are Unicode encodings.
.SH "Prepare postponed till execute"
.IX Header "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 \f(CW$sth\fR\->{\s-1NAME\s0}). This reduces
communication with the server and increases performance (reducing the
number of \s-1PARSE_CALLS\s0 inside the server).
.PP
However, it also means that \s-1SQL\s0 errors are not detected until
\&\f(CW\*(C`execute()\*(C'\fR (or \f(CW$sth\fR\->{\s-1NAME\s0} etc) is called instead of when
\&\f(CW\*(C`prepare()\*(C'\fR is called. Note that if the describe is triggered by the
use of \f(CW$sth\fR\->{\s-1NAME\s0} or a similar attribute and the describe fails then
\&\fIan exception is thrown\fR even if \f(CW\*(C`RaiseError\*(C'\fR is false!
.PP
Set \*(L"ora_check_sql\*(R" to 0 in \fIprepare()\fR to enable this behaviour.
.SH "Handling LOBs"
.IX Header "Handling LOBs"
.Sh "Simple Usage"
.IX Subsection "Simple Usage"
The value of an Oracle \s-1LOB\s0 column is not the content of the \s-1LOB\s0. It's a
\&'\s-1LOB\s0 Locator' which, after being selected or inserted needs extra
processing to read or write the content of the \s-1LOB\s0.
.PP
When fetching LOBs they are, by default, made to look just like LONGs and
are subject to the LongReadLen and LongTruncOk attributes. Note that
with \s-1OCI\s0 7 DBD::Oracle pre-allocates the whole buffer (LongReadLen) at
the time the statement is prepared.  With \s-1OCI\s0 8+ it grows the buffer to
the amount needed for the largest \s-1LOB\s0 to be fetched so far.
.PP
When inserting or updating LOBs some \fImajor\fR magic has to be performed
behind the scenes to make it transparent.  Basically the driver has to
insert a '\s-1LOB\s0 Locator' and then refetch the newly inserted \s-1LOB\s0
Locator before being able to write the data into it.  However, it works
well most of the time, and I've made it as fast as possible, just one
extra server-round-trip per insert or update after the first.  For the
time being, only single-row \s-1LOB\s0 updates are supported.
.PP
To insert or update a large \s-1LOB\s0 using a placeholder, DBD::Oracle has to
know in advance that it is a \s-1LOB\s0 type. So you need to say:
.PP
.Vb 1
\&  $sth\->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });
.Ve
.PP
The \s-1ORA_CLOB\s0 and \s-1ORA_BLOB\s0 constants can be imported using
.PP
.Vb 1
\&  use DBD::Oracle qw(:ora_types);
.Ve
.PP
or