Subject: Re: ERROR OCIDefineObject call needed but not implemented yet using XMLElement function

From: Andy Hassall <[email-masked]>
Date: Fri, 15 Aug 2003 11:32:00 +0100 (BST)

--- Susan Cassidy <[email-masked]> wrote: > I am using DBD::Oracle. 
I was on version 1.12, then I installed version 
> 1.14, with the same result.
> 
> This is Oracle 9.2.0.
> 
> I have this select statement that works fine from SQL*Plus:
> 
> select    XMLElement("Sequences",
>       XMLElement("Sequence",
>         XMLATTRIBUTES ( b.local_name AS "ic-acckey",
>                         b.mol_type AS "molecule",
>                         n.seq_name AS "title")))
>      from gcg_bioseq b, gcg_annot_seq_name a, gcg_seq_name n
>       where
>         b.local_name = 'K00306' and
>         b.seq_status = 'D' and
>         b.seq_oid = a.seq_oid and
>         a.seq_name_oid = n.seq_name_oid  and
>         n.name_type = 'LOCUS'
> 
> 
> When I run it via DBI/DBD I get this (trace level 2):
> 
>     DBI 1.32-nothread dispatch trace level set to 2
>     Note: perl is running without the recommended perl -w option
>     -> prepare for DBD::Oracle::db (DBI::db=HASH(0x1b2314)~0x122bec ' 
[snip
> Field 1 has an Oracle type (108) which is not explicitly supported
>     fbh 1: 
>
'XMLELEMENT("SEQUENCES",XMLELEMENT("SEQUENCE",XMLATTRIBUTES(B.LOCAL_NAMEAS"IC-ACCKEY",B.MOL_TYPEAS"MOLECULE",N.SEQ_NAMEAS"TITLE")))'
[snip] 
> Error:  prepare failed
>    at line 56, error: ERROR OCIDefineObject call needed but not 
> implemented yet
> 
> Is there any other workaround for this than wrapping this up in a PL/SQL 
> function?

 Don't rely on the implicit conversion to a string type that is done when
SQL*Plus displays an XMLElement; add .getClobVal() to the end of the
statement to retrieve it as a CLOB rather than the XMLElement object type
(which DBD::Oracle doesn't accept).

 i.e.

select    XMLElement("Sequences",
      XMLElement("Sequence",
        XMLATTRIBUTES ( b.local_name AS "ic-acckey",
                        b.mol_type AS "molecule",
                        n.seq_name AS "title"))).getClobVal()
     from ...

 (or getStringVal() for a VARCHAR2)

=====
-- 
Andy Hassall ([email-masked]) icq(5747695) http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space | disk usage analysis tool

________________________________________________________________________
Want to chat instantly with your online friends?  Get the FREE Yahoo!
Messenger http://uk.messenger.yahoo.com/