Home » SQL & PL/SQL » SQL & PL/SQL » ALTER TABLE table_name MODIFY column_name VARCHAR(200)
ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6856] Thu, 08 May 2003 14:36 Go to next message
Jadie
Messages: 64
Registered: January 2002
Member
I went to an interview for a DBA position. There was a written test. One of the question is:

ALTER TABLE table_name MODIFY column_name VARCHAR(200)
What does this command mean? What will make this command to fail?

I never saw ALTER TABLE command can have option MODIFY?
Please help me get the answer. thanks!

Jade
Re: ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6857 is a reply to message #6856] Thu, 08 May 2003 15:44 Go to previous messageGo to next message
Joe
Messages: 138
Registered: November 1999
Senior Member
Actually the command:
ALTER TABLE table_name MODIFY column_name VARCHAR(200)

will fail because it is missing a set of parens. The modify statement will enable you to alter the characteristics of the columns, such as length, null, not null, default values...etc. In some cases the column must be null. In order for this command to execute properly it should read:

ALTER TABLE table_name MODIFY (column_name varchar2(200));
Re: ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6869 is a reply to message #6857] Fri, 09 May 2003 01:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It works without the parentheses in Oracle 8.1.7. If your statement decreases the column length, it will fail if the column is not empty. See examples below.

SQL> DESCRIBE test
 Name                    Null?    Type
 ----------------------- -------- ----------------
 TEST                             VARCHAR2(10)

SQL> ALTER TABLE test MODIFY test VARCHAR(200);

Table altered.

SQL> DESC test
 Name                    Null?    Type
 ----------------------- -------- ----------------
 TEST                             VARCHAR2(200)

SQL> INSERT INTO test (test) VALUES ('ABCDEFGHIJ');

1 row created.

SQL> ALTER TABLE test MODIFY test VARCHAR2(20);
ALTER TABLE test MODIFY test VARCHAR2(20)
                        *
ERROR at line 1:
ORA-01441: column to be modified must be empty to
decrease column length

SQL> 
Re: ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6876 is a reply to message #6869] Fri, 09 May 2003 10:32 Go to previous messageGo to next message
Jadie
Messages: 64
Registered: January 2002
Member
Thanks Joe and Barbara. I really appreciate that you answered my quesion.

Yes, I agreed with you. And I gave the same answer.

But following this question, they gave four answers to choose:
1) table is partitioned.
2) table is not partitioned.
3) table is partitioned on this column
4) table is not partitioned on this column.

There answers made me very confused! ALTER TABLE MODIFY command is related to table partition?

Jadie
Re: ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6888 is a reply to message #6876] Sat, 10 May 2003 01:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The original question asked what would make the command fail. Although I identified trying to decrease the column size of a non-empty column as one thing that would make the command fail, there are other things that would also cause it to fail. It appears that, by providing the list of choices, they are asking you which of those situations would cause the command to fail. The best way to find out, is to test the possibilities. I have already demonstrated the command on a table that it is not partitioned. So, the remaining question is will the command fail if it is partitioned on any column, will it fail if it is partitioned on this column, will it fail if it is partitioned on some column other than this one.

Below I have demonstrated first that it will not fail if it is partitioned on some other column. Below that I have demonstrated that it will fail if it is partitioned on this column. So, the correct answer is:

The command will fail if:

3) table is partitioned on this column.

SQL> -- <b>create table partitioned on column you don't intend to modify:</b>
SQL> CREATE TABLE test
  2    (test	     VARCHAR (10),
  3  	other_column NUMBER)
  4    PARTITION BY RANGE (other_column)
  5    (PARTITION p1 VALUES LESS THAN (10),
  6  	PARTITION p2 VALUES LESS THAN (20),
  7  	PARTITION p3 VALUES LESS THAN (MAXVALUE))
  8  /

Table created.

SQL> INSERT INTO test (test, other_column)
  2  VALUES ('ABCDEFJHIJ', 1)
  3  /

1 row created.

SQL> INSERT INTO test (test, other_column)
  2  VALUES ('KLMONPQRST', 10)
  3  /

1 row created.

SQL> INSERT INTO test (test, other_column)
  2  VALUES ('UVWXYZ', 20)
  3  /

1 row created.

SQL> -- <b>modify column that table is not partitioned on:</b>
SQL> ALTER TABLE test MODIFY test VARCHAR(200)
  2  /

Table altered.

SQL> DROP TABLE test
  2  /

Table dropped.
.
.
.
.
.
SQL> -- <b>create table partitioned on column you intend to modify:</b>
SQL> CREATE TABLE test
  2    (test	     VARCHAR (10),
  3  	other_column NUMBER)
  4    PARTITION BY RANGE (test)
  5    (PARTITION p1 VALUES LESS THAN ('K'),
  6  	PARTITION p2 VALUES LESS THAN ('U'),
  7  	PARTITION p3 VALUES LESS THAN (MAXVALUE))
  8  /

Table created.

SQL> INSERT INTO test (test, other_column)
  2  VALUES ('ABCDEFJHIJ', 1)
  3  /

1 row created.

SQL> INSERT INTO test (test, other_column)
  2  VALUES ('KLMONPQRST', 10)
  3  /

1 row created.

SQL> INSERT INTO test (test, other_column)
  2  VALUES ('UVWXYZ', 20)
  3  /

1 row created.

SQL> -- <b>attempt to modify column that table is partitioned on fails:</b>
SQL> ALTER TABLE test MODIFY test VARCHAR(200)
  2  /
ALTER TABLE test MODIFY test VARCHAR(200)
                        *
ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not be 
changed 

SQL> DROP TABLE test
  2  /

Table dropped.
Re: ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6987 is a reply to message #6888] Wed, 14 May 2003 14:57 Go to previous message
Jadie
Messages: 64
Registered: January 2002
Member
Thank you very much for your reply, Barbara. Now I totally understand what is the purpose of the question.

Regards.

Jadie
Previous Topic: Missing Select Keyword
Next Topic: Select top counts with Group by and Order by
Goto Forum:
  


Current Time: Thu Mar 28 08:24:35 CDT 2024