Savepoints

[Davidl]

Hi all,
I am attempting to use the TOracleSession and TOracleScript components to develop an upgrade program for my applications. Many upgrades involve DB structure changes so I set the script lines, execute, check the output to make sure it is successful and then continue to the next DDL statement. I don't do it all in one go because there are some statements that do not mean that the entire upgrade should stop ( like creation of an index ). I have been attempting to use the Savepoint and RollbackToSavepoint to perform this task, but constantly receive ORA-01086 errors. What I have read in the forums so far lead me to believe that the savepoint is dropped as soon as the first commit happens, so therefore I am to assume that the successful execution of a script also performs a commit ? Is this the case ? Is there any way for me to achieve what I am trying ? I have all AutoCommit options at False.

 

[xjg]

when you execute any DDL statement
ORACLE commit the transaction implicitly
so the savepoint has been deleted.

 

[Davidl]

Thanks for your reply.

Am I therefore correct in assuming that there is no way to turn this off at the Oracle side ?

 

[Marco Kalter]

Your assumption is correct. This is one of the most unfortunate Oracle features.

------------------
Marco Kalter
Allround Automations

 

[Davidl]

Thanks Marco. If I may impose upon you a bit more, have you any experience with anyone attempting to do what I am ? In other words, any suggestions for how you would tackle trying to automate an upgrade which comprises DB structural changes ? Preferrably without having to export and then re-import upon failure ( as some of my customer databases are quite large ).

 

[Marco Kalter]

I think export/import or backup/restore is the only way to undo these kind of upgrades. If you are just adding objects, then you could simply drop them, but if you modify existing objects, you're probably stuck with this method.

Unless someone else has a better idea...?

------------------
Marco Kalter
Allround Automations

 

[Davidl]

Ok - Thanks.