From a3473c08d35e2cce32b014519df5f774c0166cf1 Mon Sep 17 00:00:00 2001
From: Kent Bower <kb@retailarchitects.com>
Date: Wed, 11 Apr 2018 17:21:26 -0400
Subject: [PATCH] Reflect Oracle NUMBER(NULL, 0) as INTEGER

The Oracle NUMBER datatype is reflected as INTEGER if the precision is NULL
and the scale is zero, as this is how INTEGER values come back when
reflected from Oracle's tables.  Pull request courtesy Kent Bower.

Change-Id: I4627febd46cab7085299c0a5700ee0f0bdca513c
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/437
---
 .../changelog/unreleased_12/oracle_number.rst |  7 +++
 lib/sqlalchemy/dialects/oracle/base.py        |  7 ++-
 test/dialect/oracle/test_reflection.py        | 48 ++++++++++++++++++-
 3 files changed, 58 insertions(+), 4 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_12/oracle_number.rst

diff --git a/doc/build/changelog/unreleased_12/oracle_number.rst b/doc/build/changelog/unreleased_12/oracle_number.rst
new file mode 100644
index 0000000000..7d073e11f6
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/oracle_number.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: bug, oracle
+    :versions: 1.3.0b1
+
+    The Oracle NUMBER datatype is reflected as INTEGER if the precision is NULL
+    and the scale is zero, as this is how INTEGER values come back when
+    reflected from Oracle's tables.  Pull request courtesy Kent Bower.
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 44ab9e3bbd..e55a9cbc6a 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -354,7 +354,7 @@ from sqlalchemy.sql import operators as sql_operators
 from sqlalchemy.sql.elements import quoted_name
 from sqlalchemy import types as sqltypes, schema as sa_schema
 from sqlalchemy.types import VARCHAR, NVARCHAR, CHAR, \
-    BLOB, CLOB, TIMESTAMP, FLOAT
+    BLOB, CLOB, TIMESTAMP, FLOAT, INTEGER
 from itertools import groupby
 
 RESERVED_WORDS = \
@@ -1414,7 +1414,10 @@ class OracleDialect(default.DefaultDialect):
             comment = row[7]
 
             if coltype == 'NUMBER':
-                coltype = NUMBER(precision, scale)
+                if precision is None and scale == 0:
+                    coltype = INTEGER()
+                else:
+                    coltype = NUMBER(precision, scale)
             elif coltype in ('VARCHAR2', 'NVARCHAR2', 'CHAR'):
                 coltype = self.ischema_names.get(coltype)(length)
             elif 'WITH TIME ZONE' in coltype:
diff --git a/test/dialect/oracle/test_reflection.py b/test/dialect/oracle/test_reflection.py
index d09f12e600..190fd9f38e 100644
--- a/test/dialect/oracle/test_reflection.py
+++ b/test/dialect/oracle/test_reflection.py
@@ -1,7 +1,7 @@
 # coding: utf-8
 
 
-from sqlalchemy.testing import eq_
+from sqlalchemy.testing import eq_, is_
 from sqlalchemy import exc
 from sqlalchemy.sql import table
 from sqlalchemy.testing import fixtures, AssertsCompiledSQL
@@ -12,7 +12,8 @@ from sqlalchemy import Integer, Text, LargeBinary, Unicode, UniqueConstraint,\
     literal_column, VARCHAR, create_engine, Date, NVARCHAR, \
     ForeignKeyConstraint, Sequence, Float, DateTime, cast, UnicodeText, \
     union, except_, type_coerce, or_, outerjoin, DATE, NCHAR, outparam, \
-    PrimaryKeyConstraint, FLOAT
+    PrimaryKeyConstraint, FLOAT, INTEGER
+from sqlalchemy.dialects.oracle.base import NUMBER
 from sqlalchemy.testing import assert_raises
 from sqlalchemy.testing.engines import testing_engine
 from sqlalchemy.testing.schema import Table, Column
@@ -534,3 +535,46 @@ class DBLinkReflectionTest(fixtures.TestBase):
         eq_(list(t.primary_key), [t.c.id])
 
 
+class TypeReflectionTest(fixtures.TestBase):
+    __only_on__ = 'oracle'
+    __backend__ = True
+
+    @testing.provide_metadata
+    def _run_test(self, specs, attributes):
+        columns = [Column('c%i' % (i + 1), t[0]) for i, t in enumerate(specs)]
+        m = self.metadata
+        Table('oracle_types', m, *columns)
+        m.create_all()
+        m2 = MetaData(testing.db)
+        table = Table('oracle_types', m2, autoload=True)
+        for i, (reflected_col, spec) in enumerate(zip(table.c, specs)):
+            expected_spec = spec[1]
+            reflected_type = reflected_col.type
+            is_(type(reflected_type), type(expected_spec))
+            for attr in attributes:
+                eq_(
+                    getattr(reflected_type, attr),
+                    getattr(expected_spec, attr),
+                    "Column %s: Attribute %s value of %s does not "
+                    "match %s for type %s" % (
+                        "c%i" % (i + 1),
+                        attr,
+                        getattr(reflected_type, attr),
+                        getattr(expected_spec, attr),
+                        spec[0]
+                    )
+                )
+
+    def test_integer_types(self):
+        specs = [
+            (Integer, INTEGER(),),
+            (Numeric, INTEGER(),),
+        ]
+        self._run_test(specs, [])
+
+    def test_number_types(self):
+        specs = [
+            (Numeric(5, 2), NUMBER(5, 2),),
+            (NUMBER, NUMBER(),),
+        ]
+        self._run_test(specs, ['precision', 'scale'])
-- 
2.47.2