CODEWITHSUNDEEP
sqloracle-databaseshellunix
hemanth gundabattula
hemanth gundabattula

Reputation: 13

SQL Query for executing a series of update statements

I have a set of update statements which needs to be executed one after the other. The database that i am using is Oracle and i am running these queries by connecting to the database using a shell script. What i want to know here is to how i could execute these commands one after the other only if the previous update statement is successful. The auto update is set to OFF in the shell script and i want to commit all the statements only when all of them are executed successfully else i want to exit. Is there a way to run these update commands one after the other by checking the preceding queries status and commit after all the queries are run successfully.

Upvotes: 1

Views: 509

Answers (1)

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

you can use WHENEVER SQLERROR EXIT ROLLBACK:

sqlplus -s username/password@network-name <<EOF
   WHENEVER SQLERROR EXIT ROLLBACK
   update table1 set mycolumn=value where mycolumn=1;
   update table1 set mycolumn=othervalue where mycolumn=2;
   update table1 set mycolumn=othervalue where mycolumn=3;
   update table1 set mycolumn=othervalue where mycolumn=4;
   commit;
   exit
EOF

Here is a link to the documentation:

https://docs.oracle.com/database/121/SQPUG/ch_twelve052.htm

Upvotes: 2

Related Questions