(OT) sqlplus rollback on error

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'

Comment on (OT) sqlplus rollback on error Download Code

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]
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]
Re^3: (OT) sqlplus rollback on error by Roy Johnson (Monsignor) on Oct 24, 2005 at 14:43 UTC
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^4: (OT) sqlplus rollback on error by rashley (Scribe) on Oct 24, 2005 at 15:57 UTC
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]


Think about Loose Coupling
	PerlMonks