- Added new datatype :class:`.oracle.DATE`, which is a subclass of

:class:`.DateTime`.  As Oracle has no "datetime" type per se,
it instead has only ``DATE``, it is appropriate here that the
``DATE`` type as present in the Oracle dialect be an instance of
:class:`.DateTime`.  This issue doesn't change anything as far as
the behavior of the type, as data conversion is handled by the
DBAPI in any case, however the improved subclass layout will help
the use cases of inspecting types for cross-database compatibility.
Also removed uppercase ``DATETIME`` from the Oracle dialect as this
type isn't functional in that context.  fixes #2987

7 files changed, 80 insertions, 24 deletions

diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index 25ee9b9ed..2e4e5f098 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -15,6 +15,21 @@
     :version: 0.9.4
 
     .. change::
+        :tags: bug, oracle
+        :tickets: 2987
+
+        Added new datatype :class:`.oracle.DATE`, which is a subclass of
+        :class:`.DateTime`.  As Oracle has no "datetime" type per se,
+        it instead has only ``DATE``, it is appropriate here that the
+        ``DATE`` type as present in the Oracle dialect be an instance of
+        :class:`.DateTime`.  This issue doesn't change anything as far as
+        the behavior of the type, as data conversion is handled by the
+        DBAPI in any case, however the improved subclass layout will help
+        the use cases of inspecting types for cross-database compatibility.
+        Also removed uppercase ``DATETIME`` from the Oracle dialect as this
+        type isn't functional in that context.
+
+    .. change::
         :tags: bug, sql
         :tickets: 2988
         :pullreq: github:78
diff --git a/doc/build/dialects/oracle.rst b/doc/build/dialects/oracle.rst
index 32a544877..4b4de3d4c 100644
--- a/doc/build/dialects/oracle.rst
+++ b/doc/build/dialects/oracle.rst
@@ -13,7 +13,7 @@ valid with Oracle are importable from the top level dialect, whether
 they originate from :mod:`sqlalchemy.types` or from the local dialect::
 
     from sqlalchemy.dialects.oracle import \
-                BFILE, BLOB, CHAR, CLOB, DATE, DATETIME, \
+                BFILE, BLOB, CHAR, CLOB, DATE, \
                 DOUBLE_PRECISION, FLOAT, INTERVAL, LONG, NCLOB, \
                 NUMBER, NVARCHAR, NVARCHAR2, RAW, TIMESTAMP, VARCHAR, \
                 VARCHAR2
@@ -25,31 +25,33 @@ construction arguments, are as follows:
 
 .. autoclass:: BFILE
   :members: __init__
-   
+
+.. autoclass:: DATE
+   :members: __init__
 
 .. autoclass:: DOUBLE_PRECISION
    :members: __init__
-    
+
 
 .. autoclass:: INTERVAL
   :members: __init__
-   
+
 
 .. autoclass:: NCLOB
   :members: __init__
-   
+
 
 .. autoclass:: NUMBER
    :members: __init__
-    
+
 
 .. autoclass:: LONG
   :members: __init__
-   
+
 
 .. autoclass:: RAW
   :members: __init__
-   
+
 
 cx_Oracle
 ----------
diff --git a/lib/sqlalchemy/dialects/oracle/__init__.py b/lib/sqlalchemy/dialects/oracle/__init__.py
index 070e387d0..b75762ab2 100644
--- a/lib/sqlalchemy/dialects/oracle/__init__.py
+++ b/lib/sqlalchemy/dialects/oracle/__init__.py
@@ -9,14 +9,14 @@ from sqlalchemy.dialects.oracle import base, cx_oracle, zxjdbc
 base.dialect = cx_oracle.dialect
 
 from sqlalchemy.dialects.oracle.base import \
