G-8210

🆓

Warning

Always use synonyms when accessing objects of another application schema.

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 10 mins
Local-high
Database Support: OracleDB (v7.3.4 +)

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

Non-Compliant Example

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;
/

Issues

Line	Column	Message
7	11	Always use synonyms when accessing objects of another application schema.

Compliant Solution - ★★★★★

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;
/

References

same as Trivadis G-8210