How do you roll back database changes from hotfix installation?

Resolving The Problem

Our upgrade script does not take a backup of a previously compiled package before compiling the new package. If a package implemented for a UserExit procedure was overwritten with an updated procedure definition that contains a blank body, this is because there were changes made to the UserExit functionality at the code level which required the definition of the procedure to be updated. Without this update, the UserExit may error out every time it is called.

Most often, packages are compiled by running a .sql script. It is recommended that you keep this .sql script in some known location on your system, should you ever need to make modifications to a package or recompile it. Most likely the script for this particular package exists somewhere on your system.

We can not be responsible for maintaining custom functionality in our customers' environments, but here is a suggestion of how you might go about restoring the previous functionality. Please check with your System Administrator and DBA before making any changes.

1) Locate the .sql script that was used to compile the package that was overwritten. If you can not find the .sql file, you may be able to get the code by looking up the compiled package in your production environment if the package has been implemented there.

2) Locate the .sql script used during the upgrade to compile the new updated package.

3) Save a backup copy of the new .sql file before modifying it.

4) Copy and paste the body of the old package from step 1 into the body of the package in the script from step 2. Be sure not to modify any part of the package which already exists in the .sql file. (declarations for procedure and its arguments, for example).

5) Complete the upgrade and run the .sql script normally.




Don't forget: We highly recommend you take a backup of your database before running any .sql scripts during the hotfix installation process.