reflect Oracle ROWID

Added :class:`_oracle.ROWID` to reflected types as this type may be used in
a "CREATE TABLE" statement.

For 1.4, the cherry-pick also backports a few more type reflection
tests that should pass without issue.

Fixes: #5047
Change-Id: I818dcf68ed81419d0fd5df5e2d51d6fa0f1be7fc
(cherry picked from commit 1c6c5532d8bec105d9aaa7843a176b4e1ef0340a)

4 files changed, 43 insertions, 1 deletions

diff --git a/doc/build/changelog/unreleased_14/5047.rst b/doc/build/changelog/unreleased_14/5047.rst
new file mode 100644
index 000000000..4d08d771f
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/5047.rst
@@ -0,0 +1,6 @@
+.. change::
+    :tags: bug, oracle
+    :tickets: 5047
+
+    Added :class:`_oracle.ROWID` to reflected types as this type may be used in
+    a "CREATE TABLE" statement.
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index c1736a1f9..390ea5098 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -759,6 +759,7 @@ ischema_names = {
     "LONG": LONG,
     "BINARY_DOUBLE": BINARY_DOUBLE,
     "BINARY_FLOAT": BINARY_FLOAT,
+    "ROWID": ROWID,
 }
 
 
diff --git a/setup.cfg b/setup.cfg
index 45859cb6c..b455c8a09 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -177,6 +177,6 @@ mariadb_connector = mariadb+mariadbconnector://scott:tiger@127.0.0.1:3306/test
 mssql = mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server
 mssql_pymssql = mssql+pymssql://scott:tiger@ms_2008
 docker_mssql = mssql+pymssql://scott:tiger^5HHH@127.0.0.1:1433/test
-oracle = oracle://scott:tiger@127.0.0.1:1521
+oracle = oracle://scott:tiger@oracle18c
 oracle8 = oracle://scott:tiger@127.0.0.1:1521/?use_ansi=0
 firebird = firebird://sysdba:mainkey@localhost//Users/classic/foo.fdb
diff --git a/test/dialect/oracle/test_reflection.py b/test/dialect/oracle/test_reflection.py
index b287e1024..836edc0e9 100644
--- a/test/dialect/oracle/test_reflection.py
+++ b/test/dialect/oracle/test_reflection.py
@@ -15,14 +15,23 @@ from sqlalchemy import MetaData
 from sqlalchemy import Numeric
 from sqlalchemy import PrimaryKeyConstraint
 from sqlalchemy import select
+from sqlalchemy import String
 from sqlalchemy import testing
 from sqlalchemy import text
 from sqlalchemy import Unicode
 from sqlalchemy import UniqueConstraint
+from sqlalchemy.dialects.oracle import NVARCHAR2
+from sqlalchemy.dialects.oracle import VARCHAR2
 from sqlalchemy.dialects.oracle.base import BINARY_DOUBLE
 from sqlalchemy.dialects.oracle.base import BINARY_FLOAT
 from sqlalchemy.dialects.oracle.base import DOUBLE_PRECISION
 from sqlalchemy.dialects.oracle.base import NUMBER
+from sqlalchemy.dialects.oracle.base import RAW
+from sqlalchemy.dialects.oracle.base import ROWID
+from sqlalchemy.sql.sqltypes import CHAR
+from sqlalchemy.sql.sqltypes import NCHAR
+from sqlalchemy.sql.sqltypes import NVARCHAR
+from sqlalchemy.sql.sqltypes import VARCHAR
 from sqlalchemy.testing import assert_warns
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import eq_
@@ -817,6 +826,32 @@ class TypeReflectionTest(fixtures.TestBase):
         ]
         self._run_test(metadata, connection, specs, ["precision"])
 
+    def test_string_types(
+        self,
+        metadata,
+        connection,
+    ):
+        specs = [
+            (String(125), VARCHAR(125)),
+            (String(42).with_variant(VARCHAR2(42), "oracle"), VARCHAR(42)),
+            (Unicode(125), VARCHAR(125)),
+            (Unicode(42).with_variant(NVARCHAR2(42), "oracle"), NVARCHAR(42)),
+            (CHAR(125), CHAR(125)),
+            (NCHAR(42), NCHAR(42)),
+        ]
+        self._run_test(metadata, connection, specs, ["length"])
+
+    @testing.combinations(ROWID(), RAW(1), argnames="type_")
+    def test_misc_types(self, metadata, connection, type_):
+        t = Table("t1", metadata, Column("x", type_))
+
+        t.create(connection)
+
+        eq_(
+            inspect(connection).get_columns("t1")[0]["type"]._type_affinity,
+            type_._type_affinity,
+        )
+
 
 class IdentityReflectionTest(fixtures.TablesTest):
     __only_on__ = "oracle"