in reply to (OT) sqlplus rollback on error

Add the ROLLBACK parameter to the WHENEVER sqlerror line: 
WHENEVER sqlerror ROLLBACK EXIT sql.sqlcode

[download]
Caution: Contents may have been coded under pressure.

Replies are listed 'Best First'.
Re^2: (OT) sqlplus rollback on error
by rashley (Scribe) on Oct 24, 2005 at 13:30 UTC
    Okay, if I'm reading these error statements correctly, you can ROLLBACK, or EXIT sql.sqlcode, but not both.

    I need it to rollback AND return an error code, in order to trigger our notification proticol and let someone know that something went wrong.

    Any way to do that?

[reply]
      My PL/SQL User's Guide and Reference indicates that you can do both. The syntax is: 
      WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable] [COMMIT|R
+OLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}

      [download]
      That is, you can EXIT or CONTINUE, and in either case, you can COMMIT or ROLLBACK. So just do EXIT and ROLLBACK. It should work.
      Caution: Contents may have been coded under pressure.
[reply]
[d/l]
        Yep, I had EXIT and ROLLBACK backwards.

        Now I just have to figure out if it'll return an exit code if the connection times out.

        That'll take some experimentation.

        Thanks

[reply]

In Section Seekers of Perl Wisdom