Solved

Alter SQL Statement

Posted on 2004-03-24
5
4,882 Views
Last Modified: 2012-05-05
If I have a table x w/ a column called DL that's not nullable and of type varchar2, if I did the following alter statement, "alter table x modify x varchar2 nullable", will this erase my data?
0
Comment
Question by:paulj1999
5 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 250 total points
ID: 10665596
alter table x modify x varchar2(nnn) nullable;
will not damage the data.
Only if you make the length of the column lower and truncation ocurs the operation will be denied.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10665601
No, the data will not be erased ...
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 10665605
assuming your  original table has column dl column definition as varchar2(10) not null

statement

alter table x modify dl varchar2(10) null

won't erase your data.

you can check it yourself with a dummy table and data
0
 
LVL 2

Expert Comment

by:n4nazim
ID: 10665712
Hi,

Considering yr statement that the column DL is NOT NULL. Now if u make if NULLable, the data wont be erased. U can try this with the sample data/scripts i have pasted below:


create table nztab1 ( a varchar2(10) not null);

insert into nztab1 values ( '1' );
insert into nztab1 values ( '2' );
insert into nztab1 values ( '3' );

alter table nztab1 modify a varchar2(10) null

select * from nztab1

After this select statement u can see that the data is still there.

Rgds,
NHM
0
 
LVL 3

Expert Comment

by:rajnadimpalli
ID: 10669340
If I were you..I will just try it and see...what happens...

11:45:44 SQL> desc  demo
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 COL1                          NOT NULL VARCHAR2(10)
 LOW_VALUE                              CHAR(4)
 COL2                                   NUMBER
 COL3                                   CHAR(2)

11:45:47 SQL> select * from demo;

COL1       LOW_       COL2 CO
---------- ---- ---------- --
John                  2020 LA
Mark                  5060 MI
BOND                  4500 VA

3 rows selected.

11:45:50 SQL> alter table demo modify (COL1 varchar2(10) NULL);

Table altered.

11:46:19 SQL> desc  demo
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 COL1                                   VARCHAR2(10)
 LOW_VALUE                              CHAR(4)
 COL2                                   NUMBER
 COL3                                   CHAR(2)

11:46:24 SQL>  select * from demo;

COL1       LOW_       COL2 CO
---------- ---- ---------- --
John                  2020 LA
Mark                  5060 MI
BOND                  4500 VA

3 rows selected.

11:46:28 SQL>

And if you really want to erase data in One column and make it NULLABLE..then

11:49:52 SQL> alter table demo drop column col1;

Table altered.

11:50:06 SQL> desc demo;
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 LOW_VALUE                              CHAR(4)
 COL2                                   NUMBER
 COL3                                   CHAR(2)

11:50:15 SQL> alter table demo add (COL1 VARCHAR2(10) NULL);

Table altered.

11:50:20 SQL> desc demo;
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 LOW_VALUE                              CHAR(4)
 COL2                                   NUMBER
 COL3                                   CHAR(2)
 COL1                                   VARCHAR2(10)

11:50:23 SQL> select * from demo;

LOW_       COL2 CO COL1
---- ---------- -- ----------
           2020 LA
           5060 MI
           4500 VA

3 rows selected.

11:50:27 SQL>

Or you can do ...UPDATE DEMO set COL1=NULL;
...
..

-R
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question