Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

DBD Oracle ORA_XMLTYPE Seg Fault

by grump- (Novice)
on Dec 24, 2012 at 17:44 UTC ( #1010220=perlquestion: print w/ replies, xml ) Need Help??
grump- has asked for the wisdom of the Perl Monks concerning the following question:

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.

Comment on DBD Oracle ORA_XMLTYPE Seg Fault
Select or Download Code
Re: DBD Oracle ORA_XMLTYPE Seg Fault
by Anonymous Monk on Dec 24, 2012 at 17:49 UTC

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

Re: DBD Oracle ORA_XMLTYPE Seg Fault
by dave_the_m (Parson) on Dec 25, 2012 at 12:57 UTC
    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.

      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?
        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.

Re: DBD Oracle ORA_XMLTYPE Seg Fault
by karlgoethebier (Curate) on Dec 26, 2012 at 20:01 UTC

    Perhaps you like to provide your full code example?

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

    Best regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

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

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

        Best regards, Karl

        «The Crux of the Biscuit is the Apostrophe»

Re: DBD Oracle ORA_XMLTYPE Seg Fault
by runrig (Abbot) on Jan 02, 2013 at 19:13 UTC
    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.
      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;

        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.

      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?
        Not sure, maybe binding ':out' as a LOB (making sure LongReadLen attribute is sufficiently large)? Or abusing dbms_output to output the value?
        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...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1010220]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (7)
As of 2014-10-23 08:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (125 votes), past polls