Thanks Matt. I've been able to get around the initial problem I was having by using the XMLTYPE's getClobValue() to have Oracle return a CLOB instead of the XMLTYPE, which was causing DCOracle2 to Segfault. However, no write()s to this CLOB ever get written back to the database. I've also discovered the Zope-DB mailing list, so I'll be using that to post these types of problems from now on. Matthew T. Kromer wrote:

John Ziniti wrote:

...

Hello --

The DCOracle2 Documentation states that it is possible to force type coercion on a result column to particular type, but does not give an example of how to do this. Does anyone have any suggestions? I am trying to force an XMLTYPE column into a CLOB.

TIA,

John Ziniti

It doesn't go into detail because you really, really don't want to unless you know what you are doing.

What the DCOracle2 type coercion does is assert to Oracle that the python object is whatever type you say it is.

What this is most useful for is changing a string into something else -- but you have to be very careful that your input data is in fact properly formatted.

For example, if I say

import DCOracle2 i = DCOracle2.TypeCoercion("foo", "SQLT_INT")

I'm creating a coerced variable "i" that will claim to be an int. In fact, when Oracle goes to read it, it will read 0x666f6f00 (depending on the byte order). This may or may not be 1718578944 -- but it certainly isn't a very useful value. The coercion is applied AFTER the normal python type->oracle data type binding happens, ie it really just affects the bind declaration. If I coerce the number 1, I'm really referring to an address in memory pointing to a value containing 0x00000001 with a length of four bytes. When oracle is passed this pointer, its told what the data type is it points at. The TypeCoercion overrides what the declared type is, but doesn't *reformat* the data.

Why would you want to do this? Well, maybe you want to handle the binary conversion of values yourself. The underlying C module dco2 will let you get at the raw memory behind a result, so you could extend DCOracle2 to understand new types without writing additional C code.

I have to admit ignorance on what XMLTYPE is for Oracle 9i. However, DCOracle2 shouldn't ever segfault regardless of what data it gets back, so if you have an example I can replicate I'll be happy to look at why DCOracle2 choked on it.

