Author: byterock
Date: Tue Jan 19 10:35:30 2010
New Revision: 13746
Modified:
dbd-oracle/trunk/Oracle.pm
dbd-oracle/trunk/t/58object.t
Log:
a new patch from Charles Jardine for testing objects and some clean up on the
pod
Modified: dbd-oracle/trunk/Oracle.pm
==============================================================================
--- dbd-oracle/trunk/Oracle.pm (original)
+++ dbd-oracle/trunk/Oracle.pm Tue Jan 19 10:35:30 2010
@@ -1478,11 +1478,12 @@
=item ora_ncs_buff_mtpl
You can now customize the size of the buffer when selecting LOBs with
-the built in AUTO Lob. The default value is 1 which should be fine
-for most situations. If you are converting between a NCS on the DB and
-one on the Client then you might want to set this to 2. The orignal
-value (prior to version 1.24) of 4 was found to be excessive. For
-convenience I have added support for a 'ORA_DBD_NCS_BUFFER'
+the built in AUTO Lob. The default value is 4 which should is actully
exessive
+for most situations but is needed for backward compatibilty.
+If you not converting between a NCS on the DB and the Client then you might
+want to set this to 1 to free up memory.
+
+For convenience I have added support for a 'ORA_DBD_NCS_BUFFER'
environment variable that you can use at the OS level to set this
value. If used it will take the value at the connect stage.
@@ -3145,38 +3146,6 @@
The maximum value of 'LongReadLen' seems to be dependant 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.
-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 compatable 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. In versions
-earlier than 1.24 this buffer was always 4 * LONG_READ_LEN which was very
wasteful so you might of asking for only
-10_000_000 bytes but you were actually using 40_000_000 bytes 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 set to 4 for backward compatiblty. You can lower this
value and thus increase the amount of data you can retreive. If the
-ora_ncs_buff_mtpl is too small DBD::Oracle will thow and error telling you to
increase this buffer by one.
-
-If the error is not captured then you may get
-
- ORA-03127: no new operations allowed until the active operation ends
-
-which is one of the more obscure ORA errors (have some fun and report it to
Meta-Link they will scratch their heads for hours) at some random point later
on,
-usually at a finish() or disconnect() or even a fetch().
-
-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 menory.
-
-You can tune this value by setting ora_oci_success_warn which will display the
following
-
- 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 we 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.
-
For example give this table;
CREATE TABLE test_long (
@@ -3200,6 +3169,40 @@
before the execute will return all the long1 fields but they will be truncated
at 2MBs.
+=head3 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 compatable 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 compatiblty. You can lower
this value and thus increase the amount of data you can retreive. If the
+ora_ncs_buff_mtpl is too small DBD::Oracle will thow 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 menory.
+
+You can tune this value by setting ora_oci_success_warn which will display the
following
+
+ 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.
+
+
=head3 Simple Fetch for CLOBs and BLOBs
To use this interface for CLOBs and LOBs datatypes set the 'ora_pers_lob'
attribute of the statement handle to '1' with the prepare method, as well
Modified: dbd-oracle/trunk/t/58object.t
==============================================================================
--- dbd-oracle/trunk/t/58object.t (original)
+++ dbd-oracle/trunk/t/58object.t Tue Jan 19 10:35:30 2010
@@ -5,7 +5,7 @@
use strict;
use Data::Dumper;
-use Test::More tests => 35;
+use Test::More tests => 51;
unshift @INC ,'t';
require 'nchar_test_lib.pl';
@@ -46,9 +46,16 @@
my $super_type = "${obj_prefix}_type_A";
my $sub_type = "${obj_prefix}_type_B";
my $table = "${obj_prefix}_obj_table";
+my $outer_type = "${obj_prefix}_outer_type";
+my $inner_type = "${obj_prefix}_inner_type";
+my $list_type = "${obj_prefix}_list_type";
+my $nest_table = "${obj_prefix}_nest_table";
+my $list_table = "${obj_prefix}_list_table";
sub drop_test_objects {
- for my $obj ("TABLE $table", "TYPE $sub_type", "TYPE $super_type") {
+ for my $obj ("TABLE $list_table", "TABLE $nest_table",
+ "TYPE $list_type", "TYPE $outer_type", "TYPE $inner_type",
+ "TABLE $table", "TYPE $sub_type", "TYPE $super_type") {
#do not warn if already there
eval {
local $dbh->{PrintError} = 0;
@@ -79,6 +86,30 @@
$dbh->do(qq{ INSERT INTO $table VALUES (3, $sub_type(5, 'obj3', NULL,
777.666)) }
) or die $dbh->errstr;
+$dbh->do(qq{ CREATE OR REPLACE TYPE $inner_type AS OBJECT (
+ num INTEGER,
+ name VARCHAR2(20)
+ ) FINAL }) or die $dbh->errstr;
+$dbh->do(qq{ CREATE OR REPLACE TYPE $outer_type AS OBJECT (
+ num INTEGER,
+ obj $inner_type
+ ) FINAL }) or die $dbh->errstr;
+$dbh->do(qq{ CREATE OR REPLACE TYPE $list_type AS
+ TABLE OF $inner_type }) or die $dbh->errstr;
+$dbh->do(qq{ CREATE TABLE $nest_table(obj $outer_type) }) or die $dbh->errstr;
+$dbh->do(qq{ INSERT INTO $nest_table VALUES($outer_type(91, $inner_type(1,
'one'))) }
+ ) or die $dbh->errstr;
+$dbh->do(qq{ INSERT INTO $nest_table VALUES($outer_type(92, $inner_type(0,
null))) }
+ ) or die $dbh->errstr;
+$dbh->do(qq{ INSERT INTO $nest_table VALUES($outer_type(93, null)) }
+ ) or die $dbh->errstr;
+
+$dbh->do(qq{ CREATE TABLE $list_table ( id INTEGER, list $list_type )
+ NESTED TABLE list STORE AS ${list_table}_list }) or die
$dbh->errstr;
+$dbh->do(qq{ INSERT INTO $list_table VALUES(81,$list_type($inner_type(null,
'listed'))) }
+ ) or die $dbh->errstr;
+
+
# Test old (backward compatible) interface
# test select testing objects
@@ -152,6 +183,42 @@
is_deeply($obj->attr, $expected_hash, 'DBD::Oracle::Object->attr');
is($obj->attr("NAME"), 'obj3', 'DBD::Oracle::Object->attr("NAME")');
+# try the list table
+$sth = $dbh->prepare("select * from $list_table");
+ok ($sth, 'new: Prepare select with nested table of objects');
+ok ($sth->execute(), 'new: Execute (nested table)');
+
+...@row1 = $sth->fetchrow();
+ok (scalar @row1, 'new: Fetch first row (nested table)');
+is_deeply($row1[1]->[0]->attr, {NUM=>undef, NAME=>'listed'},
+ 'Check propertes of first (and only) item in nested table');
+
+ok (!$sth->fetchrow(), 'new: No more rows expected (nested table)');
+
+#try the nested table
+$sth = $dbh->prepare("select * from $nest_table");
+ok ($sth, 'new: Prepare select with nested object');
+ok ($sth->execute(), 'new: Execute (nested object)');
+
+...@row1 = $sth->fetchrow();
+ok (scalar @row1, 'new: Fetch first row (nested object)');
+is($row1[0]->attr->{NUM}, '91', 'Check obj.num');
+is_deeply($row1[0]->attr->{OBJ}->attr, {NUM=>'1', NAME=>'one'}, 'Check
obj.obj');
+
+...@row2 = $sth->fetchrow();
+ok (scalar @row2, 'new: Fetch second row (nested object)');
+is($row2[0]->attr->{NUM}, '92', 'Check obj.num');
+is_deeply($row2[0]->attr->{OBJ}->attr, {NUM=>'0', NAME=>undef}, 'Check
obj.obj');
+
+...@row3 = $sth->fetchrow();
+ok (scalar @row3, 'new: Fetch third row (nested object)');
+is_deeply($row3[0]->attr, {NUM=>'93', OBJ=>undef}, 'Check obj');
+
+ok (!$sth->fetchrow(), 'new: No more rows expected (nested object)');
+
+#print STDERR Dumper(\...@row1, \...@row2, \...@row3);
+
+
#cleanup
&drop_test_objects;
$dbh->disconnect;
