Oracle – How to Preserve SQL*Plus Session in Shell Script

preliminary note

You issue the statement

 cat "temp.sql" | sqlplus -s sys/oracle@xe as sysdba  

1. 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.

2. 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.

3. 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"