Having thought about this some more, what's needed is something like
$lob_locator = $dbh->ora_create_temp_lob($text);
Then $lob_locator can be bound to a placeholder anywhere a LOB is required.
But I've no immediate plans to implement this as I'm snowed under
with other work. Patches welcome, as ever.
Tim.
On Mon, Aug 23, 2004 at 02:26:20PM +0100, Thomas Chiverton wrote:
> 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.***
