From f1905b619d90163771b61ff99f8f9661b031dbd7 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 25 Oct 2013 19:11:53 -0400
Subject: [PATCH] - Fixed bug where Oracle table reflection using synonyms
 would fail if the synonym and the table were in different remote schemas.
 Patch to fix courtesy Kyle Derr. [ticket:2853]

---
 doc/build/changelog/changelog_08.rst   |  9 ++++++++
 lib/sqlalchemy/dialects/oracle/base.py | 12 ++++++-----
 test/dialect/test_oracle.py            | 29 ++++++++++++++++++++++++++
 3 files changed, 45 insertions(+), 5 deletions(-)

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 5aed2b7d84..a77dd67443 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -10,6 +10,15 @@
 .. changelog::
     :version: 0.8.3
 
+    .. change::
+        :tags: bug, oracle
+        :tickets: 2853
+        :versions: 0.9.0
+
+        Fixed bug where Oracle table reflection using synonyms would fail
+        if the synonym and the table were in different remote schemas.
+        Patch to fix courtesy Kyle Derr.
+
     .. change::
         :tags: bug, sql
         :tickets: 2849
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 6d81541cc5..ca8d50636a 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -133,9 +133,10 @@ Synonym/DBLINK Reflection
 -------------------------
 
 When using reflection with Table objects, the dialect can optionally search for tables
-indicated by synonyms that reference DBLINK-ed tables by passing the flag
-oracle_resolve_synonyms=True as a keyword argument to the Table construct.  If DBLINK
-is not in use this flag should be left off.
+indicated by synonyms, either in local or remote schemas or accessed over DBLINK,
+by passing the flag oracle_resolve_synonyms=True as a
+keyword argument to the Table construct.   If synonyms are not in use
+this flag should be left off.
 
 """
 
@@ -795,14 +796,15 @@ class OracleDialect(default.DefaultDialect):
         returns the actual name, owner, dblink name, and synonym name if found.
         """
 
-        q = "SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE "
+        q = "SELECT owner, table_owner, table_name, db_link, "\
+                    "synonym_name FROM all_synonyms WHERE "
         clauses = []
         params = {}
         if desired_synonym:
             clauses.append("synonym_name = :synonym_name")
             params['synonym_name'] = desired_synonym
         if desired_owner:
-            clauses.append("table_owner = :desired_owner")
+            clauses.append("owner = :desired_owner")
             params['desired_owner'] = desired_owner
         if desired_table:
             clauses.append("table_name = :tname")
diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py
index e3d35e3945..953dfe4e36 100644
--- a/test/dialect/test_oracle.py
+++ b/test/dialect/test_oracle.py
@@ -640,9 +640,18 @@ create table test_schema.child(
     parent_id integer references test_schema.parent(id)
 );
 
+create table local_table(
+    id integer primary key,
+    data varchar2(50)
+);
+
 create synonym test_schema.ptable for test_schema.parent;
 create synonym test_schema.ctable for test_schema.child;
 
+create synonym test_schema_ptable for test_schema.parent;
+
+create synonym test_schema.local_table for local_table;
+
 -- can't make a ref from local schema to the
 -- remote schema's table without this,
 -- *and* cant give yourself a grant !
@@ -658,8 +667,12 @@ grant references on test_schema.child to public;
         for stmt in """
 drop table test_schema.child;
 drop table test_schema.parent;
+drop table local_table;
 drop synonym test_schema.ctable;
 drop synonym test_schema.ptable;
+drop synonym test_schema_ptable;
+drop synonym test_schema.local_table;
+
 """.split(";"):
             if stmt.strip():
                 testing.db.execute(stmt)
@@ -684,6 +697,22 @@ drop synonym test_schema.ptable;
         finally:
             meta.drop_all()
 
+    def test_reflect_alt_table_owner_local_synonym(self):
+        meta = MetaData(testing.db)
+        parent = Table('test_schema_ptable', meta, autoload=True, oracle_resolve_synonyms=True)
+        self.assert_compile(parent.select(),
+                "SELECT test_schema_ptable.id, "
+                "test_schema_ptable.data FROM test_schema_ptable")
+        select([parent]).execute().fetchall()
+
+    def test_reflect_alt_synonym_owner_local_table(self):
+        meta = MetaData(testing.db)
+        parent = Table('local_table', meta, autoload=True, oracle_resolve_synonyms=True, schema="test_schema")
+        self.assert_compile(parent.select(),
+                "SELECT test_schema.local_table.id, "
+                "test_schema.local_table.data FROM test_schema.local_table")
+        select([parent]).execute().fetchall()
+
     def test_create_same_names_implicit_schema(self):
         meta = MetaData(testing.db)
         parent = Table('parent', meta,
-- 
2.47.3