Oracle PL/SQL/Table/Add Column

Материал из SQL эксперт
Перейти к: навигация, поиск

Add a char type column to a table

   <source lang="sql">
  

SQL> SQL> create table bar ( n number ); Table created. SQL> SQL> alter table bar add c char(1); Table altered. SQL> SQL> drop table bar; Table dropped. SQL>


 </source>
   
  


Add a date type column to a table

   <source lang="sql">
  

SQL> SQL> create table bar ( n number ); Table created. SQL> SQL> alter table bar add d date; Table altered. SQL> SQL> select object_type, status

 2      from user_objects
 3     where object_name = "SHIELD"
 4    /

OBJECT_TYPE STATUS


-------

PACKAGE VALID PACKAGE BODY VALID SQL> SQL> drop table bar; Table dropped. SQL>


 </source>
   
  


Add more columns

   <source lang="sql">
 

SQL> SQL> SQL> CREATE TABLE myTable (

 2     Name    VARCHAR(50) PRIMARY KEY NOT NULL,
 3     PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
 4     SSN     VARCHAR(15) NOT NULL,
 5     CHECK (SSN LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]"));

Table created. SQL> SQL> SQL> SQL> ALTER TABLE myTable ADD (EMail VARCHAR(25), ICQ VARCHAR(15)); Table altered. SQL> SQL> drop table myTable; Table dropped. SQL>


 </source>
   
  


Add new column with default value and not null

   <source lang="sql">
 

SQL> create table registrations

 2  ( attendee    NUMBER(4)
 3  , course      VARCHAR2(6)
 4  , begindate   DATE
 5  , evaluation  NUMBER(1)
 6  , constraint  R_PK        primary key (attendee,course,begindate)
 7  ) ;

Table created. SQL> SQL> SQL> alter table registrations

 2  add  (entered_by number(4) default 9 not null);

Table altered. SQL> SQL> SQL> drop table registrations; Table dropped.


 </source>
   
  


Add two columns to a table

   <source lang="sql">
 

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> -- To add multiple columns to an existing table, the ALTER TABLE syntax is: SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> SQL> ALTER TABLE emp

 2        ADD ( city_name varchar2(10),
 3                 role varchar2(10) );

SQL> SQL> desc emp;

Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
EMPNO                                                                                                 NOT NULL NUMBER(4)
ENAME                                                                                                  VARCHAR2(10)
JOB                                                                                                    VARCHAR2(9)
MGR                                                                                                    NUMBER(4)
HIREDATE                                                                                               DATE
SAL                                                                                                    NUMBER(7,2)
COMM                                                                                                   NUMBER(7,2)
DEPTNO                                                                                                 NUMBER(2)
CITY_NAME                                                                                              VARCHAR2(10)
ROLE                                                                                                   VARCHAR2(10)

SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO CITY_NAME  ROLE

---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO CITY_NAME  ROLE

---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

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


 </source>
   
  


Alter a table to add two columns and use select to check

   <source lang="sql">
  

SQL> SQL> SQL> CREATE TABLE employees

 2  ( employee_id          number(10)      not null,
 3    last_name            varchar2(50)      not null,
 4    email                varchar2(30),
 5    hire_date            date,
 6    job_id               varchar2(30),
 7    department_id        number(10),
 8    salary               number(6),
 9    manager_id           number(6)
10  );

Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)

 2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1004,  "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)

 2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);

1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1001 Lawson                                             lawson@g.ru                   01-JAN-02 MGR                                        1      30000       1004
      1002 Wells                                              wells@g.ru                    01-JAN-02 DBA                                        2      20000       1005
      1003 Bliss                                              bliss@g.ru                    01-JAN-02 PROG                                       3      24000       1004
      1004 Kyte                                               tkyte@a.ru                    13-JUN-98 MGR                                        4      25000       1005
      1005 Viper                                              sdillon@a .ru                 10-JUN-08 PROG                                       1      20000       1006
      1006 Beck                                               clbeck@g.ru                   10-JUN-08 PROG                                       2      20000
      1007 Java                                               java01@g.ru                   10-JUN-08 PROG                                       3      20000       1006

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1008 Oracle                                             oracle1@g.ru                  10-JUN-08 DBA                                        4      20000       1006

8 rows selected. SQL> SQL> alter table employees

 2      add(
 3        country_name  varchar2(40),
 4        region_name   varchar2(15)
 5  );

