Home » SQL & PL/SQL » Client Tools » SQLPLUS for Oracle 11 not rolling back (Oracle 11, AIX)
SQLPLUS for Oracle 11 not rolling back [message #594799] Tue, 03 September 2013 04:53 Go to next message
sjairam
Messages: 2
Registered: September 2013
Location: Moon
Junior Member
Hi

I have a shells script which invokes a SQL file. However even with AUTOCOMMIT OFF and on SQLERROR EXIT ROLLBACK. Sqlplus fails to rollback.

My sql file has 3 lines 3 are correct and 1 is incorrect. For example:
INSERT INTO TEST_ROUTING VALUES (24, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (25, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (26, 'ROUTING);

Lets say file is called 1.sql

My shell script invokes this SQL as follows: (Where $File1 = 1.sql)

$SQLPLUS_PATH/sqlplus -s /nolog <<-EOF>> ${LOGFILE}
connect $DB_USER/$Password1@$Database1
SET AUTOCOMMIT OFF
@$File1
WHENEVER SQLERROR EXIT ROLLBACK;
EOF

if [ $? != 0 ]
then
echo "The SQL failed. Please refer to the log for more information "
echo "Error code $?"
echo "8. Outside While "
while read $LINE
do
echo $LINE
done < $LOGFILE
fi

So tried SET AUTOCOMMIT, tried SQLERROR ROLLBACK and tried few variations.
And no posts seem to cover this very well.

Any thoughts
Re: SQLPLUS for Oracle 11 not rolling back [message #594801 is a reply to message #594799] Tue, 03 September 2013 05:05 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
My sql file has 3 lines 3 are correct and 1 is incorrect

Underlined text says that file contains 4 lines, not 3.

Which one of them is incorrect? Why is it incorrect? How will Oracle know that it is incorrect?

[Updated on: Tue, 03 September 2013 05:06]

Report message to a moderator

Re: SQLPLUS for Oracle 11 not rolling back [message #594802 is a reply to message #594799] Tue, 03 September 2013 05:06 Go to previous messageGo to next message
gazzag
Messages: 356
Registered: November 2010
Location: Bristol, UK
Senior Member
Why should it rollback? A clean exit from SQL*Plus will cause an implied COMMIT. If you want to rollback, type "ROLLBACK".
Re: SQLPLUS for Oracle 11 not rolling back [message #594808 is a reply to message #594802] Tue, 03 September 2013 05:21 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to call WHENEVER SQLERROR EXIT ROLLBACK; before any errors, not after them.
Re: SQLPLUS for Oracle 11 not rolling back [message #594809 is a reply to message #594801] Tue, 03 September 2013 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
Littlefoot wrote on Tue, 03 September 2013 11:05
Which one of them is incorrect? Why is it incorrect? How will Oracle know that it is incorrect?

missing end quote on the last one.

[Updated on: Tue, 03 September 2013 05:25]

Report message to a moderator

Re: SQLPLUS for Oracle 11 not rolling back [message #594811 is a reply to message #594809] Tue, 03 September 2013 05:33 Go to previous messageGo to next message
sjairam
Messages: 2
Registered: September 2013
Location: Moon
Junior Member
Apologies. There are 3 lines. One is incorrect.

INSERT INTO TEST_ROUTING VALUES (26, 'ROUTING);

Notice the single quote at beginning of "ROUTING" and notice no closing '

This is a simple example, where users will pass in a SQL file only to a script.
Users are not SQL / DBA , hence they wont know how to rollback or commit.

Re: SQLPLUS for Oracle 11 not rolling back [message #594813 is a reply to message #594811] Tue, 03 September 2013 05:51 Go to previous message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You code says:
@$File1 
WHENEVER SQLERROR EXIT ROLLBACK; 

Substitute these two lines, i.e.
WHENEVER SQLERROR EXIT ROLLBACK; 
@$File1 

Any improvement?

[EDIT] After seeing Lalit Kumar B's message, I realized that I didn't see that I just reposted what Cookiemonster has already said. Sorry!

[Updated on: Tue, 03 September 2013 06:12]

Report message to a moderator

Previous Topic: view red cross
Next Topic: how to convert SQL server stored procedures to Oracle 11g
Goto Forum:
  


Current Time: Sun Nov 23 11:28:25 CST 2014

Total time taken to generate the page: 0.07665 seconds