Oracle 8 support for RETURNING clause

[Former user]

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()

[Mike Bayer]

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:

--- a/lib/sqlalchemy/dialects/oracle/base.py    Sat Aug 14 20:51:37 2010 -0400
+++ b/lib/sqlalchemy/dialects/oracle/base.py    Mon Aug 16 21:36:47 2010 -0400
@@ -627,8 +627,10 @@

     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',
+                                self.server_version_info > (10, )
+                            )

         if self._is_oracle_8:
             self.colspecs = self.colspecs.copy()

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 a True setting would mean "force". Since in this case you really only need the feature on a per-table basis.

[Former user]

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...)

[Former user]

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

[Mike Bayer]

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.

[Former user]

As always, thank you very much.

[Mike Bayer]

c01c6baf5715a13d707a4d57717f9ad1bc12027a

[Mike Bayer]

Removing milestone: 0.6.5 (automated comment)