[sqlalchemy] Re: Oracle: There are multiple tables visible...
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? that would be a bug, yes. but, try specifying schema and all the names using lowercase characters (not ALL_UPPERCASE as you may be doing) - SQLA will ensure that it uses case insensitive identifiers (it converts to uppercase as needed when talking to oracle). its possible that there is a mismatch between target names and specified names causing this issue. or maybe the oracle dialect just doesn't interpret the owner part of a foreign key constraint correctly yet (im not easily able to test things like that with Oracle XE). That did the trick! Thanks a lot, Sven --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle: There are multiple tables visible...
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 REQUESTSREQUESTS AINV_USER AINV_OWNER REQUESTSREQUESTS 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle: There are multiple tables visible...
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle: There are multiple tables visible...
Sven A. Schmidt wrote: 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. its been awhile since I've used oracle, but I seem to recall that the permissions on a synonym have no bearing on whether or not you can access the underlying table - you need permissions on both. am I incorrect on this ? 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? that would be a bug, yes. but, try specifying schema and all the names using lowercase characters (not ALL_UPPERCASE as you may be doing) - SQLA will ensure that it uses case insensitive identifiers (it converts to uppercase as needed when talking to oracle). its possible that there is a mismatch between target names and specified names causing this issue. or maybe the oracle dialect just doesn't interpret the owner part of a foreign key constraint correctly yet (im not easily able to test things like that with Oracle XE). Also, if your reflection can't read the tables directly, that would also be a potential explanation for this problem. 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. did you propose that the current default owner on the connection would match to the synonym owner ? 4. synonym parameter, schema = 'AINV_OWNER' on Table(...) Results in the error: Could not determine join condition between parent/child tables... See 2) above. thats a dupe of #2. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
