To the OCD developer in all of us.
You have a carefully crafted table. Then the requirements change, as they always do, and you an important column to the table. Where does that column go? Well, at the end of the table of course.
Well, I say no more.
OCD means Obsessive-compulsive disorder. It's a real disease and no laughing matter. Read more from the Mayo Clinic. Now, of course, the name of this app is tongue in cheek. Many of us feel annoyed when we add an important column to a table, and it's listed at the end. We wish we could move it and place it in a logical position. Well, now we can!
- Is it safe? I think so, I use it all the time but USE AT YOUR OWN RISK
- Is the data moved? No, not at all!
- Is a new table created? No, not at all!
This is a straightforward single page APEX app. Install the ocd-table-column-reorder.sql application.
The application will install the package ocd_table_reorder as part of the installation or upgrade as a supporting object.
If you have multiple schemas on your Workspace you can either:
- Install the package on each schema
- Create a synonym and grants
connect ORIGINAL_SCHEMA
grant execute on ocd_table_reorder to NEW_SCHEMA;
connect NEW_SCHEMA
create synonym ocd_table_reorder for ORIGINAL_SCHEMA.ocd_table_reorder;
See a video of the switching between schemas feature implementation.
This app now requires APEX 18.2 (but you could trivially backport to 18.1 if needed)
For APEX 5.1 see Release v1.01.
If you're on 19.1 or beyond, then you may want to change the clearMessages JS function to use apex.theme42.util.configAPEXMsgs instead of apex.theme42.configureSuccessMessages. But it's optional.
The functionality to re-order the columns depends on the Invisible Columns feature of Oracle Databace 12c.
- Block Move: select multiple columns that will move together ✅ (v2.01 June 18, 2020!!)
- Pre-Defined column arrangements (like move Audit Columns to the end)
The original script that kick this off is from Dermot ONeill with his SQLcl Aliases & The Invisible Column Trick. Original pos.xml.
I've shared his gem with many people, but then Gemma Wood provided me with a pretty nice enhancement that moves the "Audit Columns" of a table to the end. You can get that here. You can load it into SQLcl just like Dermont shows in his blog:
alias load c:\apos.xml
Finally, Gemma also had the idea... hey sometimes I just wish I could drag & drop the columns around....