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

How to kill inactive session from Forms

Technology: Oracle Forms 6i, Oracle Database 9i or greater

One of my friend asked how we can kill an inactive session of forms. In an Application server environment this can be attained by modifying none of the application. But as the version is not of our discussion it will not be discussed here.

Here is how we have attained such a result:

We have to use the D2KWUTIL.PLL. In the WHEN-NEW-FORM-INSTANCE trigger provide the following code:
declare
lwindow PLS_INTEGER;
ltimer TIMER;
begin
lwindow := get_window_property(FORMS_MDI_WINDOW,WINDOW_HANDLE);
Win_API_Session.Timeout_Start_Timer(lwindow);
ltimer := Create_Timer('TIMER1',1000,repeat);
end;

In WHEN-TIMER-EXPIRED trigger provide the following code:
begin
if upper(get_application_property(TIMER_NAME)) = 'TIMER1' then
:control.inactive_time := Win_api_session.Timeout_Get_Inactive_Time;
if : control.inactive_time > 120 then
Win_API_Session.timeout_delete_timer;
exit_form (NO_VALIDATE);
end if;
end if;
end;

Control is a block and inactive_timer is a text item. It need not be displayed in any canvas.

Joins: ANSI/ISO SQL: 1999 standards

Starting from 9i, Oracle has introduced various join syntax that comply with ANSI/ISO SQL: 1999 standards.

CROSS JOIN
Cross Join, Cross Product or Cartesian Product represents a direct product of records in two or more tables. In other words it lists all rows from all the tables listed in the query. Each row in the first table is paired with all the rows in the second table. That is if the first table contains 20 rows and second table contains 60 rows, the output will consist of 20 * 60 = 1200 rows of data.

This is how normally cross product was represented in existing syntax before Oracle 9i:

select field_name
from first_table, second_table;

The ANSI standard of cross joining two tables is as follows:

select field_name
from first_table CROSS JOIN second_table;

NATURAL JOIN
A natural join performs a join for all columns with matching names in the tables present in the query list. This is the ANSI variant of giving join conditions for all matching fields explicitly.

select field_name
from first_table NATURAL JOIN second_table;

The normal way of achieving this would be like:

select field_name
from first_table a, second_table b
where a.field1 = b.field1
and a.field2 = b.field2;

The USING clause have to be used in case all the matching fields should not be equated. This is also ANSI variant.

select field_name
from first_table JOIN second_table USING (field1);

The normal way of achieving this would be like:

select field_name
from first_table a, second_table b
where a.field1 = b.field1;

The ON clause have to be used instead of USING if the field names in tables do not match. This is also the ANSI variant of SQL.

select field_name
from first_table a JOIN second_table b ON (a.field = b.field1);

The existing syntax is:

select field_name
from first_table a, second_table b
where a.field = b.field1;

Example of joining three tables with ANSI syntax:

select employee_id, city, department_name
from locations l
JOIN departments d ON (d.location_id = l.location_id)
JOIN employees e ON (d.department_id = e.department_id);

The existing syntax of joining the same would be like:

select employee_id, city, department_name
from locations l, departments d, employees e
where d.location_id = l.location_id
and d.department_id = e.department_id;

OUTER JOINS
There are three variants of outer joins available in Oracle. They being LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.

LEFT OUTER JOIN
The left outer join returns all rows from the first table. It joins the second table with first table and returns those rows also. For those rows in first table which does not have a match with second table NULLs will be returned.

select field_name
from first_table LEFT OUTER JOIN second_table ON (field=field1);

The existing syntax for getting the same result is:

select field_name
from first_table, second_table
where field=field1(+);

RIGHT OUTER JOIN
The right outer join is the revers of LEFT OUTER JOIN. That is it returns all rows from second table. It then joins the first table. For those rows that does not have a match in first table, NULLs will be returned.

select field_name
from first_table RIGHT OUTER JOIN second_table ON (field=field1);

The existing syntax for getting the same result is:

select field_name
from first_table, second_table
where field(+)=field1;

FULL OUTER JOIN
The full outer join fetches all rows from both first table and second table. It will fill in blanks for the non matching rows in each table.

select field_name
from first_table FULL OUTER JOIN second_table ON (field=field1);

There is no equivalent for full outer join in previous releases.