[continued from previous message]

- OCILobRead field 2 of 3 SUCCESS: csform 1 (SQLCS_IMPLICIT), LOBlen 10240(characters), LongReadLen
- 20(characters), BufLen 80(characters), Got 28(characters)
-
-In the case above the query Got 28 characters (well really only 20 characters of 28 bytes) so we could use ora\_ncs\_buff\_mtpl=>2 (20\*2=40) thus saving 40bytes of memory.
-
-### Simple Fetch for CLOBs and BLOBs
-
-To use this interface for the CLOB and BLOB datatypes set the 'ora\_pers\_lob' attribute of the statement handle to '1' with the prepare method, and
-set the database handle's 'LongReadLen' attribute to a value that will be larger than the expected size of the LOB. If the size of the LOB 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 in the same way that LONGs and LONG RAWs are.
-
-For CLOBs and NCLOBs the limit is 64k chars if there is no truncation. This is an internal OCI limit--complain to them if you want it changed. However if your CLOB is longer than this
-and also larger than 'LongReadLen' then 'LongReadLen' chars are returned.
-
-It seems with BLOBs you are not limited by the 64k.
-
-For example give this table;
-
- CREATE TABLE test_lob (id NUMBER,
- clob1 CLOB,
- clob2 CLOB,
- blob1 BLOB,
- blob2 BLOB)
-
-this code;
-
- $dbh->{LongReadLen} = 2*1024*1024; #2 meg
- $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
- $sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
- $sth->execute();
- while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "clob1=".$clob1."\n";
- print "clob2=".$clob2."\n";
- print "blob1=".$blob2."\n";
- print "blob2=".$blob2."\n";
- }
-
-Will select out all of the LOBs in the table as long as they are all under 2MB in length. Longer lobs will throw an error. Adding this line;
-
- $dbh->{LongTruncOk}=1;
-
-before the execute will return all the lobs but they will be truncated at 2MBs.
-
-### Piecewise Fetch with Callback
-
-With a piecewise callback fetch DBD::Oracle sets up a function that will 'callback' to the DB during the fetch and gets your LOB (LONG, LONG RAW, CLOB, BLOB) piece by piece.
-To use this interface set the 'ora\_clbk\_lob' attribute of the statement handle to '1' with the prepare method. Next set the 'ora\_piece\_size' to the size of the piece that
-you want to return on the callback. Finally set the database handle's 'LongReadLen' attribute to a value that will be larger than the expected
-size of the LOB. Like the ["Simple Fetch for LONGs and LONG RAWs"](#simple-fetch-for-longs-and-long-raws) and ["Simple Fetch for CLOBs and BLOBs"](#simple-fetch-for-clobs-and-blobs) the if the size of the LOB exceeds 'LongReadLen' you can use the '
LongTruncOk' attribute to truncate the LOB
-or set 'LongReadLen' to a higher value. With this interface the value of 'ora\_piece\_size' seems to be constrained by the same memory limit as found on
-the Simple Fetch interface. If you encounter an 'ORA-01062' error try setting the value of 'ora\_piece\_size' to a smaller value. The value for 'LongReadLen' is
-dependent on the version and settings of the Oracle DB you are using. In theory it ranges from 8GBs
-in 9iR1 up to 128 terabytes with 11g but you will also be limited by the physical memory of your PERL instance.
-
-Using the table from the last example this code;
-
- $dbh->{LongReadLen} = 20*1024*1024; #20 meg
- $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
- $sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
- $sth->execute();
- while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "clob1=".$clob1."\n";
- print "clob2=".$clob2."\n";
- print "blob1=".$blob2."\n";
- print "blob2=".$blob2."\n";
- }
-
-Will select out all of the LOBs in the table as long as they are all under 20MB in length. If the LOB is longer than 5MB (ora\_piece\_size) DBD::Oracle will fetch it in at least 2 pieces to a
-maximum of 4 pieces (4\*5MB=20MB). Like the Simple Fetch examples Lobs longer than 20MB will throw an error.
-
-Using the table from the first example (LONG) this code;
-
- $dbh->{LongReadLen} = 20*1024*1024; #2 meg
- $SQL='select p_id,long1 from test_long';
- $sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
- $sth->execute();
- while (my ( $p_id,$long )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "long=".$long."\n";
- }
-
-Will select all of the long1 fields from table as long as they are is under 20MB in length. If the long1 filed is longer than 5MB (ora\_piece\_size) DBD::Oracle will fetch it in at least 2 pieces to a
-maximum of 4 pieces (4\*5MB=20MB). Like the other examples long1 fields longer than 20MB will throw an error.
-
-#### Piecewise Fetch with Polling
-
-With a polling piecewise fetch DBD::Oracle iterates (Polls) over the LOB during the fetch getting your LOB (LONG, LONG RAW, CLOB, BLOB) piece by piece. To use this interface set the 'ora\_piece\_lob'
-attribute of the statement handle to '1' with the prepare method. Next set the 'ora\_piece\_size' to the size of the piece that
-you want to return on the callback. Finally set the database handle's 'LongReadLen' attribute to a value that will be larger than the expected
-size of the LOB. Like the ["Piecewise Fetch with Callback"](#piecewise-fetch-with-callback) and Simple Fetches if the size of the LOB exceeds 'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB
-or set 'LongReadLen' to a higher value. With this interface the value of 'ora\_piece\_size' seems to be constrained by the same memory limit as found on
-the ["Piecewise Fetch with Callback"](#piecewise-fetch-with-callback).
-
-Using the table from the example above this code;
-
- $dbh->{LongReadLen} = 20*1024*1024; #20 meg
- $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
- $sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
- $sth->execute();
- while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "clob1=".$clob1."\n";
- print "clob2=".$clob2."\n";
- print "blob1=".$blob2."\n";
- print "blob2=".$blob2."\n";
- }
-
-Will select out all of the LOBs in the table as long as they are all under 20MB in length. If the LOB is longer than 5MB (ora\_piece\_size) DBD::Oracle will fetch it in at least 2 pieces to a
-maximum of 4 pieces (4\*5MB=20MB). Like the other fetch methods LOBs longer than 20MB will throw an error.
-
-Finally with this code;
-
- $dbh->{LongReadLen} = 20*1024*1024; #2 meg
- $SQL='select p_id,long1 from test_long';
- $sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
- $sth->execute();
- while (my ( $p_id,$long )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "long=".$long."\n";
- }
-
-Will select all of the long1 fields from table as long as they are is under 20MB in length. If the long1 field is longer than 5MB (ora\_piece\_size) DBD::Oracle will fetch it in at least 2 pieces to a
-maximum of 4 pieces (4\*5MB=20MB). Like the other examples long1 fields longer than 20MB will throw an error.
-
-### Binding for Updates and Inserts for CLOBs and BLOBs
-
-To bind for updates and inserts all that is required to use this interface is to set the statement handle's prepare method
-'ora\_type' attribute to 'SQLT\_CHR' in the case of CLOBs and NCLOBs or 'SQLT\_BIN' in the case of BLOBs as in this example for an insert;
-
- my $in_clob = "<document>\n";
- $in_clob .= " <value>$_</value>\n" for 1 .. 10_000;
- $in_clob .= "</document>\n";
- my $in_blob ="0101" for 1 .. 10_000;
-
- $SQL='insert into test_lob3@tpgtest (id,clob1,clob2, blob1,blob2) values(?,?,?,?,?)';
- $sth=$dbh->prepare($SQL );
- $sth->bind_param(1,3);
- $sth->bind_param(2,$in_clob,{ora_type=>SQLT_CHR});
- $sth->bind_param(3,$in_clob,{ora_type=>SQLT_CHR});
- $sth->bind_param(4,$in_blob,{ora_type=>SQLT_BIN});
- $sth->bind_param(5,$in_blob,{ora_type=>SQLT_BIN});
- $sth->execute();
-
-So far the only limit reached with this form of insert is the LOBs must be under 2GB in size.
-
-### Support for Remote LOBs;
-
-Starting with Oracle 10gR2 the interface for Persistent LOBs was expanded to support remote LOBs (access over a dblink). Given a database called 'lob\_test' that has a 'LINK' defined like this;
-
- CREATE DATABASE LINK link_test CONNECT TO test_lobs IDENTIFIED BY tester USING 'lob_test';
-
-to a remote database called 'test\_lobs', the following code will work;
-
- $dbh = DBI->connect('dbi:Oracle:','test@lob_test','test');
- $dbh->{LongReadLen} = 2*1024*1024; #2 meg
- $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs@link_test';
- $sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
- $sth->execute();
- while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
- print "p_id=".$p_id."\n";
- print "clob1=".$clob1."\n";
- print "clob2=".$clob2."\n";
- print "blob1=".$blob2."\n";
- print "blob2=".$blob2."\n";
- }
-
-Below are the limitations of Remote LOBs;
-
-- Queries involving more than one database are not supported;
-
- so the following returns an error:
-
- SELECT t1.lobcol,
- a2.lobcol
- FROM t1,
- t2.lobcol@dbs2 a2 W
- WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);
-
- as does:
-
- SELECT t1.lobcol
- FROM t1@dbs1
- UNION ALL
- SELECT t2.lobcol
- FROM t2@dbs2;
-
-- DDL commands are not supported;
-
- so the following returns an error:
-
- CREATE VIEW v AS SELECT lob_col FROM tab@dbs;
-
-- Only binds and defines for data going into remote persistent LOBs are supported.
-
- so that parameter passing in PL/SQL where CHAR data is bound or defined for remote LOBs is not allowed .
-
- These statements all produce errors:
-
- SELECT foo() FROM table1@dbs2;
-
- SELECT foo()@dbs INTO char_val FROM DUAL;
-
- SELECT XMLType().getclobval FROM table1@dbs2;
-
-- If the remote object is a view such as
-
- CREATE VIEW v AS SELECT foo() FROM ...
-
- the following would not work:
-
- SELECT * FROM v@dbs2;
-
-- Limited PL/SQL parameter passing
-
- PL/SQL parameter passing is not allowed where the actual argument is a LOB type
- and the remote argument is one of VARCHAR2, NVARCHAR2, CHAR, NCHAR, or RAW.
-
-- RETURNING INTO does not support implicit conversions between CHAR and CLOB. -
- so the following returns an error:
-
- SELECT t1.lobcol as test, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 RETURNING test
-
-## Locator Data Interface
-
-### Simple Usage
-
-When fetching LOBs with this interface a 'LOB Locator' is created then used to get the lob with the LongReadLen and LongTruncOk attributes.
-The value for 'LongReadLen' is dependent on the version and settings of the Oracle DB you are using. In theory it ranges from 8GBs
-in 9iR1 up to 128 terabytes with 11g but you will also be limited by the physical memory of your PERL instance.
-
-When inserting or updating LOBs some _major_ magic has to be performed
-behind the scenes to make it transparent. Basically the driver has to
-insert a 'LOB Locator' and then refetch the newly inserted LOB
-Locator before being able to write the data into it. However, it works
-well most of the time, and I've made it as fast as possible, just one
-extra server-round-trip per insert or update after the first. For the
-time being, only single-row LOB updates are supported.
-
-To insert or update a large LOB using a placeholder, DBD::Oracle has to
-know in advance that it is a LOB type. So you need to say:
-
- $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });
-
-The ORA\_CLOB and ORA\_BLOB constants can be imported using
-
- use DBD::Oracle qw(:ora_types);
-
-or use the corresponding integer values (112 and 113).
-
-One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has
-to be able to tell which parameters relate to which table fields.
-In all cases where it can possibly work it out for itself, it does,
-however, if there are multiple LOB fields of the same type in the table
-then you need to tell it which field each LOB param relates to:
-
- $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' }); -
-There are some limitations inherent in the way DBD::Oracle makes typical
-LOB operations simple by hiding the LOB Locator processing:
-
- - Can't read/write LOBs in chunks (except via DBMS_LOB.WRITEAPPEND in PL/SQL)
- - To INSERT a LOB, you need UPDATE privilege.
-
-The alternative is to disable the automatic LOB Locator processing.
-If ["ora\_auto\_lob"](#ora_auto_lob) is 0 in prepare(), you can fetch the LOB Locators and
-do all the work yourself using the ora\_lob\_\*() methods.
-See the ["Data Interface for LOB Locators"](#data-interface-for-lob-locators) section below.
-
-### LOB support in PL/SQL
-
-LOB Locators can be passed to PL/SQL calls by binding them to placeholders -with the proper `ora_type`. If ["ora\_auto\_lob"](#ora_auto_lob) is true, output LOB
-parameters will be automatically returned as strings.
-
-If the Oracle driver has support for temporary LOBs (Oracle 9i and higher), -strings can be bound to input LOB placeholders and will be automatically -converted to LOBs.
-
-Example:
- # Build a large XML document, bind it as a CLOB,
- # extract elements through PL/SQL and return as a CLOB
-
- # $dbh is a connected database handle
- # output will be large
-
- local $dbh->{LongReadLen} = 1_000_000;
-
- my $in_clob = "<document>\n";
- $in_clob .= " <value>$_</value>\n" for 1 .. 10_000;
- $in_clob .= "</document>\n";
-
- my $out_clob;
-
-
- my $sth = $dbh->prepare(<<PLSQL_END);
- -- extract 'value' nodes
- DECLARE
- x XMLTYPE := XMLTYPE(:in);
- BEGIN
- :out := x.extract('/document/value').getClobVal();
- END;
-
- PLSQL_END
-
- # :in param will be converted to a temp lob
- # :out parameter will be returned as a string.
-
- $sth->bind_param( ':in', $in_clob, { ora_type => ORA_CLOB } );
- $sth->bind_param_inout( ':out', \$out_clob, 0, { ora_type => ORA_CLOB } );
- $sth->execute;
-
-If you ever get an
-
- ORA-01691 unable to extend lob segment sss.ggg by nnn in tablespace ttt
-
-error, while attempting to insert a LOB, this means the Oracle user has insufficient space for LOB you are trying to insert.
-One solution it to use "alter database datafile 'sss.ggg' resize Mnnn" to increase the available memory for LOBs.
-
-## Persistent & Locator Interface Caveats
-
-Now that one has the option of using the Persistent or the Locator interface for LOBs the questions arises
-which one to use. For starters, if you want to access LOBs over a dblink you will have to use the Persistent
-interface so that choice is simple. The question of which one to use after that is a little more tricky.
-It basically boils down to a choice between LOB size and speed.
-
-The Callback and Polling piecewise fetches are very very slow
-when compared to the Simple and the Locator fetches but they can handle very large blocks of data. Given a situation where a
-large LOB is to be read the Locator fetch may time out while either of the piecewise fetches may not.
-
-With the Simple fetch you are limited by physical memory of your server but it runs a little faster than the Locator, as there are fewer round trips
-to the server. So if you have small LOBs and need to save a little bandwidth this is the one to use. It you are going after large LOBs then the Locator interface is the one to use.
-
-If you need to update more than a single row of with LOB data then the Persistent interface can do it while the Locator can't.
-
-If you encounter a situation where you have to access the legacy LOBs (LONG, LONG RAW) and the values are to large for you system then you can use
-the Callback or Polling piecewise fetches to get all of the data.
-
-Not all of the Persistent interface has been implemented yet, the following are not supported;
-
- 1) Piecewise, polling and callback binds for INSERT and UPDATE operations. - 2) Piecewise array binds for SELECT, INSERT and UPDATE operations.
-
-Most of the time you should just use the ["Locator Data Interface"](#locator-data-interface) as this is in one that has the best combination of speed and size.
-
-All this being said if you are doing some critical programming I would use the ["Data Interface for LOB Locators"](#data-interface-for-lob-locators) as this gives you very
-fine grain control of your LOBs, of course the code for this will be somewhat more involved.
-
-## Data Interface for LOB Locators
-
-The following driver-specific methods let you manipulate "LOB Locators" directly.
-To select a LOB locator directly set the if the `ora_auto_lob`
-attribute to false, or alternatively they can be returned via PL/SQL procedure calls.
-
-(If using a DBI version earlier than 1.36 they must be called via the
-func() method. Note that methods called via func() don't honour
-RaiseError etc, and so it's important to check $dbh->err after each call. -It's recommended that you upgrade to DBI 1.38 or later.)
-
-Note that LOB locators are only valid while the statement handle that
-created them is valid. When all references to the original statement
-handle are lost, the handle is destroyed and the locators are freed.
-
-- ora\_lob\_read
-
- $data = $dbh->ora_lob_read($lob_locator, $offset, $length);
-
- Read a portion of the LOB. $offset starts at 1.
- Uses the Oracle OCILobRead function.
-
- NOTE: DBD::Oracle post 1.46 will return undef for any read lob if the
- length specified in the ora\_lob\_read is 0. See RT 55028. This avoids
- the potential problem with empty lobs (created with empty\_clob) which
- return a length of 0 from ora\_lob\_length and prior to 1.46 a call to
- ora\_lob\_read with a 0 length would segfault.
-
-- ora\_lob\_write
-
- $rc = $dbh->ora_lob_write($lob_locator, $offset, $data);
-
- Write/overwrite a portion of the LOB. $offset starts at 1.
- Uses the Oracle OCILobWrite function.
-
-- ora\_lob\_append
-
- $rc = $dbh->ora_lob_append($lob_locator, $data);
-
- Append $data to the LOB. Uses the Oracle OCILobWriteAppend function.
-
-- ora\_lob\_trim
-
- $rc = $dbh->ora_lob_trim($lob_locator, $length);
-
- Trims the length of the LOB to $length.
- Uses the Oracle OCILobTrim function.
-
-- ora\_lob\_length
-
- $length = $dbh->ora_lob_length($lob_locator);
-
- Returns the length of the LOB.
- Uses the Oracle OCILobGetLength function.
-
-- ora\_lob\_is\_init
-
- $is_init = $dbh->ora_lob_is_init($lob_locator);
-
- Returns true(1) if the Lob Locator is initialized false(0) if it is not, or 'undef'
- if there is an error.
- Uses the Oracle OCILobLocatorIsInit function.
-
-- ora\_lob\_chunk\_size
-
- $chunk_size = $dbh->ora_lob_chunk_size($lob_locator);
-
- Returns the chunk size of the LOB.
- Uses the Oracle OCILobGetChunkSize function.
-
- For optimal performance, Oracle recommends reading from and
- writing to a LOB in batches using a multiple of the LOB chunk size.
- In Oracle 10g and before, when all defaults are in place, this
- chunk size defaults to 8k (8192).
-
-### LOB Locator Method Examples
-
-_Note:_ Make sure you first read the note in the section above about -multi-byte character set issues with these methods.
-
-The following examples demonstrate the usage of LOB Locators
-to read, write, and append data, and to query the size of
-large data.
-
-The following examples assume a table containing two large
-object columns, one binary and one character, with a primary
-key column, defined as follows:
-
- CREATE TABLE lob_example (
- lob_id INTEGER PRIMARY KEY,
- bindata BLOB,
- chardata CLOB
- )
-
-It also assumes a sequence for use in generating unique
-lob\_id field values, defined as follows:
-
- CREATE SEQUENCE lob_example_seq
-
-### Example: Inserting a new row with large data
-
-Unless enough memory is available to store and bind the
-entire LOB data for insert all at once, the LOB columns must
-be written interactively, piece by piece. In the case of a new row,
-this is performed by first inserting a row, with empty values in
-the LOB columns, then modifying the row by writing the large data -interactively to the LOB columns using their LOB locators as handles.
-
-The insert statement must create token values in the LOB
-columns. Here, we use the empty string for both the binary
-and character large object columns 'bindata' and 'chardata'.
-
-After the INSERT statement, a SELECT statement is used to
-acquire LOB locators to the 'bindata' and 'chardata' fields
-of the newly inserted row. Because these LOB locators are
-subsequently written, they must be acquired from a select
-statement containing the clause 'FOR UPDATE' (LOB locators
-are only valid within the transaction that fetched them, so
-can't be used effectively if AutoCommit is enabled).
-
- my $lob_id = $dbh->selectrow_array( <<" SQL" );
- SELECT lob_example_seq.nextval FROM DUAL
- SQL
-
- my $sth = $dbh->prepare( <<" SQL" );
- INSERT INTO lob_example
- ( lob_id, bindata, chardata )
- VALUES ( ?, EMPTY_BLOB(),EMPTY_CLOB() )
- SQL
- $sth->execute( $lob_id );
-
- $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
- SELECT bindata, chardata
- FROM lob_example
- WHERE lob_id = ?
- FOR UPDATE
- SQL
- $sth->execute( $lob_id );
- my ( $bin_locator, $char_locator ) = $sth->fetchrow_array();
- $sth->finish();
-
- open BIN_FH, "/binary/data/source" or die;
- open CHAR_FH, "/character/data/source" or die;
- my $chunk_size = $dbh->ora_lob_chunk_size( $bin_locator );
-
- # BEGIN WRITING BIN_DATA COLUMN
- my $offset = 1; # Offsets start at 1, not 0
- my $length = 0;
- my $buffer = '';
- while( $length = read( BIN_FH, $buffer, $chunk_size ) ) {
- $dbh->ora_lob_write( $bin_locator, $offset, $buffer );
- $offset += $length;
- }
-
- # BEGIN WRITING CHAR_DATA COLUMN
- $chunk_size = $dbh->ora_lob_chunk_size( $char_locator );
- $offset = 1; # Offsets start at 1, not 0
- $length = 0;
- $buffer = '';
- while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
- $dbh->ora_lob_write( $char_locator, $offset, $buffer );
- $offset += $length;
- }
-
-In this example we demonstrate the use of ora\_lob\_write()
-interactively to append data to the columns 'bin\_data' and
-'char\_data'. Had we used ora\_lob\_append(), we could have
-saved ourselves the trouble of keeping track of the offset
-into the lobs. The snippet of code beneath the comment
-'BEGIN WRITING BIN\_DATA COLUMN' could look as follows:
-
- my $buffer = '';
- while ( read( BIN_FH, $buffer, $chunk_size ) ) {
- $dbh->ora_lob_append( $bin_locator, $buffer );
- }
-
-The scalar variables $offset and $length are no longer
-needed, because ora\_lob\_append() keeps track of the offset
-for us.
-
-### Example: Updating an existing row with large data
-
-In this example, we demonstrate a technique for overwriting
-a portion of a blob field with new binary data. The blob
-data before and after the section overwritten remains
-unchanged. Hence, this technique could be used for updating
-fixed length subfields embedded in a binary field.
-
- my $lob_id = 5; # Arbitrary row identifier, for example
-
- $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
- SELECT bindata
- FROM lob_example
- WHERE lob_id = ?
- FOR UPDATE
- SQL
- $sth->execute( $lob_id );
- my ( $bin_locator ) = $sth->fetchrow_array();
-
- my $offset = 100234;
- my $data = "This string will overwrite a portion of the blob";
- $dbh->ora_lob_write( $bin_locator, $offset, $data );
-
-After running this code, the row where lob\_id = 5 will
-contain, starting at position 100234 in the bin\_data column,
-the string "This string will overwrite a portion of the blob".
-
-### Example: Streaming character data from the database
-
-In this example, we demonstrate a technique for streaming
-data from the database to a file handle, in this case
-STDOUT. This allows more data to be read in and written out
-than could be stored in memory at a given time.
-
- my $lob_id = 17; # Arbitrary row identifier, for example
-
- $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
- SELECT chardata
- FROM lob_example
- WHERE lob_id = ?
- SQL
- $sth->execute( $lob_id );
- my ( $char_locator ) = $sth->fetchrow_array();
-
- my $chunk_size = 1034; # Arbitrary chunk size, for example
- my $offset = 1; # Offsets start at 1, not 0
- while(1) {
- my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size );
- last unless length $data;
- print STDOUT $data;
- $offset += $chunk_size;
- }
-
-Notice that the select statement does not contain the phrase
-"FOR UPDATE". Because we are only reading from the LOB
-Locator returned, and not modifying the LOB it refers to,
-the select statement does not require the "FOR UPDATE"
-clause.
-
-A word of caution when using the data returned from an ora\_lob\_read in a conditional statement.
-for example if the code below;
-
- while( my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size ) ) {
- print STDOUT $data;
- $offset += $chunk_size;
- }
-
-was used with a chunk size of 4096 against a blob that requires more than 1 chunk to return
-the data and the last chunk is one byte long and contains a zero (ASCII 48) you will miss this last byte
-as $data will contain 0 which PERL will see as false and not print it out.
-
-### Example: Truncating existing large data
-
-In this example, we truncate the data already present in a
-large object column in the database. Specifically, for each
-row in the table, we truncate the 'bindata' value to half
-its previous length.
-
-After acquiring a LOB Locator for the column, we query its
-length, then we trim the length by half. Because we modify
-the large objects with the call to ora\_lob\_trim(), we must
-select the LOB locators 'FOR UPDATE'.
-
- my $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
- SELECT bindata
- FROM lob_example
- FOR UPATE
- SQL
- $sth->execute();
- while( my ( $bin_locator ) = $sth->fetchrow_array() ) {
- my $binlength = $dbh->ora_lob_length( $bin_locator );
- if( $binlength > 0 ) {
- $dbh->ora_lob_trim( $bin_locator, $binlength/2 );
- }
- }
-
-# SPACES AND PADDING
-
-## Trailing Spaces
-
-Only the Oracle OCI 8 strips trailing spaces from VARCHAR placeholder
-values and uses Nonpadded Comparison Semantics with the result.
-This causes trouble if the spaces are needed for
-comparison with a CHAR value or to prevent the value from
-becoming '' which Oracle treats as NULL.
-Look for Blank-padded Comparison Semantics and Nonpadded
-Comparison Semantics in Oracle's SQL Reference or Server
-SQL Reference for more details.
-
-To preserve trailing spaces in placeholder values for Oracle clients that use OCI 8,
-either change the default placeholder type with ["ora\_ph\_type"](#ora_ph_type) or the placeholder
-type for a particular call to ["bind" in DBI](https://metacpan.org/pod/DBI#bind) or ["bind\_param\_inout" in DBI](https://metacpan.org/pod/DBI#bind_param_inout)
-with ["ora\_type"](#ora_type) or `TYPE`.
-Using [ORA\_CHAR](https://metacpan.org/pod/ORA_CHAR) with [ora\_type](https://metacpan.org/pod/ora_type) or `SQL_CHAR` with `TYPE`
-allows the placeholder to be used with Padded Comparison Semantics
-if the value it is being compared to is a CHAR, NCHAR, or literal.
-
-Please remember that using spaces as a value or at the end of
-a value makes visually distinguishing values with different
-numbers of spaces difficult and should be avoided.
-
-Oracle Clients that use OCI 9.2 do not strip trailing spaces.
-
-## Padded Char Fields
-
-Oracle Clients after OCI 9.2 will automatically pad CHAR placeholder values to the size of the CHAR.
-As the default placeholder type value in DBD::Oracle is ORA\_VARCHAR2 to access this behaviour you will
-have to change the default placeholder type with ["ora\_ph\_type"](#ora_ph_type) or placeholder
-type for a particular call with ["bind" in DBI](https://metacpan.org/pod/DBI#bind) or ["bind\_param\_inout" in DBI](https://metacpan.org/pod/DBI#bind_param_inout)
-with ["ORA\_CHAR"](#ora_char).
-
-# UNICODE
-
-DBD::Oracle now supports Unicode UTF-8. There are, however, a number
-of issues you should be aware of, so please read all this section
-carefully.
-
-In this section we'll discuss "Perl and Unicode", then "Oracle and
-Unicode", and finally "DBD::Oracle and Unicode".
-
-Information about Unicode in general can be found at: -[http://www.unicode.org/](http://www.unicode.org/). It is well worth reading because there are
-many misconceptions about Unicode and you may be holding some of them.
-
-## Perl and Unicode
-
-Perl began implementing Unicode with version 5.6, but the implementation
-did not mature until version 5.8 and later. If you plan to use Unicode
-you are _strongly_ urged to use Perl 5.8.2 or later and to _carefully_ read -the Perl documentation on Unicode:
-
- perldoc perluniintro # in Perl 5.8 or later
- perldoc perlunicode
-
-And then read it again.
-
-Perl's internal Unicode format is UTF-8
-which corresponds to the Oracle character set called AL32UTF8.
-
-## Oracle and Unicode
-
-Oracle supports many characters sets, including several different forms
-of Unicode. These include:
-
- AL16UTF16 => valid for NCHAR columns (CSID=2000)
- UTF8 => valid for NCHAR columns (CSID=871), deprecated
- AL32UTF8 => valid for NCHAR and CHAR columns (CSID=873)
-
-When you create an Oracle database, you must specify the DATABASE
-character set (used for DDL, DML and CHAR datatypes) and the NATIONAL -character set (used for NCHAR and NCLOB types).
-The character sets used in your database can be found using:
-
- $hash_ref = $dbh->ora_nls_parameters()
- $database_charset = $hash_ref->{NLS_CHARACTERSET};
- $national_charset = $hash_ref->{NLS_NCHAR_CHARACTERSET};
-
-The Oracle 9.2 and later default for the national character set is AL16UTF16. -The default for the database character set is often US7ASCII.
-Although many experienced DBAs will consider an 8bit character set like -WE8ISO8859P1 or WE8MSWIN1252. To use any character set with Oracle
-other than US7ASCII, requires that the NLS\_LANG environment variable be set. -See the ["Oracle UTF8 is not UTF-8"](#oracle-utf8-is-not-utf-8) section below. -
-You are strongly urged to read the Oracle Internationalization documentation -specifically with respect the choices and trade offs for creating
-a databases for use with international character sets.
-
-Oracle uses the NLS\_LANG environment variable to indicate what
-character set is being used on the client. When fetching data Oracle
-will convert from whatever the database character set is to the client -character set specified by NLS\_LANG. Similarly, when sending data to
-the database Oracle will convert from the character set specified by -NLS\_LANG to the database character set.
-
-The NLS\_NCHAR environment variable can be used to define a different -character set for 'national' (NCHAR) character types.
-

[continued in next message]

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