DBD-Oracle
view release on metacpan or search on metacpan
README.mkdn view on Meta::CPAN
# 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](https://metacpan.org/pod/DBI) documentation first!
# CONSTANTS
- :ora\_session\_modes
ORA\_SYSDBA ORA\_SYSOPER ORA\_SYSASM ORA\_SYSBACKUP ORA\_SYSDG ORA\_SYSKM
- :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 OCI_FO_RETRY
# 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...
Oracle can use two environment variables to specify default connections: ORACLE\_SID and TWO\_TASK.
README.mkdn view on Meta::CPAN
$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](https://metacpan.org/pod/DBI#prepare) 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"](#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"](#simple-fetch-for-clobs-and-blobs) method for the ["Data Interface for Persistent LOBs"](#data-interface-for-persistent-lobs) will be
used for LOBs rather than the default method ["Data Interface for LOB Locators"](#data-interface-for-lob-locators).
- ora\_clbk\_lob
If true the ["Piecewise Fetch with Callback"](#piecewise-fetch-with-callback) method for the ["Data
Interface for Persistent LOBs"](#data-interface-for-persistent-lobs) will be used for LOBs.
- ora\_piece\_lob
If true the ["Piecewise Fetch with Polling"](#piecewise-fetch-with-polling) method for the ["Data
Interface for Persistent LOBs"](#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"](#piecewise-fetch-with-callback)
and ["Piecewise Fetch with Polling"](#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"](#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"](#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"](#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"](#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"](#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"](#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
README.mkdn 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.
## **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 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.
# 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::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 ["bind\_param"](#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);
README.mkdn view on Meta::CPAN
use DBI qw(:sql_types);
use DBD::Oracle 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](https://metacpan.org/pod/DBI#bind_param) or ["bind\_param\_inout" in DBI](https://metacpan.org/pod/DBI#bind_param_inout) 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"](#binding-cursors) for the correct way to use ORA\_RSET.
See ["LOBS AND LONGS"](#lobs-and-longs) for how to use ORA\_CLOB and ORA\_BLOB.
See ["SYS.DBMS\_SQL datatypes"](#sys-dbms_sql-datatypes) for ORA\_VARCHAR2\_TABLE, ORA\_NUMBER\_TABLE.
See ["Data Interface for Persistent LOBs"](#data-interface-for-persistent-lobs) for the correct way to use SQLT\_CHR and SQLT\_BIN.
See ["OTHER DATA TYPES"](#other-data-types) for more information.
See also ["Placeholders and Bind Values" in DBI](https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values).
- 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"](#ora_check_sql) to 0 in prepare() to enable this behaviour.
## **bind\_param\_inout**
README.mkdn view on Meta::CPAN
The number indicates the precision for `NUMERIC` columns, the size in number of
characters for `CHAR` and `VARCHAR` columns, and for all other types of columns
it returns the number of _bytes_.
This method returns undef if called before `execute()`.
## **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 `NUMERIC`.
This method returns undef if called before `execute()`.
## **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 `execute()`.
## **Database** (dbh, read-only)
Returns the database handle this statement handle was created from.
## **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 ["execute"](#execute), the literal values passed in are returned. If called after
["execute"](#execute), then the quoted versions of the values are returned.
## **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 ["bind\_param"](#bind_param) method.
## **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 `prepare`
method call failed.
## **RowsInCache**
Returns the number of un-fetched rows in the cache for selects.
# 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.
## 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.
## Scrollable Cursor Methods
The following driver-specific methods are used with scrollable cursors.
- 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.
- ora\_fetch\_scroll
$ary_ref = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);
Works the same as `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 _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.
OCI_FETCH_RELATIVE, sets the current position attribute to (current position attribute +
fetch offset value).
README.mkdn view on Meta::CPAN
The current position attribute will be 5 after this snippet.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
# 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,
- ["Data Interface for Persistent LOBs"](#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.
- ["Data Interface for LOB Locators"](#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.
- 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.
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;
- 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.
- 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.
- Populated
A LOB instance with a locator and a value exists in the cell. You actually get the LOB value.
## Data Interface for Persistent LOBs
This is the original interface for LONG and LONG RAW datatypes and from Oracle 9iR1 and later the OCI API was extended to work directly with the other LOB datatypes.
In other words you can treat all LOB type data (BLOB, CLOB) as if it was a LONG, LONG RAW, or VARCHAR2. So you can perform INSERT, UPDATE, fetch, bind, and define operations on LOBs using the same techniques
you would use on other datatypes that store character or binary data. In some cases there are fewer round trips to the server as no 'LOB Locators' are
used, normally one can get an entire LOB is a single round trip.
### Simple Fetch for LONGs and LONG RAWs
README.mkdn 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'"
## 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.
## Binding Cursors
Cursors can be returned from PL/SQL blocks, either from stored
functions (or procedures with OUT parameters) or
from direct `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 `bind_param_inout()` with an
attribute hash parameter that specifies `ora_type` as `ORA_RSET`.
If you don't do that you'll get an error from the `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 `curref.pl` script in the Oracle.ex directory in the DBD::Oracle
source distribution for a complete working example.
## Fetching Nested Cursors
Oracle supports the use of select list expressions of type REF CURSOR.
These may be explicit cursor expressions - `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 `$sth1` is a handle for a select statement
involving nested cursors, and `$sth2` is a nested cursor handle fetched
from `$sth1`. `$sth2` can only be fetched from while `$sth1` is
still active, and the row containing `$sth2` is still current in `$sth1`.
Any attempt to fetch another row from `$sth1` renders all nested cursor
handles previously fetched from `$sth1` defunct.
Fetching from such a defunct handle results in an error with the message
`ERROR nested cursor is defunct (parent row is no longer current)`.
This means that the `fetchall...` or `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 `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 ) { ... }
## 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 `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 `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, `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.
# 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 0.875 second using v1.01-cache-2.11-cpan-39bf76dae61 )