Table altered. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1001 Lawson                                             lawson@g.ru                   01-JAN-02 MGR                                        1      30000       1004
      1002 Wells                                              wells@g.ru                    01-JAN-02 DBA                                        2      20000       1005

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1003 Bliss                                              bliss@g.ru                    01-JAN-02 PROG                                       3      24000       1004
      1004 Kyte                                               tkyte@a.ru                    13-JUN-98 MGR                                        4      25000       1005

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1005 Viper                                              sdillon@a .ru                 10-JUN-08 PROG                                       1      20000       1006

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1006 Beck                                               clbeck@g.ru                   10-JUN-08 PROG                                       2      20000
      1007 Java                                               java01@g.ru                   10-JUN-08 PROG                                       3      20000       1006

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1008 Oracle                                             oracle1@g.ru                  10-JUN-08 DBA                                        4      20000       1006

8 rows selected. SQL> SQL> SQL> drop table employees; Table dropped. SQL>


 </source>
   
  


Alter table: add a column to an existing table

   <source lang="sql">
 

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> alter table employee add c char(1); Table altered. SQL> SQL> SQL> select * from employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION C


---------- ---------- --------- --------- ---------- ---------- --------------- -

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL> SQL> SQL>


 </source>
   
  


Alter table to add a column and then add "check" constraint to it

   <source lang="sql">
  

SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected. SQL> SQL> alter table emp

 2      add (
 3        gender   varchar(10));

Table altered. SQL> SQL> alter table emp

 2      add constraint ck_gender
 3      check (gender in ("MALE", "FEMALE"));

Table altered. SQL> SQL> update emp set gender = "MALE" where mod(empno,2) = 0; 10 rows updated. SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO GENDER

---------- --------- ---------- --------- ---------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20 MALE
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 MALE
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 MALE
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 MALE
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 MALE
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 MALE
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20 MALE
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO GENDER

---------- --------- ---------- --------- ---------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30 MALE
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 MALE
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 MALE

14 rows selected. SQL> SQL> update emp set gender = "FEMALE" where mod(empno,2) = 1; 4 rows updated. SQL> SQL> select ename, job, gender from emp order by gender, ename; ENAME JOB GENDER


--------- ----------

ALLEN SALESMAN FEMALE KING PRESIDENT FEMALE SMITH CLERK FEMALE WARD SALESMAN FEMALE ADAMS CLERK MALE BLAKE MANAGER MALE CLARK MANAGER MALE FORD ANALYST MALE JAMES CLERK MALE JONES MANAGER MALE MARTIN SALESMAN MALE ENAME JOB GENDER


--------- ----------

MILLER CLERK MALE SCOTT ANALYST MALE TURNER SALESMAN MALE 14 rows selected. SQL> SQL> insert into emp (empno, ename, gender) values (8001, "PAT", "UNKNOWN"); insert into emp (empno, ename, gender) values (8001, "PAT", "UNKNOWN")

ERROR at line 1: ORA-02290: check constraint (SYS.CK_GENDER) violated

SQL> SQL> drop table emp; Table dropped.


 </source>
   
  


Alter table to add a column and then add constraint to it

   <source lang="sql">
  

SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected. SQL> SQL> alter table emp add (ssn varchar2( 9 )); Table altered.

SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO SSN

---------- --------- ---------- --------- ---------- ---------- ---------- ---------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO SSN

---------- --------- ---------- --------- ---------- ---------- ---------- ---------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected. SQL> SQL> SQL> alter table emp add constraint emp_ssn_uk unique(ssn); Table altered. SQL> drop table emp; Table dropped. SQL>


 </source>
   
  


Alter table to add a primary key

   <source lang="sql">
  

SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> create table emp_reg

 2  as
 3  select * from emp;

SQL> SQL> alter table emp_reg add constraint emp_pk primary key(empno); Table altered. SQL> SQL> SQL> drop table emp_reg; Table dropped. SQL> --


 </source>
   
  


Alter table to add date type column

   <source lang="sql">
 

SQL> SQL> CREATE TABLE EMP(

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created.

SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created.

SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created.

SQL> SQL> SQL> ALTER TABLE emp ADD register_date DATE; Table altered. SQL> SQL> SQL> drop table emp; Table dropped.


 </source>
   
  


Alter table to add two columns and then drop them

   <source lang="sql">
  

SQL> SQL> SQL> CREATE TABLE employees

 2  ( employee_id          number(10)      not null,
 3    last_name            varchar2(50)      not null,
 4    email                varchar2(30),
 5    hire_date            date,
 6    job_id               varchar2(30),
 7    department_id        number(10),
 8    salary               number(6),
 9    manager_id           number(6)
10  );

Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)

 2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1004,  "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)

 2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);

