Michael, thanks for your reply! Unfortunately, I believe I have to use the synonyms feature, because I cannot use the table owner's schema owner to access the database. Everything works if I use the table owner's schema (AINV_OWNER) but the db policies forbid this for production, so I have to somehow get this to work from another account.
I should have elaborated on this in my initial mail -- I went through the following combinations of options as I far as I am aware of them to try and work around the problem: 1. no synonym parameter, no schema parameter on Table(...) Results in the error: "Couldn't find any column information..." This is obvious: AINV_USER doesn't own the tables and in the absence of the schema parameter SQLA can't find any table info 2. no synonym parameter, schema = 'AINV_OWNER' on Table(...) Results in the error: "Could not determine join condition between parent/child tables..." I had expected this to work but it seems that for some reason SQLA doesn't see the constraint info on tables in another user's schema. Is that a bug? I could work around this but it would mean I have to manually specify all relations which are correctly read from the db if I connect with the table owner schema. I'd like to avoid doing that, because I'm lazy ;) 3. synonym parameter, no schema parameter on Table(...) Results in the error: "There are multiple tables visible..." As described, this results from the _resolve_synonym call in base.py. 4. synonym parameter, schema = 'AINV_OWNER' on Table(...) Results in the error: "Could not determine join condition between parent/child tables..." See 2) above. It seems I'm stuck between a rock and a hard place here ;) Cheers, Sven On Jun 23, 5:03 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > Sven A. Schmidt wrote: > > > Hi, > > > I've hit a problem very recently with autoloading of table info from > > an oracle schema which I believe is caused by a problem inside the > > _resolve_synonym method of oracle/base.py. I've googled around a bit > > but didn't find this issue reported previously. It may well be a > > problem with our db setup but I'm hoping folks on this list will be > > able to shed some light on it either way :) > > > What's happening is that in the db there are two rows returned when > > running the query > > > select OWNER, TABLE_OWNER, TABLE_NAME, SYNONYM_NAME from ALL_SYNONYMS > > WHERE table_name = 'REQUESTS'; > > > Result: > > > AINV_REPORT AINV_OWNER REQUESTS REQUESTS > > AINV_USER AINV_OWNER REQUESTS REQUESTS > > > Inside _resolve_synonym this query is run and if len(rows) >1 the > > following error is raised: > > > "There are multiple tables visible to the schema, you must specify > > owner" > > > I was thinking that perhaps the query used inside _resolve_synonyms > > could/should include a check on the synonym owner to exclude multiple > > matches of the same table exists as a synonym in another user's > > schema. Or would that break other things? > > this assumes that you need to use the resolve_synonyms feature in the first > place (its off by default). oracle_resolve_synonyms is probably not > worth using if you aren't using DBLINK (and maybe not even if you are) - > that was the original use case for it. If you leave the feature off and > just reflect "requests", the whole function won't get involved. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---