Creates an alternate name or synonym for an existing database
object. The object may be a domain, function, module, procedure,
sequence, another synonym, table, or view.
Once defined, the synonym can be used in any query or data
definition language statement in place of the referenced object.
However, the SHOW commands do not accept synonyms. Use the SHOW
SYNONYM statement to determine if the name is a synonym.
1 – Environment
You can use the CREATE SYNONYM statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mCREATE[m [1mqwqqqqqqqqqqqqqqqwqwqqqqqqqqqqqwqqqk[m [1m [m
[1mmq> [1;4mOR[m [1;4mREPLACE[m[1m qj[m [1mmq> [1;4mPUBLIC[m[1m qj[m [1m x[m [1m [m
[1mlqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqj[m [1m [m
[1mmq> [1;4mSYNONYM[m [1m<synonym-name> [1;4mFOR[m[1m qwqqqqqqqqqqqqqqqqwqk[m
[1mmq> object-type[m [1mqj x[m
[1mlqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmq> <object-name> qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>[m
[1mmq> [1;4mCOMMENT[m[1m IS[m [1mqwq> ' <quoted-string> ' qwj [m
[1mmqqqqqqqqq / <qqqqqqqqqqqj[m
(B)0[m[1mobject-type =[m
[1mqwq> [1;4mDOMAIN[m [1mqqqqwq>[m
[1mtq>[m [1;4mFUNCTION[m [1mqqu[m
[1mtq>[m [1;4mMODULE[m [1mqqqqu[m
[1mtq>[m [1;4mPROCEDURE[m [1mqu[m
[1mtq>[m [1;4mSEQUENCE[m[1m qqu[m
[1mtq>[m [1;4mSYNONYM[m[1m qqqu[m
[1mtq>[m [1;4mTABLE[m [1mqqqqqu[m
[1mmq>[m [1;4mVIEW[m [1mqqqqqqj[m
3 – Arguments
3.1 – COMMENT IS 'quoted-string'
This optional clause can be used to add several lines of comment
to the synonym object. The comment is displayed by the SHOW
SYNONYM statement.
3.2 – FOR object-name
The name of the database object for which the synonym is
required. This name must exist for an object in the database. If
the optional object type is omitted, then Oracle Rdb will search
the database for an object with this name.
3.3 – object-type
Syntax options:
DOMAIN
FUNCTION
MODULE
PROCEDURE
SEQUENCE
SYNONYM
TABLE
VIEW
These optional object types can be used when the referenced
object name is not unique within the database. For instance,
Oracle Rdb allows a domain and a table to both be called MONEY.
Therefore, to create a synonym for the table MONEY, you must use
the FOR TABLE clause so that it is uniquely identified.
3.4 – OR_REPLACE
Instructs SQL to replace any synonym of this name if it exists.
If it does not exist, a new synonym is created. This shorthand
allows replacement of an existing synonym while maintaining
all the dependencies established by query and DDL usage of this
synonym.
3.5 – PUBLIC
This optional clause is provided for compatibility with the
Oracle database server. It is currently not used by Oracle
Rdb. Its presence or absence may be used by future releases.
Oracle Corporation recommends you use the PUBLIC keyword in
applications.
3.6 – synonym-name
The name of the synonym you want to create. The synonym name
must be unique within all domains, tables, views, functions,
procedures, modules, sequences, and synonyms within the database.
You may qualify it with an alias.
4 – Examples
Example 1: Using the Default Alias
SQL> CREATE SYNONYM emps FOR employees;
Example 2: Using an Explicit Alias for the Synonym
SQL> CREATE SYNONYM db1.emps FOR employees;
Example 3: Using an Explicit Alias for the Referenced Object
SQL> CREATE SYNONYM emps FOR db1.employees;
Example 4: Using the Alias Explicitly
SQL> CREATE SYNONYM db1.emps FOR db1.employees;
Example 5: Using the Table Type
SQL> CREATE SYNONYM cash FOR table money
cont> COMMENT IS 'use a different name to avoid confusion with'
cont> / 'the domain MONEY';
Example 6: Using Multiple Synonyms
SQL> CREATE TABLE t_employees_0001 (...);
SQL> CREATE SYNONYM employees FOR t_employees_0001;
SQL> CREATE SYNONYM emps FOR employees;