Hi!
I am trying now to debug the system crashes caused by DBD::Oracle test
suite under IRIX, but I need help in understanding the potentially
offending PL/SQL code. Here is part of the sources of t/plsql.t that
blows up our server machine, with my comments added at places that I
don't understand clear enough:
# Start of code excerpt: line 232 of t/plsql.t from DBD-Oracle-1.12
# --- test cursor variables
if (1) {
my $cur_query = q{
SELECT object_name, owner
FROM all_objects
WHERE object_name LIKE :p1
ORDER BY object_name
};
#
# What does this magic number 42 means and why it is
# assigned to $cur1 below? Does not make any sense to me
# since I can't see how it could be of use in OPEN :cur1 ...
# statement below. Looks harmless anyway, just curious.
#
my $cur1 = 42;
#$dbh->trace(4);
my $parent = $dbh->prepare(qq{
BEGIN OPEN :cur1 FOR $cur_query; END;
});
ok(0, $parent, $DBI::errstr);
ok(0, $parent->bind_param(":p1", "V%"));
#
# Is it legal to pass 0 (zero) as the variable size value
# (3d parameter) to bind_param_inout() here? Is it legal
# to pass zero in this place in *any* case??? Could this
# be that this is trashing system and/or Perl memory
# allocation routines later? However, this same code
# worked just fine in 1.06-1.07 as tested with
# Oracle 8.0.5 to 8.1.6...
#
ok(0, $parent->bind_param_inout(":cur1", \$cur1, 0,
{ ora_type => ORA_RSET } ));
#
# The next line below causes our server to crash hard,
# while all previous tests run just fine.
#
ok(0, $parent->execute());
my @r;
push @r, @tmp while @tmp = $cur1->fetchrow_array;
ok(0, @r>0, "rows: ".@r);
#$dbh->trace(0); $parent->trace(0);
# compare results with normal execution of query
my $s1 = $dbh->selectall_arrayref($cur_query, undef, "V%");
my @s1 = map { @$_ } @$s1;
ok(0, "@r" eq "@s1", "\nref=(@r),\nsql=(@s1)");
#
# Never reached the part below so can't say if it is equally
# dangerous or not.
#
# --- test re-bind and re-execute of same 'parent' statement
my $cur1_str = "$cur1";
#$dbh->trace(4); $parent->trace(4);
ok(0, $parent->bind_param(":p1", "U%"));
ok(0, $parent->execute());
ok(0, "$cur1" ne $cur1_str); # must be ref to new handle object
@r = ();
push @r, @tmp while @tmp = $cur1->fetchrow_array;
#$dbh->trace(0); $parent->trace(0); $cur1->trace(0);
my $s2 = $dbh->selectall_arrayref($cur_query, undef, "U%");
my @s2 = map { @$_ } @$s2;
ok(0, "@r" eq "@s2", "\nref=(@r),\nsql=(@s2)");
}
Overall, the code looks pretty trivial and harmless, except for this
zero value passed as a min. variable length buffer size to
bind_param_inout() the meaning of which I can't understand.
I have also compared this code to the same part as existed in
DBD-Oracle-1.06 and 1.07, the only thing that has changed is that in
versions previous to 1.08 the actual select statement used for cursor
testing included additional conditional expression in WHERE clause: AND
ROWID < 3. Thus the amount of potential SELECT results was very limited,
while now about 395 rows are reterned for our installation (e.g., all
system object names starting with 'V'). This again points to some
potential memory allocation problem, but even then I can't see how this
could lead to such terrible system failure.
I would really like to hear comments from people more verse in PL/SQL
programming and working with cursors in Oracle, either from within Perl
or not. Unfortunately, I've never used PL/SQL before other than for very
simple maintenance scripts run from SQLPlus, and most of these were
written by other people, not me.
Thanks in advance,
Ivan Adzhubei
---
How many QA engineers does it take to screw in a lightbulb?
3: 1 to screw it in and 2 to say "I told you so" when it doesn't work.
