Oracle dblink reflection

[Alexey Ismailov]

Hi, i have a very bad issue on SA using Oracle database link.

I have 2 Oracle (11g) database instances, one used for my app, in other stored some data, which i need to use.

I've created a dblink:

CREATE DATABASE LINK <link_name> CONNECT TO <SID> IDENTIFIED BY <password> USING 'localhost:1521/ORCL'

And synonym for table that i need:

CREATE SYNONYM swells FOR <table_name>@<link_name>

There is a restriction: i can only read data from this table, i can't affect another application by doing something wrong.

So, i trying to reflect on this table:

from sqlalchemy import *

engine = create_engine('oracle://<user>:<password>@<ip>:1521/<instance>', encoding = "windows-1251", echo='debug')

meta = MetaData(bind=engine)

t = Table("swells", meta,

    Column('col1', String, key='col1'),

    Column('col2', String, key='col2'),

    Column('col3', String, key='col3'),

    Column('col_pk', Integer, Sequence(u"col_pk_seq"), primary_key=True, key='id'),

    oracle_resolve_synonyms=True,

    autoload=True,

    autoload_with=engine

)

And i'm getting this error:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: таблица или представление пользователя не существует

'SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNSSELENA_WELLS WHERE table_name = :table_name AND owner = :owner ORDER BY column_id' {'owner': "<owner_name>", 'table_name': "<table_name>"}

I'm little cofused... The first of all, the owner of this table is different from my user (this obvious), and ALL_TAB_COLUMNSSELENA_WELLS should be ALL_TAB_COLUMN@SSELENA_WELLS (cause this is the way to load linked objects in Oracle).

I've searched in source of SA, found this in dialects/oracle/base.py (from line 631 in get_columns):

@reflection.cache
    def get_columns(self, connection, table_name, schema=None, **kw):
        """

        kw arguments can be:

            oracle_resolve_synonyms

            dblink

        """

        resolve_synonyms = kw.get('oracle_resolve_synonyms', False)
        dblink = kw.get('dblink', '')
        info_cache = kw.get('info_cache')

        (table_name, schema, dblink, synonym) = \
            self._prepare_reflection_args(connection, table_name, schema,
                                          resolve_synonyms, dblink,
                                          info_cache=info_cache)
        columns = []
        c = connection.execute(sql.text(
                "SELECT column_name, data_type, data_length, data_precision, data_scale, "
                "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s "
                "WHERE table_name = :table_name AND owner = :owner" % {'dblink': dblink}),
                               table_name=table_name, owner=schema)

The first, nobody used the reflection on dblink before? If this is a bug, what should i do? I can change the _prepare_reflection_args function in base.py, adding the "@" to dblink and remove owner, but is this a right way?

So, i'm stuck... And sorry for my bad english...

[Michael Bayer]

I don't have access to a DBLINK environment, so here are some things:

1. tell me the output of this query:

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

2. attempt this patch:

diff -r 79b0b8c5131b 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] != '@':

+            dblink = "@" + dblink

         if not owner:

             owner = self.denormalize_name(schema or self.default_schema_name)

         return (actual_name, owner, dblink, synonym)

Let me know here, or on http://www.sqlalchemy.org/trac/ticket/2619 - thanks.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/qcYnSCxzsk0J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.