Oracle - Execute several sql files in a transaction

Question

I have several sql files. I need to execute them in a transaction. When an error occurs all changes will be rolled back.

Is this possible using sqlplus or some other tool ?

EDIT There are not any explicit commits in those files, just DDL sql.

Answer 1

Generally: whether this is possible depends extremely on the content of the SQL files - for example:

• are there explicit COMMITs ?
• are any Stored Procedures involved ?
• are there any DDL commands ?
• are any DB Links involved ?

etc.

IF you don't know/have control over the content of the SQL files I would say no..

IF you can make sure that the files don't contain any COMMIT, any DDL, any Stored Procedure calls etc. THEN you could just concatenate them and execute them in one transaction... how to do that exactly depends on your tool...

UPDATE - after the OP added that the SQL files contain DDL command:

The answer is NO since DDL commands use implicit COMMIT - although some DBs might allow for some "workaround" (for example Oracle has a configurable "Flashback-Area" which could be "abused" to achieve what you want) but most don't.

The usual way to solve this (DDL scripts / transactions) is to write 2 scripts - one to make all changes... and a second one to run only if an error occurred while executing the first one... how exactly to write such scripts depends on the specifics of your situation.

Answer 2

Use the start command, and the whenever sqlerror then just don't commit.