Re: DBD::Oracle - Problem selecting xml column
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 -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: DBD::Oracle - Problem selecting xml column
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
inline placeholder DBIx now on CPAN
Enjoy! http://search.cpan.org/~davidnico/DBIx-bind_param_inline-0.02/lib/DBIx/bind_param_inline.pm -- Refusing to move when ordered, he was tragically mulched. -- The Onion
Error in DBD::Oracle (1.22) Makefile.PL with filetest
Hello
When I executed : perl Makefile.PL it is reporting this warning : Can't
find sqlplus. Pity, it would have helped.
I found that this problem is because of bug in function in Makefile.PL
sub find_bin{
use filetest 'access';
my $bin = shift;
my $path_sep = $Config{path_sep};
foreach (split(/\Q$path_sep/, $ENV{PATH})){
print look for = $file/$bin\n;
return $_/$bin if -x $_/$bin;
}
return undef;
}
My environment is correct :
ORACLE_HOME is correctly set and point to and OCI oracle folder
(10gR2.4 64bit)
PATH and LD_LIBRARY_PATH are correctly set too
OS is Solaris : SunOS xx 5.10 Generic_137111-06 sun4u sparc
SUNW,Sun-Fire-15000
Perl is v5.10.0 built for sun4-solaris-thread-multi-64 from
ActivePerl 5.10.0.1004
The problem is because the test -x $_/$bin does not work and it does
not work because of use filetest 'access' .
When I comment this use, it stops to complain and find sqlplus.
Do you know why this pragma is worth than it should (
http://perldoc.perl.org/filetest.html ) ?
Perhaps it is a bug of Solaris, but...
Regards
Pascal
RE: Error in DBD::Oracle (1.22) Makefile.PL with filetest
-Message d'origine-
De : Martin Evans [mailto:martin.ev...@easysoft.com]
Envoyé : mercredi 17 décembre 2008 16:19
À : dbi-users@perl.org
Objet : Re: Error in DBD::Oracle (1.22) Makefile.PL with filetest
pmonsch...@groupe-casino.fr wrote:
Hello
When I executed : perl Makefile.PL it is reporting this warning :
Can't find sqlplus. Pity, it would have helped.
I found that this problem is because of bug in function in
Makefile.PL sub find_bin{
use filetest 'access';
my $bin = shift;
my $path_sep = $Config{path_sep};
foreach (split(/\Q$path_sep/, $ENV{PATH})){
print look for = $file/$bin\n;
return $_/$bin if -x $_/$bin;
}
return undef;
}
My environment is correct :
ORACLE_HOME is correctly set and point to and OCI oracle folder
(10gR2.4 64bit)
PATH and LD_LIBRARY_PATH are correctly set too
OS is Solaris : SunOS xx 5.10 Generic_137111-06 sun4u sparc
SUNW,Sun-Fire-15000
Perl is v5.10.0 built for sun4-solaris-thread-multi-64 from
ActivePerl 5.10.0.1004
The problem is because the test -x $_/$bin does not work
and it does
not work because of use filetest 'access' .
When I comment this use, it stops to complain and find sqlplus.
Do you know why this pragma is worth than it should (
http://perldoc.perl.org/filetest.html ) ?
Perhaps it is a bug of Solaris, but...
Regards
Pascal
I am only guessing, but is the sqlplus located on a different
filesystem
- e.g. NFS, etc.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
No it is on the same file-system.
Pascal
