drop column question

[leonard905]

Hi all,

I issued the following command on oracle7.3.4 and got an error message:

SQL> alter table test3
2 drop column control_file;
drop column control_file
*
ERROR at line 2:
ORA-00905: missing keyword

Does anyone know what the problem is?

Thanks,

[jmodic]

ALTER TABLE DROP COLUMN is not available in 7.3 (or even in 8.0.*), that's the problem. You can't drop it without recreating the whole table.

[Shestakov]

alter table ... drop column ....;
avaible only in 8i and high.

u should create script for drop columns:
( i can't remember from that version i can use RENAME TABLE command, then i write 2 scripts skeletons)

--- with RENAME TABLE ----

1. create table tmp_... ( list of fields without control_file);
2. insert into tmp_... ( list of fields without control_file) select ( list of fields without control_file);
RENAME test3 to old_test3;
RENAME tmp_test3 to test3;
-- drop table old_test3;

--- without RENAME TABLE ----

1. create table tmp_... ( list of fields without control_file);
2. insert into tmp_... ( list of fields without control_file) select ( list of fields without control_file);
-- check data in tmp_... table
3. drop table test3;
4. create table test3 as select * from tmp_...;
5. drop table tmp_...;

good luck.

PS : don't forgret about indexes, triggers, constraints ...