DCOracle2 is crashing on some pretty vanilla stuff. Here is the table: create table XML_TRANSPORT_DOCS ( ID NUMBER(10) not null, DOCUMENT_ID VARCHAR2(30) not null, RECEIVED_DATE DATE not null, XML_DOC XMLType, DATA_PROVIDER_ID NUMBER(8) not null, ADD_DATE DATE DEFAULT (SYSDATE) NOT NULL, ADD_USER VARCHAR2(30) DEFAULT (USER) NOT NULL, CHG_DATE DATE DEFAULT (SYSDATE) NOT NULL, CHG_USER VARCHAR2(30) DEFAULT (USER) NOT NULL, constraint PK_XML_TRANSPORT_DOCS primary key (ID) using index tablespace users_ndx, constraint XML_DATA_PROviders_fk foreign key (DATA_PROVIDER_ID) references DATA_PROVIDERS (ID) ) The attached program will crash at the indicated spot. Let me know if you need any other info. I also attached the trace.log created by setting DCO2TRACELOG. As noted in the program, I can get it not to crash by using getClobVal(), but then any changes to the CLOB are not written to the DB (my current problem :-/ ...) JZ #!/usr/bin/env python2.1 import DCOracle2 import xml.dom.minidom CONNECTION_STRING = 'scott/tiger' if __name__ == '__main__': ### Connect and execute the query, creating an empty clob cnx = DCOracle2.connect(CONNECTION_STRING) print 'Inserting ...' cnx.execute(""" INSERT INTO xml_transport_docs (id, document_id, received_date, xml_doc, data_provider_id) VALUES(1984, 'TEST', SYSDATE, '<xml></xml>', 1) """ ) ### Retrieve the row back print 'Selecting ...' cnx.execute('SELECT x.id, x.xml_doc FROM xml_transport_docs x WHERE id=1984 FOR UPDATE') ### And now crash ... print 'Fetching ...' rst = cnx.fetchone() clob = rst[0] print clob ### This would work though (but we can't write to the returned clob ... ### cnx.execute('SELECT x.id, x.xml_doc.getClobVal() FROM xml_transport_docs x WHERE id=1984 FOR UPDATE') print 'Fetching ...' rst = cnx.fetchone() clob = rst[0] print clob * 1048113998.753, 8, ThreadSwitch, 1024, * * 1048113998.753, 17, initdco2, 0x401b0d78, * * 1048113998.754, 18, initdco2, 0, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9598, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9598, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9538, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9538, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9220, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9220, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9484, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9484, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9520, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9520, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9418, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080c9418, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x080d8be4, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x08126bdc, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.758, 18, TypeTable_lookup, 0x08126bdc, * * 1048113998.758, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x08126bd0, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080c9580, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080c9580, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080c9550, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080c9550, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080c9544, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080c9544, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080c94b4, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080c94b4, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x08126bd0, * * 1048113998.759, 17, TypeTable_lookup, * * 1048113998.759, 18, TypeTable_lookup, 0x080d8bd8, * * 1048113998.795, 17, Connect, 0x081a8a24 ?= bt, 0x08134f34 ?= b4, 0x08194ce4 ?= chldev, * * 1048113998.795, 161, OCIEnvCreate, 0x08133870, * * 1048113998.797, 162, OCIEnvCreate, 0, 0x081cc8b0, * * 1048113998.797, 161, OCIHandleAlloc, 0x08133874, OCI_HTYPE_ERROR, * * 1048113998.797, 34, OCIHandleAlloc, 0, 0x081d992c, * * 1048113998.797, 161, OCIHandleAlloc, 0x08133878, OCI_HTYPE_SERVER, * * 1048113998.798, 162, OCIHandleAlloc, 0, 0x081d9e58, * * 1048113998.798, 33, OCIServerAttach, * * 1048113998.887, 34, OCIServerAttach, 0, * * 1048113998.888, 161, OCIHandleAlloc, 0x08133878, OCI_HTYPE_SVCCTX, * * 1048113998.888, 162, OCIHandleAlloc, 0, 0x081eed28, * * 1048113998.888, 161, OCIAttrSet, OCI_HTYPE_SVCCTX, 0x081eed28, OCI_ATTR_SERVER, 0x081d9e58, * * 1048113998.888, 162, OCIAttrSet, 0, * * 1048113998.888, 161, OCIHandleAlloc, 0x0813387c, OCI_HTYPE_SESSION, * * 1048113998.888, 162, OCIHandleAlloc, 0, 0x081eeba4, * * 1048113998.888, 161, OCIAttrSet, OCI_HTYPE_SESSION, 0x081eeba4, OCI_ATTR_USERNAME, 0x081a8a24 ?= bt, 2, * * 1048113998.888, 162, OCIAttrSet, 0, * * 1048113998.888, 161, OCIAttrSet, OCI_HTYPE_SESSION, 0x081eed28, OCI_ATTR_PASSWORD, 0x08134f34 ?= b4, 2, * * 1048113998.888, 162, OCIAttrSet, 0, * * 1048113998.888, 33, OCISessionBegin, 1, * * 1048113998.922, 34, OCISessionBegin, 0, * * 1048113998.922, 161, OCIAttrSet, OCI_HTYPE_SVCCTX, 0x081eed28, OCI_ATTR_SESSION, 0x081eeba4, * * 1048113998.922, 162, OCIAttrSet, 0, * * 1048113998.922, 18, Connect, 0x08133868, * * 1048113998.922, 17, ServerContext_getattr, 0x08133868, 0x080ddab4 ?= cursor, * * 1048113998.922, 18, ServerContext_getattr, 0x08175090, * * 1048113998.922, 17, ServerContext_cursor, 0x08133868, 0x080cb85c, * * 1048113998.922, 161, OCIHandleAlloc, 0x081deaa8, OCI_HTYPE_STMT, * * 1048113998.922, 162, OCIHandleAlloc, 0, 0x081ee0d0, * * 1048113998.922, 161, OCIHandleAlloc, 0x081dea9c, OCI_HTYPE_ERROR, * * 1048113998.922, 34, OCIHandleAlloc, 0, 0x081e1ddc, * * 1048113998.922, 18, ServerContext_cursor, 0x081dea90, * * 1048113998.922, 17, Cursor_getattr, 0x081dea90, 0x080ddd2c ?= prepare, * * 1048113998.922, 18, Cursor_getattr, 0x081dea90, * * 1048113998.922, 17, Cursor_prepare, 0x081dea90, 0x08131664, * * 1048113998.923, 33, OCIStmtPrepare, 0x081ee0d0, 0x08108914 ?= INSERT INTO xml_transport_docs (id, document_id, received_date, xml_doc, data_provider_id) VALUES(1984, 'TEST', SYSDATE, '<xml></xml>', 1) , 164, * * 1048113998.923, 34, OCIStmtPrepare, 0, * * 1048113998.923, 161, OCIAttrGet, 0x081ee0d0, OCI_HTYPE_STMT, 0x081deaac, OCI_ATTR_STMT_TYPE, * * 1048113998.923, 162, OCIAttrGet, 0, 4, * * 1048113998.923, 18, Cursor_prepare, 4, * * 1048113998.923, 17, Cursor_getattr, 0x081dea90, 0x0811b1ac ?= setPrefetch, * * 1048113998.923, 18, Cursor_getattr, 0x081dea90, * * 1048113998.923, 17, Cursor_setPrefetch, 0x081dea90, 0x08124ac4, * * 1048113998.923, 161, OCIAttrSet, OCI_HTYPE_STMT, 0x081ee0d0, OCI_ATTR_PREFETCH_ROWS, 20, * * 1048113998.923, 162, OCIAttrSet, 0, * * 1048113998.923, 161, OCIAttrSet, OCI_HTYPE_STMT, 0x081ee0d0, OCI_ATTR_PREFETCH_MEMORY, 1048576, * * 1048113998.923, 162, OCIAttrSet, 0, * * 1048113998.923, 18, Cursor_setPrefetch, 0x080b8b8c, * * 1048113998.923, 17, Cursor_getattr, 0x081dea90, 0x081088ec ?= execute, * * 1048113998.923, 18, Cursor_getattr, 0x081dea90, * * 1048113998.923, 17, Cursor_execute, 0x081dea90, 0x080cb85c, * * 1048113998.923, 33, OCIStmtExecute, 1, * * 1048113998.948, 34, OCIStmtExecute, 0, * * 1048113998.948, 17, Cursor_getdesc, 0x081dea90, * * 1048113998.948, 161, OCIParamGet, 0x081ee0d0, OCI_HTYPE_STMT, 0xbffff314, 1, * * 1048113998.948, 162, OCIParamGet, -1, 0x00000000, * * 1048113998.949, 18, Cursor_getdesc, * * 1048113998.949, 161, OCIAttrGet, 0x081ee0d0, OCI_HTYPE_STMT, 0xbffff344, OCI_ATTR_ROW_COUNT, * * 1048113998.949, 162, OCIAttrGet, 0, 1, * * 1048113998.949, 18, Cursor_execute, 4, * * 1048113998.949, 17, Cursor_getattr, 0x081dea90, 0x0811b5cc ?= rowcount, * * 1048113998.949, 18, Cursor_getattr, 0x081dea90, * * 1048113998.949, 17, Cursor_rowcount, 0x081dea90, * * 1048113998.949, 18, Cursor_rowcount, 0x080c9598, 1, * * 1048113998.949, 17, Cursor_getattr, 0x081dea90, 0x081060b4 ?= describe, * * 1048113998.949, 18, Cursor_getattr, 0x081dea90, * * 1048113998.949, 17, Cursor_describe, 0x081dea90, 0x080cb85c, * * 1048113998.949, 18, Cursor_describe, * * 1048113998.949, 17, Cursor_getattr, 0x081dea90, 0x080ddd2c ?= prepare, * * 1048113998.949, 18, Cursor_getattr, 0x081dea90, * * 1048113998.949, 17, Cursor_prepare, 0x081dea90, 0x08131664, * * 1048113998.949, 33, OCIStmtPrepare, 0x081ee0d0, 0x081081dc ?= SELECT x.id, x.xml_doc FROM xml_transport_docs x WHERE id=1984 FOR UPDATE, 73, * * 1048113998.949, 34, OCIStmtPrepare, 0, * * 1048113998.949, 161, OCIAttrGet, 0x081ee0d0, OCI_HTYPE_STMT, 0x081deaac, OCI_ATTR_STMT_TYPE, * * 1048113998.949, 162, OCIAttrGet, 0, 1, * * 1048113998.949, 18, Cursor_prepare, 1, * * 1048113998.949, 17, Cursor_getattr, 0x081dea90, 0x0811b1ac ?= setPrefetch, * * 1048113998.949, 18, Cursor_getattr, 0x081dea90, * * 1048113998.949, 17, Cursor_setPrefetch, 0x081dea90, 0x08124ac4, * * 1048113998.949, 161, OCIAttrSet, OCI_HTYPE_STMT, 0x081ee0d0, OCI_ATTR_PREFETCH_ROWS, 20, * * 1048113998.949, 162, OCIAttrSet, 0, * * 1048113998.949, 161, OCIAttrSet, OCI_HTYPE_STMT, 0x081ee0d0, OCI_ATTR_PREFETCH_MEMORY, 1048576, * * 1048113998.949, 162, OCIAttrSet, 0, * * 1048113998.949, 18, Cursor_setPrefetch, 0x080b8b8c, * * 1048113998.949, 17, Cursor_getattr, 0x081dea90, 0x081088ec ?= execute, * * 1048113998.949, 18, Cursor_getattr, 0x081dea90, * * 1048113998.949, 17, Cursor_execute, 0x081dea90, 0x080cb85c, * * 1048113998.949, 33, OCIStmtExecute, 0, * * 1048113998.952, 34, OCIStmtExecute, 0, * * 1048113998.952, 17, Cursor_getdesc, 0x081dea90, * * 1048113998.952, 161, OCIParamGet, 0x081ee0d0, OCI_HTYPE_STMT, 0xbffff314, 1, * * 1048113998.952, 162, OCIParamGet, 0, 0x081ee01c, * * 1048113998.952, 161, OCIAttrGet, 0x081ee01c, OCI_DTYPE_PARAM, 0xbffff312, OCI_ATTR_DATA_TYPE, * * 1048113998.952, 162, OCIAttrGet, 0, 2, * * 1048113998.952, 161, OCIAttrGet, 0x081ee01c, OCI_DTYPE_PARAM, 0xbffff312, OCI_ATTR_DATA_SIZE, * * 1048113998.952, 162, OCIAttrGet, 0, 22, * * 1048113998.952, 161, OCIAttrGet, 0x081ee01c, OCI_DTYPE_PARAM, 0xbffff311, OCI_ATTR_PRECISION, * * 1048113998.952, 162, OCIAttrGet, 0, 10, * * 1048113998.952, 161, OCIAttrGet, 0x081ee01c, OCI_DTYPE_PARAM, 0xbffff310, OCI_ATTR_SCALE, * * 1048113998.952, 162, OCIAttrGet, 0, 0, * * 1048113998.952, 161, OCIAttrGet, 0x081ee01c, OCI_DTYPE_PARAM, 0xbffff311, OCI_ATTR_IS_NULL, * * 1048113998.952, 162, OCIAttrGet, 0, 0, * * 1048113998.952, 161, OCIAttrGet, 0x081ee01c, OCI_DTYPE_PARAM, 0xbffff30c, OCI_ATTR_NAME, * * 1048113998.952, 162, OCIAttrGet, 0, 0x081e1380 ?= ID, * * 1048113998.952, 66, Cursor_getdesc, 1, 0x081e1380 ?= ID, 2, 22, 10, 0, * * 1048113998.952, 161, OCIParamGet, 0x081ee0d0, OCI_HTYPE_STMT, 0xbffff314, 2, * * 1048113998.952, 162, OCIParamGet, 0, 0x081edffc, * * 1048113998.952, 161, OCIAttrGet, 0x081edffc, OCI_DTYPE_PARAM, 0xbffff312, OCI_ATTR_DATA_TYPE, * * 1048113998.952, 162, OCIAttrGet, 0, 108, * * 1048113998.952, 161, OCIAttrGet, 0x081edffc, OCI_DTYPE_PARAM, 0xbffff312, OCI_ATTR_DATA_SIZE, * * 1048113998.952, 162, OCIAttrGet, 0, 2000, * * 1048113998.952, 161, OCIAttrGet, 0x081edffc, OCI_DTYPE_PARAM, 0xbffff311, OCI_ATTR_PRECISION, * * 1048113998.952, 162, OCIAttrGet, 0, 0, * * 1048113998.952, 161, OCIAttrGet, 0x081edffc, OCI_DTYPE_PARAM, 0xbffff310, OCI_ATTR_SCALE, * * 1048113998.952, 162, OCIAttrGet, 0, 0, * * 1048113998.952, 161, OCIAttrGet, 0x081edffc, OCI_DTYPE_PARAM, 0xbffff311, OCI_ATTR_IS_NULL, * * 1048113998.952, 162, OCIAttrGet, 0, 1, * * 1048113998.952, 161, OCIAttrGet, 0x081edffc, OCI_DTYPE_PARAM, 0xbffff30c, OCI_ATTR_NAME, * * 1048113998.952, 162, OCIAttrGet, 0, 0x081e1394 ?= XML_DOC, * * 1048113998.952, 66, Cursor_getdesc, 2, 0x081e1394 ?= XML_DOC, 108, 2000, 0, 0, * * 1048113998.952, 161, OCIParamGet, 0x081ee0d0, OCI_HTYPE_STMT, 0xbffff314, 3, * * 1048113998.953, 162, OCIParamGet, -1, 0x00000000, * * 1048113998.953, 18, Cursor_getdesc, * * 1048113998.953, 161, OCIAttrGet, 0x081ee0d0, OCI_HTYPE_STMT, 0xbffff344, OCI_ATTR_ROW_COUNT, * * 1048113998.953, 162, OCIAttrGet, 0, 0, * * 1048113998.953, 18, Cursor_execute, 1, * * 1048113998.953, 17, Cursor_getattr, 0x081dea90, 0x0811b5cc ?= rowcount, * * 1048113998.953, 18, Cursor_getattr, 0x081dea90, * * 1048113998.953, 17, Cursor_rowcount, 0x081dea90, * * 1048113998.953, 18, Cursor_rowcount, 0x080c9574, 0, * * 1048113998.953, 17, Cursor_getattr, 0x081dea90, 0x081060b4 ?= describe, * * 1048113998.953, 18, Cursor_getattr, 0x081dea90, * * 1048113998.953, 17, Cursor_describe, 0x081dea90, 0x080cb85c, * * 1048113998.953, 18, Cursor_describe, * * 1048113998.953, 17, Cursor_getattr, 0x081dea90, 0x0811b2bc ?= fetch, * * 1048113998.953, 18, Cursor_getattr, 0x081dea90, * * 1048113998.953, 17, Cursor_fetch, 0x081dea90, 0x08131664, * * 1048113998.953, 65, Cursor_fetch, 20, * * 1048113998.953, 17, Cursor_ResultSet, 0x081dea90, 20, * * 1048113998.953, 17, ResultSet_alloc, 0x081dea90, 1, 20, * * 1048113998.953, 18, ResultSet_alloc, 0x081e25f8, * * 1048113998.953, 33, OCIDefineByPos, 1, 0x081e2638, 48, 5, 0, * * 1048113998.953, 34, OCIDefineByPos, 0, * * 1048113998.953, 17, ResultSet_alloc, 0x081dea90, 2, 20, * * 1048113998.953, 18, ResultSet_alloc, 0x0816ba48, * * 1048113998.953, 33, OCIDefineByPos, 2, 0x08211c48, 2000, 108, 0, * * 1048113998.953, 34, OCIDefineByPos, 0, * * 1048113998.953, 18, Cursor_ResultSet, * * 1048113998.953, 33, OCIStmtFetch, 20, *