DDL does a commit before the DDL, and another after. So you have potentially three commits in your script, of which the last one does nothing and is the source of the useless "commit complete" output. sol.beach is correct, the rollback is too late, and does nothing regardless. But sqlerror can be used to handle continuation of a number of DDL's Is WHENEVER SQLERROR EXIT FAILURE any good when the script contain DDLs ?
There is no need to check the file contents, just check for the error in your shell script. Something like:
oracle > cat t.sh
sql='select 1/0 from dual;'
sqlplus -s / <<EOF > t.log
whenever sqlerror exit 1
if [ $? -ne 0 ]; then
echo "An error occured"
oracle > ./t.sh
An error occured
The $? is s ahell built-in variable that holds the exit code for the last executed command. If it is 0 then the command was successful, if it is not zero, then there was an error of some kind. The exact error depends on the number returned and the command being executed.
I do not have any problems pasting things into the editor window, I just use CTRL+INS to copy then SHIFT+INS to paste.
What is does $? stand for?
In shell scripting, the "$" indicates that whatever follows is an environment variable. When processing a statement, the value of said environment variable will be substituted at that point.
oracle$ export ORACLE_SID=fubar
oracle$ echo $ORACLE_SID
So, we see that when I "echo $ORACLE_SID", what is echoed is the value of the variable named ORACLE_SID.
As for "$?", there are several built-in variables. As explained, this one is the return code of the last executed statement.
Some others are;
$n - positional variable 'n' -- the "n'th" variable passed on the command line
$* - all positional variables -- all of the variables passed on the command line
$# - the number of positional variables passed on the command line
$$ - process number of the current shell
I would strongly urge you to get yourself a copy of "Unix in a Nutshell".
and please tell me how did you manage to copy and paste code into this forum?? I have been trying to copy and paste into here from text editor but it just does not work