preliminary note
You issue the statement
cat "temp.sql" | sqlplus -s sys/oracle@xe as sysdba
You should avoid to pipe into sqlplus. Better you execute
sqlplus -s sys/oracle@xe as sysdba @temp.sql
An advantage is that no prompt will be generated in the output file.
You can better control how the output looks like. Some input will produce
results that make sense when working interactively but not make sense when executing a batch job.
I can't see any advantage in piping commands to sqlplus.
- You should not use SYS to do your work with application objects and application data. There are a lot of security reasons. Besides that you should be aware that
Queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY
This is not the behavior if you issue DML statements. This can make your data inconsistent.
- From the manual:
You can specify changes you wish to make to the information stored in the database using the
SQL Database Manipulation Language (DML)
commands UPDATE, INSERT, and DELETE—which can be used independently or within a PL/SQL block.
These changes are not made permanent until you enter a SQL COMMIT command or a
SQL Database Control Language (DCL) or Database Definition Language (DDL) command
(such as CREATE TABLE), or use the autocommit feature. The SQL*Plus autocommit feature causes
pending changes to be committed after a specified number of successful SQL DML transactions.
(A SQL DML transaction is either an UPDATE, INSERT, or DELETE command, or a PL/SQL block.)
So your script must contain the right statements so that all can be rolled back.
SET EXITCOMMAND was introduced in 11.2 to SQL*Plus. I don't think that it added something really new to SQL*Plus.
You can use the WHENEVER commands to control the SQL*Plus behavior.
I want to commit all transactions only if all queries succeed
So I assume if the first error occurs you want to rollback and exit.
Therefore you should start your script with the WHENEVER SQLERROR statement
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
If an SQL error occurs the transaction will be rolled back and SQL*Plus will be exited.
The error code that SQL*Plus is an OS-system depended value that signals an error
Maybe a WHENEVER OSERROR command should be executed, too
WHENEVER OSERROR EXIT FAILURE ROLLBACK
Of course AUTOCOMMIT should not be enabled, but
SET AUTOCOMMIT OFF
is the default, so you must not execute this command.
You can add these statements to the site profile or user profile scripts (glogin.sql or login.sql)
so that they will be executed automatically whenever you connect to a database, if this makes sense.
Here draft how you can run your scrip.
sqlplus script run01.sql
SPOOL run01.log
REM file: run01.sql
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
EXIT COMMIT
REM COMMIT is the default of EXIT
shell script main1.sh
#!/bin/bash
sqlplus $USER/$PASSWORD @run01.sql
if [[ $? = 0 ]]; then
echo "sql script executed successfully" >&
else
# alert
echo "error when executing sql script, check run01.log" >&2
fi
You added a new requirement:
I would like to execute all queries irrespective of any error and rollback all queries if there was at least one error.
You can again run the script an rollback end exit if the first error occurs. If that happens you can
run your script again
but now you don't exit in case of error but continue with the execution of the script. At the end
of the script you roll back all transactions using the {EXIT COMMAND]8.
sqlplus script run02.sql
SPOOL run02.log
REM file: run02.sql
WHENEVER SQLERROR CONTINUE NONE
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
EXIT ROLLBACK
shell script main2.sh
#!/bin/bash
sqlplus $USER/$PASSWORD @run01.sql
if [[ $? = 0 ]]; then
echo "sql script executed successfully" >&
else
sqlplus $USER/$PASSWORD @run02.sql
# alert
echo "error when executing sql script, check run02.log" >&2
fi
This is my preferred solution. The only disadvantage I can see is
that the statements of the temp.sql script
that are executed until the first error occurs are executed two times:
in script run01.sql and in script run02.sql.
In a Linux environment you can try to avoid this by running the whole
script and analyze the logfile with the grep
statement
an then exit the session with commit or rollback, depending on the
result of this analysis.
sqlplus script run03.sql
SPOOL run03.log
REM file: run03.sql
WHENEVER SQLERROR CONTINUE NONE
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
HOST ./analyze_logfile.sh
@exit.sql
shell script main3.sh
#!/bin/bash
sqlplus $USER/$PASSWORD @run03.sql
if [[ $? = 0 ]]; then
echo "sql script executed successfully" >&
else
# alert
echo "error when executing sql script, check run03.log" >&2
fi
shell script analyze_logfile.sh
#/bin/bash
# file: analyze_logfile.sh
if grep "^ORA-" run03.log; then
echo "EXIT ROLLBACK"
else
echo "EXIT COMMIT"