I am using the following:
$sth = $dbh->prepare("insert into parsed_dtlogs (id,service_name,xml_doc)
values ($item,'".$service_save."',xmltype(?))") or die "Can't prepare SQL
statement: $DBI::errstr\n";
$sth->bind_param(1,$xml,ORA_CLOB);
$sth->execute() or die "Can't execute SQL statement: $DBI::errstr\n";
to try and insert an XML document into an xmltype column in oracle.
If $xml is small (say, "<test>this</test") I get a warning about:
"SQL type 112 for ':p1' is not fully supported, bound as SQL_VARCHAR instead
at /var/www/cgi-bin/dtquery-xml.cgi line 1592."
but otherwise things are fine.
If I try and insert something more resonably sized (say, about 6K), I get:
SQL type 112 for ':p1' is not fully supported, bound as SQL_VARCHAR instead
at /tmp/a.pl line 136.
DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for
insert into a LONG column (DBD ERROR: error possibly near <*> indicator at
char 70 in'insert into parsed_dtlogs (id,service_name,xml_doc) values
(54,'test',<*>xmltype(:p1))') [for Statement "insert into parsed_dtlogs
(id,service_name,xml_doc) values (54,'test',xmltype(?))" with
ParamValues: :p1='<downTime>
<failure><group>BF-webservers</group><service>http-webservers</service>
<startTime>Wed Aug 18 18:46:27 2004</startTime>
<endTime>Wed Aug 18 18:48:08 2004</endTime>
<downTime>1 minute, 41 seconds</downTime>
<downTimeSecs>101</downTimeSecs><testInterval>1 minute, 0
seconds</testInterval>
<summary>locate.corporate-eye.co.uk locate.locateyou.co.uk
www.corporate-eye.co.uk</summary></fai...'] at /tmp/a.pl line 137 (this is
the execute() line - the other lines are the xml string).
I've tried the RC DBD::Oracle, to no avail :-(
Has anyone been able to do this ?
I think it's a CLOB/varchar2 thing, with a DBI/DBD trying to do some under the
covers conversion, and it not working, because xmltype() exects something
different to what it gets ?
--
Tom Chiverton
Advanced ColdFusion Programmer
Tel: +44(0)1749 834997
email: [EMAIL PROTECTED]
BlueFinger Limited
Underwood Business Park
Wookey Hole Road, WELLS. BA5 1AF
Tel: +44 (0)1749 834900
Fax: +44 (0)1749 834901
web: www.bluefinger.com
Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple
Quay, BRISTOL. BS1 6EG.
*** This E-mail contains confidential information for the addressee
only. If you are not the intended recipient, please notify us
immediately. You should not use, disclose, distribute or copy this
communication if received in error. No binding contract will result from
this e-mail until such time as a written document is signed on behalf of
the company. BlueFinger Limited cannot accept responsibility for the
completeness or accuracy of this message as it has been transmitted over
public networks.***
