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

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