CODEWITHSUNDEEP
sqloracle-databasecommitoracle-sqldeveloper
Sabari Ram
Sabari Ram

Reputation: 69

Commit transactions if no error,rollback if error occured in Oracle SQL* plus

Following is small block of code i use to deploy the SQL scripts in my databases. I'm just wondering if i can automate this task of commit or rollback based in the result.

disc
connect username/password@database
spool D:\Deployments\path\to\logfile\logfile.log
@D:\Deployments\path\to\script\sqlquery_script.sql

If the sql script is ran successfully with out any errors means I want the system to Commit it automatically and in case any error is occcured all teh transactions should be rollbacked (Note that my sql script has many update statements)

When I use WHENEVER SQLERROR EXIT SQL.CODE ROLLBACK; the SQL* plus window is closed without showing any error.

Please help to resolve this.

Upvotes: 0

Views: 12165

Answers (2)

Ankur Nirmalkar
Ankur Nirmalkar

Reputation: 143

Probably you can give call to a procedure or in the Sql script you give call to procedure

exec procedureName(parameter1,parameter2);

Keep this statement in your sql file

and in case of exception keep this statement along with the above statement

EXCEPTION WHEN x_Error THEN
ROLLBACK ;

Upvotes: -1

eaolson
eaolson

Reputation: 15094

You don't say what your script is. How about putting it inside a PL/SQL anonymous block?

BEGIN
    ... updates here ...

    COMMIT; 
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    RAISE; 
END;
/

Upvotes: 8

Related Questions