Oracle ROWID type support

[Former user]

It would be great if SQLA supported ROWID type for oracle.

Currently I've implemented this in my code:

class ROWID(Unicode):
    pass

@compiles(ROWID)
def compile_rowid(compiler, element, **kw):
    return "ROWID"

But a problem I've having is that when "supports_unicode_binds == False" (in my case, with oracle 8), I need ROWID to be of type cx_Oracle.STRING instead of cx_Oracle.UNICODE, or else I get a DatabaseError: (DatabaseError) ORA-12704: character set mismatch

If I subclass from String: class ROWID(String), then it works on oracle 8, but sqla complains about "Unicode type received non-unicode bind param value"

I wonder, does cx_Oracle have a ROWID type?

Another note: ROWIDs are apparently always 18 characters: http://www.orafaq.com/wiki/ROWID

[Former user]

cx_Oracle documentation does have cx_Oracle.ROWID... that better than using cx_Oracle.STRING or cx_Oracle.UNICODE?

[Mike Bayer]

For the DBAPI type issue, you just need to override get_dbapi_type() on your ROWID class appropriately. But beyond that, I don't see why you'd need to subclass Unicode and not String for ROWID, or even why you need to subclass either of those - how about TypeEngine or UserDefinedType ?

[Former user]

Very good, thanks for the instruction.

[Mike Bayer]

this is easy to add but can you show me how you're using ROWID as a type? is it for CAST ?

[Mike Bayer]

OK I don't really know why you need it since the value is just a string, I don't quite get the "unicode binds" issue unless you're...sending it as a u'' ? not sure. anyway its in 7c63679bdb50491120e205820c2c29cf42cfa9ea.

[Former user]

Replying to zzzeek:

this is easy to add but can you show me how you're using ROWID as a type? is it for CAST ?

My implementation (after your suggestion) is virtually exactly what you've implemented:

class ROWID(TypeEngine):
    def get_dbapi_type(self, dbapi):
        return dbapi.ROWID


@compiles(ROWID)
def compile_rowid(compiler, element, **kw):
    return "ROWID"

Answering your question, I am using this for: * a couple tables where I'm using implicit_returning. These are actually views to a table with no primary key (and I am unable to add such a key as it is a legacy database not directly under my control), so the view maps ROWID as a primary key column for sqla. * it is also used in a polymorphic union, where it is rendered like "CAST(NULL AS ROWID) AS transactionid," on one side of the union where the column doesn't exist.

[Mike Bayer]

Removing milestone: 0.6.4 (automated comment)