I have been playing with this as of late and the
Short answer is no with a but; and the
Long answer is yes with a maybe.
Right now with OCI (and hence DBD::Oracle as that is what is it written
in) it is not possiable to directly select a xmltype.
you have one of two options
1) use the XML C Library that oracle provides and select it into one of
their structures and then into OCI and then into Perl
Not very piratical with DBD::Oracle as you would have to include the
right lib etc which would be a nightmare and might break some copyright etc/
2) do as you suggest use 'xmltype.getclobval' in the SQL to get the data
as a clob.
That is the short No answer.
Now for the long YES answer
we already use OCIXMLTypeCreateFromSrc to create an XML type from text
there are part of a number of other 'secret/undocumented' functions in OCI
which I have listed at the end of the doc.
Having played with them they look like they do the same sorts of things
that the 'xmltype' SQL function do but as they are undocumented I have
not had much luck with them.
If we could get some examples of the code in use I could build this
right into DBD::Oracle but so far I have not managed to get any of the
functions to work correctly.
don't bother searching the web for these things as they are not there.
So unless some volunteers with some code come up or some docs I am not
going to be spending much more time with it.
Cheers
John Scoles
'secret/undocumented' XML functions in OCI
sword OCIXMLTypeNew(OCISvcCtx *svchp, OCIError *errhp, OCIDuration dur,
OraText *elname, ub4 elname_Len,
OraText *schemaURL, ub4 schemaURL_Len,
OCIXMLType **retInstance);
sword OCIXMLTypeCreateFromSrc(OCISvcCtx *svchp, OCIError *errhp,
OCIDuration dur, ub1 src_type, dvoid *src_ptr,
sb4 ind, OCIXMLType **retInstance);
sword OCIXMLTypeCreateFromSrcWithSchema(OCISvcCtx *svchp, OCIError *errhp,
OCIDuration dur, ub1 src_type, dvoid *src_ptr,
sb4 ind, OraText *schemaURL, ub4 schemaURL_Len,
boolean wellformed, boolean valid,
OCIXMLType **retInstance);
sword OCIXMLTypeExtract(OCIError *errhp,
OCIXMLType *doc, OCIDuration dur,
OraText *xpathexpr, ub4 xpathexpr_Len,
OraText *nsmap, ub4 nsmap_Len,
OCIXMLType **retDoc);
sword OCIXMLTypeTransform(OCIError *errhp, OCIDuration dur,
OCIXMLType *doc, OCIXMLType *xsldoc,
OCIXMLType **retDoc);
sword OCIXMLTypeExists(OCIError *errhp, OCIXMLType *doc,
OraText *xpathexpr, ub4 xpathexpr_Len,
OraText *nsmap, ub4 nsmap_Len,
boolean *retval);
sword OCIXMLTypeIsSchemaBased(OCIError *errhp,
OCIXMLType *doc, boolean *retval);
sword OCIXMLTypeIsFragment(OCIError *errhp, OCIXMLType *doc, boolean
*retval);
sword OCIXMLTypeGetSchema(OCIError *errhp, OCIXMLType *doc,
OCIXMLType **schemadoc,
OraText **schemaURL, ub4 *schemaURL_Len,
OraText **rootelem, ub4 *rootelem_Len);
sword OCIXMLTypeValidate(OCIError *errhp, OCIXMLType *doc,
OraText *schemaURL, ub4 schemaURL_Len, boolean *retval);
sword OCIXMLTypeGetDOM(OCIError *errhp, OCIXMLType *doc, OCIDuration dur,
OCIDOMDocument **retDom);
sword OCIXMLTypeGetFromDOM(OCIError *errhp, OCIDOMDocument *domdoc,
OCIXMLType **retXMLType);
sword OCIXMLTypeGetNS(OCIError *errhp, OCIXMLType *domdoc,
OraText **ns, ub4 *ns_len);
sword OCIDOMFree(OCIError *errhp, OCIDOMDocument *domdoc);
Martin Evans wrote:
Mickautsch, Alfred wrote:
Hello,
when I try to select from a table with a column of sys.xmltype
("select * from c041_dev.xmltest") I get the following error message:
DBD::Oracle::db prepare failed: ORA-22318: input type is not an array
type (DBD ERROR: OCIAttrGet) [for Statement "select * from
C041_DEV.XMLTEST"] at [...].
Can someone please tell me what I have to do to get the statement
executed?
I am using Activestate perl 5.10.0, DBI 1.607 and DBD::Oracle 1.21 on
Windows XP.
The release of the oracle database server is 9.2.0.8.0 and the
client is 10.2.0.1.0.
The table create statement is:
create table c041_dev.xmltest
(
id integer primary key,
xml sys.xmltype
);
insert into c041_dev.xmltest values(0, '<root/>');
Servus -- Alfred
--
Alfred Mickautsch
SCHULER Business Solutions AG
Karl-Berner-Str. 4
72285 Pfalzgrafenweiler
Deutschland
Tel: +49 7445 830-184
Fax: +49 7445 830-349
E-Mail: [email protected]
Web: www.schuler-ag.com
SCHULER Business Solutions AG Aktiengesellschaft mit Sitz in D-72285
Pfalzgrafenweiler, Karl-Berner-Str. 4 Registergericht Stuttgart HRB
430947
Vorstand: Uwe Jonas, Harald Sieber
Vorsitzender des Aufsichtsrates: Achim Gauß
Sorry answer is so late after you posted but I marked your post as I
knew I wanted to do this and I didn't get around to trying it until
today.
select xmltype.getclobval(xmltype_column) from c041_dev.xmltest
should work but I'd be interested in anyone else thoughts on this as a
straight select xmltype_column from table fails the prepare as Alfred
has documented above.
I don't mind changing my perl to retrieve the column but I was hoping
not to have to change my SQL. Anyone know what is going on with the
prepare failing?
Martin
