oracle dblink syntax

Issue #2619 resolved

Mike Bayer repo owner created an issue 2012-11-27

seems like an @ sign isn't provided to us by the SYNONYMS view:

diff -r e2697d547ec8c24c9a37a72fc60abe73b7dee81b lib/sqlalchemy/dialects/oracle/base.py
--- a/lib/sqlalchemy/dialects/oracle/base.py    Sat Nov 24 16:14:58 2012 -0500
+++ b/lib/sqlalchemy/dialects/oracle/base.py    Tue Nov 27 10:40:50 2012 -0500
@@ -841,6 +841,8 @@
             actual_name = self.denormalize_name(table_name)
         if not dblink:
             dblink = ''
+        elif dblink[0](0) != '@':
+            dblink = "@" + dblink
         if not owner:
             owner = self.denormalize_name(schema or self.default_schema_name)
         return (actual_name, owner, dblink, synonym)

Comments (12)

Former user Account Deleted

(original author: teddy) Hi! Thanks for your reply!

Here's output of

SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE synonym_name='SWELLS'

(Oracle required synonym_name in uppercase, otherwise it returns nothing):

Cols: 'OWNER', 'TABLE_OWNER', 'TABLE_NAME', 'DB_LINK', 'SYNONYM_NAME'
Rows: OMEGA, None, СКВАЖИНЫ_ЦЕЛИКОМ, SELENA_WELLS, SWELLS

Full dump from python console:

