Re: Using fully qualified table_name.database_object in

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 <jhostetter_at_decommunications.com> 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
> >
> > >>> lizzpenaorclgrp_at_yahoo.com 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: jhostetter_at_decommunications.com
> >
> > 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: lvordos_at_qwest.com

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: ListGuru_at_fatcity.com (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).