John Scoles wrote:
> 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: alfred.mickaut...@schuler-ag.com
>>> 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
>
>
I've just noticed the following new(ish) references to OCIXMLTypeNew:
http://forums.oracle.com/forums/thread.jspa?messageID=3505174
I'm not in a position to do anything about this right now but I thought
you might be interested to see the example code. May be I'll find time
to get back to this.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
