rashley has asked for the wisdom of the Perl Monks concerning the following question:

I have a sqlplus script that I'm invoking from Perl. 
-- IF SQLPLUS FAILS, RETURN BAD EXITCODE
WHENEVER sqlerror EXIT sql.sqlcode
WHENEVER oserror EXIT failure
SET TIMING ON

<sql statement>
/
<sql statement>
/
.
.
.
/
COMMIT
/
EXIT

[download]
Is there a parameter/setting I can put up top to ensure that if it hits a DB error of any kind, the entire thing rolls back?

Currently if it hits an error on one of the later statements, the first ones are still being committed, and I'm in a situation where I have no control over the sqlplus default settings.

Thanks.

2005-10-21 Retitled by Arunbear, as per Monastery guidelines
Original title: 'sqlplus rollback on error'

Replies are listed 'Best First'.
Re: (OT) sqlplus rollback on error
by Roy Johnson (Monsignor) on Oct 21, 2005 at 16:22 UTC
    Add the ROLLBACK parameter to the WHENEVER sqlerror line: 
    WHENEVER sqlerror ROLLBACK EXIT sql.sqlcode

    [download]
    Caution: Contents may have been coded under pressure.
[reply]
[d/l]
      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]
Re: (OT) sqlplus rollback on error
by robin (Chaplain) on Oct 22, 2005 at 14:28 UTC
    You should also 
    set autocommit off

    [download]
    in case some muppet has turned on autocommit in the PL/SQL defaults.
[reply]
[d/l]

Back to Seekers of Perl Wisdom