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 increment dates by 1 in oracle?

1138

---

How to convert a date to char in oracle? Give one example.

1109

---

What is the difference between primary key and unique key and foreign key in oracle?

1036

---

What is a tns file?

1043

---

How to call a stored function with parameters?

1095

---

Write a trigger example in oracle?

1114

---

Explain a segment?

1131

---

How to convert numbers to characters in oracle?

1078

---

Explain about the analyze command in oracle?

1112

---

How to define and use table alias names in oracle?

990

---

Why does oracle 9i treat an empty string as null?

1129

---

How to Truncate Table in Oracle

1213

---

How to define a variable of a specific record type?

1116

---

What are the differences between primary key and unique key?

1061

---

Name the three major set of files on disk that compose a database in Oracle?

1170

---