- changed milestone to 0.6.xx
Oracle 8 support for RETURNING clause
I'm connecting to a legacy database, some tables that I need to map in sqla have no primary key. I've created views for such tables that map the ROWID as the primary key. SQLAlchemy does a RETURNING clause when I am connected to a more recent Oracle database, but not when connected to 8i.
I've pasted the exact same SQL output which sqla compiled for newer oracle into an 8i session and it works fine.
I found reference in the Oracle 8.0 SQL Reference that the RETURNING CLAUSE
did exist in Oracle 8.0:
http://www.silab.dsi.unimi.it/manual/Oracle8%20SQL%20Reference%20-%20Release%208.0/ch4a.htm#2007780
Suggested sqlalchemy/dialects/oracle/base.py
patch (this produced correct results in my scenario for oracle 8i):
--- base.py.bak 2010-08-16 13:15:27.000000000 +0000
+++ base.py 2010-08-16 13:16:18.000000000 +0000
@@ -627,8 +627,7 @@
def initialize(self, connection):
super(OracleDialect, self).initialize(connection)
- self.implicit_returning = self.server_version_info > (10, ) and \
- self.__dict__.get('implicit_returning', True)
+ self.implicit_returning = self.__dict__.get('implicit_returning', True)
if self._is_oracle_8:
self.colspecs = self.colspecs.copy()
Comments (7)
-
repo owner -
Account Deleted I hope I'm not annoying by asking, but I am quite curious why you suspect cx_Oracle might not handle this correctly? (It worked when I tried...)
-
Account Deleted I didn't realize I need to turn implicit_returning True for engine, I thought it would force on if I specified at the table level, so I think I need one of these patches to be implemented.
I would still push for this to default True for any version of Oracle. I have personally tried it with sqlalchemy, cx_Oracle for Oracle 8i, 9 and 10. They all support this feature. I do not have any way to check for Oracle 8.0, but Oracle's documentation clearly states the feature exists for 8.0.
Either patch would be workable, but would you let me know which you are choosing?
Thanks very much, Kent
-
repo owner - changed milestone to 0.6.5
I'll commit the patch I have which at least respects engine-level implicit returning for 8. I think the approach with Oracle needs to be very conservative, particularly since Oracle's approach to RETURNING involves a lot of awkwardness (OUT params and such), and is likely to be a surprise to a lot of oracle DBAs.
-
Account Deleted As always, thank you very much.
-
repo owner - changed status to resolved
-
repo owner - removed milestone
Removing milestone: 0.6.5 (automated comment)
- Log in to comment
here's the patch I want, just because I don't trust that cx_oracle is going to handle RETURNING correctly with 8 or 8i:
That way the feature is only defaulted to ON for 11g, but can be enabled for any version.
Note there's also an "implicit_returning" flag on Table, which defaults to True. We can change that to default to
None
, and aTrue
setting would mean "force". Since in this case you really only need the feature on a per-table basis.