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

6 files changed, 90 insertions, 14 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index ca612c0ef..d6782c917 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -41,9 +41,9 @@
 
         Added :meth:`.Inspector.get_temp_table_names` and
         :meth:`.Inspector.get_temp_view_names`; currently, only the
-        SQLite dialect supports these methods.    The return of temporary
-        table and view names has been **removed** from SQLite's version
-        of :meth:`.Inspector.get_table_names` and
+        SQLite and Oracle dialects support these methods.  The return of
+        temporary table and view names has been **removed** from SQLite and
+        Oracle's version of :meth:`.Inspector.get_table_names` and
         :meth:`.Inspector.get_view_names`; other database backends cannot
         support this information (such as MySQL), and the scope of operation
         is different in that the tables can be local to a session and
@@ -317,6 +317,11 @@
         default, or a server-side default "eagerly" fetched via RETURNING.
 
     .. change::
+        :tags: feature, oracle
+
+        Added support for the Oracle table option ON COMMIT.
+
+    .. change::
         :tags: feature, postgresql
         :tickets: 2051
 
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index 246eb9a14..de9e9a64c 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -955,11 +955,11 @@ when using ODBC to avoid this issue entirely.
 
 .. _change_3204:
 
-SQLite has distinct methods for temporary table/view name reporting
--------------------------------------------------------------------
+SQLite/Oracle have distinct methods for temporary table/view name reporting
+---------------------------------------------------------------------------
 
 The :meth:`.Inspector.get_table_names` and :meth:`.Inspector.get_view_names`
-methods in the case of SQLite would also return the names of temporary
+methods in the case of SQLite/Oracle would also return the names of temporary
 tables and views, which is not provided by any other dialect (in the case
 of MySQL at least it is not even possible).  This logic has been moved
 out to two new methods :meth:`.Inspector.get_temp_table_names` and
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):
diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py
index 187042036..36eacf864 100644
--- a/test/dialect/test_oracle.py
+++ b/test/dialect/test_oracle.py
@@ -648,6 +648,23 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "CREATE INDEX bar ON foo (x > 5)"
         )
 
+    def test_table_options(self):
+        m = MetaData()
+
+        t = Table(
+            'foo', m,
+            Column('x', Integer),
+            prefixes=["GLOBAL TEMPORARY"],
+            oracle_on_commit="PRESERVE ROWS"
+        )
+
+        self.assert_compile(
+            schema.CreateTable(t),
+            "CREATE GLOBAL TEMPORARY TABLE "
+            "foo (x INTEGER) ON COMMIT PRESERVE ROWS"
+        )
+
+
 class CompatFlagsTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def _dialect(self, server_version, **kw):
diff --git a/test/requirements.py b/test/requirements.py
index cfdfc8054..80bd135e9 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -300,7 +300,7 @@ class DefaultRequirements(SuiteRequirements):
     def temp_table_names(self):
         """target dialect supports listing of temporary table names"""
 
-        return only_on(['sqlite'])
+        return only_on(['sqlite', 'oracle'])
 
     @property
     def temporary_views(self):