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.
-
Are you using Oracle or MySQL?jarlh– jarlh2017-09-27 06:55:46 +00:00Commented Sep 27, 2017 at 6:55
-
use if else blockMr. Bhosale– Mr. Bhosale2017-09-27 06:59:05 +00:00Commented Sep 27, 2017 at 6:59
-
It is oracle DBhemanth gundabattula– hemanth gundabattula2017-09-27 08:05:37 +00:00Commented Sep 27, 2017 at 8:05
-
update * from table_name where condition;hemanth gundabattula– hemanth gundabattula2017-09-27 08:05:58 +00:00Commented Sep 27, 2017 at 8:05
-
update * from table_name where condition;update * from table_name where condition;update * from table_name where condition;update * from table_name where condition;update * from table_name where condition;hemanth gundabattula– hemanth gundabattula2017-09-27 08:06:22 +00:00Commented Sep 27, 2017 at 8:06
|
Show 2 more comments
1 Answer
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:
2 Comments
hemanth gundabattula
This is exactly what i am looking for. Thanks mate :)
Cyrille MODIANO
great ! consider accepting the answer if it resolved your problem.