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 <[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 waitthat'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')
Re: Using fully qualified table_name.database_object in
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 waitthat'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).
RE: Using fully qualified table_name.database_object in
Our application vendor code doesn't pre-qualify table references and to isolate batch processes from running under the object owner. I use synonyms (public) so the code doesn't have to change but I get the security isolation that I want. I have 5800 synonyms in our OLTP environment and about 1300 in our reporting environment. I have not found it difficult to manage (well not very - the wrapped database links are a little more work) and our application is always changing (or so it seems). What's the big deal with herding cats. The sleep for like 23 1/2 hrs a day. David Davis Manulife Financial From: "Nelson, Allan" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: Using fully qualified table_name.database_object in Date: Mon, 24 Feb 2003 10:18:53 -0800 Most application developers in my experience are in love with synonyms exactly so they won't have to fully qualify the table name. Oracle uses them fairly extensively in APPs. They do require some overhead for looking up the synonym and they can be a mess if nested deeply enough. Controlling use of synonyms on the other hand is a lot like herding cats. Allan -Original Message- From: laura pena [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:29 AM To: Multiple recipients of list ORACLE-L code? 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! <http://rd.yahoo.com/finance/mailtagline/*http://taxes.yahoo.com/> Tax Center - forms, calculators, tips, and more __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: david davis 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).
Re: Using fully qualified table_name.database_object in application code?
Laura, Where I work currently there is a requirement that all database update scripts (which can be DDL, DML, or a combination the two) have all objects prefixed by schema. However the schema is passed to the script as a parameter. This is a standard which was implemented before my time, however I believe part of the reasoning is to allow the DBA's to run the scripts from their schema without problem. The other bonus is that is the person executing the script is connected to the wrong database then an error will be raised rather than modifying the incorrect schema. Personally though I prefer to stay away from them. A good example are the stored procedure's used here. Some of them had the schema as a parameter, which of course gets converted to a literal before compilation. Now if I use a tool to create a copy of the database (for a specific test or development environment) it is unable to compile the stored procedures, and the workaround is a clumsy "open it in TOAD and search-and-replace". I guess you need to consider the following: 1) Will the application be ever executed against a different schema, which may have a different schema name. We use several development and test schemas to allow parallel workstreams and I find it easier to create a schema when required, rather than an entire database. 2) If someone connects to the wrong schema do you want the application to work? Even if you do you can either fully-qualify everything or look into private or public synonyms. Sometimes connecting via a different schema is an easy way to create a read-only account. 3) Will the fully qualified name be hard-coded as a literal or a parameter? Coding as a literal is extremely limiting, coding as a parameter at least provides some flexibility. Regards, Mark. laura pena yahoo.com> cc: Sent by:Subject: Using fully qualified table_name.database_object in [EMAIL PROTECTED] application code? 25/02/2003 03:29 Please respond to ORACLE-L 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 <<>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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') a
Re: Using fully qualified table_name.database_object in
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 waitthat'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).
Re: Using fully qualified table_name.database_object in application code?
The best answer to your question is probably on Steve Adams' website - search for the word 'synonym'. On high-concurrency systems with large numbers of users, the use of public synonyms leads to lots of overhead and contention handling negative dependency checking. (When I reference DUAL, I have to know that I don't own an object called DUAL that over-rides the public synonym called DUAL - so the library cache gets filled with lots of 'non-existent' objects of the same name - which adds to the length of time that it takes to search a chain for something that should exist). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 24 February 2003 16:29 application code? > > 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Using fully qualified table_name.database_object in application code?
Consensus seems to be it isn't a good idea. An alternative to public synonyms is a to included 'alter session set current_schema=' alter session set current_schema=scott e.g. alter session set current_schema=scott Jared On Monday 24 February 2003 08:29, laura pena wrote: > 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 Content-Type: text/html; charset="us-ascii"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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).
Re: Using fully qualified table_name.database_object in application code?
I try not to do it. Here's an example: I have one central schema in development, this schema's objects relate most closely to my production schema. I keep a set of public synonyms that refer to this schema's objects. As developers want to make changes to objects, I allow them to make their own copy of the object. This allows the developer to access only his object without having to make code changes, as the name in the developer's schema is the same as the public synonym. Once the developer's code moves to test and production, no hard-coded names have to be changed. If we hard-coded the owner.object_name, we'd have to make changes to code as it moves through the various environments, something we don't want to do --- laura pena <[EMAIL PROTECTED]> wrote: > > 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 __ 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).
RE: Using fully qualified table_name.database_object in application code?
One possible disadvantage is that your apps are then 'tied' to a particular schema... if you wanted to run them against another schema, you would have to go back and modify everything... To get around this: You could create public synonyms pointing to a particular schema's tables so if a reason ever arose to switch schema you'd only have to modify one set of code... your public synonym script. Wayne Straughn -Original Message-From: laura pena [mailto:[EMAIL PROTECTED]Sent: Monday, February 24, 2003 12:29 PMTo: Multiple recipients of list ORACLE-LSubject: Using fully qualified table_name.database_object in application code? 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
RE: Using fully qualified table_name.database_object in
Title: Message Most application developers in my experience are in love with synonyms exactly so they won't have to fully qualify the table name. Oracle uses them fairly extensively in APPs. They do require some overhead for looking up the synonym and they can be a mess if nested deeply enough. Controlling use of synonyms on the other hand is a lot like herding cats. Allan -Original Message-From: laura pena [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:29 AMTo: Multiple recipients of list ORACLE-LSubject: Using fully qualified table_name.database_object in application code? 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 __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]
