There is also a bug in 8i with set current_schema. We use a login trigger to set current_schema for client database connections. It works great unless the client's code is PL/SQL. They don't get an ORA-600, but instead get PLS-201. Creating the public synonym fixes that problem.
Rachel Carmichael wrote: > > I have, unfortunately, a perfect reason for using synonyms and not > fully qualifying names... > > We've just opened an iTAR on this: > > some time this afternoon, the other DBA tried to recreate several of > the stored procedures, functions and packages. Normal code development > in the test database. Unfortunately, and we still don't know why, we > got several ora-600 errors and we cannot drop or replace several of the > stored procedures. We think that they were in use when he tried this, > and that they got locked. > > We tried bouncing the database. No help, still stuck, can't recreate > etc. Developers are sitting around twiddling their thumbs and planning > the lynching. Support says something about System types getting > dropped, which makes no sense to us, we don't touch Oracle-created > types. > > We don't want to even try to drop the user, in case that makes things > worse. Even if it works, we then have nothing to work on with Support > so that it doesn't happen again. > > HOWEVER.... we use public synonyms for the procedures and no program is > hard-coded to use the owner.procedure name. So I told the other DBA > to create a new user, create the procedures/packages/functions owned > by this user and to drop and recreate the public synonyms. > > Developers can work and we have the mess still available to work on > with Oracle. Oh yeah, the other DBA gets to go home :) > > > > --- Jay Hostetter <[EMAIL PROTECTED]> wrote: > > We use fully qualified table names to avoid confusion. Ever poke > > around in Oracle Apps (11i) databases? "OK...it references an object > > owned by APPS, but wait....that's a synonym that points to a table in > > INV..." > > Synonyms can make your applications "portable" to another schema. > > However, in the 8 years that we've been growing our own applications, > > we've never "ported" to another schema. The one advantage that I can > > think of is that you can have multiple application schemas in the > > same database for testing purposes. Your developers could then > > reference whichever schema they want to use for testing via synonyms. > > However, I prefer to spend less time tracking down synonyms by not > > using them in the first place. > > > > Jay > > > > >>> [EMAIL PROTECTED] 02/24/03 11:29AM >>> > > > > I would like to know if it is advocated to use fully qualified > > table_name.database objects in application code. > > > > Example would be schema.table_name in a PL/SQL code. > > > > I would like to know the Pros/Cons if there are any? > > > > Thanks in advance. > > > > > > > > --------------------------------- > > Do you Yahoo!? > > Yahoo! Tax Center - forms, calculators, tips, and more > > > > > > > > **DISCLAIMER > > This e-mail message and any files transmitted with it are intended > > for the use of the individual or entity to which they are addressed > > and may contain information that is privileged, proprietary and > > confidential. If you are not the intended recipient, you may not use, > > copy or disclose to anyone the message or any information contained > > in the message. If you have received this communication in error, > > please notify the sender and delete this e-mail message. The contents > > do not represent the opinion of D&E except to the extent that it > > relates to their official business. > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jay Hostetter > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting services > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, more > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
