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                                                                                        
                    <lizzpenaorclgrp@       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    yahoo.com>              cc:                                                                       
                    Sent by:                Subject:     Using fully qualified table_name.database_object in          
                    root_at_fatcity.com         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: mrichard_at_transurban.com.au

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).