I have had change control call me several times in the past to restore a table because it was accidentally dropped when executing the SQL script generated using Application Designer. Whenever you use Application Designer to generate a script with “Alter by Table Rename” option checked, the script will have the following SQL’s

— Start the Transaction

— Create temporary table

— Copy from source to temp table

— CAUTION: Drop Original Table

— Rename Table

— Done

The risk of running this script As-Is is that if any of the above SQL encounters an error then the script will still proceed to run the next SQL in the script. So if there is an error while running any SQL prior to dropping the original table then we would have lost the table and all the data.

Hence the best practice for running any scripts created using Application Designer is to include ‘WHENEVER SQLERROR EXIT’ at the beginning of the script. This will ensure that the script will abort anytime there is an error. Making this part of our Change Control checklist has ensured that there is no reoccurrence of this issue.

Scripts generated using Change Assistant automatically have ‘whenever sqlerror exit’ at the beginning of the script.

Posted by Nitin Pai
Comments (4)
September 6th, 2007

Comments (4)

Nitin - October 25th, 2007

Some of the advantages of using "ALTER In Place" for altering huge tables are as follows (Note - this is specific to Oracle db). - Original table is not dropped hence all indexes do not have to be rebuilt - Statistics need not be regenerated in most cases - Much faster - additional redo logs not generated, etc. - Since temporary table is not created and inserted into, saves space Disadvantages are - Performance of UPDATE might be slower since all rows are updated when a new column is added - Using "INSERT /*+ APPEND */ " might be faster in some cases for the "ALTER by Table Rename" option because it generates less redo My preference is to go for "Alter in Place" because of the advantages mentioned above. Thanks Nitin

Nitin - October 25th, 2007

The "WHENEVER SQLERROR EXIT" is specific to scripts generated for Oracle db. I am not a SQL Server expert but I think the equivalent for SQL Server is "EXEC SQL WHENEVER SQLERROR GOTO ..." which can be used to handle the errors. Thanks Nitin

Sudripta - October 24th, 2007

Hi Nitin, What are scenarios/advantages when we should use "Alter in Place" and "Alter by Table-Rename". Thanks, Sudripta

Sudripta C - October 24th, 2007

Hi Nitin, Is the statement "WHENEVER SQLERROR EXIT" specific for a particular DB plateform (i think it is), like oracle. IF yes, then can u tell me what would be the statement for SQL server. Thanks, Sudripta

Comments are closed.