create a synonym for a view : Synonyms « User Privilege « Oracle PL/SQL Tutorial






SQL>
SQL> create table emp(
  2           emp_id                integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,shortZipCode                   varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,company_name           varchar2(50));

Table created.

SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (1,'Jones','Joe','J','1 Ave','New York','NY','11202','1111','212', '221-4333','Big Company');

1 row created.

SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (2,'Smith','Sue','J','1 Street','New York','NY','11444','1111','212', '436-6773','Little Company');

1 row created.

SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (3,'X','Peggy','J','1 Drive','New York','NY','45502','2222','212', '234-4444','Medium Company');

1 row created.

SQL>
SQL> create or replace view phone_list as
  2  select emp_id, firstname || ' ' || midinit || '. ' || lastname as name,'(' || area_code || ')' || phone as telephone#
  3  from emp;

View created.

SQL>
SQL>
SQL>
SQL> desc phone_list
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 EMP_ID                                                                                                NOT NULL NUMBER(38)
 NAME                                                                                                   VARCHAR2(39)
 TELEPHONE#                                                                                             VARCHAR2(13)

SQL> select * from phone_list;

    EMP_ID NAME                                    TELEPHONE#
---------- --------------------------------------- -------------
         1 Joe J. Jones                            (212)221-4333
         2 Sue J. Smith                            (212)436-6773
         3 Peggy J. X                              (212)234-4444

3 rows selected.

SQL> create synonym phones for phone_list;

Synonym created.

SQL> desc phones
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 EMP_ID                                                                                                NOT NULL NUMBER(38)
 NAME                                                                                                   VARCHAR2(39)
 TELEPHONE#                                                                                             VARCHAR2(13)

SQL> select * from phones;

    EMP_ID NAME                                    TELEPHONE#
---------- --------------------------------------- -------------
         1 Joe J. Jones                            (212)221-4333
         2 Sue J. Smith                            (212)436-6773
         3 Peggy J. X                              (212)234-4444

3 rows selected.

SQL>
SQL> select view_name from user_views;

VIEW_NAME
------------------------------
EMP_HQ
V
AVG_SAL
EMPDEPT_V
DEPT_SAL
ALL_ORACLE_ERRORS
INVENTORY_VIE
TOP_EMP
EMP_BONUS
SHARED
PHONE_LIST

11 rows selected.

SQL>
SQL> select synonym_name, table_name from user_synonyms;

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
PHONES                         PHONE_LIST

1 row selected.

SQL>
SQL>
SQL> drop synonym phones ;

Synonym dropped.

SQL>
SQL>
SQL>
SQL> drop table emp;

Table dropped.








36.8.Synonyms
36.8.1.Synonyms
36.8.2.Public Synonyms
36.8.3.Creating a synonym for a table
36.8.4.Creating a public synonym
36.8.5.create a synonym for a view
36.8.6.Dropping a synonym
36.8.7.Dropping a public synonym
36.8.8.Query a table by selecting its synonym
36.8.9.Seeing SYNONYM in the Oracle data dictionary
36.8.10.Describe a synonym