Mini Tip: Synonyms

Posted in ITToolbox by Lewis

The full link for the article is here.

Today I am going to talk about synonyms and what you should NOT do with them. Since this is a MINI tip, it will be short and sweet. I will not cover every aspect of synonyms.

A quick definition: two words that can be interchanged in a context are said to be synonymous relative to that context.

In Oracle terms: An alias for a table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym.

So that means that instead of referencing a table like owner.table, you can create a synonym for that owner.table and reference it by the synonym name. For example,

SQL> connect system@unixxe
Enter password: ******
Connected.
SQL> select count(*) from employees;
select count(*) from employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
.
.
SQL> select count(*) from hr.employees;
.
COUNT(*)
----------
107
.
SQL> create public synonym employees for hr.employees;
.
Synonym created
.
.
SQL> select count(*) from employees;
.
COUNT(*)
----------
107
.
SQL>

Here I tried to select from the table EMPLOYEES which is owned by HR. I couldn't do it without adding the owner, HR.EMPLOYEES, so I created a public synonym (public means the synonym will be visible to all database users but doesn't grant any access). Now anyone who has access to the HR.EMPLOYEES table will be able to reference it as EMPLOYEES as long as they do not already have an EMPLOYEES table or a private synonym with the same name in their schema.

One reason to use a synonym is to hide the schema owner of an object. This is a use that has been made of synonyms for a long time.

Another use is to hide complexity. For example, if you frequently access a package across a link that is owned by a schema other than the connecting schema, you may want to use a synonym rather than hard coding that complexity. For example, if I frequently access hr.employees_pkg@unixxe.world, I may want to create a synonym,

create public synonym employees_pkg for hr.employees@unixxe.world;

I can then refer to just employees_pkg and not worry about where the package is actually stored.

This leads us to the real mini-tip: what not to do with synonyms. I recently helped debug an issue that had kept a developer busy for a couple of days. An application had a query something like this:

SELECT data_columns
FROM table_name
WHERE criteria_column = p_parameter_value;

When the application executed this code, it executed successfully most of the time. Occasionally, it would fail with no_data_found. The developer looked at table_name and found a corresponding criteria_column value. That is, he executed this query from SQL*Plus and it returned a record for the value that the application got a no_data_found.

This had the developer stumped for a couple of days. I sat down with him and walked through the steps he had been using to debug and he made all the right calls and looked at most of what you would expect. It really was puzzling.

As we talked, he mentioned that the application connected to a user account that he did not have access to. He was running from a different, read-only account. That clued me in to a potential issue so I had him query all objects for 'TABLE_NAME'. Sure enough, there was a table named 'TABLE_NAME' and a public synonym named 'TABLE_NAME'. He didn't think we had it figured out though because there was only a single table named 'TABLE_NAME'.

I had him take it one step further and query dba_synonyms. Sure enough, the public synonym table_name was pointing to a different table altogether. The table table_name was being maintained so when querying from that, you would find the records. The new table, the one associated with the synonym was not. This was the discrepancy.

So after all of that, what is my tip? If you use synonyms, do not make life more confusing than it needs to be. Do not create a synonym to the employees table and name it customers. You can hide a schema or reduce complexity, but try to do so in the least confusing way. If every schema in your database uses a table called control_tab, do not use a synonym at all, just use the schema owner.

Synonyms are indirection, like a pointer. Pointers are dangerous because they are confusing. They are useful but require thought. Do not use a pointer to rename. If you need a new name, change your table and then update your code.

In general, when you create a synonym, use the object's base name as the synonym name, as I did in the HR.EMPLOYEES example above.

Courtesy: Lewis