>>> conn = engine.connect() # engine created with echo='debug'
>>> conn.execute("SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE synonym_name='SWELLS'").fetchone()
2012-11-28 10:31:00,292 INFO sqlalchemy.engine.base.Engine SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE synonym_name='SWELLS'
2012-11-28 10:31:00,296 INFO sqlalchemy.engine.base.Engine {}
2012-11-28 10:31:00,404 DEBUG sqlalchemy.engine.base.Engine Col ('OWNER', 'TABLE_OWNER', 'TABLE_NAME', 'DB_LINK', 'SYNONYM_NAME')
2012-11-28 10:31:00,405 DEBUG sqlalchemy.engine.base.Engine Row (u'OMEGA', None, u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b_\u0426\u0415\u041b\u0418\u041a\u041e\u041c', u'SELENA_WELLS', u'SWELLS')
(u'OMEGA', None, u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b_\u0426\u0415\u041b\u0418\u041a\u041e\u041c', u'SELENA_WELLS', u'SWELLS')

Results after applying a patch on /lib/sqlalchemy/dialects/oracle/base.py:

>>> skv = Table("SWELLS", meta, oracle_resolve_synonyms=True, autoload=True, autoload_with=engine)
2012-11-28 10:44:11,713 INFO sqlalchemy.engine.base.Engine SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE synonym_name = :synonym_name
2012-11-28 10:44:11,717 INFO sqlalchemy.engine.base.Engine {'synonym_name': u'SWELLS'}
2012-11-28 10:44:11,846 DEBUG sqlalchemy.engine.base.Engine Col ('OWNER', 'TABLE_OWNER', 'TABLE_NAME', 'DB_LINK', 'SYNONYM_NAME')
2012-11-28 10:44:11,849 DEBUG sqlalchemy.engine.base.Engine Row (u'OMEGA', None, u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b_\u0426\u0415\u041b\u0418\u041a\u041e\u041c', u'SELENA_WELLS', u'SWELLS')
2012-11-28 10:44:11,857 INFO sqlalchemy.engine.base.Engine SELECT column_name, data_type, char_length, data_precision, data_scale, nullable,
 data_default FROM ALL_TAB_COLUMNS@SELENA_WELLS WHERE table_name = :table_name AND owner = :owner ORDER BY column_id
2012-11-28 10:44:11,867 INFO sqlalchemy.engine.base.Engine {'owner': u'OMEGA', 'table_name': u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b_\u0426\u0415\u041b\u0418\u041a\u041e\u041c'}
2012-11-28 10:44:11,878 DEBUG sqlalchemy.engine.base.Engine Col ('COLUMN_NAME', 'DATA_TYPE', 'CHAR_LENGTH', 'DATA_PRECISION', 'DATA_SCALE', 'NULLABLE', 'DATA_DEFAULT')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 318, in __new__
    table._init(name, metadata, *args, **kw)
  File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 381, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 397, in _autoload
    self, include_columns, exclude_columns
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2425, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1968, in run_callable
    return callable_(self, *args, **kwargs)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 260, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 412, in reflecttable
    raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: SWELLS

2012-11-28T01:46:28+00:00

Former user Account Deleted

(original author: teddy) I'm tryed to change base.py like that:

*** basediff.py 2012-11-28 11:04:03.869209700 +0400
--- base.py 2012-11-28 11:02:45.559730700 +0400
***************
*** 882,890 ****
          c = connection.execute(sql.text(
                  "SELECT column_name, data_type, %(char_length_col)s, data_precision, data_scale, "
                  "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s "
!                 "WHERE table_name = :table_name AND owner = :owner "
                  "ORDER BY column_id" % {'dblink': dblink, 'char_length_col':char_length_col}),
!                                table_name=table_name, owner=schema)

          for row in c:
              (colname, orig_colname, coltype, length, precision, scale, nullable, default) = \
--- 882,890 ----
          c = connection.execute(sql.text(
                  "SELECT column_name, data_type, %(char_length_col)s, data_precision, data_scale, "
                  "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s "
!                 "WHERE table_name = :table_name "
                  "ORDER BY column_id" % {'dblink': dblink, 'char_length_col':char_length_col}),
!                                table_name=table_name)

          for row in c:
              (colname, orig_colname, coltype, length, precision, scale, nullable, default) = \

And now table reflected:

skv = Table("swells", meta, oracle_resolve_synonyms=True, autoload=True)
>>> meta.tables
immutabledict({'swells': Table('swells', MetaData(bind=Engine(...)), 
Column(...), schema=None)})

It's interesting that for now table name accepted in lowercase too...

2012-11-28T02:18:02+00:00

Former user Account Deleted

(original author: teddy) And some info about table from database linked:

SQL> SELECT owner, table_name FROM all_tables WHERE table_name='СКВАЖИНЫ_ЦЕЛИКОМ';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SELENA                         СКВАЖИНЫ_ЦЕЛИКОМ

So, as we can see, Oracle doesn't shows owner if table is in remote db... Looks like this happens because owner of table is present only in remote base, there is no such user in local.

2012-11-28T04:58:11+00:00

Mike Bayer reporter
can you try the attached patch against the latest default tip:

http://hg.sqlalchemy.org/sqlalchemy/archive/9d817406e2da0bda91c1e9a1411165f229e3d5c2.tar.gz

this patch attempts to ignore the "owner" parameter for all queries if dblink is present.
- 2012-12-06T19:53:28+00:00
Mike Bayer reporter
- changed status to resolved
OK, well I was able to test this by creating a DBLINK in oracle XE to itself, though this test involved the same "owner" on both sides. I have it reading "username" from user_db_links right now in order to determine the username, we'll see how that goes. Reopen if futher issues.

70d38af42e42016601ad81119b42f7bc381f5f43
- 2012-12-09T18:29:12+00:00
Former user Account Deleted
(original author: teddy) Sorry, it take some time to test between work. Found a little bug, which appears when there is no owner. This patch for default tip.
- 2012-12-09T18:53:07+00:00
Former user Account Deleted
- changed status to open
- removed status
(original author: teddy)
- 2012-12-09T18:55:36+00:00

Former user Account Deleted

(original author: teddy) Oh, yeah, the bug was:

sqlalchemy.exc.StatementError: A value is required for bind parameter u'table_nameORDER' (original cause: InvalidRequestError: A value is required for bind parameter u'table_nameORDER')

Sorry for my stupidity at the night =)

2012-12-09T18:58:53+00:00

Mike Bayer reporter
yeah, I fixed that already. try out the latest tip. just need to see if what I'm doing for "owner" works for you.
- 2012-12-10T08:37:27+00:00

Former user Account Deleted

(original author: teddy) Thank you for your attention and help! With latest default tip (http://hg.sqlalchemy.org/sqlalchemy/archive/70d38af42e42016601ad81119b42f7bc381f5f43.tar.gz) everything seems to work fine (full log for debug):

ActivePython 2.7.2.5 (ActiveState Software Inc.) based on
Python 2.7.2 (default, Jun 24 2011, 12:21:10) [v.1500 32 bit (Intel)](MSC) on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import *
>>> engine = create_engine('oracle://<user>:<pass>@<IP>:1521/omega', encoding = "windows-1251", echo='debug')
>>> meta = MetaData(bind=engine)
>>> skv = Table("WELLI", meta, oracle_resolve_synonyms=True, autoload=True)
2012-12-10 17:42:25,118 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL
2012-12-10 17:42:25,118 INFO sqlalchemy.engine.base.Engine {}
2012-12-10 17:42:25,121 DEBUG sqlalchemy.engine.base.Engine Col ('USER',)
2012-12-10 17:42:25,121 DEBUG sqlalchemy.engine.base.Engine Row (u'OMEGA',)
2012-12-10 17:42:25,128 INFO sqlalchemy.engine.base.Engine SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE synonym_name = :synonym_name
2012-12-10 17:42:25,128 INFO sqlalchemy.engine.base.Engine {'synonym_name': u'WELLI'}
2012-12-10 17:42:25,255 DEBUG sqlalchemy.engine.base.Engine Col ('OWNER', 'TABLE_OWNER', 'TABLE_NAME', 'DB_LINK', 'SYNONYM_NAME')
2012-12-10 17:42:25,256 DEBUG sqlalchemy.engine.base.Engine Row (u'OMEGA', None, u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b', u'SELENA', u'WELLI')
2012-12-10 17:42:25,259 INFO sqlalchemy.engine.base.Engine SELECT username FROM user_db_links WHERE db_link=:link
2012-12-10 17:42:25,259 INFO sqlalchemy.engine.base.Engine {'link': u'SELENA'}
2012-12-10 17:42:25,269 DEBUG sqlalchemy.engine.base.Engine Col ('USERNAME',)
2012-12-10 17:42:25,272 INFO sqlalchemy.engine.base.Engine SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS@SELENA WHERE table_name = :table_name ORDER BY column_id
2012-12-10 17:42:25,273 INFO sqlalchemy.engine.base.Engine {'table_name': u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b'}
2012-12-10 17:42:29,828 DEBUG sqlalchemy.engine.base.Engine Col ('COLUMN_NAME', 'DATA_TYPE', 'CHAR_LENGTH', 'DATA_PRECISION', 'DATA_SCALE', 'NULLABLE', 'DATA_DEFAULT')
2012-12-10 17:42:29,924 DEBUG sqlalchemy.engine.base.Engine Row (u'\u0421\u041a\u0412\u0410\u0416_\u0418\u0414', u'NUMBER', 0, None, None, u'N', None)
2012-12-10 17:42:29,924 DEBUG sqlalchemy.engine.base.Engine Row (u'\u041d\u041e\u041c\u0415\u0420_\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b', u'NUMBER', 0, None, None, u'N', None)
2012-12-10 17:42:29,926 DEBUG sqlalchemy.engine.base.Engine Row (u'\u041b\u0418\u0422\u0415\u0420\u0410', u'VARCHAR2', 5, None, None, u'Y', None)
2012-12-10 17:42:29,926 DEBUG sqlalchemy.engine.base.Engine Row (u'\u0421\u0422\u0420\u0423\u041a\u0422\u0423\u0420\u041d\u042b\u0419_\u041d\u041e\u041c\u0415\u0420', u'VARCHAR2', 20, None, None, u'N', None)
2012-12-10 17:42:29,926 DEBUG sqlalchemy.engine.base.Engine Row (u'\u041a\u041f\u041b_\u041a\u041f\u041b_\u0418\u0414', u'NUMBER', 0, None, None, u'N', None)
2012-12-10 17:42:29,927 DEBUG sqlalchemy.engine.base.Engine Row (u'\u041a\u041e\u0414_OIS', u'VARCHAR2', 20, None, None, u'Y', None)
2012-12-10 17:42:29,927 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints@SELENA ac,
all_cons_columns@SELENA loc,
all_cons_columns@SELENA rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2012-12-10 17:42:29,927 INFO sqlalchemy.engine.base.Engine {'table_name': u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b'}
2012-12-10 17:42:35,118 DEBUG sqlalchemy.engine.base.Engine Col ('CONSTRAINT_NAME', 'CONSTRAINT_TYPE', 'LOCAL_COLUMN', 'REMOTE_TABLE', 'REMOTE_COLUMN', 'REMOTE_OWNER', 'LOC_POS', 'REM_POS')
2012-12-10 17:42:35,121 DEBUG sqlalchemy.engine.base.Engine Row (u'\u0421\u041a\u0412\u0410\u0416_PK', u'P', u'\u0421\u041a\u0412\u0410\u0416_\u0418\u0414', None, None, None, 1, None)
2012-12-10 17:42:35,124 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position
2012-12-10 17:42:35,131 INFO sqlalchemy.engine.base.Engine {'table_name': u'WELLI', 'schema': u'OMEGA'}
2012-12-10 17:42:35,134 DEBUG sqlalchemy.engine.base.Engine Col ('INDEX_NAME', 'COLUMN_NAME', 'UNIQUENESS')
2012-12-10 17:42:35,138 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2012-12-10 17:42:35,151 INFO sqlalchemy.engine.base.Engine {'owner': u'OMEGA', 'table_name': u'WELLI'}
2012-12-10 17:42:37,703 DEBUG sqlalchemy.engine.base.Engine Col ('CONSTRAINT_NAME', 'CONSTRAINT_TYPE', 'LOCAL_COLUMN', 'REMOTE_TABLE', 'REMOTE_COLUMN', 'REMOTE_OWNER', 'LOC_POS', 'REM_POS')
>>> conn = engine.connect()
>>> q = select([skv](skv))
>>> r = conn.execute(q)
2012-12-10 17:43:20,000 INFO sqlalchemy.engine.base.Engine SELECT "WELLI"."СКВАЖ_ИД", "WELLI"."НОМЕР_СКВАЖИНЫ", "WELLI"."ЛИТЕРА", "WELLI"."СТРУКТУРНЫЙ_НОМЕР", "WELLI"."КПЛ_КПЛ_ИД", "WELLI"."КОД_OIS"
FROM "WELLI"
2012-12-10 17:43:20,000 INFO sqlalchemy.engine.base.Engine {}
2012-12-10 17:43:20,006 DEBUG sqlalchemy.engine.base.Engine Col ('\xd1\xca\xc2\xc0\xc6_\xc8\xc4', '\xcd\xce\xcc\xc5\xd0_\xd1\xca\xc2\xc0\xc6\xc8\xcd\xdb', '\xcb\xc8\xd2\xc5\xd0\xc0', '\xd1\xd2\xd0\xd3\xca\xd2\xd3\xd0\xcd\xdb\xc9_\xcd\xce\xcc\xc5\xd0', '\xca\xcf\xcb_\xca\xcf\xcb_\xc8\xc4', '\xca\xce\xc4_OIS')
>>> t = r.fetchone()
2012-12-10 17:43:41,759 DEBUG sqlalchemy.engine.base.Engine Row (3175, 10114, None, u'10114', 527, None)
>>> t
(3175.0, 10114.0, None, u'10114', 527.0, None)

2012-12-10T08:48:45+00:00

Mike Bayer reporter

changed status to resolved

great, that worked perfectly, we can see that your DB returned no "username" for the DB_LINK:

2012-12-10 17:42:25,259 INFO sqlalchemy.engine.base.Engine SELECT username FROM user_db_links WHERE db_link=:link
2012-12-10 17:42:25,259 INFO sqlalchemy.engine.base.Engine {'link': u'SELENA'}
2012-12-10 17:42:25,269 DEBUG sqlalchemy.engine.base.Engine Col ('USERNAME',)

and then we just didn't use OWNER in the queries:

2012-12-10 17:42:25,272 INFO sqlalchemy.engine.base.Engine SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS@SELENA WHERE table_name = :table_name ORDER BY column_id
2012-12-10 17:42:25,273 INFO sqlalchemy.engine.base.Engine {'table_name': u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b'}
2012-12-10 17:42:29,828

on my test setup, I get "username" back, probably because I made the dblink a fixed user.

2012-12-10T08:58:06+00:00

Mike Bayer reporter
- removed milestone
Removing milestone: 0.8.0final (automated comment)
- 2014-02-23T18:15:12+00:00
Log in to comment

Assignee: Mike Bayer

Type: bug

Priority: major

Status: resolved

Component: oracle

Milestone: –

Votes: 0

Watchers: 0