[continued from previous message]

- # 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 **SQLT\_INT**, like:
-
- ora_internal_type => SQLT_INT
-
-you get:
-
- Result: cc=2
- arr=$VAR1 = [
- 1,
- 2,
- 3,
- -1,
- -2
- ];
-
-## **bind\_param\_inout\_array**
-
-DBD::Oracle supports this undocumented feature of DBI. See ["Returning A Value from an INSERT"](#returning-a-value-from-an-insert) for an example.
-
-## **bind\_param\_array**
-
- $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
- $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type) - $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
-
-Binds an array of values to a placeholder, so that each is used in turn by a call
-to the ["execute\_array"](#execute_array) method.
-
-## **execute**
-
- $rv = $sth->execute(@bind_values);
-
-Perform whatever processing is necessary to execute the prepared statement.
-
-## **execute\_array**
-
- $tuples = $sth->execute_array() or die $sth->errstr;
- $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
- $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
-
- ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
- ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
-
-Execute a prepared statement once for each item in a passed-in hashref, or items that
-were previously bound via the ["bind\_param\_array"](#bind_param_array) method. See the DBI documentation
-for more details.
-
-DBD::Oracle takes full advantage of OCI's array interface so inserts and updates using this interface will run very
-quickly.
-
-## **execute\_for\_fetch**
-
- $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
- $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
-
- ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
- ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
-
-Used internally by the ["execute\_array"](#execute_array) method, and rarely used directly. See the
-DBI documentation for more details.
-
-## **fetchrow\_arrayref**
-
- $ary_ref = $sth->fetchrow_arrayref;
-
-Fetches the next row of data from the statement handle, and returns a reference to an array
-holding the column values. Any columns that are NULL are returned as undef within the array.
-
-If there are no more rows or if an error occurs, this method returns undef. You should
-check `$sth->err` afterwards (or use the ["RaiseError"](#raiseerror) attribute) to discover if the undef returned
-was due to an error.
-
-Note that the same array reference is returned for each fetch, so don't store the reference and
-then use it after a later fetch. Also, the elements of the array are also reused for each row,
-so take care if you want to take a reference to an element. See also ["bind\_columns"](#bind_columns).
-
-## **fetchrow\_array**
-
- @ary = $sth->fetchrow_array;
-
-Similar to the ["fetchrow\_arrayref"](#fetchrow_arrayref) method, but returns a list of column information rather than
-a reference to a list. Do not use this in a scalar context.
-
-## **fetchrow\_hashref**
-
- $hash_ref = $sth->fetchrow_hashref;
- $hash_ref = $sth->fetchrow_hashref($name);
-
-Fetches the next row of data and returns a hashref containing the name of the columns as the keys
-and the data itself as the values. Any NULL value is returned as undef value. -
-If there are no more rows or if an error occurs, this method returns undef. You should
-check `$sth->err` afterwards (or use the ["RaiseError"](#raiseerror) attribute) to discover if the undef returned
-was due to an error.
-
-The optional `$name` argument should be either `NAME`, `NAME_lc` or `NAME_uc`, and indicates
-what sort of transformation to make to the keys in the hash. By default Oracle uses upper case.
-
-## **fetchall\_arrayref**
-
- $tbl_ary_ref = $sth->fetchall_arrayref();
- $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
- $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
-
-Returns a reference to an array of arrays that contains all the remaining rows to be fetched from the
-statement handle. If there are no more rows, an empty arrayref will be returned. If an error occurs,
-the data read in so far will be returned. Because of this, you should always check `$sth->err` after
-calling this method, unless ["RaiseError"](#raiseerror) has been enabled.
-
-If `$slice` is an array reference, fetchall\_arrayref uses the ["fetchrow\_arrayref"](#fetchrow_arrayref) method to fetch each
-row as an array ref. If the `$slice` array is not empty then it is used as a slice to select individual
-columns by perl array index number (starting at 0, unlike column and parameter numbers which start at 1).
-
-With no parameters, or if $slice is undefined, fetchall\_arrayref acts as if passed an empty array ref.
-
-If `$slice` is a hash reference, fetchall\_arrayref uses ["fetchrow\_hashref"](#fetchrow_hashref) to fetch each row as a hash reference.
-
-See the DBI documentation for a complete discussion.
-
-## **fetchall\_hashref**
-
- $hash_ref = $sth->fetchall_hashref( $key_field );
-
-Returns a hashref containing all rows to be fetched from the statement handle. See the DBI documentation for
-a full discussion.
-
-## **finish**
-
- $rv = $sth->finish;
-
-Indicates to DBI that you are finished with the statement handle and are not going to use it again. Only needed
-when you have not fetched all the possible rows.
-
-## **rows**
-
- $rv = $sth->rows;
-
-Returns the number of rows affected for updates, deletes and inserts and -1 for selects.
-
-## **bind\_col**
-
- $rv = $sth->bind_col($column_number, \$var_to_bind);
- $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
- $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
-
-Binds a Perl variable and/or some attributes to an output column of a SELECT statement.
-Column numbers count up from 1. You do not need to bind output columns in order to fetch data.
-
-NOTE: DBD::Oracle does not use the `$bind_type` to determine how to
-bind the column; it uses what Oracle says the data type is. You can
-however set the StrictlyTyped/DiscardString attributes and these will
-take effect as these attributes are applied after the column is
-retrieved.
-
-See the DBI documentation for a discussion of the optional parameters `\%attr` and `$bind_type`
-
-## **bind\_columns**
-
- $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
-
-Calls the ["bind\_col"](#bind_col) method for each column in the SELECT statement, using the supplied list.
-
-## **dump\_results**
-
- $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
-
-Fetches all the rows from the statement handle, calls `DBI::neat_list` for each row, and
-prints the results to `$fh` (which defaults to `STDOUT`). Rows are separated by `$lsep` (which defaults
-to a newline). Columns are separated by `$fsep` (which defaults to a comma). The `$maxlen` controls
-how wide the output can be, and defaults to 35.
-
-This method is designed as a handy utility for prototyping and testing queries. Since it uses
-"neat\_list" to format and edit the string for reading by humans, it is not recommended
-for data transfer applications.
-
-# STATEMENT HANDLE ATTRIBUTES
-
-## **NUM\_OF\_FIELDS** (integer, read-only)
-
-Returns the number of columns returned by the current statement. A number will only be returned for
-SELECT statements for INSERT,
-UPDATE, and DELETE statements which contain a RETURNING clause.
-This method returns undef if called before `execute()`.
-
-## **NUM\_OF\_PARAMS** (integer, read-only)
-
-Returns the number of placeholders in the current statement.
-
-## **NAME** (arrayref, read-only)
-
-Returns an arrayref of column names for the current statement. This
-method will only work for SELECT statements, for SHOW statements, and for -INSERT, UPDATE, and DELETE statements which contain a RETURNING clause.
-This method returns undef if called before `execute()`.
-
-## **NAME\_lc** (arrayref, read-only)
-
-The same as the `NAME` attribute, except that all column names are forced to lower case.
-
-## **NAME\_uc** (arrayref, read-only)
-
-The same as the `NAME` attribute, except that all column names are forced to upper case.
-
-## **NAME\_hash** (hashref, read-only)
-
-Similar to the `NAME` attribute, but returns a hashref of column names instead of an arrayref. The names of the columns
-are the keys of the hash, and the values represent the order in which the columns are returned, starting at 0.
-This method returns undef if called before `execute()`.
-
-## **NAME\_lc\_hash** (hashref, read-only)
-
-The same as the `NAME_hash` attribute, except that all column names are forced to lower case.
-
-## **NAME\_uc\_hash** (hashref, read-only)
-
-The same as the `NAME_hash` attribute, except that all column names are forced to lower case.
-
-## **TYPE** (arrayref, read-only)
-
-Returns an arrayref indicating the data type for each column in the statement. -This method returns undef if called before `execute()`.
-
-## **PRECISION** (arrayref, read-only)
-
-Returns an arrayref of integer values for each column returned by the statement.
-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).
- OCI_FETCH_RELATIVE, and a fetch offset value that makes the current position less than 1,
- does not change fetch offset value so it is equivalent to a OCI_FETCH_CURRENT.
- OCI_FETCH_RELATIVE, and a fetch offset value that makes it greater than the number of records
- in the record set, does not change fetch offset value so it is equivalent
- to a OCI_FETCH_CURRENT.
-
- The effects of the differing orientation constants on the first fetch (current position attribute at 0) are as follows.
-
- OCI_FETCH_CURRENT, dose not fetch a row or change the current position attribute.
- OCI_FETCH_FIRST, fetches row 1 and sets the current position attribute to 1.
- OCI_FETCH_LAST, fetches the last row in the record set and sets the current position
- attribute to the total number of rows in the record set.
- OCI_FETCH_NEXT, equivalent to a OCI_FETCH_FIRST.
- OCI_FETCH_PRIOR, equivalent to a OCI_FETCH_CURRENT.
-
- OCI_FETCH_ABSOLUTE, and a fetch offset value that is less than 1 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 is equivalent to a OCI_FETCH_CURRENT.
- OCI_FETCH_RELATIVE, and a fetch offset value that is less than 1 is equivalent
- to a OCI_FETCH_CURRENT.
- OCI_FETCH_RELATIVE, and a fetch offset value that makes it greater than the number
- of records in the record set, is equivalent to a OCI_FETCH_CURRENT.
-
-## Scrollable Cursor Usage
-
-Given a simple code like this:
-
- use DBI;
- use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
- my $dbh = DBI->connect($dsn, $dbuser, '');
- my $SQL = "select id,
- first_name,
- last_name
- from employee";
- my $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY}); - $sth->execute();
- my $value;
-
-and one assumes that the number of rows returned from the query is 20, the code snippets below will illustrate the use of ora\_fetch\_scroll
-method;
-
-- Fetching the Last Row
-
- $value = $sth->ora_fetch_scroll(OCI_FETCH_LAST,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 to will be 20 after this snippet. This is also a way to get the number of rows in the record set, however,
- if the record set is large this could take some time.
-
-- Fetching the Current Row
-
- $value = $sth->ora_fetch_scroll(OCI_FETCH_CURRENT,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 still be 20 after this snippet.
-
-- Fetching the First Row
-
- $value = $sth->ora_fetch_scroll(OCI_FETCH_FIRST,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 Next Row
-
- for(my $i=0;$i<=3;$i++){
- $value = $sth->ora_fetch_scroll(OCI_FETCH_NEXT,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 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
-
-As the name implies this is the simplest way to use this interface. DBD::Oracle just attempts to get your LONG datatypes as a single large piece.
-There are no special settings, simply set the database handle's 'LongReadLen' attribute to a value that will be larger than the expected size of the LONG or LONG RAW.
-If the size of the LONG or LONG RAW exceeds 'LongReadLen' DBD::Oracle will return an 'ORA-24345: A Truncation' error. To stop this set the database handle's 'LongTruncOk' attribute to '1'.
-The maximum value of 'LongReadLen' seems to be dependent on the physical memory limits of the box that Oracle is running on. You have most likely reached this limit if you run into
-an 'ORA-01062: unable to allocate memory for define buffer' error. One solution is to set the size of 'LongReadLen' to a lower value.
-
-For example give this table;
-
- CREATE TABLE test_long (
- id NUMBER,
- long1 long)
-
-this code;
-
- $dbh->{LongReadLen} = 2*1024*1024; #2 meg
- $SQL='select p_id,long1 from test_long';
- $sth=$dbh->prepare($SQL);
- $sth->execute();
- while (my ( $p_id,$long )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "long=".$long."\n";
- }
-
-Will select out all of the long1 fields in the table as long as they are all under 2MB in length. A value in long1 longer than this will throw an error. Adding this line;
-
- $dbh->{LongTruncOk}=1;
-
-before the execute will return all the long1 fields but they will be truncated at 2MBs.
-
-### Using ora\_ncs\_buff\_mtpl
-
-When getting CLOBs and NCLOBs in or out of Oracle, the Server will translate from the Server's NCharSet to the
-Client's. If they happen to be the same or at least compatible then all of these actions are a 1 char to 1 char bases.
-Thus if you set your LongReadLen buffer to 10\_000\_000 you will get up to 10\_000\_000 char.
-
-However if the Server has to translate from one NCharSet to another it will use bytes for conversion. The buffer
-value is set to 4 \* LONG\_READ\_LEN which was very wasteful as you might only be asking for 10\_000\_000 bytes
-but you were actually using 40\_000\_000 bytes of buffer under the hood. You would still get 10\_000\_000 bytes
-(maybe less characters though) but you are using allot more memory that you need.
-
-You can now customize the size of the buffer by setting the 'ora\_ncs\_buff\_mtpl' either on the connection or statement handle. You can
-also set this as 'ORA\_DBD\_NCS\_BUFFER' OS environment variable so you will have to go back and change all your code if you are getting into trouble.
-
-The default value is still set to 4 for backward compatibility. You can lower this value and thus increase the amount of data you can retrieve. If the
-ora\_ncs\_buff\_mtpl is too small DBD::Oracle will throw and error telling you to increase this buffer by one.
-
-If the error is not captured then you may get at some random point later on, usually at a finish() or disconnect() or even a fetch() this error;
-
- ORA-03127: no new operations allowed until the active operation ends
-
-This is one of the more obscure ORA errors (have some fun and report it to Meta-Link they will scratch their heads for hours)
-
-If you get this, simply increment the ora\_ncs\_buff\_mtpl by one until it goes away.
-
-This should greatly increase your ability to select very large CLOBs or NCLOBs, by freeing up a large block of memory.
-
-You can tune this value by setting ora\_oci\_success\_warn which will display the following
-

[continued in next message]

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)