Merge "CAST Oracle table_name, owner, others to VARCHAR(128)"

3 files changed, 30 insertions, 12 deletions

diff --git a/doc/build/changelog/unreleased_14/4486.rst b/doc/build/changelog/unreleased_14/4486.rst
new file mode 100644
index 000000000..45007a237
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/4486.rst
@@ -0,0 +1,13 @@
+.. change::
+    :tags: bug, oracle, performance
+    :tickets: 4486
+
+    Added a CAST(VARCHAR2(128)) to the "table name", "owner", and other
+    DDL-name parameters as used in reflection queries against Oracle system
+    views such as ALL_TABLES, ALL_TAB_CONSTRAINTS, etc to better enable
+    indexing to take place against these columns, as they previously would be
+    implicitly handled as NVARCHAR2 due to Python's use of Unicode for strings;
+    these columns are documented in all Oracle versions as being VARCHAR2 with
+    lengths varying from 30 to 128 characters depending on server version.
+    Additionally, test support has been enabled for Unicode-named DDL
+    structures against Oracle databases.
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index b1d7bc440..8b790c70c 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -1608,10 +1608,12 @@ class OracleDialect(default.DefaultDialect):
 
         if not schema:
             schema = self.default_schema_name
+
         cursor = connection.execute(
             sql.text(
                 "SELECT table_name FROM all_tables "
-                "WHERE table_name = :name AND owner = :schema_name"
+                "WHERE table_name = CAST(:name AS VARCHAR2(128)) "
+                "AND owner = CAST(:schema_name AS VARCHAR2(128))"
             ),
             dict(
                 name=self.denormalize_name(table_name),
@@ -1665,13 +1667,15 @@ class OracleDialect(default.DefaultDialect):
         clauses = []
         params = {}
         if desired_synonym:
-            clauses.append("synonym_name = :synonym_name")
+            clauses.append(
+                "synonym_name = CAST(:synonym_name AS VARCHAR2(128))"
+            )
             params["synonym_name"] = desired_synonym
         if desired_owner:
-            clauses.append("owner = :desired_owner")
+            clauses.append("owner = CAST(:desired_owner AS VARCHAR2(128))")
             params["desired_owner"] = desired_owner
         if desired_table:
-            clauses.append("table_name = :tname")
+            clauses.append("table_name = CAST(:tname AS VARCHAR2(128))")
             params["tname"] = desired_table
 
         q += " AND ".join(clauses)
@@ -1846,12 +1850,12 @@ class OracleDialect(default.DefaultDialect):
         text = (
             "SELECT %(columns)s "
             "FROM ALL_TABLES%(dblink)s "
-            "WHERE table_name = :table_name"
+            "WHERE table_name = CAST(:table_name AS VARCHAR(128))"
         )
 
         if schema is not None:
             params["owner"] = schema
-            text += " AND owner = :owner "
+            text += " AND owner = CAST(:owner AS VARCHAR(128)) "
         text = text % {"dblink": dblink, "columns": ", ".join(columns)}
 
         result = connection.execute(sql.text(text), params)
@@ -1916,6 +1920,7 @@ class OracleDialect(default.DefaultDialect):
             identity_cols = "NULL as default_on_null, NULL as identity_options"
 
         params = {"table_name": table_name}
+
         text = """
             SELECT
                 col.column_name,
@@ -1933,7 +1938,7 @@ class OracleDialect(default.DefaultDialect):
             ON col.table_name = com.table_name
             AND col.column_name = com.column_name
             AND col.owner = com.owner
-            WHERE col.table_name = :table_name
+            WHERE col.table_name = CAST(:table_name AS VARCHAR2(128))
             AND col.hidden_column = 'NO'
         """
         if schema is not None:
@@ -2076,7 +2081,8 @@ class OracleDialect(default.DefaultDialect):
         COMMENT_SQL = """
             SELECT comments
             FROM all_tab_comments
-            WHERE table_name = :table_name AND owner = :schema_name
+            WHERE table_name = CAST(:table_name AS VARCHAR(128))
+            AND owner = CAST(:schema_name AS VARCHAR(128))
         """
 
         c = connection.execute(
@@ -2117,7 +2123,7 @@ class OracleDialect(default.DefaultDialect):
             "\na.index_name = b.index_name "
             "\nAND a.table_owner = b.table_owner "
             "\nAND a.table_name = b.table_name "
-            "\nAND a.table_name = :table_name "
+            "\nAND a.table_name = CAST(:table_name AS VARCHAR(128))"
         )
 
         if schema is not None:
@@ -2209,13 +2215,13 @@ class OracleDialect(default.DefaultDialect):
             "\nFROM all_constraints%(dblink)s ac,"
             "\nall_cons_columns%(dblink)s loc,"
             "\nall_cons_columns%(dblink)s rem"
-            "\nWHERE ac.table_name = :table_name"
+            "\nWHERE ac.table_name = CAST(:table_name AS VARCHAR2(128))"
             "\nAND ac.constraint_type IN ('R','P', 'U', 'C')"
         )
 
         if schema is not None:
             params["owner"] = schema
-            text += "\nAND ac.owner = :owner"
+            text += "\nAND ac.owner = CAST(:owner AS VARCHAR2(128))"
 
         text += (
             "\nAND ac.owner = loc.owner"
diff --git a/test/requirements.py b/test/requirements.py
index 6786c8daf..644c01f75 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -900,7 +900,6 @@ class DefaultRequirements(SuiteRequirements):
 
         return skip_if(
             [
-                no_support("oracle", "FIXME: no support in database?"),
                 no_support("sybase", "FIXME: guessing, needs confirmation"),
                 no_support("mssql+pymssql", "no FreeTDS support"),
             ]