DBD Oracle ORA_XMLTYPE Seg Fault

[grump-]

Here is the stored procedure:

PROCEDURE TEST
(
  PARAM1 IN VARCHAR2,
  PARAM2 OUT XMLTYPE
);

And here is the Perl having trouble:

#!/usr/bin/perl
use DBI;
use DBD::Oracle qw(:ora_types);
use Data::Dumper;

my $dbh = DBI->connect( "dsn", "user", "pass", { RaiseError => 1 } );
$dbh->trace(15,"trace");

my $query = $dbh->prepare( qq{BEGIN schema.package.TEST('blah', ?); co
+mmit; END;});

my $xml;
$query->bind_param_inout(1, \$xml, 32000, { ora_type => ORA_XMLTYPE } 
+);
$query->execute();
$dbh->DESTROY;
undef $dbh;

print Dumper($xml);

The binding causes a segmentation fault and never returns, even when wrapped in an eval statement. Here is the last text in the trace:

-> bind_param_inout for DBD::Oracle::st (DBI::st=HASH(0x18d6828)~0x18d
+67c8 1 SCALAR(0x183ddac) 32000 HASH(0x18d67bc))
dbd_bind_ph(): bind :p1 <== undef (type 0 (DEFAULT (varchar)), inout 0
+x183ddac, maxlen 32000, attribs: HASH(0x18d67bc))
dbd_rebind_ph() (1): rebinding :p1 as NULL (not-utf8, ftype 108 (ORA_X
+MLTYPE or SQLT_NTY), csid 0, csform 0, inout 1)
 in  dbd_rebind_ph_xml

Can anyone tell me if I am doing something wrong to get this XMLTYPE out of the stored procedure? I should add that the names of the innocent have been protected in the above code and that I should tell you that this runs in an Apache/MOD Perl environment (I have modified the displayed code to ignore the web bits). Additionally, we are running DBD version 1.42 with Oracle on the 10 version. I should also note that I am able to execute the procedure using Oracle Sql Developer.

[dave_the_m]

A seg fault implies a bug in one of perl, DBI, DBD::Oracle, or the Oracle drivers (rather than in your code). If possible, upgrade to the newest version of each of those, and if the problem doesn't go away, report it as a bug: probably to the DBD::Oracle maintainers initially.

Dave.

[grump-]

Upgraded the DBD::Oracle to 1.52. It still seg faults. I should also mention that I do not have control over the Oracle end. Does the output need to be Perl friendly xml? Or can it just be any kind of string?

[dave_the_m]

Upgraded the DBD::Oracle to 1.52. It still seg faults.

So you haven't tried upgrading perl, DBI, or the oracle drivers? What versions are they currently at?

I should also mention that I do not have control over the Oracle end. Does the output need to be Perl friendly xml? Or can it just be any kind of string?

I don't know what "output" you are referring to. Note that I know nothing about Oracle's XML and stored procedures etc; I commented in this thread purely to point out that a segfault implies a bug in some part of the perl system you are using, rather than a bug in your code itself (which is not to say your code isn't buggy too; I have no idea about that.)

Dave.

[Anonymous Monk]

I can't help but FYI, eval doesn't trap segmentation faults, they happen in C-land

[runrig]

Here is what works for me:

my $sth = $dbh->prepare(<<SQL);
DECLARE
  v_xml XMLTYPE;
BEGIN
  schema.package.TEST('foo', v_xml);
  :out := xmltype.getclobval(v_xml);
END;
SQL

$sth->bind_param_inout(':out', \my $xml, 32000);

$sth->execute();

I've noticed that for XMLTYPE output, Perl seems to need the explicit conversions to string whereas SQL Developer (and probably other SQL query tools) seem to do them automagically.

[grump-]

How delightful! I had been dancing around the getclobval method but could not make it work with the stored procedure. Syntax is always in the way! Thank you very much! I would gladly become your Kappiya if you would have me. On another note for any on lookers, and I hope I have this right, if v_xml is null, getclobval chokes. I ended up wrapping the getclobval line with an if statement:

if v_xml is not null then
:out := xmltype.getclobval(v_xml);
end if;

[mje]

You might want to read XMLTYPE which says:

XMLType is a system-defined opaque type for handling XML data. It as predefined member functions on it to extract XML nodes and fragments.

You cannot call the member function getclobval on a NULL.

[grump-]

First I want to thank you for the working response. Now, in testing, we are finding that when the data set is large, either the xmltype or the getclobval method fail. Indeed looking around the internet other folks have run into a 4k limitation. Any help on how to get around this one?

[runrig]

I just tested selecting an XMLTYPE column over 32K in size and it worked ok. But for inserting, there seems to be a 4K limit, and even with sqlldr I'm having trouble loading really large documents. Since you need to bind an in/out parameter, maybe you hit that limit? Perhaps create a PL/SQL block that returns a cursor for selecting your data, and then select from that cursor?

Update: Selecting an XMLTYPE column over about 50K gives me an ORA-12152 (~50K) or ORA-0600 (> ~64K) error on $dbh->disconnect...but it does select the data. Weird...

[runrig]

Not sure, maybe binding ':out' as a LOB (making sure LongReadLen attribute is sufficiently large)? Or abusing dbms_output to output the value?

[karlgoethebier]

Perhaps you like to provide your full code example?

BEGIN --Full Query; --simplified version...? END

Best regards, Karl

[grump-]

This is the full code. Just changed the names to generic so I don't disclose information.

[karlgoethebier]

OK, thanks. Sorry i don't have any other idea. I'm assuming that you have a BEGIN..END block ;-)

Best regards, Karl