Mega Code Archive

 

Categories / Oracle PLSQL / Table

 

Check the view structure

SQL> SQL> 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,   3   '(' || area_code || ')' || phone as telephone#   4  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> SQL> drop view phone_list; View dropped. SQL> SQL> drop table emp; Table dropped.