http://www.perlmonks.org?node_id=1011336


in reply to DBD Oracle ORA_XMLTYPE Seg Fault

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();

[download]
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.

Replies are listed 'Best First'.
Re^2: DBD Oracle ORA_XMLTYPE Seg Fault
by grump- (Novice) on Jan 03, 2013 at 15:30 UTC
    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;

    [download]
[reply]
[d/l]

      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.

[reply]
Re^2: DBD Oracle ORA_XMLTYPE Seg Fault
by grump- (Novice) on Jan 11, 2013 at 18:03 UTC
    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?
[reply]
      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...

[reply]
      Not sure, maybe binding ':out' as a LOB (making sure LongReadLen attribute is sufficiently large)? Or abusing dbms_output to output the value?
[reply]
        I don't believe the 4k limitation is in Perl. Its in the xmltype and/or the getclobval method. Read the last entry here: https://forums.oracle.com/forums/thread.jspa?messageID=1696372 . So I think I need another work around in the query. One that continues to hide the xmltype from the Perl client.
[reply]

In Section Seekers of Perl Wisdom