Home » Open Source » Programming Interfaces » How to SELECT XMLTYPE data with Perl DBD::Oracle? ((Oracle: 10.2-11.2, Perl: 5.20.2, DBI: 1.633, DBD::Oracle: 1.74))
How to SELECT XMLTYPE data with Perl DBD::Oracle? [message #641583] Tue, 18 August 2015 08:27 Go to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can simply select an XMLTYPE column in SQL*Plus like:
SQL> select  WAREHOUSE_SPEC
  2  from OE.WAREHOUSES
  3  where WAREHOUSE_SPEC is not null and rownum = 1
  4  /
WAREHOUSE_SPEC
--------------------------------------------------------
<?xml version="1.0"?>
<Warehouse>
  <Building>Owned</Building>
  <Area>25000</Area>
  <Docks>2</Docks>
  <DockType>Rear load</DockType>
  <WaterAccess>Y</WaterAccess>
  <RailAccess>N</RailAccess>
  <Parking>Street</Parking>
  <VClearance>10 ft</VClearance>
</Warehouse>

This is a shorcut for the more correct:
select W.WAREHOUSE_SPEC.getclobval() WAREHOUSE_SPEC
from OE.WAREHOUSES W
where WAREHOUSE_SPEC is not null and rownum = 1
/

Now without changing the first query into the second one, is it possible to get the XML string with Perl DBD::Oracle?

I find one page on the web saying "don't waste your time to search on the web, it is not possible" and another one saying "I succeeded to do it binding a variable with attribute SQLT_STR type" but he did not show the code.
I tried with many different types and failed.

I attach a sample of code if you want to try by yourself.

[Cross-ref: http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=78184]

  • Attachment: t7.pl
    (Size: 1.84KB, Downloaded 3090 times)

[Updated on: Tue, 18 August 2015 08:41]

Report message to a moderator

Re: How to SELECT XMLTYPE data with Perl DBD::Oracle? [message #642303 is a reply to message #641583] Mon, 07 September 2015 07:15 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Interesting. It seems to work on an old version I still have around (ActiveState Perl 5.6 on Windows, DBD::Oracle compiled for the 9.2.0.8 client) The only thin I had to change was to comment out the not supported "PrintWarn" in that versions.

Your Original Script:

G:\tga>perl t7.pl
Can't set DBI::db=HASH(0x40564dc)->{PrintWarn}: unrecognised attribute at C:/Pro
gramme/perl/site/lib/DBI.pm line 437.
Issuing rollback() for database handle being DESTROY'd without explicit disconne
ct().


PrintWarn commented out, select change to "SELECT data FROM REPORT_FILES WHERE FILENAME = 'report.xsd'":

G:\tga>perl t7.pl
XML: <?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="quali
fied" attributeFormDefault="unqualified">
  <xs:include schemaLocation="file://rdbms/xml/orarep/report_ref.xsd" />
    <!-- Base type declaration for top-level report type.
         Clients should create their report elements as extensions
         off this type -->
    <xs:complexType name="reportType">
      <xs:sequence>
        <xs:element name="report_id" type="reportRefType" />
      </xs:sequence>
      <xs:attribute name="db_version" use="required" type="xs:string"/>
    </xs:complexType>
</xs:schema>

Dump: $VAR1 = '<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="quali
fied" attributeFormDefault="unqualified">
  <xs:include schemaLocation="file://rdbms/xml/orarep/report_ref.xsd" />
    <!-- Base type declaration for top-level report type.
         Clients should create their report elements as extensions
         off this type -->
    <xs:complexType name="reportType">
      <xs:sequence>
        <xs:element name="report_id" type="reportRefType" />
      </xs:sequence>
      <xs:attribute name="db_version" use="required" type="xs:string"/>
    </xs:complexType>
</xs:schema>
';



On any newer versions I have tried it doesn't. ( I assume that the old version is from before they added the XML_TYPE insert capability. )
Re: How to SELECT XMLTYPE data with Perl DBD::Oracle? [message #642304 is a reply to message #642303] Mon, 07 September 2015 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, interesting, thanks for the tests.
What is your version of DBD::Oracle (the one which works)?
You can check it at top of oracle.pm file, "DBD::Oracle::VERSION" variable.

Re: How to SELECT XMLTYPE data with Perl DBD::Oracle? [message #642307 is a reply to message #642304] Mon, 07 September 2015 08:31 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
DBD:Oracle version: DBD::Oracle::VERSION = '1.06';

DBI Version: $DBI::VERSION = "1.14";

(It's on a old Windows 2000 box we can't get rid of because of Legacy Software)
Re: How to SELECT XMLTYPE data with Perl DBD::Oracle? [message #642308 is a reply to message #642307] Mon, 07 September 2015 08:50 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks, I will check if I have something like that in my archives.

Previous Topic: How to get SELECT columns size with Perl DBI+DBD::Oracle?
Next Topic: Downloaded 114 BLOBS using PHP before getting ORA-12537: TNS Connection Closed
Goto Forum:
  


Current Time: Thu Mar 28 10:26:08 CDT 2024