Re: Synonyms and multiple schemas

Alex Vilner wrote:
> I would like to create a set of synonyms for a group, so that
> all members of the group can reference a set of objects,
> without having to prefix them with schema owner name.
> A set of PUBLIC synonyms will not work in this case, since
> we have 2 schemas.
>
> I also do NOT want to create a set of synonyms for EACH user
> login. I was unable to create synonyms for a role. Is there
> a way to accomplish what we're trying to do?

I had a similiar problem with maintaining versions of PL/SQL code that matched functionality in application code during development. I wrote a PL/SQL procedure that is always called when a user logins. The schema the current user needs to use is passed to the procedure. The procedure uses that and the DBA views to generate and execute dynamic SQL that creates the synonyms to the correct version of the PL/SQL code.

You could something similiar, but just create a synonym to the tables in the correct schema.

One note, don't drop the schema without first droping the synonyms for each user. I used to just drop old schemas and encountered an error where I couldn't drop old synonyms pointing to non-existent schemas. I now have a PL/SQL procedure that first finds all referencing synonyms to a schema, drops them and then drops the schema.

later,
Ed Bruce
Raytheon Systems Co.

p.s. I can't make copies of this code available due to U.S. law restricting export of technical data outside of the U.S. Received on Mon May 04 1998 - 17:38:15 CDT