OVERFLOW SYNONYM FULL
To simplify this, you can assign a simple name for an object by creating a synonym that has the full qualified path to the object, and then grant appropriate privileges on the synonym. Moreover, you must also ensure that the user has required privileges on the target object. Simplified and secure access: In a distributed environment, you must use the schema name along with the object names to ensure that you are accessing the correct object.
OVERFLOW SYNONYM UPDATE
To work around this, you can use a synonym for the table in the stored procedure, and update the synonym definition if there is a change in the name or location of the table. Now, if the table name changes or the table is moved to some other location then your stored procedure will stop working. You can modify the synonym definition to accommodate any changes in the name or location of the underlying objects.įor example, suppose that you are using a table in one of your stored procedures. This ensures that you do not have to modify your SQL statements to perform operation on the object in different schemas.Ĭhanges in the underlying objects: The synonyms insulate you from any changes in the name or location of the underlying objects on which you are performing an operation. Whenever you need to perform operation on the “Prod.Employee” table, modify the definition of the “EMP” synonym to point it to the “Prod.Employee” table. To get around this issue, you can create a synonym for the “Test.Employee” table (say “EMP”), and then use it in your SQL statements. If you want to use the “Employee” table in the production schema, you must now use Prod.Employee or Employee (change the default schema to “Prod”) in your SQL statement. Thus, the applications based on the synonym continue to function without modification in the SQL statement.įor example, suppose you have two identical schemas for your test and production environments: “Test” and “Prod.” To access a table called “Employee” in the “Test” schema, you must use Test.Employee or Employee (if “Test” is the default schema) in your SQL statement. If you need to access the underlying object in a different schema, modify the definition of the synonym to point to the object in a different schema. You can create a synonym for an object in a schema, and use the synonym in your SQL statement to access the object.
Working with different schemas: If you are working with different schemas, and need to access the objects across schemas, you have to use different SQL statements to access those objects. Synonyms are helpful in the following scenarios: For more information about synonyms in Oracle, see. A synonym is an alias or friendly name for the database objects (such as tables, views, stored procedures, functions, and packages). The Oracle Database adapter allows you to perform operations on synonyms.
0 kommentar(er)
