GeekInterview.com
  I am new, Sign me up!
 
GeekInterview.com  >  Interview Questions  >  Oracle  >  PL/SQL
Go To First  |  Previous Question  |  Next Question 
 PL/SQL  |  Question 210 of 241    Print  
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?



  
Total Answers and Comments: 4 Last Update: May 29, 2008     Asked by: Kanhucharan 
  
 Sponsored Links

 
 Best Rated Answer
Submitted by: ashwinee2
 
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.

Above answer was rated as good by the following members:
satyam_Ora, neelapu_k, ratna82, Keith358, kinkyfellow
April 11, 2008 10:18:17   #1  
ashwinee2 Member Since: April 2008   Contribution: 3    

RE: PL/SQL Block output
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.

 
Is this answer useful? Yes | NoAnswer is useful 3   Answer is not useful 0Overall Rating: +3    
May 05, 2008 06:19:21   #2  
nagarajkv Member Since: May 2008   Contribution: 2    

RE: PL/SQL Block output

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.


 
Is this answer useful? Yes | NoAnswer is useful 0   Answer is not useful 2Overall Rating: -2    
May 06, 2008 16:38:53   #3  
satyam_Ora Member Since: July 2007   Contribution: 29    

RE: PL/SQL Block output

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.


 
Is this answer useful? Yes | NoAnswer is useful 1   Answer is not useful 0Overall Rating: +1    
May 29, 2008 00:06:36   #4  
krishnaindia2007 Member Since: September 2007   Contribution: 854    

RE: PL/SQL Block output

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);


 
Is this answer useful? Yes | NoAnswer is useful 2   Answer is not useful 2Overall Rating: -N/A-    


 
Go To Top


 Sponsored Links

 
About Us -  Privacy Policy -  Terms and Conditions -  Contact -  Ask Question -  Propose Category -  Site Updates 

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape