Sometimes you need to update a database with the synchronization script generated when comparing two schemas. However, this script will not always be executed against the schema used for generating this script (the developer has run the comparison between the earlier and present schema, generated the script and sent it out to the administrators of the customer, for example). Locally, the earlier schema has been manually modified and, as a result, the updating script executed only partially and the rest produced errors. After some changes, there appears a need to execute it again, whereas the first part of the changes was already applied and will now lead to the errors in the log.

Or, let's review another case. The updating script is included into an installation package (updating the software and the database). Let's assume that something is broken in the program. To repair this, the installation package was run once again. It's preferrable that the database updating script would "understand" that there is no need to do anything in the database (all is done already), would work out silently with no errors and that's all.

In that regard, there is a need to manually adapt the updating synchronization scripts with the purpose to make it reusable (i.e., you can execute it as many times as you want and this wouldn't produce errors).

For example, instead of:

ALTER TABLE HR.JOBS ADD MAX_SALARY NUMBER(6);

you can write the following construction:

BEGIN
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE HR.JOBS ADD MAX_SALARY NUMBER(6)';
EXCEPTION WHEN OTHERS THEN
IF SQLCODE <> -1430 THEN --NOT "column being added already exists in
table"
RAISE;
END IF;
END;
END;

I.e., if the error arises due to the fact the field was already created, it needs to be ignored. If the error differs from this, it needs to be shown as usual in order to mask out all possible errors. And so on, the same principle with index creation, deletion, etc. So, it would be great if there was some "reuse" option when generating the updating synchronization script that would generate similar constructions to the shown above. The suggested solution may be not optimal and, may be, you have to check if the object exists before creating/deleting it by referring to the corresponding view and then decide if something needs to be done or not, rather than process the error.

Obviously, the implementation wouldn't be that simple (there may be many different ways in practice). However, if it could be implemented, it would be really an advantage over PL/SQL Developer, TOAD and any other competitors.