 |
|
Oracle Tips by Burleson |
Altering Tables: Modifying
Columns
You can make some modifications to columns,
if needed. Basically, any column can grow. Any column can shrink
if it does not contain data larger than the new size, but only empty
columns can change data types. Columns are modified using the ALTER
TABLE MODIFY command.
SQL> desc author
Name
Null? Type
-----------------------------------------
-------- ------------
AUTHOR_KEY
VARCHAR2(11)
AUTHOR_LAST_NAME
VARCHAR2(40)
AUTHOR_FIRST_NAME
VARCHAR2(20)
AUTHOR_PHONE
VARCHAR2(12)
AUTHOR_STREET
VARCHAR2(40)
AUTHOR_CITY
VARCHAR2(20)
AUTHOR_STATE
VARCHAR2(2)
AUTHOR_ZIP
VARCHAR2(5)
AUTHOR_CONTRACT_NBR
NUMBER(5)
SQL> alter table author modify
2 (
3 author_last_name varchar2(80),
4 author_first_name varchar2(15)
5 );
Table altered.
SQL> desc author
Name
Null? Type
-----------------------------------------
-------- ------------
AUTHOR_KEY
VARCHAR2(11)
AUTHOR_LAST_NAME
VARCHAR2(80)
AUTHOR_FIRST_NAME
VARCHAR2(15)
AUTHOR_PHONE
VARCHAR2(12)
AUTHOR_STREET
VARCHAR2(40)
AUTHOR_CITY
VARCHAR2(20)
AUTHOR_STATE
VARCHAR2(2)
AUTHOR_ZIP
VARCHAR2(5)
AUTHOR_CONTRACT_NBR
NUMBER(5)
In the example above, we extended the size
of author_last_name to 80 characters and reduced the size of
author_first_name to 15 characters. If we try to reduce
author_first_name smaller than the largest name in the table, the
command will fail.
SQL> alter table author modify (author_first_name
varchar2(5));
alter table author modify (author_first_name
varchar2(5))
*ERROR at line 1:
ORA-01441: cannot decrease column length
because some value is too big
All data in the database is stored in
tables, and so far we have covered in detail how to retrieve the
data from a table. In this chapter, we talked about creating and
maintaining tables. Now it is time to cover putting data into
tables and changing the data already in tables. This is referred to
as data manipulation and involves the INSERT, UPDATE and DELETE
statements. We want to start with getting the data into the table,
so we start with the INSERT statement.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
