Alphabetizing Columns

[dgs!]

We have recently decided to alphabetize the column names whenever we add columns to a table.
There are many ways to do this.
1) Create newtable as select a_column, b_column, c_column from oldtable....
2) rename newtable to old table, ETC...

Can PL/SQL developer generate such a script that will disable all the constraints, triggers, ETC, then reenable them all?
My collegue has mentioned that TOAD can generate such a script.

 

[Marco Kalter]

PL/SQL Developer does not have such a feature.

 

[dgs!]

Shucks....
Any plans to add it?

 

[Maxmix]

dgs!
Not sure why you want it, but wouldn't Tools|ExportTables|SQLInserts be sufficient? You could put "1=0" into 'Where clause' to avoid INSERTS in the script.

 

[dgs!]

Recreating the table with the columns in a different order is the easy part, but there is much, much more to tables.
There are PK, UK, FK, Column comments, triggers, ETC to consider.
1) Extract PK, UK, FK definitions
2) Drop/disable them as needed.
3) Rename existing table
4) Create new/improved table
5) Add PK, UK, FK to new table
6) Bring data from original table to new/improved table.

I'm sure I missed some steps here, but you get the idea. Creating the table is easy, it's all the other stuff that adds complexity.
When I heard that we did not have a one button solution to this I found that Oracle has something to make this easier.

dbms_redefinition.can_redef_table
dbms_redefinition.start_redef_table
dbms_redefinition.copy_table_dependents
dbms_redefinition.sync_interim_table
dbms_redefinition.finish_redef_table