- Added support for the Oracle table option ON COMMIT. This is being

kept separate from Postgresql's ON COMMIT for now even though ON COMMIT
is in the SQL standard; the option is still very specific to temp tables
and we eventually would provide a more first class temporary table
feature.
- oracle can apparently do get_temp_table_names() too, so implement that,
fix its get_table_names(), and add it to #3204.  fixes #3204 again.

2 files changed, 61 insertions, 7 deletions

diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 81a9f1a95..837a498fb 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -213,6 +213,21 @@ is reflected and the type is reported as ``DATE``, the time-supporting
    examining the type of column for use in special Python translations or
    for migrating schemas to other database backends.
 
+Oracle Table Options
+-------------------------
+
+The CREATE TABLE phrase supports the following options with Oracle
+in conjunction with the :class:`.Table` construct:
+
+
+* ``ON COMMIT``::
+
+    Table(
+        "some_table", metadata, ...,
+        prefixes=['GLOBAL TEMPORARY'], oracle_on_commit='PRESERVE ROWS')
+
+.. versionadded:: 1.0.0
+
 """
 
 import re
@@ -784,6 +799,16 @@ class OracleDDLCompiler(compiler.DDLCompiler):
         return super(OracleDDLCompiler, self).\
             visit_create_index(create, include_schema=True)
 
+    def post_create_table(self, table):
+        table_opts = []
+        opts = table.dialect_options['oracle']
+
+        if opts['on_commit']:
+            on_commit_options = opts['on_commit'].replace("_", " ").upper()
+            table_opts.append('\n ON COMMIT %s' % on_commit_options)
+
+        return ''.join(table_opts)
+
 
 class OracleIdentifierPreparer(compiler.IdentifierPreparer):
 
@@ -842,7 +867,10 @@ class OracleDialect(default.DefaultDialect):
     reflection_options = ('oracle_resolve_synonyms', )
 
     construct_arguments = [
-        (sa_schema.Table, {"resolve_synonyms": False})
+        (sa_schema.Table, {
+            "resolve_synonyms": False,
+            "on_commit": None
+        })
     ]
 
     def __init__(self,
@@ -1029,7 +1057,21 @@ class OracleDialect(default.DefaultDialect):
             "WHERE nvl(tablespace_name, 'no tablespace') NOT IN "
             "('SYSTEM', 'SYSAUX') "
             "AND OWNER = :owner "
-            "AND IOT_NAME IS NULL")
+            "AND IOT_NAME IS NULL "
+            "AND DURATION IS NULL")
+        cursor = connection.execute(s, owner=schema)
+        return [self.normalize_name(row[0]) for row in cursor]
+
+    @reflection.cache
+    def get_temp_table_names(self, connection, **kw):
+        schema = self.denormalize_name(self.default_schema_name)
+        s = sql.text(
+            "SELECT table_name FROM all_tables "
+            "WHERE nvl(tablespace_name, 'no tablespace') NOT IN "
+            "('SYSTEM', 'SYSAUX') "
+            "AND OWNER = :owner "
+            "AND IOT_NAME IS NULL "
+            "AND DURATION IS NOT NULL")
         cursor = connection.execute(s, owner=schema)
         return [self.normalize_name(row[0]) for row in cursor]
 
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index 690a880bb..60db9eb47 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -100,19 +100,31 @@ class ComponentReflectionTest(fixtures.TablesTest):
 
     @classmethod
     def define_temp_tables(cls, metadata):
-        temp_table = Table(
+        # cheat a bit, we should fix this with some dialect-level
+        # temp table fixture
+        if testing.against("oracle"):
+            kw = {
+                'prefixes': ["GLOBAL TEMPORARY"],
+                'oracle_on_commit': 'PRESERVE ROWS'
+            }
+        else:
+            kw = {
+                'prefixes': ["TEMPORARY"],
+            }
+
+        user_tmp = Table(
             "user_tmp", metadata,
             Column("id", sa.INT, primary_key=True),
             Column('name', sa.VARCHAR(50)),
             Column('foo', sa.INT),
             sa.UniqueConstraint('name', name='user_tmp_uq'),
             sa.Index("user_tmp_ix", "foo"),
-            prefixes=['TEMPORARY']
+            **kw
         )
         if testing.requires.view_reflection.enabled and \
                 testing.requires.temporary_views.enabled:
             event.listen(
-                temp_table, "after_create",
+                user_tmp, "after_create",
                 DDL("create temporary view user_tmp_v as "
                     "select * from user_tmp")
             )
@@ -186,7 +198,7 @@ class ComponentReflectionTest(fixtures.TablesTest):
 
     @testing.requires.temp_table_names
     def test_get_temp_table_names(self):
-        insp = inspect(self.metadata.bind)
+        insp = inspect(testing.db)
         temp_table_names = insp.get_temp_table_names()
         eq_(sorted(temp_table_names), ['user_tmp'])
 
@@ -485,6 +497,7 @@ class ComponentReflectionTest(fixtures.TablesTest):
         self._test_get_unique_constraints()
 
     @testing.requires.temp_table_reflection
+    @testing.requires.unique_constraint_reflection
     def test_get_temp_table_unique_constraints(self):
         insp = inspect(self.metadata.bind)
         eq_(
@@ -503,7 +516,6 @@ class ComponentReflectionTest(fixtures.TablesTest):
             [{'unique': False, 'column_names': ['foo'], 'name': 'user_tmp_ix'}]
         )
 
-
     @testing.requires.unique_constraint_reflection
     @testing.requires.schemas
     def test_get_unique_constraints_with_schema(self):