Worse things happen, but you have options

One day you find yourself in a situation you have to change something in an application and the worst happens... after the change the application doesn't work anymore.

It happened to me... it was a very small APEX application I created for my wife a long time ago. The application allows her to give points on the work of her students. At that time I created a tabular form on top of a view. The underlying tables get updated by an instead of trigger, like I describe here. Of course I forgot about the trigger, so when she asked me to add a field to the view, I did a replace of the view with the new definition.

I guess you know what happened? My wife wasn't able to update the points anymore because with the replace of the view the trigger got deleted. Nothing to worry I thought, I'll use a script to recreate the trigger. If I could find the script now! I created that app very quick and on the side and didn't treat it as a real project, so my source was not in source control.

Then you are happy you are inside the database with APEX as you can use all the features to recover from "disaster" or better human errors. So I thought to use flashback... but how does that work with a view and a trigger linked to that? I then thought I would find my object in the recyclebin of the Oracle database, but nothing there either...

What else can I try... in APEX you have the SQL Workshop and I sometimes use that to do some quick things and I know there is a history in there. The history is really handy in cases like this, but I was unlucky, the code was not there...


Next to try on the list... in SQL Developer there is also a history of the commands you do. I could go back in the SQL history till January, but that was not far enough. I guess there is a preference in SQL Developer to say how much it needs to remember, but I never touched that. Or it must have been with an upgrade of SQL Developer that I lost some history?
Nevertheless I'm so happy you can use F8 or go to View - SQL History and see all your statements.


The only thing left on my list was to restore a backup or a dump. In my case I kindly asked John to get a copy of my scheme like it was the day before.

But then you see the trigger was not there either ;-)

My wife only uses the application a few times a year, so I probably did another change on that view which had caused the trigger to disappear earlier. Finally John found a backup of a few months ago where the trigger still existed, so I could use that. Thanks again my friend!

So lessons learned:
-) Always treat your project as a real project, even if it's very small
-) Save your scripts, if it's not in source control, at least make a backup when you finish development (or even during development)
-) Use the features of the database if things go wrong
-) SQL Developer and APEX have a SQL history, which can save you hours
-) Get a good hosting provider in case all of the above goes wrong. Make sure backups are taken and kept (for a long time) and keep friendly with them
-) and if all that doesn't help, make sure you have a good memory so you can recreate it, because it seems that will be your only option