-    VARCHAR, NVARCHAR, CHAR, DATE, DATETIME, NUMBER,\
+    VARCHAR, NVARCHAR, CHAR, DATE, NUMBER,\
     BLOB, BFILE, CLOB, NCLOB, TIMESTAMP, RAW,\
     FLOAT, DOUBLE_PRECISION, LONG, dialect, INTERVAL,\
     VARCHAR2, NVARCHAR2, ROWID, dialect
 
 
 __all__ = (
-'VARCHAR', 'NVARCHAR', 'CHAR', 'DATE', 'DATETIME', 'NUMBER',
+'VARCHAR', 'NVARCHAR', 'CHAR', 'DATE', 'NUMBER',
 'BLOB', 'BFILE', 'CLOB', 'NCLOB', 'TIMESTAMP', 'RAW',
 'FLOAT', 'DOUBLE_PRECISION', 'LONG', 'dialect', 'INTERVAL',
 'VARCHAR2', 'NVARCHAR2', 'ROWID'
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index d883d74f7..8bacb885f 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -176,6 +176,24 @@ accepted, including methods such as :meth:`.MetaData.reflect` and
 
 If synonyms are not in use, this flag should be left disabled.
 
+DateTime Compatibility
+----------------------
+
+Oracle has no datatype known as ``DATETIME``, it instead has only ``DATE``,
+which can actually store a date and time value.  For this reason, the Oracle
+dialect provides a type :class:`.oracle.DATE` which is a subclass of
+:class:`.DateTime`.   This type has no special behavior, and is only
+present as a "marker" for this type; additionally, when a database column
+is reflected and the type is reported as ``DATE``, the time-supporting
+:class:`.oracle.DATE` type is used.
+
+.. versionchanged:: 0.9.4 Added :class:`.oracle.DATE` to subclass
+   :class:`.DateTime`.  This is a change as previous versions
+   would reflect a ``DATE`` column as :class:`.types.DATE`, which subclasses
+   :class:`.Date`.   The only significance here is for schemes that are
+   examining the type of column for use in special Python translations or
+   for migrating schemas to other database backends.
+
 """
 
 import re
@@ -185,7 +203,7 @@ from sqlalchemy.engine import default, base, reflection
 from sqlalchemy.sql import compiler, visitors, expression
 from sqlalchemy.sql import operators as sql_operators, functions as sql_functions
 from sqlalchemy import types as sqltypes, schema as sa_schema
-from sqlalchemy.types import VARCHAR, NVARCHAR, CHAR, DATE, DATETIME, \
+from sqlalchemy.types import VARCHAR, NVARCHAR, CHAR, \
                 BLOB, CLOB, TIMESTAMP, FLOAT
 
 RESERVED_WORDS = \
@@ -258,6 +276,22 @@ class BFILE(sqltypes.LargeBinary):
 class LONG(sqltypes.Text):
     __visit_name__ = 'LONG'
 
+class DATE(sqltypes.DateTime):
+    """Provide the oracle DATE type.
+
+    This type has no special Python behavior, except that it subclasses
+    :class:`.types.DateTime`; this is to suit the fact that the Oracle
+    ``DATE`` type supports a time value.
+
+    .. versionadded:: 0.9.4
+
+    """
+    __visit_name__ = 'DATE'
+
+
+    def _compare_type_affinity(self, other):
+        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date)
+
 
 class INTERVAL(sqltypes.TypeEngine):
     __visit_name__ = 'INTERVAL'
@@ -306,6 +340,7 @@ class _OracleBoolean(sqltypes.Boolean):
 colspecs = {
     sqltypes.Boolean: _OracleBoolean,
     sqltypes.Interval: INTERVAL,
+    sqltypes.DateTime: DATE
 }
 
 ischema_names = {
diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index e875c4667..b8ee90b53 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -610,7 +610,6 @@ class OracleDialect_cx_oracle(OracleDialect):
     colspecs = colspecs = {
         sqltypes.Numeric: _OracleNumeric,
         sqltypes.Date: _OracleDate,  # generic type, assume datetime.date is desired
-        oracle.DATE: oracle.DATE,  # non generic type - passthru
         sqltypes.LargeBinary: _OracleBinary,
         sqltypes.Boolean: oracle._OracleBoolean,
         sqltypes.Interval: _OracleInterval,
diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py
index 7e8ddffcd..041875879 100644
--- a/test/dialect/test_oracle.py
+++ b/test/dialect/test_oracle.py
@@ -1022,7 +1022,8 @@ class DialectTypesTest(fixtures.TestBase, AssertsCompiledSQL):
             (oracle.OracleRaw(), cx_oracle._OracleRaw),
             (String(), String),
             (VARCHAR(), cx_oracle._OracleString),
-            (DATE(), DATE),
+            (DATE(), cx_oracle._OracleDate),
+            (oracle.DATE(), oracle.DATE),
             (String(50), cx_oracle._OracleString),
             (Unicode(), cx_oracle._OracleNVarChar),
             (Text(), cx_oracle._OracleText),
@@ -1403,22 +1404,26 @@ class TypesTest(fixtures.TestBase):
         metadata = self.metadata
         Table(
             "date_types", metadata,
-            Column('d1', DATE),
-            Column('d2', TIMESTAMP),
-            Column('d3', TIMESTAMP(timezone=True)),
-            Column('d4', oracle.INTERVAL(second_precision=5)),
+            Column('d1', sqltypes.DATE),
+            Column('d2', oracle.DATE),
+            Column('d3', TIMESTAMP),
+            Column('d4', TIMESTAMP(timezone=True)),
+            Column('d5', oracle.INTERVAL(second_precision=5)),
         )
         metadata.create_all()
         m = MetaData(testing.db)
         t1 = Table(
             "date_types", m,
             autoload=True)
-        assert isinstance(t1.c.d1.type, DATE)
-        assert isinstance(t1.c.d2.type, TIMESTAMP)
-        assert not t1.c.d2.type.timezone
+        assert isinstance(t1.c.d1.type, oracle.DATE)
+        assert isinstance(t1.c.d1.type, DateTime)
+        assert isinstance(t1.c.d2.type, oracle.DATE)
+        assert isinstance(t1.c.d2.type, DateTime)
         assert isinstance(t1.c.d3.type, TIMESTAMP)
-        assert t1.c.d3.type.timezone
-        assert isinstance(t1.c.d4.type, oracle.INTERVAL)
+        assert not t1.c.d3.type.timezone
+        assert isinstance(t1.c.d4.type, TIMESTAMP)
+        assert t1.c.d4.type.timezone
+        assert isinstance(t1.c.d5.type, oracle.INTERVAL)
 
     def test_reflect_all_types_schema(self):
         types_table = Table('all_types', MetaData(testing.db),
diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py
index 446bb8a36..a53893549 100644
--- a/test/engine/test_reflection.py
+++ b/test/engine/test_reflection.py
@@ -979,11 +979,11 @@ class ReflectionTest(fixtures.TestBase, ComparesTables):
             users_v = Table("users_v", m2, autoload=True)
             addresses_v = Table("email_addresses_v", m2, autoload=True)
 
-            for c1, c2 in zip(users.c, users_v.c):
+            for c1, c2 in zip(users_v.c, users.c):
                 eq_(c1.name, c2.name)
                 self.assert_types_base(c1, c2)
 
-            for c1, c2 in zip(addresses.c, addresses_v.c):
+            for c1, c2 in zip(addresses_v.c, addresses.c):
                 eq_(c1.name, c2.name)
                 self.assert_types_base(c1, c2)
         finally: