Solved

Alter SQL Statement

Posted on 2004-03-24
5
4,877 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 47

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 36 82
SQL query of Oracle 10g database. 8 75
sort a spool into file output in oracle 1 22
How to free up undo space? 3 24
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

914 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now