| How to SELECT XMLTYPE data with Perl DBD::Oracle? [message #641583] |
Tue, 18 August 2015 08:27  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 3989 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  |
ThomasG
Messages: 3212
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. )
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
