Re: Oracle transactions and DDL statements.

Just a few simple thoughts...
Can we step back, I'd like to know why doing a commit on creating a table becomes an issue.
We often use 'execute immediate' to release different version of our software, providing the flexibility to conditionally create/drop/alter database object definitions.
Our software upgrade process also includes sections to load/update/delete data in configuration tables.

We have a requirement that the whole database be backed up before the upgrade process is initiated, so the database can always be brought back to the image prior to starting the upgrade. On a few rare occasion we have also choosen to create a backup of a specific table (which could be done with something like ... Select count(*) in my_variab from user_objects  where object_name = 'SPEC_TAB';
if my_variab > 0 then

   execute immediate 'Create table SPEC_TAB_BAK as select * from SPEC_TAB';
   execute immediate 'Drop table SPEC_TAB cascade constraints';    execute immediate 'Create table SPEC_TAB ( ... end if;

If later on a condition occurs that requires that you roll back a number of the changes,
it can all be done programatically (execute immediate 'Drop table ...')

The short answer to 'can you rollback to a point partially through the installation' - no - not automatically.

This does not cause us a major issue, since we typically do the complete installation, or in a disaster case roll back everything, using a backup.

HTH. Received on Wed May 10 2006 - 11:13:39 CDT