Re: Synonym Question

On May 9, 12:48 pm, "pankaj_wolfhun..._at_yahoo.co.in" <pankaj_wolfhun..._at_yahoo.co.in> wrote:
> Greetings,
> I have 3 schemas in my Development ORACLE database
> say SCHEMA1, SCHEMA2 and SCHEMA3.
> And
> I have 3 schemas in QA ORACLE database namely SCHEMA1, SCHEMA2 and
> SCHEMA4.
>
> Objects in different schemas referring to each other. (Select * from
> SCHEMA1.TBL1, SCHEMA2.TBL2, SCHEMA3.TBL3).
>
> When I move my scripts from Development to QA environment,
> these scripts are not working because of SCHEMA3 is referred as
> SCHEMA4
> in QA environment.
>
> Client does not want to change the DB scripts. One alternative is
> creating a synonym
>
> Can I create a SYNONYM for SCHEMA4 as SCHEMA3 in QA environment? Whats
> the other alternatives?
>
> Help would be appreciated.

Synonyms are created on *object* level, not on *schema* level. You would need synonyms for every individual object. A small bit of pl/sql would loop through all_objects and create them. Apparently you are currently hardcoding everything when you reference schema3 from schema1, because otherwise you would have simply recreated the schema1 synonyms and you would have been done. Instead of creating spaghetti (having refer schema1 to schema3, where all objects in schema3 are synonyms to schema4) it would be advisable to reconsider and remove all hardcoding. You see what has come from it.

BTW: will you ever learn to do your homework prior to asking a question?
Why are 99 percent of all of your questions questions you could have looked up yourself?

--
Sybrand Bakker
Senior Oracle DBA