PL/SQL Block output

begin
For i in 1..5 loop
insert into A values(i);
savepoint 1;
end loop;
rollback to savepoint 1;
commit
end;
--initially there are no data in table A. So my question is after execution of this block what should be the data present in table A?

Questions by Kanhucharan   answers by Kanhucharan

Showing Answers 1 - 12 of 12 Answers

ashwinee2

  • Apr 11th, 2008
 

After the loop exits 1 to 5 is inserted into the table and a savepoint is also created with the name savepoint1.
Now after rollback no data will be deleted and the table A will have values 1 to 5.

nagarajkv

  • May 5th, 2008
 

1 to 4 records, because if the same savepoint name is given the previous savepoint is erased. So the last savepoint refers to record 5. Rollback will rollback the value to record no 4. now when the commit executs then 4 records are commited.

  Was this answer useful?  Yes

The flow of execution will be as below:
step1: loop will execute 5 times and the variable value(i) will store in the table a in series of values from 1 to 5.
step2: all 5 values in series from 1..5 will store in table a.
step3: transaction will store in savepoint 1.
step4:rollback to savepoint 1will consist the all value in the table.
step5: next time when program will
execute again loop will store another series of values from 1 to 5 in table a.
So out will be like this:

my program name is a1.sql
SQL> start a1;

PL/SQL procedure successfully completed.

SQL> select * from a;

A
----------
1
2
3
4
5
again
SQL> start a1

PL/SQL procedure successfully completed.

SQL> select * from a;

A
----------
1
2
3
4
5
1
2
3
4
5

10 rows selected.

The first mistake in this code is
savepoint 1;
You can't give 1 as identifier. Identifier name must begin with a letter.

insert into A values(i);
savepoint x;

Here you are defining savepoint after inserting the value. So the final savepoint will be created after inserting value 5.

rollback to savepoint x;
Since no transaction was done after inserting value 5 it does  not have any effect.

Output will be 1 2  3 4 5

If you change the order of these two statements as follows

savepoint x;
insert into A values(i);

Then the output will be 1 2  3 4




savepoint x;
insert into A values(i);

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions