G-8210: Always use synonyms when accessing objects of another application schema.

Major

Changeability, Maintainability

Reason

If a connection is needed to a table that is placed in a foreign schema, using synonyms is a good choice. If there are structural changes to that table (e.g. the table name changes or the table changes into another schema) only the synonym has to be changed no changes to the package are needed (single point of change). If you only have read access for a table inside another schema, or there is another reason that does not allow you to change data in this table, you can switch the synonym to a table in your own schema. This is also good practice for testers working on test systems.

Example (bad)

declare
   l_product_name oe.products.product_name%type;
   co_price       constant oe.products@list_price%type := 1000;
begin
   select p.product_name
     into l_product_name
     from oe.products p
    where list_price > co_price;
exception
   when no_data_found then
      null; -- handle_no_data_found;
   when too_many_rows then
      null; -- handle_too_many_rows;
end;
/

Example (good)

create synonym oe_products for oe.products;

declare
   l_product_name oe_products.product_name%type;
   co_price       constant oe_products.list_price%type := 1000;
begin
   select p.product_name
     into l_product_name
     from oe_products p
    where list_price > co_price;
exception
   when no_data_found then
      null; -- handle_no_data_found;
   when too_many_rows then
      null; -- handle_too_many_rows;
end;
/