1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1001 Lawson                                             lawson@g.ru                   01-JAN-02 MGR                                        1      30000       1004
      1002 Wells                                              wells@g.ru                    01-JAN-02 DBA                                        2      20000       1005
      1003 Bliss                                              bliss@g.ru                    01-JAN-02 PROG                                       3      24000       1004
      1004 Kyte                                               tkyte@a.ru                    13-JUN-98 MGR                                        4      25000       1005
      1005 Viper                                              sdillon@a .ru                 10-JUN-08 PROG                                       1      20000       1006
      1006 Beck                                               clbeck@g.ru                   10-JUN-08 PROG                                       2      20000
      1007 Java                                               java01@g.ru                   10-JUN-08 PROG                                       3      20000       1006

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1008 Oracle                                             oracle1@g.ru                  10-JUN-08 DBA                                        4      20000       1006

8 rows selected. SQL> SQL> alter table employees

 2      add(
 3        country_name  varchar2(40),
 4        region_name   varchar2(15)
 5  )
 6  /

Table altered. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1001 Lawson                                             lawson@g.ru                   01-JAN-02 MGR                                        1      30000       1004
      1002 Wells                                              wells@g.ru                    01-JAN-02 DBA                                        2      20000       1005

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1003 Bliss                                              bliss@g.ru                    01-JAN-02 PROG                                       3      24000       1004
      1004 Kyte                                               tkyte@a.ru                    13-JUN-98 MGR                                        4      25000       1005

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1005 Viper                                              sdillon@a .ru                 10-JUN-08 PROG                                       1      20000       1006

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1006 Beck                                               clbeck@g.ru                   10-JUN-08 PROG                                       2      20000
      1007 Java                                               java01@g.ru                   10-JUN-08 PROG                                       3      20000       1006

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------

COUNTRY_NAME REGION_NAME


---------------
      1008 Oracle                                             oracle1@g.ru                  10-JUN-08 DBA                                        4      20000       1006

8 rows selected. SQL> SQL> SQL> alter table employees

 2      drop (
 3        country_name, region_name )
 4      /

SQL> SQL> SQL> drop table employees; Table dropped. SQL>


 </source>
   
  


Alter table to add varray type column

   <source lang="sql">
 

SQL> SQL> create table courses

 2  ( code        VARCHAR2(6)  constraint C_PK primary key
 3  , description VARCHAR2(30)
 4  , category    CHAR(3)
 5  , duration    NUMBER(2)
 6  ) ;

Table created. SQL> insert into courses values("SQL","SQL","GEN",4); 1 row created. SQL> insert into courses values("OAU","Java","GEN",1); 1 row created. SQL> insert into courses values("JAV","C++","BLD",4); 1 row created. SQL> insert into courses values("PLS","C","BLD",1); 1 row created. SQL> insert into courses values("XML","XML","BLD",2); 1 row created. SQL> insert into courses values("ERM","ERP","DSG",3); 1 row created. SQL> insert into courses values("PMT","ERP","DSG",1); 1 row created. SQL> insert into courses values("RSD","jQuery","DSG",2); 1 row created. SQL> insert into courses values("PRO","Linux","DSG",5); 1 row created. SQL> insert into courses values("GEN","Oracle","DSG",4); 1 row created. SQL> SQL> SQL> SQL> create table c

 2  as
 3  select * from courses;

Table created. SQL> SQL> create or replace type erratum_t as object

 2  ( code varchar2(4)
 3  , ch   number(2)
 4  , pg   number(3)
 5  , txt  varchar2(40)
 6  ) ;
 7  /

Type created. SQL> SQL> create or replace type errata_tab_t as table of erratum_t;

 2  /

Type created. SQL> SQL> describe errata_tab_t

errata_tab_t TABLE OF ERRATUM_T
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CODE                                               VARCHAR2(4)
CH                                                 NUMBER(2)
PG                                                 NUMBER(3)
TXT                                                VARCHAR2(40)

SQL> SQL> alter table c

 2  add (errata errata_tab_t)
 3  nested table errata store as errata_tab;

Table altered. SQL> SQL> update c

 2  set    errata = errata_tab_t();

10 rows updated. SQL> SQL> drop table c; Table dropped. SQL> drop type errata_tab_t force; Type dropped. SQL> drop type erratum_t force; Type dropped. SQL> SQL> SQL> drop table courses; Table dropped.


 </source>