The SQL statements executed in a user session are as follows:
SQL> CREATE TABLE product
(pcode NUMBER(2),
pname VARCHAR2(10));
SQL> INSERT INTO product VALUES (1, ‘pen’);
SQL> INSERT INTO product VALUES (2,’pencil’);
SQL> SAVEPOINT a;
SQL> UPDATE product SET pcode = 10 WHERE pcode = 1;
SQL> SAVEPOINT b;
SQL> DELETE FROM product WHERE pcode = 2;
SQL> COMMIT;
SQL> DELETE FROM product WHERE pcode=10;
Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a command
in the session? (Choose two.)
A.
The rollback generates an error.
B.
No SQL statements are rolled back.
C.
Only the DELETE statements are rolled back.
D.
Only the second DELETE statement is rolled back.
E.
Both the DELETE statements and the UPDATE statement are rolled back.
Can any one tell me why rollback is generating error?
A commit will erase all the save points above it.
Why the update not rolled back,if there are no COMMIT after execute the statement=?
I think just Delete statement will rolled back.
Can someone explain me=?
Sorry, it was.. ‘Why the option D is wrong..?’
After commit there is not any more any save points. So command
ROLLBACK TO SAVE POINT a
will raise an error and nothing will be rolled back.
True. Savepoint A is not valid after commit. so it will give an error.
D is the only Right answer
Did you tested Mr. Charu? I don’t think D is the right answer. I tested it.
ROLLBACK TO SAVEPOINT a give error
SQL Error: ORA-01086: savepoint ‘A’ never established in this session or is invalid
01086. 00000 – “savepoint ‘%s’ never established in this session or is invalid”
*Cause: An attempt was made to roll back to a savepoint that was never
established in this session, or was invalid.
*Action: Try rolling back to the savepoint from the session where it is established.
Actually – i tested it
And when I execute
“select * from product”
after error is generated
there is record in database:
PCODE,PNAME
10,pen
So last action (DELETE) was rollback. otherwise it wouldn’t be in table.
If you closed the session without a second COMMIT (explicit or by your application interface) it’s sure that the “DELETE FROM product WHERE pcode=10;” is not COMMITTED and so the row with pcode=10 remains into the table. So:
A. and B. is the correct answer.
A,B IS CORRECT ANSWER
SQL> create table product(pcode number(2),pname varchar2(10));
Table created.
SQL> insert into product values(1,’A’);
1 row created.
SQL> insert into product values(2,’B’);
1 row created.
SQL> savepoint a;
Savepoint created.
SQL> update product set pcode=10 where pcode=1;
1 row updated.
SQL> savepoint b;
Savepoint created.
SQL> delete from product where pcode=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> delete from product where pcode=10;
1 row deleted.
SQL> rollback to savepoint a;
rollback to savepoint a
*
ERROR at line 1:
ORA-01086: savepoint ‘A’ never established