| SQLPLUS for Oracle 11 not rolling back [message #594799] |
Tue, 03 September 2013 04:53  |
 |
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 #594813 is a reply to message #594811] |
Tue, 03 September 2013 05:51  |
 |
Littlefoot
Messages: 21782
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
