Answer Posted / tvgiriprasad

If you want to UPDATE or DELETE record from table by using cursor which is defined from same table,"WHERE CURRENT OF" clause can be used. We have to create cursor with "FOR UPDATE" clause to use above clause.

The most recent record fetched from the table (by looping of cursor records) should be updated or deleted by using "WHERE CURRENT OF". When a cursor open with FOR UPDATE clause ,cursor's active set will have row level Exclusive Lock. Other sessions can query the table records, and not able to delete/update or SELECT with FOR UPDATE clause.


Example: Want to update value for GRADE column to '1' for the student who has Id = '1'.

Declare
cursor C1 is
select st_id, grade,st_last_name from student where st_id = 3;
for update of grade;
[ variable declaration ……] ;
begin
open C1;
fetch C1 into v_id,v_grade,v_name;
if C1%notfound then
dbms_output.put_line (‘No Record is found.’);
else
update student set grade=1 WHERE CURRENT OF;
COMMIT;
END IF;

CLOSE C1;
end;

How to experiment a data lock in oracle?

1039

---

what is reindexing?

1649

---

What are the different types of modules in oracle forms?

989

---

how to convert .db (extention) database file into .dmp (extention ) for oracle database ?

2541

---

What are the different types of synonyms?

1114

---

what is dynamic SGA and static SGA

4385

---

normally database take to refresh time 2 hours. but client asked iwant to refresh with in 5 min that same database. do you have any option in BO and Oracle? explain me briefly...kavi

2182

---

How to create a new oracle data file?

1019

---

What is a cursor and what are the steps need to be taken?

1184

---

What is the recommended interval at which to run statspack snapshots, and why?

2897

---

WHAT IS THE DEFINITION OF DEFAULT CUSTOMER IN AR?

2195

---

What is oracle analytical function?

1110

---

15. Display the item_cost and then truncate it to the nearest hundred, ten, unit, tenth and hundredth.

2281

---

What is an oracle transaction?

1113

---

What are the major difference between truncate and delete?

1035

---