Oracle Synonyms

Overview

Using Synonyms in Oracle (Best Practices)

An Oracle synonym basically allows you to create a pointer to an object. This object can exist in the same database, or in another database. Oracle synonyms are used because, by default, when you are logged into Oracle, it will look for all objects being queried within your schema, i.e. accounts.
A synonym allows a database administrator to define alternate names for existing database objects from other schemas.

Configurable Searches and External queries

With Govern Open Forms, you can build customized queries, open forms, Dynamic Searches, etc. based on data in different schemas and/or external databases.

Data in a different Schema but same Database Instance

When using a synonym to access data in a different schema but within the same database, it is recommended that you add the synonyms directly in the govern schema. For example, in a schema called MANTA, if you need to work with the tables, it is recommended that you create synonyms with a Govern prefix, in the Govern schema for the required tables.

Data in a different Database or Server

When using a synonym to access data in a different database or on a different server, there are two (2) methods that can be used.

Method 1

When the data is located in a different database, a connection key will need to be generated to access this database. To ensure success, you will need to be able to create stored procedures that will be used by Govern, within the database and schema. Note that this may not be the preferred methodology for System Administrators as this mixes different information within the same schema.
An alternative is to create a Govern schema within the other database, and use the synonyms to point to the desired tables. When this method is used, stored procedures will be created in the Govern Schema along with the synonyms. This is a recommended practice.

Method 2

This second method allows you to connect to an external database or server. Once a connection has been established, the synonym can be used in your current Govern database and schema.

Example of a database link

The following is an example of the commands that can be used to establish a database link.
CREATE DATABASE LINK manta.manta_server_name

CONNECT TO the manta database, IDENTIFIED BY manta_password

USING ‘manta_service_name’;

The above connects to the ‘manta’ database using the net service name ‘manta_service_name’, and is connected with a username of ‘manta’ and a password of ‘manta_password’.

With this, you can do a
SELECT * FROM upload_address@manta.manta_server_name

This will allow you to retrieve the rows from the upload_address table on the manta database residing on the manta_server_name Oracle server.

Creating the Synonym

You can also create a synonym in the following manner:

CREATE SYNONYM upload_address for upload_address@manta.manta_server_name
So that now you can do a SELECT * FROM upload_address.

Syntax

Create synonym [SYNONYM_NAME] for [SCHEMA].[TABLE_NAME] Example: CREATE synonym [UPDATE]

Using Synonym for table residing on same Server but different Schema

The following steps are for using a Synonym for a table that is on the same Server, but in a different Schema.

1. Create a synonym for the MANTA.UPLOAD_PROPERTY table and name it GOVERN.UPLOAD_PROPERTY.
2. Start the Business Entity Designer (BED)
3. Create the UPLOAD_PROPERTY Business Entity (BE); create all required attributes and ensure that each attribute matches the MANTA.UPLOAD_PROPERTY table.
4. Select the ParcelInfo Business Model (BM) and add the UPLOAD_PROPERTY business entity to it.
5. Click on the ENTITIES (relationship) tab (A) and select PC_ParcelInformation as the parent entity and UPLOAD_PROPERTY as the child entity
6. Under the same tab (A), define the relationship between the TAX_MAP_UFMT and ROLL_NUMBER attributes
7. Start the Model Object Designer (MoD) and open the PC (Property Information) model.
8. Expand the ParcelInfo item in the loaded Business Model section. You will now see the UPLOAD_PROPERTY Business Entity at the end of the list.
9. Drag the UPLOAD_PROPERTY business entity to the property information model.
10. Select the UPLOAD_PROPERTY cell to change it’s position.
11. Change the Row sequence to 1 and cell sequence to 2.
    NOTE: You may also add action buttons to the UPLOAD_PROPERTY model to save/delete/etc…
12. Click Save to save the model.
13. Start the Govern Security Manager (GSM). Right click on Applications (A) and select Synchronize Profiles from the floating menu.
14. Expand the applications node (A) and confirm that UPLOAD_PROPERTY is now present in the Property Control profile (B).
    NOTE: For details about the synchronization process, refer to the Govern Security Manager (GSM) release 5.0 for details.

Verification in Govern

After the synchronization process, the presence of the new tab can be verified in Govern.

1. Start Govern and go to the Property Control profile.
2. Open the Property Information openform.
3. Search for and edit the Property Information for tax map number 13092095000. You will now have a tabbed document for the UPLOAD_PROPERTY business entity.
4. Click on the UPLOAD_PROPERTY tab and the related data from the UPLOAD_PROPERTY business model will be displayed.

All editable fields can be edited and saved.

Using a Synonym for a Table residing on another Server and Schema

The following steps are for using a Synonym for a table that is on another Server with a different Schema.

1. Create a database link to where the remote tables reside.
2. Test and validate the new database link
   NOTE: The test must be successful.
3. Export the DDL (save to worksheet) of the synonym UPLOAD_PROPERTY that we created earlier.
4. Edit the DDL to add a reference to the database link to the server that we are linking to by specifying ‘@WPG11G’ as a suffix to the table name.
5. Run a test query to verify that the link is working.

We are now querying (and joining), transparently, tables that reside on separate Oracle servers. If you run the MS Govern application, at this point, you will obtain identical results.

A Note about Security for Synonyms

In order for a synonym to function correctly, it is essential that the security be set to have access to the external tables that are pointed to by the synonym. For example to, to access a MANTA schema, you must have security access to the MANTA tables in question..

See Also

Oracle