Schema Refactoring

Ever wanted to change a column or table name? I'm pretty sure there aint no way to globally update changes like this throughout your application via the APEX interface. So here's a little tip on how you can get away with it and the gotchas to look out for.

Simply export the application, make a copy of the exported SQL file and then open it in your favourite text editor. Perform a search and replace for all instances of the required change. I recommend reviewing each replacement one by one. It may seem like a tedious option, but imagine trying to search the application manually for all these references via the web interface.

Keep track of the pages, particularly reports, that you are altering. You'll want to test them all after the import. You can usually find the page id at the top of the section you're messing with, or else an item name prefix in the vicinity will give it away.

Import the application, then check each of the pages you've altered for workingness. Often I've noticed that report regions may throw a "no data found" exception for no obvious reason (the report itself will not render at all). The resolution to this is to:

• duplicate the region by using the region copy tool
• move any region items to the copy
• confirm the copy works as expected
• delete the original

This is a handy workaround to know about for when regions start behaving oddly in general. This problem is not only triggered by editing an export file. It's some kind of bug that tends to crop up when you make major changes to a report region's query. I've seen plenty of people complaining about it on the forum, but no official explanation of how or why it occurs. Perhaps some cache like mechanism is at play somewhere.

You could also try deleting the application before importing it, I've not tried it. But if you intend to eventually migrate to production and you want to minimise your outage, I wouldn't recommend that as an option. I've had weird bugs appear after deploying to production that didn't exist in the exact same application in dev or test. Better to annihilate the risk of delaying the inevitable by getting your self a new region ID.

Perhaps I could put together a groovy tool which intelligently identifies all database objects in an export file, allows you to change them and also manages the assignment of new region IDs for altered parts of the application. That would rock!