It seems the current state of affairs is that for inserting to XMLTYPE fields:
a) for <32k, can just insert text
b) for >32k, must insert to CLOB, then use a procedure to update XMLTYPE
(see http://www.issociate.de/board/post/206125/DBD::Oracle_and_XMLType.html
for a summary of the details)
Personally I've tried just about every trick in the book to try and
get a direct-insert of large xml documents into xmltype fields, but to
no avail.
What troubles me is that this _should_ work:
# NB: CREATE TABLE tryit ( formname VARCHAR(25), x XMLTYPE )
INSERT INTO tryit (formname, x) VALUES (?, XMLTYPE(?))"
$sth = $dbh->prepare( "INSERT INTO tryit (formname, x) VALUES (?,
XMLTYPE(?))" );
$sth->bind_param(1, "INSERTXMLTYPE" );
$sth->bind_param(2, XMLout( \%dslong , RootName => "books"), { TYPE =>
SQL_CLOB } );
$sth->execute or warn "INSERTXMLTYPE creation failure";
but it actually just gives ORA-00942: table or view does not exist.
this is a bogus message (search metalink for "XMLTYPE ORA-00942"). try
an alternative like "INSERT INTO tryit (formname, x) VALUES (?,
XMLTYPE(CAST(? as CLOB)))" and you get ORA-00932: inconsistent
datatypes.
All of the above is old news I think.
Why I raise this now is that I discovered the python guys seem to have
got it working OK. See
http://blog.gmane.org/gmane.comp.python.db.cx-oracle/month=20050401
connection = cx_Oracle.Connection("user/pw <at> tns")
cursor = connection.cursor()
cursor.setinputsizes(value = cx_Oracle.CLOB)
cursor.execute("insert into xmltable values (xmltype(:value))",
value = "A very long XML string")
Seems very much like a binding issue on the DBI/DBD side.
Any thoughts?
~paul
