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;
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.