DBD-Oracle
view release on metacpan or search on metacpan
lib/DBD/Oracle.pm view on Meta::CPAN
}
sub private_attribute_info {
return { ora_home_key=>undef};
}
}
{ package # hide from PAUSE
DBD::Oracle::db; # ====== DATABASE ======
use strict;
use DBI qw(:sql_types);
sub prepare {
my($dbh, $statement, @attribs)= @_;
# create a 'blank' sth
my $sth = DBI::_new_sth($dbh, {
'Statement' => $statement,
});
# Call Oracle OCI parse func in Oracle.xs file.
# and populate internal handle data.
DBD::Oracle::st::_prepare($sth, $statement, @attribs)
or return undef;
$sth;
}
#Ah! I see you have the machine that goes PING!!
#Yes!! We leased it from the company that made it
#then the cost came out of the operating budget
#not the capital ...
sub ping {
my($dbh) = @_;
local $@;
my $ok = 0;
eval {
local $SIG{__DIE__};
local $SIG{__WARN__};
$ok=ora_ping($dbh);
};
return ($@) ? 0 : $ok;
}
sub get_info {
my($dbh, $info_type) = @_;
require DBD::Oracle::GetInfo;
my $v = $DBD::Oracle::GetInfo::info{int($info_type)};
$v = $v->($dbh) if ref $v eq 'CODE';
return $v;
}
sub private_attribute_info { #this should only be for ones that have setters and getters
return { ora_max_nested_cursors => undef,
ora_array_chunk_size => undef,
ora_ph_type => undef,
ora_ph_csform => undef,
ora_parse_error_offset => undef,
ora_dbh_share => undef,
ora_envhp => undef,
ora_svchp => undef,
ora_errhp => undef,
ora_init_mode => undef,
ora_events => undef,
ora_charset => undef,
ora_ncharset => undef,
ora_session_mode => undef,
ora_verbose => undef,
ora_oci_success_warn => undef,
ora_objects => undef,
ora_ncs_buff_mtpl => undef,
ora_drcp => undef,
ora_drcp_class => undef,
ora_drcp_min => undef,
ora_drcp_max => undef,
ora_drcp_incr => undef,
ora_drcp_rlb => undef,
ora_oratab_orahome => undef,
ora_module_name => undef,
ora_driver_name => undef,
ora_client_info => undef,
ora_client_identifier => undef,
ora_action => undef,
ora_taf_function => undef,
};
}
sub table_info {
my($dbh, $CatVal, $SchVal, $TblVal, $TypVal) = @_;
# XXX add knowledge of temp tables, etc
# SQL/CLI (ISO/IEC JTC 1/SC 32 N 0595), 6.63 Tables
if (ref $CatVal eq 'HASH') {
($CatVal, $SchVal, $TblVal, $TypVal) =
@$CatVal{'TABLE_CAT','TABLE_SCHEM','TABLE_NAME','TABLE_TYPE'};
}
my @Where = ();
my $SQL;
if ( defined $CatVal && $CatVal eq '%' && (!defined $SchVal || $SchVal eq '') && (!defined $TblVal || $TblVal eq '')) { # Rule 19a
$SQL = <<'SQL';
SELECT NULL TABLE_CAT
, NULL TABLE_SCHEM
, NULL TABLE_NAME
, NULL TABLE_TYPE
, NULL REMARKS
FROM DUAL
SQL
}
elsif ( defined $SchVal && $SchVal eq '%' && (!defined $CatVal || $CatVal eq '') && (!defined $TblVal || $TblVal eq '')) { # Rule 19b
$SQL = <<'SQL';
SELECT NULL TABLE_CAT
, s TABLE_SCHEM
, NULL TABLE_NAME
, NULL TABLE_TYPE
lib/DBD/Oracle.pm view on Meta::CPAN
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 L<DBI> documentation first!
=head1 CONSTANTS
=over 4
=item :ora_session_modes
ORA_SYSDBA ORA_SYSOPER ORA_SYSASM ORA_SYSBACKUP ORA_SYSDG ORA_SYSKM
=item :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
=item SQLCS_IMPLICIT
=item SQLCS_NCHAR
SQLCS_IMPLICIT and SQLCS_NCHAR are I<character set form> values.
See notes about Unicode elsewhere in this document.
=item SQLT_INT
=item 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
=item 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 C<9.10> it will have a lower numeric
value than the preceding C<9.9> release. So use with care.
The contents and format of ORA_OCI are subject to change (it may,
for example, become a I<version object> in later releases).
I recommend that you avoid checking for exact values.
=item :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.
=item :ora_exe_modes
OCI_STMT_SCROLLABLE_READONLY
=item :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 OCI_FO_RETRY
=back
=head1 DBI CLASS METHODS
=head2 B<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:
=over
=item 1
If the database is local, specifying the SID or service name will be enough.
=item 2
If the database is defined in a TNSNAMES.ORA file, you can use the service name given in the file
=item 3
To connect without TNSNAMES.ORA file, you can use an EZCONNECT url, of the form:
//host[:port][/service_name]
=back
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');
=head3 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:','/','');
lib/DBD/Oracle.pm view on Meta::CPAN
$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.
=head2 B<Name> (string, read-only)
Returns the name of the current database. This is the same as the DSN, without the
"dbi:Oracle:" part.
=head2 B<Username> (string, read-only)
Returns the name of the user connected to the database.
=head2 B<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}
=head2 B<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.
=head3 B<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 C<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 C<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 C<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.
=head3 Row Prefetching
Row prefetching occurs on the server side and uses the DBI database handle attribute C<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 position 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.
=head1 ORACLE-SPECIFIC STATEMENT HANDLE METHODS
=head2 B<ora_stmt_type>
Returns the OCI Statement Type number for the SQL of a statement handle.
=head2 B<ora_stmt_type_name>
Returns the OCI Statement Type name for the SQL of a statement handle.
=head1 DBI STATEMENT HANDLE OBJECT METHODS
=head2 B<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 C<$param_num> is a number if using the '?' or if using ":foo" style placeholders, the complete name
(e.g. ":foo") must be given.
The C<$bind_value> argument is fairly self-explanatory. A value of C<undef> will
bind a C<NULL> to the placeholder. Using C<undef> is useful when you want
to change just the type and will be overwriting the value later.
(Any value is actually usable, but C<undef> is easy and efficient).
The C<\%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::Oracle. 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 L</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::Oracle qw(:ora_types);
lib/DBD/Oracle.pm view on Meta::CPAN
The number indicates the precision for C<NUMERIC> columns, the size in number of
characters for C<CHAR> and C<VARCHAR> columns, and for all other types of columns
it returns the number of I<bytes>.
This method returns undef if called before C<execute()>.
=head2 B<SCALE> (arrayref, read-only)
Returns an arrayref of integer values for each column returned by the statement. The number
indicates the scale of the that column. The only type that will return a value is C<NUMERIC>.
This method returns undef if called before C<execute()>.
=head2 B<NULLABLE> (arrayref, read-only)
Returns an arrayref of integer values for each column returned by the statement. The number
indicates if the column is nullable or not. 0 = not nullable, 1 = nullable, 2 = unknown.
This method returns undef if called before C<execute()>.
=head2 B<Database> (dbh, read-only)
Returns the database handle this statement handle was created from.
=head2 B<ParamValues> (hash ref, read-only)
Returns a reference to a hash containing the values currently bound to placeholders. If the "named parameters"
type of placeholders are being used (such as ":foo"), then the keys of the hash will be the names of the
placeholders (without the colon). If the "dollar sign numbers" type of placeholders are being used, the keys of the hash will
be the numbers, without the dollar signs. If the "question mark" type is used, integer numbers will be returned,
starting at one and increasing for every placeholder.
If this method is called before L</execute>, the literal values passed in are returned. If called after
L</execute>, then the quoted versions of the values are returned.
=head2 B<ParamTypes> (hash ref, read-only)
Returns a reference to a hash containing the type names currently bound to placeholders. The keys
are the same as returned by the ParamValues method. The values are hashrefs containing a single key value
pair, in which the key is either 'TYPE' if the type has a generic SQL equivalent, and 'pg_type' if the type can
only be expressed by a Postgres type. The value is the internal number corresponding to the type originally
passed in. (Placeholders that have not yet been bound will return undef as the value). This allows the output of
ParamTypes to be passed back to the L</bind_param> method.
=head2 B<Statement> (string, read-only)
Returns the statement string passed to the most recent "prepare" method called in this database handle, even if that method
failed. This is especially useful where "RaiseError" is enabled and the exception handler checks $@ and sees that a C<prepare>
method call failed.
=head2 B<RowsInCache>
Returns the number of un-fetched rows in the cache for selects.
=head1 SCROLLABLE CURSORS
Oracle supports the concept of a 'Scrollable Cursor' which is defined as a 'Result Set' where
the rows can be fetched either sequentially or non-sequentially. One can fetch rows forward,
backwards, from any given position or the n-th row from the current position in the result set.
Rows are numbered sequentially starting at one and client-side caching of the partial or entire result set
can improve performance by limiting round trips to the server.
Oracle does not support DML type operations with scrollable cursors so you are limited
to simple 'Select' operations only. As well you can not use this functionality with remote
mapped queries or if the LONG datatype is part of the select list.
However, LOBSs, CLOBSs, and BLOBs do work as do all the regular bind, and fetch methods.
Only use scrollable cursors if you really have a good reason to. They do use up considerable
more server and client resources and have poorer response times than non-scrolling cursors.
=head2 Enabling Scrollable Cursors
To enable this functionality you must first import the 'Fetch Orientation' and the 'Execution Mode' constants by using;
use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes);
Next you will have to tell DBD::Oracle that you will be using scrolling by setting the ora_exe_mode attribute on the
statement handle to 'OCI_STMT_SCROLLABLE_READONLY' with the prepare method;
$sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
When the statement is executed you will then be able to use 'ora_fetch_scroll' method to get a row
or you can still use any of the other fetch methods but with a poorer response time than if you used a
non-scrolling cursor. As well scrollable cursors are compatible with any applicable bind methods.
=head2 Scrollable Cursor Methods
The following driver-specific methods are used with scrollable cursors.
=over
=item ora_scroll_position
$position = $sth->ora_scroll_position();
This method returns the current position (row number) attribute of the result set. Prior to the first fetch this value is 0. This is the only time
this value will be 0 after the first fetch the value will be set, so you can use this value to test if any rows have been fetched.
The minimum value will always be 1 after the first fetch. The maximum value will always be the total number of rows in the record set.
=item ora_fetch_scroll
$ary_ref = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
Works the same as C<fetchrow_arrayref>, excepts one passes in a 'Fetch Orientation' constant and a fetch_offset
value which will then determine the row that will be fetched. It returns the row as a list containing the field values.
Null fields are returned as I<undef> values in the list.
The valid orientation constant and fetch offset values combination are detailed below
OCI_FETCH_CURRENT, fetches the current row, the fetch offset value is ignored.
OCI_FETCH_NEXT, fetches the next row from the current position, the fetch offset value
is ignored.
OCI_FETCH_FIRST, fetches the first row, the fetch offset value is ignored.
OCI_FETCH_LAST, fetches the last row, the fetch offset value is ignored.
OCI_FETCH_PRIOR, fetches the previous row from the current position, the fetch offset
value is ignored.
OCI_FETCH_ABSOLUTE, fetches the row that is specified by the fetch offset value.
OCI_FETCH_ABSOLUTE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_FIRST.
OCI_FETCH_ABSOLUTE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
OCI_FETCH_RELATIVE, fetches the row relative from the current position as specified by the
fetch offset value.
OCI_FETCH_RELATIVE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
OCI_FETCH_RELATIVE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_NEXT.
OCI_FETCH_RELATIVE, and a fetch offset value of -1 is equivalent to a OCI_FETCH_PRIOR.
The effect that a ora_fetch_scroll method call has on the current position attribute is detailed below.
OCI_FETCH_CURRENT, has no effect on the current position attribute.
OCI_FETCH_NEXT, increments current position attribute by 1
OCI_FETCH_NEXT, when at the last row in the record set does not change current position
attribute, it is equivalent to a OCI_FETCH_CURRENT
OCI_FETCH_FIRST, sets the current position attribute to 1.
OCI_FETCH_LAST, sets the current position attribute to the total number of rows in the
record set.
OCI_FETCH_PRIOR, decrements current position attribute by 1.
OCI_FETCH_PRIOR, when at the first row in the record set does not change current position
attribute, it is equivalent to a OCI_FETCH_CURRENT.
OCI_FETCH_ABSOLUTE, sets the current position attribute to the fetch offset value.
OCI_FETCH_ABSOLUTE, and a fetch offset value that is less than 1 does not change
current position attribute, it is equivalent to a OCI_FETCH_CURRENT.
OCI_FETCH_ABSOLUTE, and a fetch offset value that is greater than the number of records in
the record set, does not change current position attribute, it is
equivalent to a OCI_FETCH_CURRENT.
lib/DBD/Oracle.pm view on Meta::CPAN
The current position attribute will be 5 after this snippet.
=item Fetching the Prior Row
for(my $i=0;$i<=3;$i++){
$value = $sth->ora_fetch_scroll(OCI_FETCH_PRIOR,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
}
print "current scroll position=".$sth->ora_scroll_position()."\n";
The current position attribute will be 1 after this snippet.
=item Fetching the 10th Row
$value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,10);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";
The current position attribute will be 10 after this snippet.
=item Fetching the 10th to 14th Row
for(my $i=10;$i<15;$i++){
$value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,$i);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
}
print "current scroll position=".$sth->ora_scroll_position()."\n";
The current position attribute will be 14 after this snippet.
=item Fetching the 14th to 10th Row
for(my $i=14;$i>9;$i--){
$value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,$i);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
}
print "current scroll position=".$sth->ora_scroll_position()."\n";
The current position attribute will be 10 after this snippet.
=item Fetching the 5th Row From the Present Position.
$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,5);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";
The current position attribute will be 15 after this snippet.
=item Fetching the 9th Row Prior From the Present Position
$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,-9);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";
The current position attribute will be 6 after this snippet.
=item Use Finish
$sth->finish();
When using scrollable cursors it is required that you use the $sth->finish() method when you are done with the cursor as this type of
cursor has to be explicitly cancelled on the server. If you do not do this you may cause resource problems on your database.
=back
=head1 LOBS AND LONGS
The key to working with LOBs (CLOB, BLOBs) is to remember the value of an Oracle LOB column is not the content of the LOB. It's a
'LOB Locator' which, after being selected or inserted needs extra processing to read or write the content of the LOB. There are also legacy LONG types (LONG, LONG RAW)
which are presently deprecated by Oracle but are still in use. These LONG types do not utilize a 'LOB Locator' and also are more limited in
functionality than CLOB or BLOB fields.
DBD::Oracle now offers three interfaces to LOB and LONG data,
=over
=item L</Data Interface for Persistent LOBs>
With this interface DBD::Oracle handles your data directly utilizing regular OCI calls, Oracle itself takes care of the LOB Locator operations in the case of
BLOBs and CLOBs treating them exactly as if they were the same as the legacy LONG or LONG RAW types.
=item L</Data Interface for LOB Locators>
With this interface DBD::Oracle handles your data utilizing LOB Locator OCI calls so it only works with CLOB and BLOB datatypes. With this interface DBD::Oracle takes care of the LOB Locator operations for you.
=item LOB Locator Method Interface
This allows the user direct access to the LOB Locator methods, so you have to take care of the LOB Locator operations yourself.
=back
Generally speaking the interface that you will chose will be dependent on what end you are trying to achieve. All have their benefits and
drawbacks.
One point to remember when working with LOBs (CLOBs, BLOBs) is if your LOB column can be in one of three states;
=over
=item NULL
The table cell is created, but the cell holds no locator or value.
If your LOB field is in this state then there is no LOB Locator that DBD::Oracle can work so if your encounter a
DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr
error when working with a LOB.
You can correct this by using an SQL UPDATE statement to reset the LOB column to a non-NULL (or empty LOB) value with either EMPTY_BLOB or EMPTY_CLOB as in this example;
UPDATE lob_example
SET bindata=EMPTY_BLOB()
WHERE bindata IS NULL.
=item Empty
A LOB instance with a locator exists in the cell, but it has no value. The length of the LOB is zero. In this case DBD::Oracle will return 'undef' for the field.
=item Populated
A LOB instance with a locator and a value exists in the cell. You actually get the LOB value.
=back
lib/DBD/Oracle.pm view on Meta::CPAN
b0 := to_bool( :i0 );
b1 := to_bool( :i1 );
-- Converting values from BOOLEAN
:o0 := from_bool( b0 );
:o1 := from_bool( b1 );
END;
PLSQL_END
my ( $i0, $i1, $o0, $o1 ) = ( "", "Something else" );
$sth2->bind_param( ":i0", $i0 );
$sth2->bind_param( ":i1", $i1 );
$sth2->bind_param_inout( ":o0", \$o0, 32 );
$sth2->bind_param_inout( ":o1", \$o1, 32 );
$sth2->execute();
foreach ( $i0, $b0, $o0, $i1, $b1, $o1 ) {
$_ = "(undef)" if ! defined $_;
}
print "$i0 to $o0, $i1 to $o1\n";
# Result is : "'' to '(undef)', 'Something else' to '1'"
=head2 Support for Insert of XMLType (ORA_XMLTYPE)
Inserting large XML data sets into tables with XMLType fields is now supported by DBD::Oracle. The only special
requirement is the use of bind_param() with an attribute hash parameter that specifies ora_type as ORA_XMLTYPE. For
example with a table like this;
create table books (book_id number, book_xml XMLType);
one can insert data using this code
$SQL='insert into books values (1,:p_xml)';
$xml= '<Books>
<Book id=1>
<Title>Programming the Perl DBI</Title>
<Subtitle>The Cheetah Book</Subtitle>
<Authors>
<Author>T. Bunce</Author>
<Author>Alligator Descartes</Author>
</Authors>
</Book>
<Book id=10000>...
</Books>';
my $sth =$dbh-> prepare($SQL);
$sth-> bind_param("p_xml", $xml, { ora_type => ORA_XMLTYPE });
$sth-> execute();
In the above case we will assume that $xml has 10000 Book nodes and is over 32k in size and is well formed XML.
This will also work for XML that is smaller than 32k as well. Attempting to insert malformed XML will cause an error.
=head2 Binding Cursors
Cursors can be returned from PL/SQL blocks, either from stored
functions (or procedures with OUT parameters) or
from direct C<OPEN> statements, as shown below:
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect(...);
my $sth1 = $dbh->prepare(q{
BEGIN OPEN :cursor FOR
SELECT table_name, tablespace_name
FROM user_tables WHERE tablespace_name = :space;
END;
});
$sth1->bind_param(":space", "USERS");
my $sth2;
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute;
# $sth2 is now a valid DBI statement handle for the cursor
while ( my @row = $sth2->fetchrow_array ) { ... }
The only special requirement is the use of C<bind_param_inout()> with an
attribute hash parameter that specifies C<ora_type> as C<ORA_RSET>.
If you don't do that you'll get an error from the C<execute()> like:
"ORA-06550: line X, column Y: PLS-00306: wrong number or types of
arguments in call to ...".
Here's an alternative form using a function that returns a cursor.
This example uses the pre-defined weak (or generic) REF CURSOR type
SYS_REFCURSOR. This is an Oracle 9 feature.
# Create the function that returns a cursor
$dbh->do(q{
CREATE OR REPLACE FUNCTION sp_ListEmp RETURN SYS_REFCURSOR
AS l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR select ename, empno from emp
ORDER BY ename;
RETURN l_cursor;
END;
});
# Use the function that returns a cursor
my $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
my $sth2;
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute;
# $sth2 is now a valid DBI statement handle for the cursor
while ( my @row = $sth2->fetchrow_array ) { ... }
A cursor obtained from PL/SQL as above may be passed back to PL/SQL
by binding for input, as shown in this example, which explicitly
closes a cursor:
my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
$sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
$sth3->execute;
It is not normally necessary to close a cursor explicitly in this
way. Oracle will close the cursor automatically at the first
client-server interaction after the cursor statement handle is
destroyed. An explicit close may be desirable if the reference to the
cursor handle from the PL/SQL statement handle delays the destruction
of the cursor handle for too long. This reference remains until the
PL/SQL handle is re-bound, re-executed or destroyed.
NOTE: From DBD::Oracle 1.57 functions or procedures returning
SYS_REFCURSORs which have not been opened (are still in the
initialised state) will return undef for the cursor statement handle
e.g., in the example above if the sp_ListEmp function simply returned l_cursor
instead of opening it. This means you can have a function/procedure
which can elect to open the cursor or not, Before this change if you called
a function/procedure which returned a SYS_REFCURSOR which was not opened
DBD::Oracle would error in the execute for a OCIAttrGet on the uninitialised
cursor.
See the C<curref.pl> script in the Oracle.ex directory in the DBD::Oracle
source distribution for a complete working example.
=head2 Fetching Nested Cursors
Oracle supports the use of select list expressions of type REF CURSOR.
These may be explicit cursor expressions - C<CURSOR(SELECT ...)>, or
calls to PL/SQL functions which return REF CURSOR values. The values
of these expressions are known as nested cursors.
The value returned to a Perl program when a nested cursor is fetched
is a statement handle. This statement handle is ready to be fetched from.
It should not (indeed, must not) be executed.
Oracle imposes a restriction on the order of fetching when nested
cursors are used. Suppose C<$sth1> is a handle for a select statement
involving nested cursors, and C<$sth2> is a nested cursor handle fetched
from C<$sth1>. C<$sth2> can only be fetched from while C<$sth1> is
still active, and the row containing C<$sth2> is still current in C<$sth1>.
Any attempt to fetch another row from C<$sth1> renders all nested cursor
handles previously fetched from C<$sth1> defunct.
Fetching from such a defunct handle results in an error with the message
C<ERROR nested cursor is defunct (parent row is no longer current)>.
This means that the C<fetchall...> or C<selectall...> methods are not useful
for queries returning nested cursors. By the time such a method returns,
all the nested cursor handles it has fetched will be defunct.
It is necessary to use an explicit fetch loop, and to do all the
fetching of nested cursors within the loop, as the following example
shows:
use DBI;
my $dbh = DBI->connect(...);
my $sth = $dbh->prepare(q{
SELECT dname, CURSOR(
SELECT ename FROM emp
WHERE emp.deptno = dept.deptno
ORDER BY ename
) FROM dept ORDER BY dname
});
$sth->execute;
while ( my ($dname, $nested) = $sth->fetchrow_array ) {
print "$dname\n";
while ( my ($ename) = $nested->fetchrow_array ) {
print " $ename\n";
}
}
The cursor returned by the function C<sp_ListEmp> defined in the
previous section can be fetched as a nested cursor as follows:
my $sth = $dbh->prepare(q{SELECT sp_ListEmp FROM dual});
$sth->execute;
my ($nested) = $sth->fetchrow_array;
while ( my @row = $nested->fetchrow_array ) { ... }
=head2 Pre-fetching Nested Cursors
By default, DBD::Oracle pre-fetches rows in order to reduce the number of
round trips to the server. For queries which do not involve nested cursors,
the number of pre-fetched rows is controlled by the DBI database handle
attribute C<RowCacheSize> (q.v.).
In Oracle, server side open cursors are a controlled resource, limited in
number, on a per session basis, to the value of the initialization
parameter C<OPEN_CURSORS>. Nested cursors count towards this limit.
Each nested cursor in the current row counts 1, as does
each nested cursor in a pre-fetched row. Defunct nested cursors do not count.
An Oracle specific database handle attribute, C<ora_max_nested_cursors>,
further controls pre-fetching for queries involving nested cursors. For
each statement handle, the total number of nested cursors in pre-fetched
rows is limited to the value of this parameter. The default value
is 0, which disables pre-fetching for queries involving nested cursors.
=head1 PL/SQL Examples
Most of these PL/SQL examples come from: Eric Bartley <bartley@cc.purdue.edu>.
/*
* PL/SQL to create package with stored procedures invoked by
* Perl examples. Execute using sqlplus.
*
* Use of "... OR REPLACE" prevents failure in the event that the
* package already exists.
*/
CREATE OR REPLACE PACKAGE plsql_example
IS
PROCEDURE proc_np;
PROCEDURE proc_in (
err_code IN NUMBER
);
PROCEDURE proc_in_inout (
test_num IN NUMBER,
is_odd IN OUT NUMBER
);
FUNCTION func_np
RETURN VARCHAR2;
END plsql_example;
/
CREATE OR REPLACE PACKAGE BODY plsql_example
IS
PROCEDURE proc_np
IS
whoami VARCHAR2(20) := NULL;
BEGIN
SELECT USER INTO whoami FROM DUAL;
END;
PROCEDURE proc_in (
err_code IN NUMBER
)
IS
BEGIN
RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
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
( run in 1.109 second using v1.01-cache-2.11-cpan-39bf76dae61 )